欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

mysql存储过程中使用变量批量修改数据_MySQL

程序员文章站 2022-05-10 11:16:11
...
bitsCN.com
mysql存储过程中使用变量批量修改数据 BEGIN declare i int;#查找表的列数 declare column_name_temp varchar(100);##查找表的列名 DECLARE column_un_change varchar(100); DECLARE table_temp_name varchar(100); declare description varchar(100); declare code varchar(100); set column_un_change = 'OTHER_INCOME'; set description='description'; set code = 'code'; set table_temp_name = 'wy_notebill_detail2'; ######################查找表的列名存储到临时表################### drop table if EXISTS update_column; create temporary table update_column (a INT NOT NULL AUTO_INCREMENT,PRIMARY KEY (a)) TYPE = MyISAM SELECT column_name from information_schema.`COLUMNS` where table_name = table_temp_name and column_name not in(column_un_change,description,code) and data_type not in ('date','datetime'); #######################统计列数量#################### SELECT count(*) into i from update_column; SELECT i; loop1: WHILE i>2 DO ##########################给变量赋值列名####################### SELECT column_name into column_name_temp from update_column where a = i; SET i=i-1; #########################更新表的数据########################## set @sqls=concat('update ',table_temp_name ,' set ',column_name_temp,' = 0'); PREPARE stmt1 from @sqls; EXECUTE stmt1 ; END WHILE loop1; #SELECT * from wy_notebill_detail; END 作者 刘宗才 bitsCN.com
相关标签: mysql 存储