MySQL Append comment or a lot of changes comment 2016-01-25 20:28:05
Category: MySQL
MySQL 5.6.14
The previous project was rather hasty, with no comments on the built-in statements.
Now you want to complete the annotation information.
But MySQL late append comment is more troublesome
You need to use the Modify syntax.
As long as you accidentally write a wrong point, it can lead to a change in the table structure, not a comment.
The experimental table is as follows:
- Create table T (
- C1 int primary key auto_increment,
- C2 char (a) not null default ' C2 ' comment ' c2 comment ',
- C3 Date default ' 2016-01-25 ' comment ' date type test ',
- C4 varchar (+) not null default ' ',
- C5 bigint,
- C6 text comment ' text test ',
- C7 timestamp not null The default current_timestamp on update current_timestamp,
- C8 datetime not null default Now ()
- );
Through the following SQL, parsing meta-data information, you can directly display the contents of the Modify.
After you append or modify a comment, execute the statement.
This can avoid human error.
- SELECT
- Concat
- ' ALTER TABLE ',
- Table_schema, '. ', TABLE_NAME,
- ' Modify column ', column_name, ', Column_type, ',
- if (is_nullable = ' YES ', ' ', ' not null '),
- if (Column_default is NULL, ",
- If
- Data_type in (' char ', ' varchar ')
- OR
- Data_type in (' Date ', ' datetime ', ' timestamp ') and column_default! = ' Current_timestamp ',
- Concat (' default ', Column_default,'),
- Concat (' default ', Column_default)
- )
- ),
- if (extra is null or Extra=",", Concat (", extra)),
- ' comment ', column_comment, ' ; '
- ) s
- From Information_schema.columns
- WHERE table_schema = ' Test '
- and table_name = ' t '
For an example of an experimental table, the resulting modify statement is as follows.
- Alter table TEST.T modify column C1 int (one) not null auto_increment comment ';
- Alter table TEST.T modify column C2 char (a) not null default ' C2 ' comment ' C2 ' comment ';
- Alter table TEST.T modify column C3 Date default ' 2016-01-25 ' comment ' date type test ';
- Alter table TEST.T modify column C4 varchar (a) not null default ' comment ';
- Alter table TEST.T modify column C5 bigint (comment ") ;
- Alter table TEST.T modify column C6 text comment ' text test ';
- Alter table TEST.T modify column C7 timestamp not null default current_timestamp on update current_timestamp comment ";
- Alter table TEST.T modify column C8 datetime not null default current_timestamp comment ' ‘;
MySQL Append comment or bulk edit comment