35

I know that mysql table comment can be defined at the creation with:

create table (...)comment='table_comment';

And you can display comments by:

show table status where name='table_name';

How do you change (alter?) table comment after it's been created. I mean wihtout dropping and recreating the table again.

38
DROP TABLE IF EXISTS test_comments;
Query OK, 0 rows affected (0.08 sec)

CREATE TABLE test_comments (ID INT, name CHAR(30)) COMMENT 'Hello World';
Query OK, 0 rows affected (0.22 sec)

Check you comments in table structure

show create table test_comments\G
*************************** 1. row ***************************
       Table: test_comments
Create Table: CREATE TABLE `test_comments` (
  `ID` int(11) DEFAULT NULL,
  `name` char(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Hello World'
1 row in set (0.00 sec)

You can also check comments from information_schema like below

SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_NAME = 'test_comments';
+---------------+
| TABLE_COMMENT |
+---------------+
| Hello World   |
+---------------+
1 row in set (0.00 sec)

Alter table to modify comments

ALTER TABLE test_comments COMMENT = 'This is just to test how to alter comments';
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

Check the modified comments

show create table test_comments\G
*************************** 1. row ***************************
       Table: test_comments
Create Table: CREATE TABLE `test_comments` (
  `ID` int(11) DEFAULT NULL,
  `name` char(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='This is just to test how to alter comments'
1 row in set (0.00 sec)

SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_NAME = 'test_comments';
+--------------------------------------------+
| TABLE_COMMENT                              |
+--------------------------------------------+
| This is just to test how to alter comments |
+--------------------------------------------+
1 row in set (0.00 sec)
  • 1
    thank you for the detailed explanation, alter table to modify comments was exactly what i was looking for – v14t Feb 25 '14 at 10:22
  • Bonus question: would it be safe to directly modify column_comment from information_schema.columns (since the alter table ... requires to specify all the column definition, again) ? – Ring Ø Nov 6 '17 at 8:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.