mysql语句拼接

-- 假设我们要根据用户输入的条件动态生成查询语句
SET @name = 'John';
SET @age = 25;

SELECT * FROM users WHERE name = @name AND age = @age;

-- 使用预处理语句和参数绑定
PREPARE stmt FROM 'SELECT * FROM users WHERE name = ? AND age = ?';
SET @name = 'John';
SET @age = 25;
EXECUTE stmt USING @name, @age;
DEALLOCATE PREPARE stmt;

-- 不安全的做法
SET @input = 'John'; -- 用户输入
SELECT * FROM users WHERE name = @input;

-- 安全的做法
PREPARE stmt FROM 'SELECT * FROM users WHERE name = ?';
SET @input = 'John'; -- 用户输入
EXECUTE stmt USING @input;
DEALLOCATE PREPARE stmt;

-- 不好的做法
SET @sql = 'SELECT * FROM users WHERE ';
IF @age > 18 THEN
    SET @sql = CONCAT(@sql, 'age > 18 AND ');
END IF;
IF @name = 'John' THEN
    SET @sql = CONCAT(@sql, 'name = ''John'' AND ');
END IF;
SET @sql = SUBSTRING(@sql, 1, LENGTH(@sql) - 4); -- 去掉最后的 'AND '
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 好的做法
SELECT * FROM users WHERE age > 18 AND name = 'John';