sql语句的问题,有人遇到过吗

下面两条sql语句,先执行第一条,查出来的结果复制到第二条sql语句中,就能出现7月4日到7月10日的日期,
第一条语句查询出来的结果集
select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1
可是把第一条sql语句直接放到第二条sql语句中,查询就错了,可能它把结果集当做字符串来处理,能把两条sql直接拼接在一起,查询的结果还是对的吗?

第一条sql
SELECT LEFT(REPEAT(" select 1 union all",-DATEDIFF('2017-07-04','2017-07-10')+1),
LENGTH(REPEAT(" select 1 union all",-DATEDIFF('2017-07-04','2017-07-10')+1))-LENGTH(" union all"))

对的日期
SELECT DATE_ADD('2017-07-04',INTERVAL @i:=@i+1 DAY) as date FROM (
select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1
) as tmp,(SELECT @i:= -1) t

错的日期
SELECT DATE_ADD('2017-07-04',INTERVAL @i:=@i+1 DAY) as date FROM (
SELECT LEFT(REPEAT(" select 1 union all",-DATEDIFF('2017-07-04','2017-07-10')+1),
LENGTH(REPEAT(" select 1 union all",-DATEDIFF('2017-07-04','2017-07-10')+1))-LENGTH(" union all"))
) as tmp,(SELECT @i:= -1) t

3个回答

代码检查可以忽略,因为不会报错

SELECT LEFT(REPEAT(" select 1 union all",-DATEDIFF('2017-07-04','2017-07-10')+1),
LENGTH(REPEAT(" select 1 union all",-DATEDIFF('2017-07-04','2017-07-10')+1))-LENGTH(" union all"))
上面这一句的结果只是一个字符串sql而已,并不能作为一个结果集,要让他返回结果集你就要预编译sql然后执行,下面是可以正常返回的

 SELECT LEFT(REPEAT(" select 1 union all",-DATEDIFF('2017-07-04','2017-07-10')+1),
LENGTH(REPEAT(" select 1 union all",-DATEDIFF('2017-07-04','2017-07-10')+1))-LENGTH(" union all")) INTO @ss;
SET @result = CONCAT('SELECT DATE_ADD(\'2017-07-04\',INTERVAL @i:=@i+1 DAY) AS DATE FROM (',@ss,') AS tmp,(SELECT @i:= -1) t');
PREPARE stmt FROM @result;
EXECUTE stmt;

不使用预编译可以吗,本来分开写可以的,脑残的kw代码检查说有可能sql注入,才需要改,

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!