如何将整个MySQL数据库字符集转换为UTF-8和排序为UTF-8?
当前回答
你也可以使用数据库工具Navicat,这样做更容易。
湿 婆。
右键单击您的数据库,并在下拉菜单中选择数据库属性和更改
其他回答
你可以创建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)的回答更为优雅。
在命令行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"
要更改数据库本身的字符集编码为UTF-8,在mysql>提示符处键入以下命令。将DBNAME替换为数据库名称。
ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci;
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
Make a backup! Then you need to set the default char sets on the database. This does not convert existing tables, it only sets the default for newly created tables. ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci; Then, you will need to convert the char set on all existing tables and their columns. This assumes that your current data is actually in the current char set. If your columns are set to one char set but your data is really stored in another then you will need to check the MySQL manual on how to handle this. ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;