mysql升级数据库后批量修改表为utf8mb4模式

mysql升级数据库版本后，以前的utf8mb3存储的表查询会报错，需要修改为utf8mb4模式

问题

mysql升级数据库版本后，以前的utf8mb3存储的表查询会报错，需要修改为utf8mb4模式

解决

1、修改说明

检测数据库中编码不是utf8mb4、排序规则不是utf8mb4_general_ci的表，将原表重命名为bak_表名。

重新创建相同的名，然后将bak表数据复制到新表。

2、创建存储过程

  
drop procedure if exists pr_resettabcharacter;

-- delimiter $$ -- 根据需要加入
-- 优化处理无主键报错
create procedure pr_resettabcharacter(in dbName varchar(100))
begin
	declare v_tableName varchar(100);
	DECLARE v_sql,v_renameTb,v_copysql,v_incCol VARCHAR(8000);
    declare done int default false;
	declare v_incVal bigint;
    declare v_cnt int;
    -- 声名游标循环
	DECLARE cur_tables CURSOR FOR
    SELECT table_name from information_schema.tables a where table_schema=dbName and (
	    TABLE_COLLATION<>'utf8mb4_general_ci' or
	    exists(select * from information_schema.columns where
	    a.table_name=table_name and a.table_schema=table_schema
	    and data_type rlike 'varchar%'
	    and (ifnull(CHARACTER_SET_NAME,'')<>'utf8mb4' or ifnull(COLLATION_NAME,'')<>'utf8mb4_general_ci'))
    )
    -- 处理后的表以bak_开始
   and table_name not like 'bak\_%'
   and not exists(select 1 from information_schema.tables where a.TABLE_SCHEMA=TABLE_SCHEMA and TABLE_NAME=concat('bak_',a.table_name))
   order by table_name;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=true;

   SET SESSION group_concat_max_len = 1024000;
   -- 判断是否创建记录变更表
   select count(*) into v_cnt from information_schema.tables where TABLE_SCHEMA=dbName and table_name='bak_utf8bm4_tables';
   if v_cnt=0 then
	   set v_sql=(select concat('create table `',dbName,'`.`bak_utf8bm4_tables`(`id` int primary key AUTO_INCREMENT,`name` varchar(100))'));
	   set @sql = v_sql;
	   PREPARE sl from @sql;
	   EXECUTE sl;
	   deallocate prepare sl;
   end if;
   -- alter table 表名 AUTO_INCREMENT = 1000;
   OPEN cur_tables;
   FETCH cur_tables into v_tableName;
   while_label: while (not done) do
     set v_sql=(select concat('insert into `',dbName,'`.`bak_utf8bm4_tables`(`name`) values(''',v_tableName,''')'));
      -- 1、记录处理的表
     set @sql = v_sql;
	 PREPARE sl from @sql;
	 EXECUTE sl;
	 deallocate prepare sl;

      -- 2、获取原表创建脚本
      select replace(concat(colSql,if(priKey<>'',concat('\n  primary key(',priKey,')'),''),'\n)'),',\n)','\n)'),b.`AUTO_INCREMENT`,incCol
	    into v_sql,v_incVal,v_incCol
        from (
		select TABLE_SCHEMA,table_name,concat('create table `',TABLE_SCHEMA,'`.`',table_name,'`\n(\n',
		group_concat(concat('  `',column_name,'` ',column_type,if(is_nullable='NO',' NOT NULL','')) order by ORDINAL_POSITION separator ',\n'),
		',') colSql,
		trim(BOTH ',' from group_concat(if(EXTRA='auto_increment',concat('`',column_name,'` ',column_type),''))) as incCol,
		trim(BOTH ',' from group_concat(if(COLUMN_KEY='PRI',concat('`',column_name,'`,'),'') order by ORDINAL_POSITION separator '')) priKey
		--  primary key(,')\n)'
		-- ) as createTbSc
		from information_schema.`COLUMNS`
		where TABLE_SCHEMA = dbName
		and table_name=v_tableName
		group by TABLE_SCHEMA,table_name
      )temp join information_schema.tables b on temp.table_name=b.table_name and temp.table_schema=b.table_schema;
     -- select v_sql,v_incVal,v_incCol;
     -- leave while_label;
     -- 3、重命名原表
     set v_renameTb=(select concat('rename table `',dbName,'`.`',v_tableName,'` to `',dbName,'`.`bak_',v_tableName,'`'));
     -- select v_renameTb;
     set @sql = v_renameTb;
	 PREPARE sl from @sql;
	 EXECUTE sl;
	 deallocate prepare sl;

	-- 4、重新创建原表
	 set @sql = v_sql;
	 PREPARE sl from @sql;
	 EXECUTE sl;
	 deallocate prepare sl;

     -- 5、复制表数据
	 set v_copysql=(select concat('insert into `',dbName,'`.`',v_tableName,'` select * from `',dbName,'`.`bak_',v_tableName,'`'));
	 set @sql = v_copysql;
	 PREPARE sl from @sql;
	 EXECUTE sl;
	 deallocate prepare sl;

	 if ifnull(v_incCol,'')<>'' THEN
	    -- 设置处境列
		set v_copySql=(select concat('alter table `',dbName,'`.`',v_tableName,'` modify ',v_incCol,' auto_increment'));
		set @sql = v_copysql;
		PREPARE sl from @sql;
		EXECUTE sl;
		deallocate prepare sl;

		set v_copySql=(select concat('alter table `',dbName,'`.`',v_tableName,'` auto_increment=',v_incVal));
		set @sql = v_copysql;
		PREPARE sl from @sql;
		EXECUTE sl;
		deallocate prepare sl;
	 end if;

	 -- 6、下一个取值
     FETCH cur_tables into v_tableName;
   end while;
   close cur_tables;
   select v_tableName;
end  -- $$
-- DELIMITER ;

3、使用存储过程改变数据库字符集

3.1、执行需要修改表字符集的数据库

  
-- NortWind 为数据库名称

alter database NortWind default character set 'utf8mb4' default collate 'utf8mb4_general_ci'; -- 修改库表字符集

SET SESSION group_concat_max_len = 1024000;   -- 修改执行session 使用group_concat 字符连接的最大长度

call pr_resettabcharacter('NortWind');

3.2、执行完成后可通过表查看调整了那些表

select * from NortWind.bak_utf8bm4_tables

3.3、查看未改成功的表

  
SELECT table_name from information_schema.tables a where table_schema='NortWind'
and table_name like 'bak\_%' and not exists(select 1 from information_schema.tables where a.TABLE_SCHEMA=TABLE_SCHEMA
and table_name=substr(a.table_name,5)) and table_name<>'bak_utf8bm4_tables'

本文会经常更新，请阅读原文： https://dashenxian.github.io/post/mysql%E5%8D%87%E7%BA%A7%E6%95%B0%E6%8D%AE%E5%BA%93%E5%90%8E%E6%89%B9%E9%87%8F%E4%BF%AE%E6%94%B9%E8%A1%A8%E4%B8%BAutf8mb4%E6%A8%A1%E5%BC%8F ，以避免陈旧错误知识的误导，同时有更好的阅读体验。

本作品采用知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议进行许可。欢迎转载、使用、重新发布，但务必保留文章署名小神仙（包含链接： https://dashenxian.github.io ），不得用于商业目的，基于本文修改后的作品务必以相同的许可发布。如有任何疑问，请与我联系 (125880321@qq.com) 。