mysql存储过程实现动态sql

客官°小女子只卖身不卖艺 2022-12-15 03:47 301阅读 0赞

### 1.简介 ###

mysql存储过程可以使用预编译语句的方式来使用动态SQL，主要就是将SQL字符串预编译为statement，然后执行次语句：

1.  `SET @sql = sql`
2.   
3.  `PREPARE stmt_name FROM @sql;`
4.   
5.  `EXECUTE stmt_name;`
6.   
7.  `{DEALLOCATE | DROP} PREPARE stmt_name;`

*  SET @sql：设置SQL字符串变量，一般为用户变量，即前面带有@的变量
 *  PREPARE stmt\_name FROM @sql：预编译SQL字符串为语句
 *  EXECUTE stmt\_name：执行预编译后的语句
 *  DEALLOCATE PREPARE stmt\_name：释放预编译后的语句，释放资源，也可以使用DROP

### 2.简单示例 ###

1.  ``CREATE DEFINER=`root`@`localhost` PROCEDURE `dynamic_sql_test`()``
2.  `BEGIN`
3.   
4.  `set @sql = "SELECT user.id, user.username, user.birthday FROM user;";`
5.  `prepare statement_user_select_all from @sql;`
6.  `execute statement_user_select_all;`
7.  `deallocate prepare statement_user_select_all;`
8.   
9.  `END`

1.  ``mysql> CALL `test`.`dynamic_sql_test`();``
2.  `+----+----------+-------------------------+`
3.  `| id | username | birthday |`
4.  `+----+----------+-------------------------+`
5.  `| 1 | zhangsan | 2020-10-08 19:42:17.000 |`
6.  `+----+----------+-------------------------+`
7.  `1 row in set (0.00 sec)`
8.   
9.  `Query OK, 0 rows affected (0.19 sec)`

*  编译了一个查询语句
 *  在存储过程中使用动态SQL时，SQL字符串不需要带有数据库标识

### 3.带参数的动态SQL语句 ###

预编译语句中可以使用？作为占位符来表示一个参数，执行语句时使用USING语句依次传入参数变量

1.  `SET @sql = sql`
2.   
3.  `PREPARE stmt_name FROM @sql;`
4.   
5.  `SET @var_name = xxx;`
6.   
7.  `EXECUTE stmt_name [USING @var_name [, @var_name] ...];`
8.   
9.  `{DEALLOCATE | DROP} PREPARE stmt_name;`

*  @sql：sql字符串中使用？占位符代表语句参数
 *  USING：执行语句时使用USING指定传入参数名称

1.  ``CREATE DEFINER=`root`@`localhost` PROCEDURE `dynamic_sql_param_test`(username varchar(45),birthday timestamp(3))``
2.  `BEGIN`
3.   
4.  `set @sql = "insert into user (username,birthday) values (?,?);";`
5.  `prepare statement_user_insert from @sql;`
6.   
7.  `set @username = username,@birthday = birthday;`
8.  `execute statement_user_insert using @username,@birthday;`
9.   
10. `deallocate prepare statement_user_insert;`
11.  
12. `END`

1.  ``mysql> CALL `test`.`dynamic_sql_param_test`('lisi', now());``
2.  `Query OK, 0 rows affected (0.76 sec)`
3.   
4.  `mysql> select * from test.user ;`
5.  `+----+----------+-------------------------+`
6.  `| id | username | birthday |`
7.  `+----+----------+-------------------------+`
8.  `| 1 | zhangsan | 2020-10-08 19:42:17.000 |`
9.  `| 2 | lisi | 2020-10-08 20:00:16.000 |`
10. `+----+----------+-------------------------+`
11. `2 rows in set (0.00 sec)`

*  插入语句使用？占位符代表插入的用户名、生日
 *  using语句只能传入用户变量或者全局变量，即带有@的变量，不能直接传入参数或者本地声明的变量
 *  ？占位符传入字符串时不需要使用字符串包裹