mysql(21)-存储过程-动态插入数据（）

#用navicat 做查询运行下列语句是OK的。
insert into newday20210701 values('var1','2021-07-01 00:15:00','10');

#调用存储过程拼接字符会报各种错
#1.表里必须要有主键
#2.拼接字符串要有‘\’

1.表要设主键
2.字符串拼接必须有转义字符'\'
 

delimiter //
use `lp_test`;
CREATE  definer =`root`@`%` PROCEDURE pro_insertTable()
BEGIN
  declare i INT;
  SET i=0;
  while i<2 DO  
  select DATE_FORMAT(DATE_SUB(NOW(),INTERVAL i DAY),'newday%Y%m%d') into @newtableName;
  set @myData='\'10\'';                   #单引号要有转义字符
  set @myTime='\'2018-12-05\ 00:35:00\''; #空格要有转义字符
  set @strsql = CONCAT('insert into ',@newtableName,'(`VARIANTNAME`, `TIME`, `DATA`)values(\'var_test\',',@mytime,',',@mydata,');');
  PREPARE stmt from @strsql;
  EXECUTE stmt;
  set i=i+1;
  end while; 
END;
//
delimiter ;
call pro_insertTable()