概述
动态 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
