Skip to content

Instantly share code, notes, and snippets.

@mozhu1024

mozhu1024/change_database_table_columns_charset.sql

Created July 24, 2020 19:09
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save mozhu1024/dcf37c3fd23d18a49360cffe58ea352c to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save mozhu1024/dcf37c3fd23d18a49360cffe58ea352c to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. mozhu1024 created this gist Jul 24, 2020.
    42 changes: 42 additions & 0 deletions change_database_table_columns_charset.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,42 @@
    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`;