ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
或者,如果您支持4字节UTF-8,请使用 utf8 而不是 utf8mb4 :
ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
71
Make a backup!
然后,您需要在数据库上设置默认字符集 . 这不会转换现有表,它只为新创建的表设置默认值 .
ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
593
在命令行shell上
如果您是命令行shell,那么您可以非常快速地执行此操作 . 只需填写“dbname”:D
DB="dbname"
(
echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'
mysql "$DB" -e "SHOW TABLES" --batch --skip-column-names \
| xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;'
) \
| mysql "$DB"
用于简单复制/粘贴的单行程
DB="dbname"; ( echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'; mysql "$DB" -e "SHOW TABLES" --batch --skip-column-names | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) | mysql "$DB"
65
您可以使用以下命令创建sql以更新所有表:
SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET utf8 COLLATE utf8_general_ci; ",
"ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ")
AS alter_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = your_database_name;
捕获输出并运行它 .
Arnold Daniels的回答更优雅 .
3
在继续之前,请确保您:已完成完整的数据库备份!
第1步:数据库级别更改
标识数据库的排序规则和字符集
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM
information_schema.SCHEMATA S
WHERE schema_name = 'your_database_name'
AND
(DEFAULT_CHARACTER_SET_NAME != 'utf8'
OR
DEFAULT_COLLATION_NAME not like 'utf8%');
修复数据库的排序规则
ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
第2步:表级更改
使用不正确的字符集或排序规则识别数据库表
SELECT CONCAT(
'ALTER TABLE ', table_name, ' CHARACTER SET utf8 COLLATE utf8_general_ci; ',
'ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ')
FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
WHERE C.collation_name = T.table_collation
AND T.table_schema = 'your_database_name'
AND
(C.CHARACTER_SET_NAME != 'utf8'
OR
C.COLLATION_NAME not like 'utf8%')
调整表列的排序规则和字符集
捕获上层sql输出并运行它 . (如下)
ALTER TABLE rma CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE rma_history CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_history CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE rma_products CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_products CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE rma_report_period CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_report_period CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE rma_reservation CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_reservation CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE rma_supplier_return CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_supplier_return CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE rma_supplier_return_history CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_supplier_return_history CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE rma_supplier_return_product CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_supplier_return_product CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
#!/bin/bash
printf "### Converting MySQL character set ###\n\n"
printf "Enter the encoding you want to set: "
read -r CHARSET
# Get the MySQL username
printf "Enter mysql username: "
read -r USERNAME
# Get the MySQL password
printf "Enter mysql password for user %s:" "$USERNAME"
read -rs PASSWORD
DBLIST=( mydatabase1 mydatabase2 )
printf "\n"
for DB in "${DBLIST[@]}"
do
(
echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE `'"$CHARSET"'`;'
mysql "$DB" -u"$USERNAME" -p"$PASSWORD" -e "SHOW TABLES" --batch --skip-column-names \
| xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE `'"$CHARSET"'`;'
) \
| mysql "$DB" -u"$USERNAME" -p"$PASSWORD"
echo "$DB database done..."
done
echo "### DONE ###"
exit
ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci;
4
DELIMITER $$
CREATE PROCEDURE `databasename`.`update_char_set`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE t_sql VARCHAR(256);
DECLARE tableName VARCHAR(128);
DECLARE lists CURSOR FOR SELECT table_name FROM `information_schema`.`TABLES` WHERE table_schema = 'databasename';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN lists;
FETCH lists INTO tableName;
REPEAT
SET @t_sql = CONCAT('ALTER TABLE ', tableName, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci');
PREPARE stmt FROM @t_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
FETCH lists INTO tableName;
UNTIL done END REPEAT;
CLOSE lists;
END$$
DELIMITER ;
CALL databasename.update_char_set();
117
最安全的方法是首先将列修改为二进制类型,然后使用所需的字符集将其修改回类型 .
每个列类型都有各自的二进制类型,如下所示:
CHAR => BINARY
TEXT => BLOB
TINYTEXT => TINYBLOB
MEDIUMTEXT => MEDIUMBLOB
LONGTEXT => LONGBLOB
VARCHAR => VARBINARY
例如 . :
ALTER TABLE [TABLE_SCHEMA].[TABLE_NAME] MODIFY [COLUMN_NAME] VARBINARY;
ALTER TABLE [TABLE_SCHEMA].[TABLE_NAME] MODIFY [COLUMN_NAME] VARCHAR(140) CHARACTER SET utf8mb4;
我尝试了几张latin1表,它保留了所有的变音符号 .
您可以为执行此操作的所有列提取此查询:
SELECT
CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.', TABLE_NAME,' MODIFY ', COLUMN_NAME,' VARBINARY;'),
CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.', TABLE_NAME,' MODIFY ', COLUMN_NAME,' ', COLUMN_TYPE,' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
FROM information_schema.columns
WHERE TABLE_SCHEMA IN ('[TABLE_SCHEMA]')
AND COLUMN_TYPE LIKE 'varchar%'
AND (COLLATION_NAME IS NOT NULL AND COLLATION_NAME NOT LIKE 'utf%');
完成所有这些后列然后你在所有表上执行:
ALTER TABLE [TABLE_SCHEMA].[TABLE_NAME] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
要为所有表生成此查询,请使用以下查询:
SELECT
CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_COLLATION NOT LIKE 'utf8%'
and TABLE_SCHEMA in ('[TABLE_SCHEMA]');
现在您修改了所有列和表,在数据库上执行相同操作:
ALTER DATABASE [DATA_BASE_NAME] CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;
17 回答
如果你不能让你的表转换或者你的表总是被设置为某个非utf8字符集,但你想要utf8,你最好的选择可能是把它擦除并重新开始并明确指定:
使用ALTER DATABASE和ALTER TABLE命令 .
或者,如果您支持4字节UTF-8,请使用
utf8
而不是utf8mb4
:Make a backup!
然后,您需要在数据库上设置默认字符集 . 这不会转换现有表,它只为新创建的表设置默认值 .
在命令行shell上
如果您是命令行shell,那么您可以非常快速地执行此操作 . 只需填写“dbname”:D
用于简单复制/粘贴的单行程
您可以使用以下命令创建sql以更新所有表:
捕获输出并运行它 .
Arnold Daniels的回答更优雅 .
在继续之前,请确保您:已完成完整的数据库备份!
第1步:数据库级别更改
第2步:表级更改
捕获上层sql输出并运行它 . (如下)
参考:https://confluence.atlassian.com/display/CONFKB/How+to+Fix+the+Collation+and+Character+Set+of+a+MySQL+Database
使用HeidiSQL . 它是免费的,非常好的数据库工具 .
从工具菜单中,输入批量表编辑器
选择完整的数据库或选择要转换的表格,
tick更改默认排序规则:utf8mb4_general_ci
tick转换为charset:utf8
执行
这可以在几秒钟内将完整的数据库从拉丁语转换为utf8 .
奇迹般有效 :)
HeidiSQL默认连接为utf8,因此任何特殊字符现在应该被视为字符(æøå),而不是在检查表数据时编码 .
从拉丁语转到utf8时真正的陷阱是确保pdo与utf8 charset连接 . 如果没有,你会将垃圾数据插入到utf8表中并在网页上的所有地方出现问号,让你认为表数据不是utf8 ...
受@sdfor评论的启发,这是一个完成这项工作的bash脚本
如果数据不在同一个字符集中,您可以考虑来自http://dev.mysql.com/doc/refman/5.0/en/charset-conversion.html的此片段
这是一个例子:
确保选择正确的排序规则,否则您可能会遇到唯一的关键冲突 . 例如在一些校对中,Éleanore和Eleanore可能被认为是相同的 .
在旁边:
我有一种情况,即某些字符在电子邮件中“破坏”,即使它们在数据库中存储为UTF-8 . 如果您使用utf8数据发送电子邮件,您可能还希望将电子邮件转换为以UTF8格式发送 .
在PHPMailer中,只需更新此行:
public $CharSet = 'utf-8';
对于具有大量表的数据库,您可以使用简单的PHP脚本使用以下内容更新数据库的charset和所有表:
唯一对我有用的解决方案:http://docs.moodle.org/23/en/Converting_your_MySQL_database_to_UTF8
转换包含表的数据库
alter table table_name charset ='utf8';
这是我能够用于我的案例的简单查询,您可以根据您的要求更改table_name .
要将数据库本身的字符集编码更改为UTF-8,请在mysql>提示符下键入以下命令 . 将DBNAME替换为数据库名称:
最安全的方法是首先将列修改为二进制类型,然后使用所需的字符集将其修改回类型 .
每个列类型都有各自的二进制类型,如下所示:
CHAR => BINARY
TEXT => BLOB
TINYTEXT => TINYBLOB
MEDIUMTEXT => MEDIUMBLOB
LONGTEXT => LONGBLOB
VARCHAR => VARBINARY
例如 . :
我尝试了几张latin1表,它保留了所有的变音符号 .
您可以为执行此操作的所有列提取此查询:
完成所有这些后列然后你在所有表上执行:
要为所有表生成此查询,请使用以下查询:
现在您修改了所有列和表,在数据库上执行相同操作:
您还可以使用数据库工具Navicat,它更容易实现 .
右键单击您的数据库,然后在下拉列表中根据需要选择数据库属性和更改