prepare语句中的’?'可以用于limit ?,? 或是values(?,?)抑或是行值,但是不能是表名
下面举例所用表students的列名
studentID | score | city | phone |
---|
所用表teachers的列名
teacherID | score | city | phone |
---|
所用表employees的列名
employeeID | score | city | phone |
---|
prepare 与limit
prepare stmt from'select * from students order by score desc limit?,?';
excute stmt using @x,@y;
deallocate stmt;
上述语句检索出从分数排名为@x+1开始,取@y个学生记录
prepare 与concat
用concat+rand()+elt可以实现变量为表名
select @tablename:=elt(floor(rand()*3)+1, 'teachers','students','employees') as tablename ;
set @a=concat('select * from ',@tablename);
prepare stmt from @a;
execute stmt;
deallocate prepare stmt;
用concat+rand()+elt可以实现变量为语句中的其它字符,用(玩)法丰富
如set @a=concat(‘select ‘,@x,’,@y’,’ from ‘,@z,’ where city = ‘,’@xx’)
再变量@x,@y,@z,@xx随机出来
prepare stmt from @a;
execute stmt;
deallocate prepare stmt;