Mysql 快速生成修改某种字段数据的语句 与存储过程事务处理

因为同事从别的地方使用第三方工具导入数据，很多数据不规范，需要导入之后用update语句批量修改。这里有两个需求：
1、把所有的日期时间字段数据0000-00-00 00:00:00 的改成null
2、有很多条update语句，要使用事务控制，要么全部执行成功要么回滚

一、利用mysql自带的系统库information_schema 访问元数据方式快速生成sql语句

select CONCAT('-- ',COLUMN_COMMENT ,'\\n update t_data_treatinfo set ',COLUMN_NAME,'= null where ',COLUMN_NAME,'= ''0000-00-00 00:00:00'' ;') from information_schema.`COLUMNS` where TABLE_SCHEMA = 'my_db' and TABLE_NAME = 't_data_treatinfo' AND DATA_TYPE ='datetime';

这样把所有datetime字段凭借成一条条查询结果，如下：

-- 隔离时间\n update t_data_treatinfo set isolate_time= null where isolate_time= '0000-00-00 00:00:00' ;
-- 核酸检测时间\n update t_data_treatinfo set nucleic_acid_testing_time= null where nucleic_acid_testing_time= '0000-00-00 00:00:00' ;
-- 发病时间\n update t_data_treatinfo set disease_time= null where disease_time= '0000-00-00 00:00:00' ;

只要用类似notepad++把\n一键替换成回车换行即可

上述示例结果如下

-- 隔离时间
 update t_data_treatinfo set isolate_time= null where isolate_time= '0000-00-00 00:00:00' ;
-- 核酸检测时间
 update t_data_treatinfo set nucleic_acid_testing_time= null where nucleic_acid_testing_time= '0000-00-00 00:00:00' ;
-- 发病时间
 update t_data_treatinfo set disease_time= null where disease_time= '0000-00-00 00:00:00' ;

二、使用事务控制

这里使用临时存储过程对脚本中执行的sql进行管理。

值得注意的是mysql写存储过程要用 DELIMITER进行声明一个除了分号之外的分隔符，否则SQL执行会报错。

因为MySQL默认以";“为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将”;"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

执行的sql脚本文件内容，如下：

-- 利用存储过程进行事务控制
drop procedure if exists pro_t_proname_202020611;

DELIMITER &&
create procedure pro_t_proname_202020611()
	BEGIN
		DECLARE t_error INTEGER DEFAULT 0;  
		declare msg text;-- 记录错误信息
		-- 异常的时候msg捕获报错信息
		declare continue handler for sqlexception 
		begin get diagnostics condition 1  msg = message_text;set t_error = 1; end ;

 START TRANSACTION;
-- 隔离时间
 update t_data_treatinfo set isolate_time= null where isolate_time= '0000-00-00 00:00:00' ;
-- 核酸检测时间
 update t_data_treatinfo set nucleic_acid_testing_time= null where nucleic_acid_testing_time= '0000-00-00 00:00:00' ;
-- 发病时间
 update t_data_treatinfo set disease_time= null where disease_time= '0000-00-00 00:00:00' ;

IF t_error = 1 THEN  
		ROLLBACK;
		SELECT msg; 	 
	ELSE  
   COMMIT;
	END IF;
END &&
DELIMITER ;
-- 调用完删除存储过程
call pro_t_proname_202020611();
drop procedure if exists pro_t_proname_202020611;