mybatis 中 #{} 和 ${}

推荐到我语雀文档中阅读，格式友好：mybatis 中 #{} 和 ${} （语雀）https://www.yuque.com/wuhanjie-lzk8u/fr0u6y/hsr8s1g6ky92g3cq?singleDoc#%20%E3%80%8Amybatis%20%E4%B8%AD%20%23%7B%7D%20%E5%92%8C%20$%7B%7D%E3%80%8B

MyBatis 排序时使用 order by 动态参数时需要注意，用 $ 而不是 #，#{} 和 ${} 的区别以及 order by 注入问题

必读！！！以下语法只针对 mysql，至于 oracle 本人并没有亲自测试！！！请不要代入到 oracle

---

1、模糊查询与排序

---

ORDER BY ${columnName}

这里 MyBatis 不会修改或转义字符串

重要！！接收从用户输出的内容并提供给语句中不变的字符串，这样做是不安全的。这回导致潜在的 SQL 注入攻击，因此在开发中不应该允许用户输入这些字段，或者通常自行转义并检查

#{} 相当于 jdbc 中的 preparedStatement

${} 是输出变量的值

接下来看两段代码：

String sql = "select * from admin_domain_location order by ?";
PreparedStatement st = con.prepareStatement(sql);
st.setString(1, "domain_id");
System.out.println(st.toString());
ResultSet rs = st.executeQuery();  
while(rs.next()) {  
    System.out.println(rs.getString("domain_id"));  
}
输出结果:
com.mysql.jdbc.PreparedStatement@1fa1ba1: select * from admin_domain_location order by 'domain_id'
3
4
5
2
6

这是个jdbc的preparedstatement例子,不要吐槽我这么写是否合法,这里只是为了说明问题.

以上例子有得出以下信息:

1）order by后面如果采用预编译的形式动态输入参数,那么实际插入的参数是一个字符串,例子中是：order by 'domain_id'

2）输出结果并没有排序,从sql语句中的形式我们也可以推测出此sql语句根本也不合法（正常应该是 order by domain_id）

这一段其实就对应 order by #{columnName}，mybatis 在处理 #{} 时的底层做法是使用预编译语句（PreparedStatement），jdbc 会将 #{} 替换为占位符 ?，然后将参数值传递给SQL语句，以防止SQL注入。这是为了确保 SQL 查询的安全性和性能。

具体做法如下：

1. 将#{}中的参数名称或属性名提取出来
2. 将参数值按照其数据类型进行类型转换
3. 构建预编译语句（PreparedStatement）
4. 使用占位符（通常是?）替代SQL语句中的#{}部分
5. 将经过类型转换的参数值填充到预编译语句中的占位符中

这个过程可以确保参数值被正确地传递给数据库，并且不受恶意输入的影响。这是一种防止SQL注入的有效方法，因为参数值会被数据库引擎正确解释，而不会被当作SQL代码的一部分。

此外，使用预编译语句还可以提高SQL查询的性能，因为数据库可以在执行之前编译SQL语句，减少了每次查询时的解析和编译开销。

但如果你把上述代码改成：

String input = "domain_id";
String sql = "select * from admin_domain_location order by " + input;
...
. 其余步骤一致
...
输出结果:
com.mysql.jdbc.PreparedStatement@1fa1ba1: select * from admin_domain_location order by domain_id
2
3
4
5
6

我们发现，这样可以得到正确的结果，这其实对应 mybatis 使用 ${} 的情况，正因为使用 order by 是通过字段名去排序，而不是字段值，因此直接替换为变量值才是恰当的。

因此在遇到下面这些情况，你就可以区分为什么有时候用 ${}，而有时候却用 #{} 了：

1. 表达式：name like "%"#{name}"%"

SELECT * FROM your_table WHERE name LIKE "%"#{name}"%"
--> 例如 name == "张三"
--> 编译得到：
SELECT * FROM your_table WHERE name LIKE "%""张三""%"
--> 可以进行模糊查询，没有问题

1. 表达似：name like '%${searchKeyword}%'

SELECT * FROM your_table WHERE name LIKE "%${searchKeyword}%"
--> 例如 name == "张三"
--> 编译得到：
SELECT * FROM your_table WHERE name LIKE "%张三%"
--> 可以进行模糊查询，没有问题

1. 表达式：name LIKE CONCAT('%', #{searchKeyword}, '%')

SELECT * FROM your_table WHERE column_name LIKE CONCAT("%", #{searchKeyword}, "%")
--> 例如 name == "张三"
--> concat 与字符串拼接"+"效果一样，CONCAT('%', #{searchKeyword}, '%') -> "%"+"张三"+"%"
--> 编译得到：
SELECT * FROM your_table WHERE name LIKE "%张三%"
--> 可以进行模糊查询，没有问题

2、范围查询

---

在进行范围查询时，不能使用 #{} 而必须使用 ${}，举个例子：

select * from your_table where id in (#{idList});

当传入的字符串是 "1, 2, 4" 时，用 # 只能查询 id 为 1 的记录，这是因为使用了 #，就是一个占位符，经过编译后是 where id in ("1,2,4") 这种，在 SQL 中只会删除 id = 1 的记录。但如果使用 $，那么编译后是 where id in (1,2,4)，则会去匹配 id 为 1，2，4 的记录。

下面举例说明为什么是这样？

首先创建一个数据表：

CREATE TABLE test (
	`id` INT(11) NOT NULL,
	`age` INT(11) DEFAULT NULL,
	`name` VARCHAR(50) DEFAULT NULL,
	PRIMARY KEY(`id`)
)

插入数据：

然后查询：

SELECT *, CONVERT("1,2,4", SIGNED) FROM test WHERE test.id IN("1,2,4");

得到结果：

可以看到，只匹配到 id = 1 的记录，并且 CONVERT("1,2,4", SIGNED)(字符串转整型的函数)的结果是 1

这是因为，"1,2,4" 是字符串，而 id 是整型， 在 mysql 中整型与字符串的比较是将字符串转为整型，然后再进行比较，而当字符串无法解释为整型的时候（"1,2,4"，这种情况 mysql 就无法将其转为整型），mysq 只会考虑字符串的起始部分，直到遇到非数字字符。这就解释了为什么 CONVERT("1,2,4", SIGNED) = 1。

这里顺便说一下上面的直到遇到非数字字符是什么意思：

同样举例说明：

SELECT *, CONVERT("a,2,4", SIGNED) FROM test WHERE test.id IN("1,2,4");

查询结果：

可以看到结果是 0。因此上面的说明还不准确，完整的描述是：如果在SQL中将一个整数字段与一个字符串进行比较，MySQL会尝试将字符串转换为整数。如果字符串不能被解释为整数，MySQL会将其视为0。

因此，不建议使用 #，如果非要使用：

1. 可以在 mysql 中使用 FIND_IN_SET函数来实现这一点，FIND_IN_SET函数将 id 与idList 中的每个 ID 值进行比较，而不是将整个字符串作为一个单独的值。这将匹配多个 ID 值，而不仅仅是第一个 ID。
2. 在代码的业务层面对参数进行处理。

参考文章：

关于mybatis中llike模糊查询中#和$的使用

MyBatis排序时使用order by 动态参数时需要注意，用$而不是#, #{}和${}的区别以及order by注入问题