我不得不在一个有很多基的集群中更改所有数据库、表和列的排序规则。

我使用了一个运行在php 8.1和mysql 8.0上的脚本

function changeCollate() {
    $databases = $this->fetchQueryToArray("SHOW DATABASES LIKE 'nova_%'")->rows;
    foreach ($databases as $value) {
        $db = $value['Database (nova_%)'];
        $this->LOG("-- banco de dados --- " . $db);
        $this->exeQuery("ALTER DATABASE `$db` COLLATE utf8mb4_0900_ai_ci;");
        $this->exeQuery("use $db");
        $tables = $this->fetchQueryToArray("SHOW tables")->rows;
        foreach ($tables as $table) {
            $tb_name = $table["Tables_in_$db"];
            $this->exeQuery("ALTER TABLE `$tb_name` COLLATE utf8mb4_0900_ai_ci;");
            $QUERY = "ALTER TABLE `$db`.`$tb_name`\n";
            $columns = $this->fetchQueryToArray("SHOW FULL COLUMNS FROM $tb_name WHERE Type LIKE 'varchar%' OR Type = 'text' OR Type like 'enum%' OR Type = 'longtext' OR Type = 'mediumtext'")->rows;
            foreach ($columns as $column) {
                $QUERY .= "CHANGE `{$column['Field']}` `{$column['Field']}` {$column['Type']} COLLATE 'utf8mb4_0900_ai_ci'";
                $QUERY .= ($column['Null'] == 'YES') ?  " NULL" : " NOT NULL";
                if ($column['Default']) $QUERY .= " DEFAULT '{$column['Default']}'";
                if ($column['Comment']) $QUERY .= " COMMENT '{$column['Comment']}'";
                $QUERY .= ",\n";
            }
            if ($QUERY == "ALTER TABLE `$db`.`$tb_name`\n") continue;
            $QUERY = substr($QUERY, 0, -2) . ";\n\n";
            $this->exeQuery($QUERY);
        }
    }
}

只需运行这个SQL语句就可以一次转换所有数据库表。根据需要更改COLLATION和databaseName。

SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," COLLATE utf8_general_ci;") AS    ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="databaseName"
AND TABLE_TYPE="BASE TABLE";

更好的变种生成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');

如果你运行phpMyAdmin >>选择数据库>>选择表>>到“操作”选项卡>>在“表选项”部分>>，你可以选择排序从下拉列表>>，一旦你按{go}在屏幕的顶部，你会看到一条消息:

您的SQL查询已成功执行

还有一个脚本

ALTER TABLE `tableName` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci 

但是它不会改变现有列的排序规则。 为此，您可以使用这个脚本(这个脚本也来自phpMyAdmin)

ALTER TABLE  `tableName` CHANGE  `Name`  `Name` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL

你需要单独转换每个表:

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 

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

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

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