2

I want to set column's comment with CREATE TABLE and SELECT statement in MySQL(MySQL version is 5.1.69.).
I try the following query. However column's comment is not set.

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)

How do I set column's comment with CREATE TABLE and SELECT statement.

Improve this question
2
  • What does the SELECT statement have to do with your table creation? Why is it important / what significance does it have? Commented May 22, 2014 at 5:04
  • In fact the portion of the SELECT statement, it adds to insert the records that match the condition from other table. Commented May 22, 2014 at 8:42

2 Answers 2

Reset to default
3

Custom comment using CREATE TABLE .... SELECT seems not possible.

Documentation on CREATE TABLE ... SELECT does only specify that the parent comments are retained. It did not mention anything if an in line new comment is defined using create ... select syntax, what the behaviour is.

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

Following observations say that that the new custom comment being tried on new column is ignored.

And if you still want to re-define your own comment, you have to use alter table command to add on the newly created table column.

Example: 

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)

Now, try to create a table based on the previously created table but with custom comment. 

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)

You can clearly see that custom comment is ignored.

Now, try with no custom comment on the new table's column.

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)

You can see that parent comment is retained. Now, you can alter the new table's column to add custom comment.

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)

And, you can't define new comment though the table column being selected does not have a comment on it.

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you. I understood behaviour of the CREATE TABLE ... SELECT. I will redefine the column comment with ALTER TABLE.
0

Remove that SELECT 1 AS parent_id; from create table query. See a demo fiddle here http://sqlfiddle.com/#!2/cc46e1/1

your CREATE TABLE query should look like 

CREATE TABLE t ( parent_id INT(10) NULL COMMENT 'test' ) ENGINE=INNODB

EDIT:

Yes you can but if you are defining a new column in CREATE TABLE AS SELECT ... example as below. if you say this then comment will be same as original table

CREATE TABLE t1 as select parent_id from t;

But if you define a new column then you will get a new column comment. See a update fiddle here http://sqlfiddle.com/#!2/44567/1

CREATE TABLE t2(id int null comment 'NEW TEST') as select parent_id from t;

enter image description here

Improve this answer

2 Comments

Thank you. To set column's comment with CREATE TABLE ... SELECT statement is impossible?
@hiro, Yes you can. See edited answer. don't forget to accept the answer if it helped.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.