【DB系列】Mybatis传参作为字段/表名时的注意事项

今天遇到一个非常有意思的事情，一个小伙伴反馈，同样的sql，为啥直接通过mysql终端执行的结果，和mybatis的执行结果不一致，感觉有点鬼畜；然后看了一下，发现这是个比较典型的问题，#{}与${}的使用区别

接下来我们看一下这个问题，顺带也重新学习一下它们两的区别

# I. 环境配置

我们使用SpringBoot + Mybatis + MySql来搭建实例demo

springboot: 2.2.0.RELEASE
mysql: 5.7.22

# 1. 项目配置

<dependencies>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.2.0</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
</dependencies>

1
2
3
4
5
6
7
8
9
10
11

核心的依赖mybatis-spring-boot-starter，至于版本选择，到mvn仓库中，找最新的

另外一个不可获取的就是db配置信息，appliaction.yml

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password:

1
2
3
4
5

# 2. 数据库表

用于测试的数据库

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '用户名',
  `money` int(26) NOT NULL DEFAULT '0' COMMENT '钱',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=551 DEFAULT CHARSET=utf8mb4;

1
2
3
4
5
6
7
8
9
10

# II. 场景复现

一个简单的demo来演示下使用姿势，根据传参，来指定排序的字段；

List<MoneyPo> orderBy(@Param("order") String order);

对应的xml如下

<select id="orderBy" resultMap="BaseResultMap">
    select * from `money` order by #{order} asc
</select>

1
2
3

上面这个执行之后可能与我们预期的不一致，如下

# 1. 问题修复

上面的演示中，本来是希望根据传参进行排序，最后的执行结果会发现都是按照id进行排序

要解决上面这个问题，也很简单，将#改成$

<select id="orderBy" resultMap="BaseResultMap">
    select * from `money` order by ${order} asc
</select>

1
2
3

再次测试如下，和我们的预期一致了

# 2. 原因分析

上面这个问题的关键原因在于 $ 与 #的本质区别，有过一点了解的小伙伴会知道$最终的效果是替换，而#则是占位

比如上面的两个，转成sql，对应如下

#{}: select * from money order by 'money' asc
- 注意money作为字符串传入的
${}: select * from money order by money asc
- 注意money作为列名

上面的第一个sql，非常有意思，执行居然不会抛错，可以正常执行(注意，这个与数据库版本有关，并不是所有的版本都可以正常执行)

# 3. #{}与${}对比

#{}	${}
参数占位，相当于 ?	直接替换到sql的一部分
动态解析 -> 预编译 -> 执行	动态解析 -> 编译 -> 执行
变量替换是在DBMS 中	变量替换是在 DBMS 外
变量替换后，#{} 对应的变量自动加上单引号 ''	变量替换后，${} 对应的变量不会加上单引号 ''
防sql注入	不能防sql注入

注意事项：

select * from money where name = #{name}
select * from money where name = ${name}

1
2

如上面两条sql，在具体传参的时候，就会有一个显著的去呗

#{name}: 传参 一灰灰，对应sql如下
- select * from money where name = '一灰灰'
${name}: 传参 一灰灰，对应sql如下
- select * from money where name = 一灰灰
- 注意上面的sql中，name的传参没有引号，直接就是bad sql
- 所以传参应该是 '一灰灰'，需要手动的加上单引号

使用姿势：