mysql - 如何使用 CREATE TABLE 和 SELECT 语句设置列的注释

Question

我想在MySQL中使用CREATE TABLE和SELECT语句设置列的注释(MySQL版本是5.1.69。)。
我尝试以下查询。但是未设置列的评论。

CREATE TABLE t (
  parent_id INT(10) NULL COMMENT 'test'
) ENGINE=INNODB 
SELECT 1 AS parent_id;

SHOW FULL COLUMNS FROM t;

+-----------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+
| Field     | Type    | Collation | Null | Key | Default | Extra | Privileges                      | Comment |
+-----------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+
| parent_id | int(10) | NULL      | YES  |     | NULL    |       | select,insert,update,references |         |
+-----------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)

如何使用 CREATE TABLE 和 SELECT 语句设置列的注释。

Answer 1

使用 CREATE TABLE .... SELECT 的自定义评论似乎是不可能的。

有关 CREATE TABLE ... SELECT 的文档只指定保留父注释。如果使用 create ... select 语法定义行内新评论，它没有提及任何行为，行为是什么。

Retrained attributes are NULL (or NOT NULL) and, for those columns that have them, CHARACTER SET, COLLATION, COMMENT, and the DEFAULT clause

以下观察表明，在新列上尝试的新自定义 comment 将被忽略。

如果你还想重新定义你自己的评论，你必须使用alter table 命令在新创建的表列上添加。

示例:

mysql> create table tbl_so_q23798048_1( i int not null comment 'parent comment' );
Query OK, 0 rows affected (0.41 sec)

mysql> select table_name, column_name, column_comment
    ->   from information_schema.columns
    ->  where table_schema='so' and length(column_comment)>0;
+--------------------+-------------+-----------------+
| table_name         | column_name | column_comment  |
+--------------------+-------------+-----------------+
| tbl_so_q23798048_1 | i           | parent comment  |
+--------------------+-------------+-----------------+
1 row in set (0.01 sec)

现在，尝试根据之前创建的表格创建一个表格，但使用自定义评论。

mysql> create table tbl_so_q23798048_2( i int comment 'custom comment' )
    ->     as select i from tbl_so_q23798048_1;
Query OK, 0 rows affected (0.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select table_name, column_name, column_comment
    ->   from information_schema.columns
    ->  where table_schema='so' and length(column_comment)>0;
+--------------------+-------------+-----------------+
| table_name         | column_name | column_comment  |
+--------------------+-------------+-----------------+
| tbl_so_q23798048_1 | i           | parent comment  |
| tbl_so_q23798048_2 | i           | parent comment  |
+--------------------+-------------+-----------------+
2 rows in set (0.01 sec)

你可以清楚地看到自定义评论被忽略了。

现在，尝试在新表的列上不使用自定义注释。

mysql> create table tbl_so_q23798048_3( i int )
    ->     as select i from tbl_so_q23798048_1;
Query OK, 0 rows affected (0.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select table_name, column_name, column_comment
    ->   from information_schema.columns
    ->  where table_schema='so' and length(column_comment)>0;
+--------------------+-------------+-----------------+
| table_name         | column_name | column_comment  |
+--------------------+-------------+-----------------+
| tbl_so_q23798048_1 | i           | parent comment  |
| tbl_so_q23798048_2 | i           | parent comment  |
| tbl_so_q23798048_3 | i           | parent comment  |
+--------------------+-------------+-----------------+
3 rows in set (0.01 sec)

可以看到父评论被保留了下来。现在，您可以更改新表格的列以添加自定义评论。

mysql> alter table tbl_so_q23798048_3
    ->       modify column i int comment 'custom comment';
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select table_name, column_name, column_comment
    ->   from information_schema.columns
    ->  where table_schema='so' and length(column_comment)>0;
+--------------------+-------------+-----------------+
| table_name         | column_name | column_comment  |
+--------------------+-------------+-----------------+
| tbl_so_q23798048_1 | i           | parent comment  |
| tbl_so_q23798048_2 | i           | parent comment  |
| tbl_so_q23798048_3 | i           | custom comment  |
+--------------------+-------------+-----------------+
3 rows in set (0.01 sec)

而且，您不能定义新的评论，尽管被选中的表列上没有评论。

mysql> create table tbl_so_q23798048_4( i int );
Query OK, 0 rows affected (0.68 sec)

mysql> create table tbl_so_q23798048_5( i int comment 'new comment' )
    -> as select i from tbl_so_q23798048_4;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select table_name, column_name, column_comment
    ->   from information_schema.columns
    ->  where table_schema='so' and length(column_comment)>0
    ->    and table_name in ( 'tbl_so_q23798048_4', 'tbl_so_q23798048_5' );
Empty set (0.01 sec)