更好的变种生成SQL脚本的SQL请求。它不会破坏默认值/空值。

SELECT concat
    (
        'ALTER TABLE ', 
            t1.TABLE_SCHEMA, 
            '.', 
            t1.table_name, 
            ' MODIFY ', 
            t1.column_name, 
            ' ', 
            t1.column_type,
            ' CHARACTER SET utf8 COLLATE utf8_general_ci',
            if(t1.is_nullable='YES', ' NULL', ' NOT NULL'),
            if(t1.column_default is not null, concat(' DEFAULT \'', t1.column_default, '\''), ''),
            ';'
    )
from 
    information_schema.columns t1
where 
    t1.TABLE_SCHEMA like 'your_table_here' AND
    t1.COLLATION_NAME IS NOT NULL AND
    t1.COLLATION_NAME NOT IN ('utf8_general_ci');

你可以运行一个php脚本。

               <?php
                   $con = mysql_connect('localhost','user','password');
                   if(!$con) { echo "Cannot connect to the database ";die();}
                   mysql_select_db('dbname');
                   $result=mysql_query('show tables');
                   while($tables = mysql_fetch_array($result)) {
                            foreach ($tables as $key => $value) {
                             mysql_query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
                       }}
                   echo "The collation of your database has been successfully changed!";
                ?>

您可以通过PHP脚本更改所有表的CHARSET和COLLATION，如下所示。我喜欢hkasera的答案，但它的问题是查询在每个表上运行两次。这段代码几乎是一样的，除了使用MySqli而不是mysql和防止双重查询。如果我可以投票的话，我会给hkasera的答案投票。

<?php
$conn1=new MySQLi("localhost","user","password","database");
if($conn1->connect_errno){
    echo mysqli_connect_error();
    exit;
}
$res=$conn1->query("show tables") or die($conn1->error);
while($tables=$res->fetch_array()){
    $conn1->query("ALTER TABLE $tables[0] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci") or die($conn1->error);
}
echo "The collation of your database has been successfully changed!";

$res->free();
$conn1->close();

?>

生成查询以更新每个表和每个表的列。 我以前在我的一些项目中使用过这种方法，并且能够解决我的大部分COLLATION问题。(特别是在join上)

要使用，只需将结果导出为带分隔符的文本(可能是新行'\n')

每个表

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, 
              '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') 
       AS 'USE `DATABASE_NAME`;' 
FROM   INFORMATION_SCHEMA.TABLES 
WHERE  TABLE_SCHEMA = 'DATABASE_NAME' 
       AND TABLE_TYPE LIKE 'BASE TABLE' 

每一列

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` MODIFY COLUMN `', COLUMN_NAME,'` ', 
              DATA_TYPE, IF(CHARACTER_MAXIMUM_LENGTH IS NULL 
       OR DATA_TYPE LIKE 'longtext', '', CONCAT('(', CHARACTER_MAXIMUM_LENGTH, 
                                         ')') 
       ), ' COLLATE utf8mb4_unicode_ci;') AS 'USE `DATABASE_NAME`;' 
FROM   INFORMATION_SCHEMA.COLUMNS 
WHERE  TABLE_SCHEMA = 'DATABASE_NAME' 
       AND (SELECT INFORMATION_SCHEMA.TABLES.TABLE_TYPE 
            FROM   INFORMATION_SCHEMA.TABLES 
            WHERE  INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = 
                   INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA 
                   AND INFORMATION_SCHEMA.TABLES.TABLE_NAME = 
                       INFORMATION_SCHEMA.COLUMNS.TABLE_NAME 
            LIMIT  1) LIKE 'BASE TABLE' 
       AND DATA_TYPE IN ( 'char', 'varchar' ) /* include other types if necessary */

你需要单独转换每个表:

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 

(这同样可以转换列)，或者用latin1导出数据库，然后用utf8mb4导入。

我刚刚编写了一个bash脚本来查找给定数据库中的所有表并隐藏它们(及其列)。

脚本下载地址:https://github.com/Juddling/mysql-charset