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

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

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

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

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');

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

注意,在改变数据库的字符集/表/列,实际上你可能需要将现有的数据(例如,如果你看到类似“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..

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

你需要单独转换每个表:

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 

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

我的解决方案是@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>的所有表转换为所需的编码。这也改变了每一列的编码!