Can show the comments writing in the create table command?

Question

We can write comment to create a table:

create database `mytest`;
use `mytest`;
create table `mytest` (
/* mytest mytest */ 
  `code` varchar(15) NOT NULL,
  `type` varchar(20) NOT NULL
);

How can show the comment /* mytest mytest */ in create table command?

show create table mytest;
+--------+------------------------------------+
| Table  | Create Table                                                                                                                  |
+--------+------------------------------------+
| mytest | CREATE TABLE `mytest` (
  `code` varchar(15) NOT NULL,
  `type` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

score 4 · Accepted Answer · edited Aug 16 '21 at 19:21

This kind of comment is not stored in the table definition, so you can't get it with SQL.

A workaround is to do this :

CREATE TABLE mydb.tests (
    c1 INT
) COMMENT = 'Just a comment';
SHOW CREATE TABLE mydb.tests;

That gives :

CREATE TABLE `tests` (
  `c1` int DEFAULT NULL
) COMMENT='Just a comment' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Or better :

SELECT ALL table_comment 
FROM information_schema.tables
WHERE table_schema = 'mydb' AND table_name = 'tests';

Giving :

table_comment
Just a comment

You can also have column-level comments :

CREATE TABLE mydb.tests (
    c1 INT COMMENT 'c1''s comment',
    c2 INT COMMENT 'c2''s comment'
);
SELECT ALL column_name, column_comment
FROM information_schema.columns
WHERE table_schema = 'mydb' AND table_name = 'tests';

Gives :

column_name column_comment
c1          c1's comment
c2          c2's comment

Can show the comments writing in the create table command?

1 Answers1