#如果存在 UpdateDataByColumnType 存储过程则删除 UpdateDataByColumnType 存储过程 drop procedure if exists UpdateDataByColumnType; #如果存在 tmpTable 临时表则删除 UpdateDataByColumnType 临时表 DROP TABLE if EXISTS tmpTable; #创建 UpdateDataByColumnType 存储过程 create procedure UpdateDataByColumnType(db char(20)) begin #申明变量 DECLARE tntmp VARCHAR(100); DECLARE tctmp VARCHAR(100); DECLARE indexx int; #创建临时表 create table tmpTable (tablename VARCHAR(1000),columnsname VARCHAR(1000),flag int); #清空临时表 truncate TABLE tmpTable; #将需要清空的表插入到临时表 INSERT INTO tmpTable(tablename ,columnsname, flag ) (SELECT a.TABLE_NAME,a.COLUMN_NAME,0 as flag FROM information_schema.COLUMNS as a left JOIN information_schema.TABLES as b on a.TABLE_NAME = b.TABLE_NAME and a.TABLE_SCHEMA = b.TABLE_SCHEMA WHERE a.table_schema = db and a.DATA_TYPE in ('datetime','timestamp') and b.TABLE_TYPE ="BASE TABLE"); #循环获取所有的表明以及删除状态 SELECT tablename,columnsname into tntmp,tctmp FROM tmpTable WHERE flag = 0 limit 1; WHILE tntmp <> '' and tctmp <> '' DO #拼写删除语句 set @sqlText := concat("update ",tntmp," set ",tctmp ,"='1990-01-01 00:00:00' where ",tctmp," is NULL;"); prepare stmt from @sqlText; #执行语句 execute stmt; #释放删除语句 deallocate prepare stmt; #更新表状态 UPDATE tmpTable SET flag=1 WHERE tablename = tntmp and columnsname = tctmp; set tntmp = ""; set tctmp = ""; #选择一下条语句 SELECT tablename,columnsname into tntmp,tctmp FROM tmpTable WHERE flag = 0 limit 1; END WHILE; end; call UpdateDataByColumnType("hb_dyrmyy"); #如果存在 UpdateDataByColumnType 存储过程则删除 UpdateDataByColumnType 存储过程 drop procedure if exists UpdateDataByColumnType; #如果存在 tmpTable 临时表则删除 UpdateDataByColumnType 临时表 DROP TABLE if EXISTS tmpTable;