【MyBatis】#{} 和 ${} 区别

取值引用

#{}

自动给其添加引号

  SELECT * FROM user WHERE username=#{value}

SELECT * FROM user WHERE username="Amy"

${}

  SELECT * FROM user WHERE username=${value}

SELECT * FROM user WHERE username=Amy

SQL 注入

#{}可以防止SQL注入而${}却不行

  SELECT * FROM user WHERE username='${value}'

SELECT * FROM user WHERE username='' OR 1=1 OR '';

适用场景

#{} 和 ${} 均适用场景——模糊查询

${}——模糊查询

  SELECT * FROM user WHERE username LIKE '%${value}%'

SELECT * FROM user WHERE username LIKE '%Amy%';

#{}——模糊查询

  SELECT * FROM USER WHERE username LIKE CONCAT('%', #{username}, '%')

SELECT * FROM USER WHERE username LIKE CONCAT('%', 'Amy','%');

只能使用${}的场景——字段、表明（不需要加引号的）

使用#{}——错误

  SELECT * FROM USER WHERE username LIKE '%Am%' ORDER BY #{value} ASC

SELECT * FROM USER WHERE username LIKE '%Am%' ORDER BY 'sex' ASC;

改为${}——正确

  SELECT * FROM USER WHERE username LIKE '%Am%' ORDER BY ${value} ASC

SELECT * FROM USER WHERE username LIKE '%Am%' ORDER BY sex ASC;