如何将整个MySQL数据库字符集转换为UTF-8和排序为UTF-8?
当前回答
如果你不能让你的表转换,或者你的表总是设置为一些非utf8字符集,但你想要utf8,你最好的办法可能是擦掉它,重新开始,并显式地指定:
create database database_name character set utf8;
其他回答
你可以创建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)的回答更为优雅。
mysqldump -uusername -ppassword -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B dbname > dump.sql
cp dump.sql dump-fixed.sql
vim dump-fixed.sql
:%s/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/
:%s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/
:wq
mysql -uusername -ppassword < dump-fixed.sql
如果数据不在相同的字符集中,您可以考虑http://dev.mysql.com/doc/refman/5.0/en/charset-conversion.html中的这个片段
如果列具有非二进制数据类型(CHAR、VARCHAR、TEXT),则其 内容应该在列字符集中编码,而不是其他字符集 字符集。如果内容用不同的字符编码 设置后,您可以先将列转换为使用二进制数据类型,然后 然后转换到具有所需字符集的非二进制列。
这里有一个例子:
ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 VARCHAR(100) CHARACTER SET utf8;
确保选择正确的排序规则,否则可能会得到唯一的键冲突。如。 Éleanore和Eleanore在某些排序中可能被认为是相同的。
旁白:
我曾遇到过这样的情况,电子邮件中的某些字符“坏了”,尽管它们在数据库中以UTF-8格式存储。如果您使用utf8数据发送电子邮件,您可能还需要将电子邮件转换为utf8发送。
在phpailer中,只需更新这一行:public $CharSet = 'utf-8';
命令行解决方案和排除视图
我只是在为像我和布莱恩这样在我们的数据库中有视图的人完成@Jasny的回答。
如果你有这样的错误:
ERROR 1347 (HY000) at line 17: 'dbname.table_name' is not of type 'BASE TABLE'
这是因为你可能有自己的观点,你需要排除它们。 但是当试图排除它们时,MySQL返回2列而不是1列。
SHOW FULL TABLES WHERE Table_Type = 'BASE TABLE';
-- table_name1 BASE TABLE
-- table_name2 BASE TABLE
因此,我们必须用awk修改Jasny的命令,只提取包含表名的第一列。
DB="dbname"
(
echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'
mysql "$DB" -e "SHOW FULL TABLES WHERE Table_Type = 'BASE TABLE'" --batch --skip-column-names \
| awk '{print $1 }' \
| 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 FULL TABLES WHERE Table_Type = 'BASE TABLE'" --batch --skip-column-names | awk '{print $1 }' | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) | mysql "$DB"
受@sdfor注释的启发,这里有一个bash脚本可以完成这项工作
#!/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