Mysql:how to change table comment after created already?

Question

How to do it with mysql-cli? table comment means for example foo below:

create table t (c1 int) comment='foo';

Answer 1

EDIT: As it turns out there is an alter statement that allows to change table comments. As Dogs Cute pointed out, this command actually works:

alter table t comment='f1';

Below we see a way one can change a table definition if there is no way to perform a change with alter. It was applied for comment change, but, as Dogs Cute has pointed out, it's unnecessary for this scenario, but it may be useful for some other scenarios.

As far as I know it is impossible to achieve that with an alter. Instead, I would do the following:

Step 1: Find out the definition of the table

Run

show create table t;

and save the result somewhere (clipboard or file, whatever)

Step 2: Create a similar table

Run

create table t_temp ...

(where ... is the continuation of the command, do not take that literally, see Step 1)

Step 3: Copy your data to the temp table

Run

insert into t_temp(...)
select ...
from t;

where ... represents the column list. Again, do not take this literally.

Step 4: Drop the current table

Run

drop table t;

Step 5: Rename the temp table

alter table t_temp rename t;

Final note

I strongly recommend that you back up your database regularly and specifically before you do such large changes to avoid irreparable data-loss.

Answer 2

you can use mysql> alter table t comment='f1';
mysql> commit;

Answer 3

Check if this answers your question. stackoverflow

thanks. @JoseLora