It is a very good idea, and often overlooked, to create a table comment when creating a table. Months later when you’ve forgotten what eggs_x_chickens was for, the comment will be extremely useful. It is especially useful if multiple people will be accessing the database, but you should go to the effort to do it even if the table is for your private consumption.
How to create a comment:
CREATE TABLE testing (
name VARCHAR(5)
) COMMENT='this is testing';
How to view the comment:
SHOW CREATE TABLE testing;
+---------+--------------------------------------------------------- | Table | CreateTable | +---------+--------------------------------------------------------- | testing | CREATE TABLE `testing` ( `name` varchar(5) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='this is testing' | +---------+---------------------------------------------------------
Even more often overlooked: if you’ve repurposed or redesigned a table an out of date comment should be updated, because an incorrect table comment is worse than none at all.
ALTER TABLE tablename COMMENT = 'new updated comment';
If you liked this post, check out Seven Deadly SQL Sins series at hubpages. Are you making any of these potentially career-ending mistakes with SQL?
I really need to get to grips with MYSQL as well as other stuff like PHP and html. The skills would definitely come in handy with my internet stuff, but all the computer language gives me nothing but a headache at times.
Phone Boys last blog post..Scientists Study Risks Of Long-Term Mobile Phone Use
as written this does not function….
Beg pardon Michael? whoever you are? It works fine. I am not in the habit of posting stuff that doesn’t work. You didn’t say what version you used or what the error message is, so I can only assume that you are either trying to cause trouble or you tried just cut/pasteing the last statement verbatim without understanding the post. It is quite clear from the context that tablename is meant to be replaced with the name of an existing table.
[...] while back I was lamenting that table comments in MySQL get overlooked, and did a post on it, both how to create them, how to update them, and how to view them. The fact that my table [...]
Thank you!
Here’s another way to view table comments:
SHOW TABLE STATUS [WHERE Name = '...']
This will output details about the tables, including a column for Comment.
http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html
awesome, there are often several ways to skin the same cat.
Thanks, works like a charm!
The ALTER TABLE seems to be ignored, at least by the “show create table” statement:
mysql> create table comment_test (id integer);
Query OK, 0 rows affected (0.08 sec)
mysql> show create table comment_test;
+--------------+-------------------------------------------------------------+
| Table | Create Table |
+--------------+-------------------------------------------------------------+
| comment_test | CREATE TABLE "comment_test" (
"id" int(11) DEFAULT NULL
) |
+--------------+-------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table comment_test comment = 'My Comment';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table comment_test;
+--------------+-------------------------------------------------------------+
| Table | Create Table |
+--------------+-------------------------------------------------------------+
| comment_test | CREATE TABLE "comment_test" (
"id" int(11) DEFAULT NULL
) |
+--------------+-------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
But it is displayed when using SHOW TABLE STATUS
Weird.
Tested on 5.1.32-community using Windows XP
There are better ways to do it now. This post was fine when it was written but is pretty outdated.
I don’t see any other possibility to add a comment through SQL.
So what would be the “better way now”?
I mean use show table status if you want to see it.