Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

i know that:

mysql table comment can be defined while creating it:

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

and how to display it:

show table status where name='table_name';

and i know how to create and change column comment too.

my question is:

how to change (alter?) table comment (after it's been created, by not dropping and recreating it)?

share|improve this question
add comment

1 Answer

up vote 6 down vote accepted
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)
share|improve this answer
    
thank you for the detailed explanation, alter table to modify comments was exactly what i was looking for –  v14t Feb 25 at 10:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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