MySQL - 动态SQL与预处理语句

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: MySQL - 动态SQL与预处理语句

在存储过程或者函数中,有时SQL语句是通过变量传值生成的。这时候就需要使用动态SQL,如果直接在SQL语句中植入变量,将提示无该字段或表。

如下所示:

v_db 和v_table均是变量传值动态生成。

create table v_table like  v_db.v_table;

直接执行将提示错误。


【动态SQL】

动态SQL示例如下(在存储过程中使用):

CREATE  PROCEDURE `proc_copy_table`(IN v_table VARCHAR(20),IN v_db VARCHAR(20),OUT o_result int(4))
BEGIN
    DECLARE exit HANDLER FOR SQLEXCEPTION  
    begin
        rollback; -- 有异常,进行回滚
        set o_result = -500;
    end;
    START TRANSACTION;
        SET @sql1 = CONCAT('drop table if EXISTS ',v_table,';');
        -- drop table if EXISTS v_table;
      prepare stmt from @sql1;  -- 预处理需要执行的动态SQL,
        EXECUTE stmt;
        deallocate prepare stmt;     -- 释放掉预处理段
        set @sql2 = CONCAT('create table ',v_table,' like ',v_db,'.',v_table,';');
        -- create table v_table like  v_db.v_table;
        prepare stmt from @sql2;  -- 预处理需要执行的动态SQL,
        EXECUTE stmt;
        deallocate prepare stmt;     -- 释放掉预处理段
        set @sql3 = CONCAT('insert into  ',v_table,' select * from  ',v_db,'.',v_table,';');
        -- insert into v_table select * from v_db.v_table;
        prepare stmt from @sql3;  -- 预处理需要执行的动态SQL,
        EXECUTE stmt;
        deallocate prepare stmt;     -- 释放掉预处理段
  COMMIT;
    set o_result :=0;
end

这里动态SQL的生成说明如下:

使用concat拼接,将变量传值进去:

SET @sql1 = CONCAT('drop table if EXISTS ',v_table,';');
-- 效果如下:
 drop table if EXISTS [v_table实际变量值];

【预处理语句】

MySQL官方将prepare、execute、deallocate统称为PREPARE STATEMENT。

即,预处理语句。

其用法十分简单:

//获取预处理语句
PREPARE stmt_name FROM preparable_stmt;
//执行预处理语句(可传入用户变量)
EXECUTE stmt_name
    [USING @var_name [, @var_name] ...];
//释放掉预处理资源
{DEALLOCATE | DROP} PREPARE stmt_name;

使用PAREPARE STATEMENT可以减少每次执行SQL的语法分析,

比如用于执行带有WHERE条件的SELECT和DELETE,或者UPDATE,或者INSERT,只需要每次修改变量值即可。

同样可以防止SQL注入,参数值可以包含转义符和定界符。

PREPARE … FROM可以直接接用户变量:

    SET @sql1 = CONCAT('drop table if EXISTS ',v_table,';');
      prepare stmt from @sql1;  -- 预处理需要执行的动态SQL,

每一次执行完EXECUTE时,养成好习惯,须执行DEALLOCATE PREPARE …语句,这样可以释放执行中使用的所有数据库资源(如游标)。

不仅如此,如果一个session的预处理语句过多,可能会达到max_prepared_stmt_count的上限值。

预处理语句只能在创建者的会话中可以使用,其他会话是无法使用的。而且在任意方式(正常或非正常)退出会话时,之前定义好的预处理语句将不复存在。

如果在存储过程中使用,如果不在过程中DEALLOCATE掉,在存储过程结束之后,该预处理语句仍然会有效。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
4天前
|
SQL 关系型数据库 MySQL
【MySQL 数据库】7、SQL 优化
【MySQL 数据库】7、SQL 优化
24 0
|
26天前
|
SQL 缓存 关系型数据库
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
43 0
|
12天前
|
SQL 缓存 关系型数据库
一文搞懂MySQL中一条SQL语句是如何执行的
一文搞懂MySQL中一条SQL语句是如何执行的
28 0
|
1天前
|
存储 关系型数据库 MySQL
最全MySQL面试60题(含答案):存储引擎+数据库锁+索引+SQL优化等
最全MySQL面试60题(含答案):存储引擎+数据库锁+索引+SQL优化等
8 0
|
1天前
|
SQL 存储 关系型数据库
MySQL 常用30种SQL查询语句优化方法
MySQL 常用30种SQL查询语句优化方法
6 0
|
4天前
|
SQL druid Java
【MyBatis】2、MyBatis 的动态 SQL 和增删改操作
【MyBatis】2、MyBatis 的动态 SQL 和增删改操作
18 0
|
6天前
|
SQL 关系型数据库 MySQL
|
11天前
|
SQL XML Java
【JavaEE进阶】 MyBatis之动态SQL
【JavaEE进阶】 MyBatis之动态SQL
|
16天前
|
SQL 存储 关系型数据库
MySQL索引原理以及SQL优化
MySQL索引原理以及SQL优化
50 0
|
26天前
|
SQL 安全 关系型数据库
MySQL技能完整学习列表3、SQL语言基础——3、SQL运算符和函数
MySQL技能完整学习列表3、SQL语言基础——3、SQL运算符和函数
25 0