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';

Check if this answers your question. https://stackoverflow.com/questions/2162420/alter-mysql-table-to-add-comments-on-columns — Jose Lora, Nov 19 '21 at 13:16
@JoseLora that's about adding comments to columns. This question is about changing comments on tables. — Lajos Arpad, Nov 19 '21 at 13:19

score 3 · Answer 1 · answered Nov 19 '21 at 13:23

3

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

answered Nov 19 '21 at 13:23

dogs Cute

564
3
9

Lajos Arpad · Answer 2 · 2021-11-19T14:19:19.830

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.

Thanks for answering.i'm new in mysql, so i wonder what's the difference between this way and `alter table t comment='f1';` — user15320797, Nov 19 '21 at 13:40
@user15320797 Please accept Dogs Cute's answer. It shows an alter command that actually changes the comment on a table. I tested it and it works. My initial answer assumed that the alter command does not support this feature and has shown an alternative approach by creating a copy of the table with the new comment, moving all the data and removing the original table. It uses a lot of storage space, memory and time if you have millions of records, so the simple alter command that Dogs Cute suggested should be preferred over the solution I originally proposed. I have edited my answer as well — Lajos Arpad, Nov 19 '21 at 14:21

score 0 · Answer 3 · edited Nov 19 '21 at 16:58

0

Check if this answers your question. stackoverflow

thanks. @JoseLora

edited Nov 19 '21 at 16:58

Poul Bak

10,450
5
32
57