前情提要

MySQL 这两年越来越被开发者重视,市面上也出现了一些优秀的教程,使更多的人了解到了 MySQL 底层的逻辑。

但对于我们来说 MySQL 最基本最常用的查询语句的编写,仍然需要最扎实的基本功。

本文以一个简单的两表关联来讲解一个 SQL 语法。

MySQL 两个表进行关联,table1.a = table2.a,如果 a 字段可能出现 null 值,实际上查询结果可能并不是我们期望的。和一些有多年开发经验的老司机进行过沟通,有部分会采用间接的方式解决。

场景再现

我们新建两张表,老司机表 t_old_driver 、大豪宅表 t_big_house, 两张表通过 namelover_name 两个字段来进行关联,才能唯一确定一个大豪宅的所属,其中 lover_name 可能为 null

虽然例子看起来不是很恰当,但能说明问题就好。

20210522125446.png

创建表并初始化数据

create table t_old_driver(
  id bigint(20) primary key,
  name varchar(64) not null comment '名字',
  age tinyint(2) not null comment '年龄',
  lover_name varchar(64) comment '共有产权人'
);

create table t_big_house(
  id bigint(20) primary key,
  address varchar(128) not null comment '房屋地址',
  build_area decimal(10,4) not null comment '面积', 
  owner_name varchar(64) not null comment '名字',
  lover_name varchar(64) comment '共有产权人'
);

insert into t_old_driver values (1, '谢师傅', 36, null);
insert into t_old_driver values (2, '陈师傅', 29, null);
insert into t_old_driver values (3, '洪师傅', 29, '岳师妹');
insert into t_old_driver values (4, '洪师傅', 29, null);

insert into t_big_house values (1, '朝阳区望京1号', 100.8, '谢师傅', null);
insert into t_big_house values (2, '西城区XX2号', 200.5, '陈师傅', null);
insert into t_big_house values (3, '房山区XXX100号', 1000.9, '洪师傅', '岳师妹');
insert into t_big_house values (4, '房山区XXX200号', 3000.9, '洪师傅', null);

当电脑上恰好没有安装 MySQL 的话,可以通过在线模拟的方式来测试,网址:http://sqlfiddle.com

20210522112726.png

关联表查询

当关联两张表查询时,结果如下。

SELECT
	d.NAME,
	d.lover_name,
	h.address,
	h.build_area 
FROM
	t_old_driver d,
	t_big_house h 
WHERE
	d.NAME = h.owner_name 
	AND d.lover_name = h.lover_name

20210522112829.png

可以看到,结果记录只有一条,另外三条数据并不能关联上,null 不能等于 null 吗?

20210522125216.png

采用 union 方式解决

一些老司机会采用下面的写法,使用 union 曲线救国,也能实现。

SELECT
	d.NAME,
	d.lover_name,
	h.address,
	h.build_area 
FROM
	t_old_driver d,
	t_big_house h 
WHERE
	d.NAME = h.owner_name 
	AND d.lover_name = h.lover_name 
	AND d.lover_name IS NOT NULL 

UNION

SELECT
	d.NAME,
	d.lover_name,
	h.address,
	h.build_area 
FROM
	t_old_driver d,
	t_big_house h 
WHERE
	d.NAME = h.owner_name 
	AND d.lover_name IS NULL 
	AND h.lover_name IS NULL

20210522113219.png

采用 IFNULL 函数解决

IFNULL(expression, alt_value)

IFNULL() 函数用于判断第一个表达式是否为 NULL,如果为 NULL 则返回第二个参数的值,如果不为 NULL 则返回第一个参数的值。

SELECT
	d.name,
	d.lover_name,
	h.address,
	h.build_area 
FROM
	t_old_driver d,
	t_big_house h 
WHERE
	d.name = h.owner_name 
	AND ifnull(d.lover_name, 0) = ifnull(h.lover_name, 0)

使用这个函数也可以解决我们的问题。

方案

从上面的例子可以看到,使用等号 = 进行关联,在碰到 null 值时,并不能关联上。

其实在写简单 SQL 进行等值比较时,同样会遇到这个问题,例如我们会使用第一种写法,而不是第二种,第二种是查不到数据的。

select * from t_old_driver where lover_name is null;

select * from t_old_driver where lover_name = null;

我们看看官方是怎么说的。

The NULL value can be surprising until you get used to it. Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values.

解决方式是使用 <=> 来进行等值比较。

NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL

所以我们举例的 SQL 正确关联写法如下。

SELECT
	d.NAME,
	d.lover_name,
	h.address,
	h.build_area 
FROM
	t_old_driver d,
	t_big_house h 
WHERE
	d.NAME = h.owner_name 
	AND d.lover_name <=> h.lover_name

20210522124143.png

就是这么简单。

20210522125352.png

另外表设计规范中也建议将表字段设置为非空约束。

下期见。

关注一下,不迷路,老司机不定期发车。
公众号二维码.jpeg