MySQL Append comment or bulk edit comment

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:

2. Create table T (
4. C1 int primary key auto_increment,
6. C2 char (a) not null default ' C2 ' comment ' c2 comment ',
8. C3 Date default ' 2016-01-25 ' comment ' date type test ',
10. C4 varchar (+) not null default ' ',
12. C5 bigint,
14. C6 text comment ' text test ',
16. C7 timestamp not null The default current_timestamp on update current_timestamp,
18. C8 datetime not null default Now ()
20. );

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.

2. SELECT
4. Concat
6. ' ALTER TABLE ',
8. Table_schema, '. ', TABLE_NAME,
10. ' Modify column ', column_name, ', Column_type, ',
12. if (is_nullable = ' YES ', ' ', ' not null '),
14. if (Column_default is NULL, ",
16. If
18. Data_type in (' char ', ' varchar ')
20. OR
22. Data_type in (' Date ', ' datetime ', ' timestamp ') and column_default! = ' Current_timestamp ',
24. Concat (' default ', Column_default,'),
26. Concat (' default ', Column_default)
28. )
30. ),
32. if (extra is null or Extra=",", Concat (", extra)),
34. ' comment ', column_comment, ' ; '
36. ) s
38. From Information_schema.columns
40. WHERE table_schema = ' Test '
42. and table_name = ' t '

For an example of an experimental table, the resulting modify statement is as follows.

2. Alter table TEST.T modify column C1 int (one) not null auto_increment comment ';
4. Alter table TEST.T modify column C2 char (a) not null default ' C2 ' comment ' C2 ' comment ';
6. Alter table TEST.T modify column C3 Date default ' 2016-01-25 ' comment ' date type test ';
8. Alter table TEST.T modify column C4 varchar (a) not null default ' comment ';
10. Alter table TEST.T modify column C5 bigint (comment ") ;
12. Alter table TEST.T modify column C6 text comment ' text test ';
14. Alter table TEST.T modify column C7 timestamp not null default current_timestamp on update current_timestamp comment ";
16. Alter table TEST.T modify column C8 datetime not null default current_timestamp comment '  ‘;

MySQL Append comment or bulk edit comment