mysql存储过程解决无主键数据导入【省大数据链路报送：自动生成uuid】

思路：

1. 在导入数据之前，遍历某数据库下的所有表，取消表的主键约束和非空约束
2. 导入数据之后，遍历某数据库下的所有表，对id值为null或空字符串的数据进行主键值更新操作
3. 处理完所有数据之后，恢复主键和非空约束

根据思路分三个存储过程

1. 取消主键和非空约束

CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`()
BEGIN
	# 数据导入后，添加id的主键约束和非空约束
	-- 循环标识
	declare i int default 0;
	-- 表名称
	declare tablename varchar(255);
	-- 声明游标
	declare result cursor for select table_name from information_schema.tables where table_schema='数据库名字';
	-- 当下一条没数据时 i
	declare continue handler for not found set i = 1;
	-- 打开游标
	open result; 
	-- 遍历数据表
	while i <> 1 do
			-- 逐个取出当前记录字段的值
			fetch result into tablename;

			-- 判断是否有主键
			set @judgePrimaryKeySql = concat("select count(*) into @num from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where table_name = '",tablename,"';");
						prepare judgePrimaryKeySql from @judgePrimaryKeySql;
						execute judgePrimaryKeySql;
						deallocate prepare judgePrimaryKeySql;
			
			if(@num = 0) then 
				-- 	取消主键和非空约束
				set @addConstraint1 = concat("alter table ",tablename," add primary key(id);");
							prepare addConstraint1 from @addConstraint1;
							execute addConstraint1;
							deallocate prepare addConstraint1;

				set @addConstraint2 = concat("alter table ",tablename," change column id id varchar(50) not null;");
							prepare addConstraint2 from @addConstraint2;
							execute addConstraint2;
							deallocate prepare addConstraint2;	
			end if;		
	end while;
	-- 关闭游标
	close result;

END;

2. 设置主键（这里用的uuid()）

CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`()
BEGIN
	# 设置id为uuid()
	-- 循环标识
	declare i int default 0;
	-- 表名称
	declare tablename varchar(255);
	-- 声明游标
	declare result cursor for select table_name from information_schema.tables where table_schema='数据库名字';
	-- 当下一条没数据时 i
	declare continue handler for not found set i = 1;
	-- 打开游标
	open result; 
	-- 遍历数据表
	while i <> 1 do
			-- 逐个取出当前记录字段的值
			fetch result into tablename;

			-- 设置主键
			set @setuuid = concat("update ",tablename," set id=replace(uuid(),'-','') where (id is null or id='');");
						prepare setuuid from @setuuid;
						execute setuuid;
						deallocate prepare setuuid;			
	
	end while;
	-- 关闭游标
	close result;	
	
END;

3. 添加主键和非空约束

CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`()
BEGIN
	# 数据导入后，添加id的主键约束和非空约束
	-- 循环标识
	declare i int default 0;
	-- 表名称
	declare tablename varchar(255);
	-- 声明游标
	declare result cursor for select table_name from information_schema.tables where table_schema='zjedu_sz_datacenter';
	-- 当下一条没数据时 i
	declare continue handler for not found set i = 1;
	-- 打开游标
	open result; 
	-- 遍历数据表
	while i <> 1 do
			-- 逐个取出当前记录字段的值
			fetch result into tablename;

			-- 判断是否有主键
			set @judgePrimaryKeySql = concat("select count(*) into @num from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where table_name = '",tablename,"';");
						prepare judgePrimaryKeySql from @judgePrimaryKeySql;
						execute judgePrimaryKeySql;
						deallocate prepare judgePrimaryKeySql;
			
			if(@num = 0) then 
				-- 	添加主键和非空约束
				set @addConstraint1 = concat("alter table ",tablename," add primary key(id);");
							prepare addConstraint1 from @addConstraint1;
							execute addConstraint1;
							deallocate prepare addConstraint1;

				set @addConstraint2 = concat("alter table ",tablename," change column id id varchar(50) not null;");
							prepare addConstraint2 from @addConstraint2;
							execute addConstraint2;
							deallocate prepare addConstraint2;
				
			end if;
			
	end while;
	-- 关闭游标
	close result;
	
END;