一个循环更新某库所有表所有非主键列的值方法代码,MYSQL代码库,德仔网

代码语言

代码分类

【MYSQL】一个循环更新某库所有表所有非主键列的值方法

作者:Dezai.CN / 发布于2011/11/22/ 1158

<div>
-- =============================================
-- Author: LEAMON
-- Create date: 2011-06-13
-- =============================================

use test;
drop procedure if exists P_UPALLTAB_LEAMON;
CREATE PROCEDURE P_UPALLTAB_LEAMON()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE cTbl varchar(64);
DECLARE cCol varchar(64);
DECLARE cur1 CURSOR FOR
select TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA='test' AND DATA_TYPE in ('varchar','int','BIGINT','decimal') AND ORDINAL_POSITION <> 1 order by TABLE_NAME;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
set @sqlDrop='';
OPEN cur1;
FETCH cur1 INTO cTbl, cCol;/*得到表名及列名*/
WHILE done = 0 DO
set @x=0;
set @sql=concat('select 1 into @x from ',cTbl,' where ',cCol,' in (''-999'',''-95'',''-99'',''-97'') limit 1');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
if @x<>0 then
set @sqlupdate=concat('update `',cTbl,'` set `',cCol,'` = ''null'' where `' ,cCol,'` in (''-999'',''-95'',''-99'',''-97'') ;');
PREPARE stmt1 FROM @sqlupdate;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
end if ;
set done = 0;
FETCH cur1 INTO cTbl, cCol;
END WHILE;
CLOSE cur1;
END

--执行： CALL P_UPALLTAB_LEAMON( )

--备注：mysql更新为null 的字段，仅varchar为更新为null；其余三个数值字段会自动更新为0。
</div>

试试其它关键字

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)