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

受@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

Alter table table_name charset = 'utf8';

这是一个简单的查询，我能够使用我的情况下，你可以改变table_name根据您的需求(s)。

在继续之前，请确保您:已完成完全数据库备份!

步骤1:数据库级别更改

标识数据库的排序规则和字符集 选择default_character_set_name, default_collation_name from information_schema。图式年代 WHERE schema_name = 'your_database_name' 和 (DEFAULT_CHARACTER_SET_NAME != 'utf8' 或 DEFAULT_COLLATION_NAME不像'utf8%'); 修复数据库的排序规则 ALTER DATABASE DATABASE utf8 COLLATE utf8_unicode_ci;

步骤2:表级别更改

Identifying Database Tables with the incorrect character set or collation 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%') Adjusting table columns' collation and character set

捕获上层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; 

参考网址:https://confluence.atlassian.com/display/CONFKB/How+to+Fix+the+Collation+and+Character+Set+of+a+MySQL+Database

对我来说唯一有效的解决方案是:http://docs.moodle.org/23/en/Converting_your_MySQL_database_to_UTF8

转换包含表的数据库

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;