17. Mysql 动态SQL

概述

动态 sql 是指在程序运行时根据不同的条件和需求生成不同的 sql 语句。通过动态 sql，我们可以实现灵活的查询、更新和删除操作，提高代码的复用性和可维护性。

应用场景：

• 动态查询：根据用户输入的条件动态生成查询语句，实现灵活的数据查询。
• 动态更新：根据不同的业务需求生成不同的更新语句，实现数据的动态更新。
• 动态删除：根据特定条件动态生成删除语句，实现数据的动态删除。
• 动态插入：根据不同的数据来源动态生成插入语句，实现数据的动态插入。

优势：

• 灵活性：根据不同的条件和需求生成不同的 sql 语句，实现灵活的数据操作。
• 可维护性：通过使用动态 sql，可以减少代码冗余，提高代码的可维护性。
• 安全性：动态 sql 可以通过参数化查询来防止 sql 注入攻击。
• 性能优化：动态 sql 可以根据具体情况生成高效的 sql 语句，提高查询性能。

基本语法示例：

# 定义变量
set @db_tb_name = '`sql_test1`.`test`';
# 定义拼接后的最终sql
set @sql = replace('show create table @db_tb_name', '@db_tb_name', @db_tb_name);
select @sql; -- 查看拼接后的sql语句
# 预编译，准备拼接的sql语句
prepare stmt from @sql;
# 执行拼接的sql语句
execute stmt;
# 释放语句
deallocate prepare stmt;

+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                   |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  `sale` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

• prepare：准备要执行的语句。
• execute：执行由prepare语句准备的预准备语句。
• deallocate prepare：发布准备好的声明，并且释放语句。

动态SQL操作

编写一个查询，可以动态添加查询条件。

set @db_name = 'sql_test1'; -- 指定一个数据库
set @tb_name = 'category';-- 指定一个表

set @sql = 'select TABLE_SCHEMA,TABLE_NAME,concat(table_schema, \'.\', table_name) as db_tb_name from information_schema.tables where 1=1';
# 判断是否指定数据库，如果不指定则跳过
if @db_name is not null then
    set @sql = concat(@sql, ' and TABLE_SCHEMA = \'', @db_name,'\'');
end if;
select @sql;
# 判断是否指定数据表，如果不指定则跳过
if @tb_name is not null then
    set @sql = concat(@sql, ' and TABLE_NAME = \'', @tb_name,'\'');
end if;
select @sql;
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
+--------------+------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | db_tb_name         |
+--------------+------------+--------------------+
| sql_test1    | category   | sql_test1.category |
+--------------+------------+--------------------+

首先初始化变量@sql为一个基本的查询语句。然后使用IF语句判断变量@db_name和@tb_name是否为空，如果不为空，则将对应的查询条件拼接到@sql中。最后使用 prepare 语句准备 sql 语句，并使用 execute 语句执行 sql 语句。

编写一个sql，可以实现动态更新表和删除数据。

# 根据当前日期，定义最近四周变量
set @cur_year_week_begin_dt = date_sub(curdate(), interval weekday(curdate()) day);
set @cur_year_week = yearweek(curdate(), 1);
set @cur_year_week_lw1 = yearweek(curdate() - interval 1 week, 1);
set @cur_year_week_lw2 = yearweek(curdate() - interval 2 week, 1);
set @cur_year_week_lw3 = yearweek(curdate() - interval 3 week, 1);
# 查看变量的值
select @cur_year_week_begin_dt,@cur_year_week,@cur_year_week_lw1,@cur_year_week_lw2,@cur_year_week_lw3;
+-------------------------+----------------+--------------------+--------------------+--------------------+
| @cur_year_week_begin_dt | @cur_year_week | @cur_year_week_lw1 | @cur_year_week_lw2 | @cur_year_week_lw3 |
+-------------------------+----------------+--------------------+--------------------+--------------------+
| 2023-12-25              |         202352 |             202351 |             202350 |             202349 |
+-------------------------+----------------+--------------------+--------------------+--------------------+
# 动态sql修改表结构，添加字段
set @alter_sql = concat('alter table sql_test1.category add week_',@cur_year_week,' int after category_name',';');
select @alter_sql;
+-------------------------------------------------------------------------+
| @alter_sql                                                              |
+-------------------------------------------------------------------------+
| alter table sql_test1.category add week_202352 int after category_name; |
+-------------------------------------------------------------------------+
# 动态sql删除表数据
set @del_sql = concat('delete from sql_test1.order_info where order_date between ',@cur_year_week_begin_dt,' and ',curdate(),';');
select @del_sql;
+--------------------------------------------------------------------------------------+
| @del_sql                                                                             |
+--------------------------------------------------------------------------------------+
| delete from sql_test1.order_info where order_date between 2023-12-25 and 2023-12-30; |
+--------------------------------------------------------------------------------------+
# 动态sql清空表分区
set @turn_sql = concat('alter table sql_test1.orders truncate partition p',@cur_year_week, ',p', @cur_year_week_lw1, ',p', @cur_year_week_lw2,',p',@cur_year_week_lw3, ';');
select @turn_sql;
+----------------------------------------------------------------------------------+
| @turn_sql                                                                        |
+----------------------------------------------------------------------------------+
| alter table sql_test1.orders truncate partition p202352,p202351,p202350,p202349; |
+----------------------------------------------------------------------------------+
# 动态sql插入数据
set @insert_sql = concat('insert into sql_test1.orders select * from sql_test1.order_info where year_week between',@cur_year_week_lw3,' and ',@cur_year_week,';');
select @insert_sql;
+-----------------------------------------------------------------------------------------------------------+
| @insert_sql                                                                                               |
+-----------------------------------------------------------------------------------------------------------+
| insert into sql_test1.orders select * from sql_test1.order_info where year_week between202349 and 202352; |
+-----------------------------------------------------------------------------------------------------------+
# 执行以上四个sql语句
prepare stmt1 from @alter_sql;
execute stmt1;
deallocate prepare stmt1;

prepare stmt2 from @del_sql;
execute stmt2;
deallocate prepare stmt2;

prepare stmt3 from @turn_sql;
execute stmt3;
deallocate prepare stmt3;

prepare stmt4 from @insert_sql;
execute stmt4;
deallocate prepare stmt4;

数据库操作无非就是增删改查，通过定义变量、concat 拼接 sql 都可以实现，最后使用 execute 执行 sql 。动态 sql 操作常常和存储过程一起配合使用，能起到更好的作用，存储过程我们放到下一节讲。

总结

动态 sql 是实现灵活数据操作的重要手段，可以根据不同的条件和需求生成不同的 sql 语句。

主要使用 concat 函数拼接 sql 语句和使用IF语句生成动态 sql。此外，通过使用动态 sql 和聚合函数，我们还可以实现动态的数据透视，将新行数据转换为列数据，方便进行数据分析和报表生成。

参考资料：

• Mysql Tutoria：https://www.mysqltutorial.org/?s=dynamic
• Mysql 官方文档：https://dev.mysql.com/doc/x-devapi-userguide/en/dynamic-sql.html