看了那么多 MySQL 教程，90% 的同学依然写不对的一个简单 SQL

前情提要

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

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

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

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

场景再现

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

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

创建表并初始化数据

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 的话，可以通过在线模拟的方式来测试，网址：sqlfiddle.com 。

关联表查询

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

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

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

采用 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

采用 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

就是这么简单。

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

下期见。

关注一下，不迷路，老司机不定期发车。

参考资料：

• dev.mysql.com/doc/refman/…
• dev.mysql.com/doc/refman/…