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