Shulou(Shulou.com)05/31 Report--

This article mainly explains the "MySQL additional comments or a large number of modified comments", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "MySQL additional comments or a large number of modified notes" bar!

MySQL 5.6.14

The previous project was in a hurry, and the table statement developed had no comments.

Now we need to complete the comment information.

But it is troublesome to add comments in the later stage of MySQL.

You need to use the modify syntax.

As long as you accidentally make a mistake, it may lead to a change in the table structure, not a change in comments.

The experimental table is as follows:

Create table t (

C1 int primary key auto_increment

C2 char (20) not null default 'c2' comment' c2 comments'

C3 date default '2016-01-25' comment 'date Type Test'

C4 varchar (20) not null default''

C5 bigint

C6 text comment 'text Test'

C7 timestamp not null default on update not null default now ()

);

Through the following SQL, parsing metadata information, you can directly display the content of modify.

After appending or modifying comments, execute the statement.

In this way, human error can be avoided.

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'

Taking the experimental table as an example, the generated modify statement is as follows.

Alter table test.t modify column C1 int (11) not null auto_increment comment''

Alter table test.t modify column c2 char (20) not null default 'c2' comment' c2 comments'

Alter table test.t modify column c3 date default '2016-01-25' comment 'date Type Test'

Alter table test.t modify column c4 varchar (20) not null default''comment''

Alter table test.t modify column c5 bigint (20) comment''

Alter table test.t modify column c6 text comment 'text Test'

Alter table test.t modify column c7 timestamp not null default on update''

Alter table test.t modify column c8 datetime not null default''

Thank you for your reading, the above is the content of "MySQL additional comments or a large number of modified comments". After the study of this article, I believe you have a deeper understanding of the problem of MySQL additional comments or a large number of modified annotations, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.