更改 MySQL 表以在列上添加注释
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2162420/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Alter MySQL table to add comments on columns
提问by Jhonny D. Cano -Leftware-
I have been checking the MySQL Documentation for ALTER TABLEand it does not seem to include a way to add or modify a comment to a column. How can I do this?
我一直在检查ALTER TABLE的MySQL 文档，它似乎没有包含向列添加或修改注释的方法。我怎样才能做到这一点？
-- for table
ALTER TABLE myTable COMMENT 'Hello World'
-- for columns
-- ???
回答by Rufinus
try:
尝试：
ALTER TABLE `user` CHANGE `id` `id` INT( 11 ) COMMENT 'id of user'
回答by Mark Amery
You can use MODIFY COLUMNto do this. Just do...
您可以使用它MODIFY COLUMN来执行此操作。做就是了...
ALTER TABLE YourTable
MODIFY COLUMN your_column
your_previous_column_definition COMMENT "Your new comment"
substituting:
替代：
YourTablewith the name of your tableyour_columnwith the name of your commentyour_previous_column_definitionwith the column's column_definition, which I recommend getting via aSHOW CREATE TABLE YourTablecommand and copying verbatim to avoid any traps.*Your new commentwith the column comment you want.
YourTable与您的表的名称your_column以您的评论名称your_previous_column_definition使用列的column_definition，我建议通过SHOW CREATE TABLE YourTable命令获取并逐字复制以避免任何陷阱。*Your new comment带有您想要的列注释。
For example...
例如...
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)
* Whenever you use MODIFYor CHANGEclauses in an ALTER TABLEstatement, I suggest you copy the column definition from the output of a SHOW CREATE TABLEstatement. This protects you from accidentally losing an important part of your column definition by not realising that you need to include it in your MODIFYor CHANGEclause. For example, if you MODIFYan AUTO_INCREMENTcolumn, you need to explicitly specify the AUTO_INCREMENTmodifier again in the MODIFYclause, or the column will cease to be an AUTO_INCREMENTcolumn. Similarly, if the column is defined as NOT NULLor has a DEFAULTvalue, these details need to be included when doing a MODIFYor CHANGEon the column or they will be lost.
* 每当您在语句中使用MODIFYorCHANGE子句时ALTER TABLE，我建议您从SHOW CREATE TABLE语句的输出中复制列定义。这可以防止您不小心丢失列定义的重要部分，因为您没有意识到您需要将其包含在您的MODIFYorCHANGE子句中。例如，如果您MODIFY是一AUTO_INCREMENT列，则需要AUTO_INCREMENT在MODIFY子句中再次明确指定修饰符，否则该列将不再是一AUTO_INCREMENT列。同样，如果该列被定义为NOT NULL或具有DEFAULT值，则在对列执行 aMODIFY或时需要包含这些详细信息，CHANGE否则它们将丢失。
回答by workdreamer
Script for all fields on database:
数据库中所有字段的脚本：
SELECT
table_name,
column_name,
CONCAT('ALTER TABLE `',
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 = 'CURRENT_TIMESTAMP', 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
- Export all to a CSV
- Open it on your favorite csv editor
- 全部导出为 CSV
- 在您最喜欢的 csv 编辑器上打开它
Note: You can improve to only one table if you prefer
注意：如果您愿意，可以改进为只有一张桌子
The solution given by @Rufinus is great but if you have auto increments it will break it.
@Rufinus 给出的解决方案很好，但如果你有自动增量，它会破坏它。
回答by mpoletto
The information schema isn't the place to treat these things (see DDL database commands).
信息模式不是处理这些事情的地方（请参阅 DDL 数据库命令）。
When you add a comment you need to change the table structure (table comments).
添加注释时，您需要更改表结构（表注释）。
From MySQL 5.6 documentation:
来自 MySQL 5.6 文档：
INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases that the MySQL server maintains. The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name.
Although you can select INFORMATION_SCHEMA as the default database with a USE statement, you can only read the contents of tables, not perform INSERT, UPDATE, or DELETE operations on them.
INFORMATION_SCHEMA 是每个 MySQL 实例中的一个数据库，该位置存储有关 MySQL 服务器维护的所有其他数据库的信息。INFORMATION_SCHEMA 数据库包含几个只读表。它们实际上是视图，而不是基表，因此没有与它们关联的文件，并且您不能对它们设置触发器。此外，没有具有该名称的数据库目录。
尽管您可以使用 USE 语句选择 INFORMATION_SCHEMA 作为默认数据库，但您只能读取表的内容，而不能对其执行 INSERT、UPDATE 或 DELETE 操作。
回答by Nageswara Rao
As per the documentation you can add comments only at the time of creating table. So it is must to have table definition. One way to automate it using the script to read the definition and update your comments.
根据文档，您只能在创建表时添加注释。所以必须要有表定义。使用脚本自动读取定义并更新注释的一种方法。
Reference:
参考：
http://cornempire.net/2010/04/15/add-comments-to-column-mysql/
http://cornempire.net/2010/04/15/add-comments-to-column-mysql/
