EN
Mysql - create new column with index on existing database table with data
13 points
We can achieve it by executing below query.
xxxxxxxxxx1
ALTER TABLE `posts`2
ADD COLUMN `category_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '1';3
4
CREATE INDEX index_category_id ON posts ( category_id );Just change:
- database table - posts
- column name - category_id
- index name - index_category_id
xxxxxxxxxx1
CREATE DATABASE `post_tests`;2
USE `post_tests`;3
4
CREATE TABLE `posts` (5
`post_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,6
`post_content` LONGTEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',7
PRIMARY KEY (`post_id`)8
)9
COLLATE='utf8mb4_general_ci'10
ENGINE=InnoDB11
;12
13
INSERT INTO `posts` (`post_id`, `post_content`) VALUES (1, 'post 1');14
INSERT INTO `posts` (`post_id`, `post_content`) VALUES (2, 'post 2');15
INSERT INTO `posts` (`post_id`, `post_content`) VALUES (3, 'post 3');Screenshot 1:
Screenshot 2:
xxxxxxxxxx1
ALTER TABLE `posts`2
ADD COLUMN `category_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '1';3
4
CREATE INDEX index_category_id ON posts ( category_id );Our database after execution
Screenshot 3:
Screenshot 4:
