MySQL动态SQL

MySQL动态SQL

MySQL的动态SQL指的是在运行时根据条件动态生成SQL语句的过程。实现动态SQL可以通过拼接字符串、使用条件判断和循环等方式来构建要执行的SQL语句。

数据示例

-- 示例表：employees
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  salary DECIMAL(10, 2)
);

-- 动态插入数据
SET @id := 1;
SET @name := 'John';
SET @age := 28;
SET @salary := 5000.00;

SET @sql := CONCAT('INSERT INTO employees (id, name, age, salary) VALUES (', @id, ', ''', @name, ''', ', @age, ', ', @salary, ')');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 动态查询数据
SET @id := 1;

SET @sql := CONCAT('SELECT * FROM employees WHERE id = ', @id , ' AND age = "15"');

SET @paramName:= '张三';

SET @sql := CONCAT('SELECT * FROM employees WHERE id = ',  '\'', @paramName,  '\'', ' AND age = "15"');


-- sql 预编译
PREPARE stmt FROM @sql;
-- 执行查询
EXECUTE stmt;

-- 释放语句
DEALLOCATE PREPARE stmt;

查询语句拼接

查询拼接语句

SELECT @sql;
用于查询拼接后的sql

引号拼接

'\''

结论

Mysql动态执行，关键点在于动态参数的拼接;