Prepare SQL的使用

1. 优势

1. 每次执行语句时，解析开销更小。
2. 防止SOL注入攻击。
3. 动态搜索条件。

2. 实操

set @sql := 'select * from sql_trains.t_user where name=?';
set @param := 'jack';
prepare stmt from @sql;
execute stmt using @param;
deallocate prepare stmt;

3. 防范sql注入

select * from sql_trains.t_user where name='jack' or 1=1;

4. 动态查询

在where后面加上1=1方便拼装动态sql:

set @s := 'SELECT * FROM employees WHERE 1=1';
set @s := CONCAT(@s，' AND gender ="m"');
set @s := CONCAT(@s, ' AND birth_date >="1960-01-01"');
set @s := CONCAT(@s, ' ORDER BY emp_no LIMIT ?,?');
SET @page_no =0;
SET @page_count = 10;
PREPARE stmt FROM @s;
EXECUTE stmt USING @page_no, @page_count;
DEALLOCATE PREPARE stmt;