SQL statement for adding columns in MySQL table

Here is Mysql Add column SQL grammar .

1、 Add column

1.1、 Add a row ：

Add a column to an already built table ：

alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(20) not null ;

1.2、 Specify the insertion position ：

By default , The inserted column is at the end of the table , If you want to specify where to insert the column , as follows ：

alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(20) not null after COLUMN_NAME ;

1.3、 Insert into 1 Column ：

Above is inserted after a column , If you want to insert into the 1 Column , as follows ：

alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(20) not null first ;

2、 Example

Want to insert several identical fields into each table , such as Creation time 、 Update time 、 remarks , Its type and length are as follows ：

CREATE TABLE `contact` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ' Primary key ',
  `platform_id` varchar(50) DEFAULT NULL COMMENT ' platform ID',
  `code` varchar(10) DEFAULT NULL COMMENT ' Response identification .00 It means success ',
  `swift_number` varchar(50) DEFAULT NULL COMMENT ' Serial number ', 
  
  `create_time` datetime DEFAULT NULL COMMENT ' Creation time ',
  `update_time` datetime DEFAULT NULL COMMENT ' Update time ',
  `remark` varchar(100) DEFAULT NULL COMMENT ' remarks ',
  
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The operation is as follows ：

1） Copy above contact In the sentence Creation time 、 Update time 、 remarks Of Three elements , And add SQL sentence alter table TABLE_NAME add column , complete SQL The statement is as follows ：

alter table TABLE_NAME add column   `create_time` datetime DEFAULT NULL COMMENT ' Creation time ' ;
alter table TABLE_NAME add column   `update_time` datetime DEFAULT NULL COMMENT ' Update time ' ;
alter table TABLE_NAME add column   `remark` varchar(100) DEFAULT NULL COMMENT ' remarks ' ;

2） TABLE_NAME Modify to all table names .

alter table tb_user  add column   `create_time` datetime DEFAULT NULL COMMENT ' Creation time ' ;
alter table tb_user  add column   `update_time` datetime DEFAULT NULL COMMENT ' Update time ' ;
alter table tb_user  add column   `remark` varchar(100) DEFAULT NULL COMMENT ' remarks ' ;