Mysql：设置列字符集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15781886/
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
Mysql: Set column charset
提问by Subway
I have an existing table and I want to convert the charset only for one specific column to utf-8.
我有一个现有表，我只想将一个特定列的字符集转换为 utf-8。
I know that this command ALTER TABLE table_name CONVERT TO CHARACTER SET utf8does it for the whole table but I'm looking for a column-specific command.
我知道此命令ALTER TABLE table_name CONVERT TO CHARACTER SET utf8对整个表执行此操作，但我正在寻找特定于列的命令。
Is there a command for that?
有命令吗？
回答by divyabharathi
Try this:
尝试这个：
ALTER TABLE t MODIFY col1 CHAR(50) CHARACTER SET utf8;
回答by Abdel
I share that, it can always help... I modified a database recently; moving from utf8 to utf8mb4; here is the script that allowed me to generate the alters...
我分享，它总是有帮助...我最近修改了一个数据库；从 utf8 移动到 utf8mb4；这是允许我生成更改的脚本...
Generate SQL commands to alter the tables:
生成 SQL 命令来更改表：
SELECT CONCAT("ALTER TABLE `",`TABLE_NAME`,"` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;")
FROM `information_schema`.`TABLES`
WHERE `TABLE_SCHEMA` = 'xxxx';
Generate SQL commands to alter each column:
生成 SQL 命令来改变每一列：
SELECT CONCAT("ALTER TABLE `",`TABLE_NAME`,"` MODIFY `",`COLUMN_NAME`,"` ",COLUMN_TYPE," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ",IF(`IS_NULLABLE`='YES', 'NULL', 'NOT NULL')," ",IF(`COLUMN_DEFAULT` IS NOT NULL, CONCAT(" DEFAULT '", `COLUMN_DEFAULT`, "'"), ''),";")
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'xxx' AND `TABLE_NAME` = 'xxxx' AND (`CHARACTER_SET_NAME` IS NOT NULL OR `COLLATION_NAME` IS NOT NULL);
Note that for foreign keys and primary keys that make a relationship, you will need to disable foregin key checks before modifying the column
请注意，对于建立关系的外键和主键，您需要在修改列之前禁用外键检查
SET FOREIGN_KEY_CHECKS=0;
and enable afterwards.
并在之后启用。
SET FOREIGN_KEY_CHECKS=1;
回答by Niroopchowdary
Below one worked for me.
下面一个对我有用。
ALTER TABLE table_name
MODIFY column_name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
回答by Rishab
This one worked for me.
这个对我有用。
ALTER TABLE `table_name` CHANGE `column_name` `column_name` TEXT CHARACTER SET utf8 COLLATE utf8mb4_unicode_ci;
