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

步骤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

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

在命令行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"

Alter table table_name charset = 'utf8';

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

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;

要更改数据库本身的字符集编码为UTF-8，在mysql>提示符处键入以下命令。将DBNAME替换为数据库名称。

ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci;