我一直在查看MySQL的ALTER TABLE文档,但它似乎没有提供添加或修改列注释的方法。我该怎么做?
-- for table
ALTER TABLE myTable COMMENT 'Hello World'
-- for columns
-- ???
我一直在查看MySQL的ALTER TABLE文档,但它似乎没有提供添加或修改列注释的方法。我该怎么做?
-- for table
ALTER TABLE myTable COMMENT 'Hello World'
-- for columns
-- ???
尝试:
ALTER TABLE `user` CHANGE `id` `id` INT( 11 ) COMMENT 'id of user'
修改列
来完成此操作。只需...ALTER TABLE YourTable
MODIFY COLUMN your_column
your_previous_column_definition COMMENT "Your new comment"
替换:
YourTable
your_column
your_previous_column_definition
。我建议通过SHOW CREATE TABLE YourTable
命令获取并逐字复制,以避免任何陷阱。Your new comment
。例如...
mysql> CREATE TABLE `Example` (
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `some_col` varchar(255) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> );
Query OK, 0 rows affected (0.18 sec)
mysql> ALTER TABLE Example
-> MODIFY COLUMN `id`
-> int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Look, I''m a comment!';
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE Example;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Example | CREATE TABLE `Example` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Look, I''m a comment!',
`some_col` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
SELECT
table_name,
column_name,
CONCAT('ALTER TABLE `',
TABLE_SCHEMA,
'`.`',
table_name,
'` CHANGE `',
column_name,
'` `',
column_name,
'` ',
column_type,
' ',
IF(is_nullable = 'YES', '' , 'NOT NULL '),
IF(column_default IS NOT NULL, concat('DEFAULT ', IF(column_default IN ('CURRENT_TIMESTAMP', 'CURRENT_TIMESTAMP()', 'NULL', 'b\'0\'', 'b\'1\''), column_default, CONCAT('\'',column_default,'\'') ), ' '), ''),
IF(column_default IS NULL AND is_nullable = 'YES' AND column_key = '' AND column_type = 'timestamp','NULL ', ''),
IF(column_default IS NULL AND is_nullable = 'YES' AND column_key = '','DEFAULT NULL ', ''),
extra,
' COMMENT \'',
column_comment,
'\' ;') as script
FROM
information_schema.columns
WHERE
table_schema = 'my_database_name'
ORDER BY table_name , column_name
注意:如果您愿意,可以优化为仅一个表格
@Rufinus 提供的解决方案很好,但如果您有自动增量,它将会破坏它。
ALTER TABLE MODIFY id INT NOT NULL AUTO_INCREMENT COMMENT 'id of user';
。这不会破坏自增。 - Having a life on a beachhttp://cornempire.net/2010/04/15/add-comments-to-column-mysql/