我很惊讶地发现，所以我不得不回到这里报告，优秀和维护良好的Interconnect/it SAFE SEARCH and REPLACE ON DATABASE脚本有一些选项可以将表转换为utf8 / unicode，甚至转换为innodb。它是一个脚本，通常用于将数据库驱动的网站(Wordpress, Drupal, Joomla等)从一个域迁移到另一个域。

https://github.com/interconnectit/Search-Replace-DB https://interconnectit.com/products/search-and-replace-for-wordpress-databases/

我的解决方案是@Dzintars和@Quassnoi Answer的组合。

SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 ;") AS    ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="<your-database>"
AND TABLE_TYPE="BASE TABLE";

通过使用CONVERT TO，这将生成一个脚本，它将<your-database>的所有表转换为所需的编码。这也改变了每一列的编码!

你可以运行一个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!";
                ?>

要更改表的排序规则，您可以使用，

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8

若要为整个数据库设置默认排序规则，请执行以下操作

ALTER DATABASE  `databasename` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin

否则,

Goto PhpMyAdmin - >操作- >排序。

在这里，您可以找到包含所有现有排序规则的选择框。这样你就可以改变排序规则。在创建新列时，数据库表将遵循这个排序规则。创建新列时不需要选择排序规则。

更好的变种生成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','pw');
if(!$con) { echo "Cannot connect to the database ";die();}
mysql_select_db('database_name');
$result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {

foreach ($tables as $key => $table) {                   // for each table

    $sql = "ALTER TABLE $table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci";
    echo "\n".$sql;
    mysql_query($sql);

    $sql = "show fields in ".$table." where type like 'varchar%' or type like 'char%' or type='text' or type='mediumtext';";
    $rs2=mysql_query($sql);
    while( $rw2 = mysql_fetch_array($rs2) ){            // for each field in table

        $sql = "ALTER TABLE `".$table."` CHANGE `".$rw2['Field']."` `".$rw2['Field']."` ".$rw2['Type']." CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;";
        echo "\n".$sql;
        mysql_query($sql);

    } 


}
}
echo "The collation of your database has been successfully changed!";

?>}