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

ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci;

要将字符集编码更改为UTF-8，请在PHPMyAdmin中执行简单步骤

选择数据库 进入操作区 在操作选项卡中，在底部排序规则下拉菜单中，选择您想要的编码，即(utf8_general_ci)，并勾选复选框(1)更改所有表排序规则，(2)更改所有表列排序规则。然后点击Go。

最安全的方法是先将列修改为二进制类型，然后使用所需的字符集将其修改回二进制类型。

每种列类型都有其各自的二进制类型，如下所示:

>二进制 文本=> BLOB 丁文字=> TINYBLOB 文本=> memblob LONGTEXT => LONGBLOB VARCHAR => VARBINARY

Eg.:

ALTER TABLE [TABLE_SCHEMA].[TABLE_NAME] MODIFY [COLUMN_NAME] VARBINARY;

ALTER TABLE [TABLE_SCHEMA].[TABLE_NAME] MODIFY [COLUMN_NAME] VARCHAR(140) CHARACTER SET utf8mb4;

我尝试了几个拉丁表，它保留了所有的变音符。

你可以为所有列提取这个查询:

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;

使用ALTER DATABASE和ALTER TABLE命令。

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

或者如果你仍然使用MySQL 5.5.2或更早的版本，不支持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;

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;

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

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