如何将 MySQL 表更改为 UTF-8？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2903318/
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
How do I change a MySQL table to UTF-8?
提问by TIMEX
I know there are many settings for a language for a table and a database.
我知道表和数据库的语言有很多设置。
I already created the database. I believe when I created it, it was default/LATIN. I want to change everything-I mean...both the table and the database, to UTF-8.
我已经创建了数据库。我相信当我创建它时，它是默认的/拉丁语。我想改变一切 - 我的意思是......表和数据库，到 UTF-8。
How can I do that? thanks.
我怎样才能做到这一点？谢谢。
回答by Developer-Sid
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8;
回答by aioobe
Have a look at Using alter command to change character set.
看看Using alter command to change character set。
Another useful link: http://dev.mysql.com/doc/refman/5.0/en/charset-table.html
另一个有用的链接：http: //dev.mysql.com/doc/refman/5.0/en/charset-table.html
The general form is
一般形式是
ALTER DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]
and for a specific column in a table
和表中的特定列
ALTER TABLE column COLLATE utf8_general_ci
回答by Philip Eve
aioobe's answer tells how to change the character set of a database, table or column. You should keep in mind that
aioobe 的回答告诉了如何更改数据库、表或列的字符集。你应该记住
setting the character set for a table just specifies the default character set for new columns in that table. It doesn't change the character set for preexisting columns; you have to do those columns individually, OR if you want to change every single string-type column in the table to the same character set there's a command you can use to do that: "alter table ... convert to character set" ( http://dev.mysql.com/doc/refman/5.1/en/alter-table.html)
if you already have data that is stored mis-encoded in a column, then using "alter table ... modify" to change the column will not quite solve the problem. For example, if you're been storing UTF-8 data in a Latin1 column and you change the character set directly from Latin1 to UTF-8, it'll still be mis-encoded afterwards. This can be worked around by converting from Latin-1 to UTF-8 via binary.
为表设置字符集只是为该表中的新列指定默认字符集。它不会更改预先存在的列的字符集；您必须单独执行这些列，或者如果您想将表中的每个字符串类型列更改为相同的字符集，您可以使用一个命令来执行此操作：“alter table ... convert to character set”（http://dev.mysql.com/doc/refman/5.1/en/alter-table.html)
如果您已经在列中存储了错误编码的数据，那么使用“alter table ... modify”来更改列并不能完全解决问题。例如，如果您将 UTF-8 数据存储在 Latin1 列中，并且您将字符集直接从 Latin1 更改为 UTF-8，之后它仍然会被错误编码。这可以通过二进制从 Latin-1 转换为 UTF-8 来解决。
回答by Roly
1) Database default character set and collation:
1) 数据库默认字符集和排序规则：
SELECT @@character_set_database, @@collation_database;
Altered via: ALTER DATABASE CHARACTER SET utf8 COLLATE utf8_general_ci;
通过以下方式更改： ALTER DATABASE CHARACTER SET utf8 COLLATE utf8_general_ci;
2) Table default character set and collation:
2）表默认字符集和排序规则：
SELECT T.table_name, CCSA.character_set_name
FROM information_schema.TABLES T, information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA
WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "YOUR_DB";`
Altered via: ALTER TABLE [table_name] CHARACTER SET utf8 COLLATE utf8_general_ci
通过以下方式更改： ALTER TABLE [table_name] CHARACTER SET utf8 COLLATE utf8_general_ci
3) Column character set and collation:
3）列字符集和排序规则：
SELECT c.TABLE_NAME, c.COLUMN_NAME, c.CHARACTER_SET_NAME, c.COLLATION_NAME
FROM information_schema.COLUMNS c
WHERE c.table_schema = "YOUR_DB";`
Altered via: ALTER TABLE [table_name] CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci
通过以下方式更改： ALTER TABLE [table_name] CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci
The third one requires that you disable foreign key checks for the data conversion. So putting this all together:
第三个要求您禁用数据转换的外键检查。所以把这一切放在一起：
DELIMITER //
CREATE PROCEDURE migrate_charset_to_utf8()
BEGIN
DECLARE done TINYINT DEFAULT 0;
DECLARE curr_table VARCHAR(64);
DECLARE table_cursor CURSOR FOR
SELECT T.table_name
FROM information_schema.TABLES T
WHERE T.TABLE_TYPE = 'BASE TABLE' AND
T.TABLE_SCHEMA = 'YOUR_DB';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN table_cursor;
table_loop: LOOP
FETCH table_cursor INTO curr_table;
IF done THEN
LEAVE table_loop;
END IF;
# Convert table data(columns) charset
SET @sql_str1 = CONCAT("ALTER TABLE ", curr_table, " CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
PREPARE stmt1 FROM @sql_str1;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
# Set table's default charset e.g for new columns added
SET @sql_str2 = CONCAT("ALTER TABLE ", curr_table, " CHARACTER SET utf8 COLLATE utf8_general_ci");
PREPARE stmt2 FROM @sql_str2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
END LOOP table_loop;
CLOSE table_cursor;
END//
DELIMITER ;
SET @@FOREIGN_KEY_CHECKS = 0;
CALL migrate_charset_to_utf8();
SET @@FOREIGN_KEY_CHECKS = 1;
ALTER DATABASE CHARACTER SET utf8 COLLATE utf8_general_ci;
EDIT: look hereinstead
编辑：看这里，而不是
回答by mosg
Add to your my.cnf this:
将以下内容添加到您的 my.cnf 中：
[mysqld]
character-set-server=utf8
default-collation=utf8_unicode_ci
And restart mysqld deamon.
并重新启动 mysqld 守护进程。
ADDED:
添加：
ALTER DATABASE your_base_name CHARACTER SET utf8 COLLATE utf8_unicode_ci;
and my.cnf
和我的.cnf
SET collation_connection = utf8_unicode_ci;
SET character_set_results = utf8;
SET character_set_connection = utf8;
SET character_set_client = utf8;
