mysql动态sql

 

1.加字段

CREATE DEFINER=`root`@`192.168.2.84` PROCEDURE `sp_add_column`()
BEGIN
declare l_dutyno      int;
declare i         int default 0;
declare l_state_date  varchar(16);

set l_state_date = '20250421';
set l_dutyno=101;

set i=0;
while i<=2 DO
insert into tb_stat_duty_log(dutydate,dutyno,status,remark) values(l_state_date,l_dutyno,0,concat('开始添加字段',i));
      set @strsql01 = CONCAT('alter table tb_test',i,' MODIFY COLUMN channel int(3) UNSIGNED NULL DEFAULT NULL COMMENT ''渠道(1-测试1 2-测试2 10-测试3 20-测试4 30-测试5)'' AFTER id,
ADD COLUMN action_seq varchar(64) NULL COMMENT ''行为唯一标识，channel=10,20,30不能为空'' AFTER channel,
ADD COLUMN source_type varchar(10) NULL COMMENT ''数据来源(TSD) 见platform_info，channel=10,20,30不能为空'' AFTER action_seq');
      /*select @strsql01;*/
      
      PREPARE stmt01 FROM @strsql01;
        execute stmt01;
      deallocate prepare stmt01;
      select @strsql01;
insert into tb_stat_duty_log(dutydate,dutyno,status,remark) values(l_state_date,l_dutyno,1,concat('结束添加字段',i));
set i = i +1;

end while;


END