Mysql 的prepare语句中limit ?,？concat+rand()+elt可以实现变量为语句中的其它字符，用(玩)法丰富

prepare语句中的’？'可以用于limit ？,? 或是values(?,?)抑或是行值，但是不能是表名

下面举例所用表students的列名

studentID	score	city	phone	QQ

所用表teachers的列名

teacherID	score	city	phone	QQ

所用表employees的列名

employeeID	score	city	phone	QQ

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;