mysql存储过程实现动态sql

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语句只能传入用户变量或者全局变量，即带有@的变量，不能直接传入参数或者本地声明的变量
• ？占位符传入字符串时不需要使用字符串包裹