mysql修改某个库里所有表的字符集，统一字段、表、库的字符集用,解决建表不规范导致的字符集不一致问题

-- 在对应的库创建并执行以下存储过程，可以将库中所有表的字符集转换为指定字符集，如果不指定字符集，则转换为库的默认字符集。
-- 1用法：CALL ResetAllTablesCharsetToDefault('mydb1', ''); 所有表和字段会转换为库的默认字符集
-- 2用法：CALL ResetAllTablesCharsetToDefault('mydb1', 'utf8mb4'); 所有表和字段会转换为utf8mb4字符集
-- 注意：如果更改库的字符集，新建表会使用新的字符集，但是已有表不会自动转换，需要手动转换。

CREATE
DEFINER=`root`@`%` PROCEDURE `ResetAllTablesCharsetToDefault`(IN dbName VARCHAR(64), IN charsetName VARCHAR(64))
BEGIN
    DECLARE
done INT DEFAULT FALSE;
    DECLARE
tableName VARCHAR(64);
    DECLARE
cur CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_schema = dbName;
DECLARE
CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    IF
charsetName IS NULL OR charsetName = '' THEN
        SET charsetName = (SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = dbName);
END IF;

OPEN cur;

read_loop
: LOOP
        FETCH cur INTO tableName;
        IF
done THEN
            LEAVE read_loop;
END IF;

        SET
@s = CONCAT('ALTER TABLE `', dbName, '`.`', tableName, '` CONVERT TO CHARACTER SET ', charsetName);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END LOOP;

CLOSE cur;
END