更改 MySQL 表以在列上添加注释

发布于 2024-08-19 18:40:19 字数 252 浏览 3 评论 0原文

我一直在检查 MySQL ALTER TABLE 文档和它似乎不包括向列添加或修改评论的方法。我该怎么做？

-- for table
ALTER TABLE myTable COMMENT 'Hello World'

-- for columns
-- ???

原文

I have been checking the MySQL Documentation for ALTER TABLE and it does not seem to include a way to add or modify a comment to a column. How can I do this?

-- for table
ALTER TABLE myTable COMMENT 'Hello World'

-- for columns
-- ???

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

层林尽染 2024-08-26 18:40:19

尝试：

 ALTER TABLE `user` CHANGE `id` `id` INT( 11 ) COMMENT 'id of user'

try:

 ALTER TABLE `user` CHANGE `id` `id` INT( 11 ) COMMENT 'id of user'

回复收藏 0 原文

只为守护你 2024-08-26 18:40:19

您可以使用 MODIFY COLUMN 来执行此操作。只需...

ALTER TABLE YourTable
MODIFY COLUMN your_column
your_previous_column_definition COMMENT "Your new comment"

替换：

YourTable 为您的表格名称
your_column 为您的评论名称
< em>your_previous_column_definition 与列的 column_definition，我建议通过 SHOW CREATE TABLE YourTable 命令获取并逐字复制以避免任何陷阱。*
您的新评论 加上您想要的专栏评论。

例如...

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)

* 每当您在 ALTER TABLE 语句中使用 MODIFY 或 CHANGE 子句时，我建议您从输出中复制列定义SHOW CREATE TABLE 语句的。这可以防止您因为没有意识到需要将列定义的重要部分包含在 MODIFY 或 CHANGE 子句中而意外丢失它。例如，如果您 MODIFY 一个 AUTO_INCRMENT 列，则需要在 MODIFY 中再次显式指定 AUTO_INCRMENT 修饰符子句，否则该列将不再是 AUTO_INCRMENT 列。同样，如果列定义为 NOT NULL 或具有 DEFAULT 值，则在执行 MODIFY 或 时需要包含这些详细信息>更改列，否则它们将丢失。

You can use MODIFY COLUMN to do this. Just do...

ALTER TABLE YourTable
MODIFY COLUMN your_column
your_previous_column_definition COMMENT "Your new comment"

substituting:

YourTable with the name of your table
your_column with the name of your comment
your_previous_column_definition with the column's column_definition, which I recommend getting via a SHOW CREATE TABLE YourTable command and copying verbatim to avoid any traps.*
Your new comment with the column comment you want.

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 MODIFY or CHANGE clauses in an ALTER TABLE statement, I suggest you copy the column definition from the output of a SHOW CREATE TABLE statement. This protects you from accidentally losing an important part of your column definition by not realising that you need to include it in your MODIFY or CHANGE clause. For example, if you MODIFY an AUTO_INCREMENT column, you need to explicitly specify the AUTO_INCREMENT modifier again in the MODIFY clause, or the column will cease to be an AUTO_INCREMENT column. Similarly, if the column is defined as NOT NULL or has a DEFAULT value, these details need to be included when doing a MODIFY or CHANGE on the column or they will be lost.

回复收藏 0 原文

笑饮青盏花 2024-08-26 18:40:19

数据库上所有字段的脚本：

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

全部导出到 CSV
在您最喜欢的 csv 编辑器上打开它

注意：如果您愿意，您可以改进为只有一张表

@Rufinus 给出的解决方案很棒，但如果您有自动递增会破坏它。

Script for all fields on database:

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

Export all to a CSV
Open it on your favorite csv editor

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.

回复收藏 0 原文

拒绝两难 2024-08-26 18:40:19

信息模式不是处理这些事情的地方（请参阅 DDL 数据库命令）。

添加注释时，您需要更改表结构（表注释）。

来自 MySQL 5.6 文档：

INFORMATION_SCHEMA 是每个 MySQL 实例中的一个数据库，位于
它存储有关 MySQL 的所有其他数据库的信息
服务器维护。 INFORMATION_SCHEMA 数据库包含几个
只读表。它们实际上是视图，而不是基表，所以有
没有与它们关联的文件，并且您无法设置触发器
他们。此外，没有具有该名称的数据库目录。
尽管您可以选择 INFORMATION_SCHEMA 作为默认数据库
使用USE语句，只能读取表的内容，而不能读取
对它们执行 INSERT、UPDATE 或 DELETE 操作。

第 21 章 INFORMATION_SCHEMA 表