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

ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci;

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

ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci;

如果数据不在相同的字符集中，您可以考虑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';

对于有大量表的数据库，你可以使用一个简单的php脚本来更新数据库和所有表的字符集，使用以下方法:

$conn = mysqli_connect($host, $username, $password, $database);

if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$alter_database_charset_sql = "ALTER DATABASE ".$database." CHARACTER SET utf8 COLLATE utf8_unicode_ci";
mysqli_query($conn, $alter_database_charset_sql);

$show_tables_result = mysqli_query($conn, "SHOW TABLES");
$tables  = mysqli_fetch_all($show_tables_result);

foreach ($tables as $index => $table) {
  $alter_table_sql = "ALTER TABLE ".$table[0]." CONVERT TO CHARACTER SET utf8  COLLATE utf8_unicode_ci";
  $alter_table_result = mysqli_query($conn, $alter_table_sql);
  echo "<pre>";
  var_dump($alter_table_result);
  echo "</pre>";
}

你可以创建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)的回答更为优雅。

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

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