################### 测试表的内容 #######################
mysql> select * from event_invoke;
+------------+---------------------+
| event_name | event_started |
+------------+---------------------+
| test | 2009-11-04 00:45:55 |
| test | 2009-11-17 10:12:00 |
| test | 2009-11-17 10:34:42 |
| test | 2009-11-17 14:22:12 |
+------------+---------------------+
################## 不带参数的 #######################
prepare s1 from 'select * from event_invoke';
execute s1;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> execute s1;
+------------+---------------------+
| event_name | event_started |
+------------+---------------------+
| test | 2009-11-04 00:45:55 |
| test | 2009-11-17 10:12:00 |
| test | 2009-11-17 10:34:42 |
| test | 2009-11-17 14:22:12 |
+------------+---------------------+
4 rows in set (0.00 sec)
################ 用户变量 ########################
set @test='select * from event_invoke';
prepare s1 from @test;
execute s1;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> execute s1;
+------------+---------------------+
| event_name | event_started |
+------------+---------------------+
| test | 2009-11-04 00:45:55 |
| test | 2009-11-17 10:12:00 |
| test | 2009-11-17 10:34:42 |
| test | 2009-11-17 14:22:12 |
+------------+---------------------+
4 rows in set (0.00 sec)
#################### 删除预处理语句 ###################
deallocate prepare s1;
drop prepare s1;
二者等效
################### 传入用户变量 #####################
prepare s1 from 'select * from event_invoke where event_name=@test';
set @test='test';
execute s1;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> execute s1;
+------------+---------------------+
| event_name | event_started |
+------------+---------------------+
| test | 2009-11-04 00:45:55 |
| test | 2009-11-17 10:12:00 |
| test | 2009-11-17 10:34:42 |
| test | 2009-11-17 14:22:12 |
+------------+---------------------+
4 rows in set (0.00 sec)
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
prepare s1 from 'select * from event_invoke where event_name=? and event_started=?';
set @test='test';
set @test2= '2009-11-04 00:45:55';
execute s1 using @test,@test2;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> execute s1 using @test,@test2;
+------------+---------------------+
| event_name | event_started |
+------------+---------------------+
| test | 2009-11-04 00:45:55 |
+------------+---------------------+
1 row in set (0.00 sec)
