修改数据库中所有表中所有字段的排序规则。

我只是通过前面提到的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!";

?>}

我在这里读到，你需要手动转换每个表，这是不正确的。下面是一个如何使用存储过程的解决方案:

DELIMITER $$

DROP PROCEDURE IF EXISTS changeCollation$$

-- character_set parameter could be 'utf8'
-- or 'latin1' or any other valid character set
CREATE PROCEDURE changeCollation(IN character_set VARCHAR(255))
BEGIN
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_table_name varchar(255) DEFAULT "";
DECLARE v_message varchar(4000) DEFAULT "No records";

-- This will create a cursor that selects each table,
-- where the character set is not the one
-- that is defined in the parameter

DECLARE alter_cursor CURSOR FOR SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
AND COLLATION_NAME NOT LIKE CONCAT(character_set, '_%');

-- This handler will set the value v_finished to 1
-- if there are no more rows

DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;

OPEN alter_cursor;

-- Start a loop to fetch each rows from the cursor
get_table: LOOP

-- Fetch the table names one by one
FETCH alter_cursor INTO v_table_name;

-- If there is no more record, then we have to skip
-- the commands inside the loop
IF v_finished = 1 THEN
LEAVE get_table;
END IF;

IF v_table_name != '' THEN

IF v_message = 'No records' THEN
SET v_message = '';
END IF;

-- This technic makes the trick, it prepares a statement
-- that is based on the v_table_name parameter and it means
-- that this one is different by each iteration inside the loop

SET @s = CONCAT('ALTER TABLE ',v_table_name,
' CONVERT TO CHARACTER SET ', character_set);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET v_message = CONCAT('The table ', v_table_name ,
' was changed to the default collation of ', character_set,
'.\n', v_message);

SET v_table_name = '';

END IF;
-- Close the loop and the cursor
END LOOP get_table;
CLOSE alter_cursor;

-- Returns information about the altered tables or 'No records'
SELECT v_message;

END $$

DELIMITER ;

创建过程后，可以简单地调用它:

CALL changeCollation('utf8');

更多细节请阅读这篇博客。

我很惊讶地发现，所以我不得不回到这里报告，优秀和维护良好的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/

下面的查询将生成ALTER查询，将所有表中所有适当列的排序规则更改为某种类型(在下面的示例中为utf8_general_ci)。

SELECT concat
        (
            'ALTER TABLE ', 
                t1.TABLE_SCHEMA, 
                '.', 
                t1.table_name, 
                ' MODIFY ', 
                t1.column_name, 
                ' ', 
                t1.data_type, 
                '(' , 
                    CHARACTER_MAXIMUM_LENGTH, 
                ')', 
                ' CHARACTER SET utf8 COLLATE utf8_general_ci;'
        )
from 
    information_schema.columns t1
where 
    t1.TABLE_SCHEMA like 'you_db_name_goes_here' AND
    t1.COLLATION_NAME IS NOT NULL AND
    t1.COLLATION_NAME NOT IN ('utf8_general_ci');

注意,在改变数据库的字符集/表/列,实际上你可能需要将现有的数据(例如,如果你看到类似“U…Ø·U”ˆØ¨ØªUˆØ±UŠØ¯Ø¬U”)是这样的:

update country set name = convert(cast(convert(name using latin1) as binary) using utf8), cn_flag = convert(cast(convert(cn_flag using latin1) as binary) using utf8), and so on..

对于转换数据库、表和字段，我建议从这个线程中得到这个答案，它会生成一组大的查询，你只需要在粘贴时复制，在这里我还找不到一个自动的解决方案。 还要注意的是，如果你将同一个字段转换两次，你将得到不可恢复的问号:"?? "。如果在转换字段/表之前转换数据，也会得到这个问号。

如果你运行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