[修改MySQL字符集] 修改MySQL数据库表字段字符集 #mysql #mariadb #sql #charset

change_database_table_columns_charset.sql

DROP PROCEDURE IF EXISTS `chanageCharSet`;
CREATE PROCEDURE `chanageCharSet`() 
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE scheamName VARCHAR(100);
    DECLARE tableName VARCHAR(100);
    DECLARE columnName VARCHAR(100);
    DECLARE columnType VARCHAR(100);
    DECLARE alertSql VARCHAR(200);
    DECLARE _Cur CURSOR FOR (
            SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE 
            FROM `information_schema`.`COLUMNS` 
            WHERE (DATA_TYPE='varchar' or DATA_TYPE like '%text%') AND COLLATION_NAME <> 'utf8mb4_unicode_ci' AND TABLE_SCHEMA ='数据库'
    );
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN _Cur;
    REPEAT  
        FETCH _Cur INTO scheamName, tableName, columnName, columnType;  
            IF NOT done THEN 
                SET alertSql = CONCAT(
                    'ALTER TABLE `',
                    scheamName,
                    '`.`',
                    tableName,
                    '` MODIFY COLUMN `',
                    columnName,
                    '` ',
                    columnType,
                    ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
                );        
                SET @ESQL = alertSql;
                PREPARE stmt1 FROM @ESQL;
                EXECUTE stmt1;
                DEALLOCATE PREPARE stmt1;
            END IF;
    UNTIL done END REPEAT;
    CLOSE _Cur;
END;

CALL chanageCharSet();
DROP PROCEDURE IF EXISTS `chanageCharSet`;