将某个MySQL库中的UTF8字符列都转成GBK格式_MySQL

bitsCN.com

将某个MySQL库中的UTF8字符列都转成GBK格式

 

DELIMITER $$DROP PROCEDURE IF EXISTS `dba`.`Proc_ChangeCharacter2GBK`$$CREATE DEFINER=`root`@`%` PROCEDURE ` Proc_ChangeCharacter2GBK`(in DATABASENAME varchar(20))BEGIN    DECLARE done INT DEFAULT 0;    DECLARE a VARCHAR(64) DEFAULT '';    DECLARE b VARCHAR(64) DEFAULT '';    DECLARE c VARCHAR(64) DEFAULT '';    DECLARE d VARCHAR(64) DEFAULT '';    DECLARE l_sql VARCHAR(500);        DECLARE AlterColumnsCharacter CURSOR FOR            SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_TYPE            FROM information_schema.COLUMNS            WHERE TABLE_SCHEMA=DATABASENAME                 and TABLE_NAME in (  SELECT B.TABLE_NAME FROM information_schema.TABLES B                                                          WHERE B.TABLE_SCHEMA=DATABASENAME  AND B.TABLE_TYPE='BASE TABLE' )                  and COLUMN_TYPE like '%VARCHAR%' and CHARACTER_SET_NAME='utf8' and COLLATION_NAME='utf8_general_ci';    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;      OPEN AlterColumnsCharacter;      REPEAT  FETCH AlterColumnsCharacter INTO a,b,c,d;    if(done = 0) then       SET l_sql=CONCAT(' alter table ',a, '.',b,' change ',c,' ',c,' ',d,' character set gbk collate gbk_chinese_ci NULL; ');       SET @sql=l_sql;      PREPARE s1 FROM @sql;      EXECUTE s1;      DEALLOCATE PREPARE s1;    end if;           UNTIL done     END REPEAT;    CLOSE AlterColumnsCharacter;    END$$DELIMITER ;

 

bitsCN.com