Top community members
All Wiki Articles Create Wiki Article

Many years me and my friends we were volunteers on my university doing programming classes for young people.

I got main conclusion: Young people are awesome, especially students, because they always bring new and fresh ideas :)
* I loved to do it but my time is limited

  I decided to create something that will help millions of students

Now I am here

co-founder

Mysql - create new column with index on existing database table with data

0 contributions
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 from Heidi SQL after execution above query - database schema
Screenshot from Heidi SQL after execution above query - database schema

Screenshot 2:

Screenshot from Heidi SQL after execution above query - inserted data
Screenshot from Heidi SQL after execution above query - inserted data

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:

database schema with new column and index
database schema with new column and index

Screenshot 4:

database data with new column and index and default value 1
database data with new column and index and default value 1
0 contributions

Checkout latest Findings & News:

Checkout latest questions:

Checkout latest wiki articles:

Hey 👋
Would you like to know what we do?
  • Dirask is IT community, where we share coding knowledge and help each other to solve coding problems.
  • We welcome everyone,
    no matter what the experience,
    no matter how basic the question is,
    this community will help you.
Read more