MySQL 已提供了 INSERT IGNORE INTO
、REPLACE INTO
、INSERT … ON DUPLICATE KEY UPDATE
等表达式实现不重复插入的功能,不过,要使用这些表达式,表上必须有主键或者唯一索引字段,主键或者唯一索引作为判断重复记录的依据。
如果我们想根据非主键或非唯一索引的字段做重复插入判断:不存在就插入新记录,存在则忽略。如果不用事务,这个需求有没有办法实现呢?
有的!
下面就为大伙端上这道菜,请慢用。
我们需要明确的是:单纯使用 INSERT INTO 表 VALUES()
语句是没法实现这个功能的,需要使用复合语句 INSERT INTO 表 SELECT 目标值 FROM ...
才能搞定。
判断一个表里面的某个字段是否存在特定的值,可以使用 not exists
或者 not in
表达式。
# not exists 表达式
not exists(select null from 目标表
where 目标字段 = 目标值)
# not in 表达式
目标字段 not in (目标值)
那怎么把输入的数据看作是从表里面查出来,并能用到上面的过滤条件呢?
MySQL 支持一些不需要查表的 SQL 语句,比如 SELECT 1
、SELECT NOW()
语句。因此我们可以把输入的数据当成 select
子句的字段。当需要用到 where
子句时就必须得有一个表,我们生成只有一条记录的衍生表。
解决方案已经呼之欲出,上面的 SQL 片段拼接起来的伪 SQL 看起来是这样。
insert into 目标表
select 包含目标值的输入数据
from (select 1) as t
where not exists(
select null from 目标表
where 目标字段 = 目标值
)
假设要操作的表叫作 lucky
,它有一个字段 address
,当有新的地址出现的时候就往 lucky
表插入数据。现在 lucky
是一张空表,里面什么数据也没有。
CREATE TABLE `lucky` (
`address` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
执行下面的 SQL,将会往 lucky
表里插入一个地址为 abc
的记录。
INSERT INTO lucky (address)
SELECT
'abc'
FROM
(SELECT
1) t
WHERE NOT EXISTS
(SELECT
NULL
FROM
lucky
WHERE address = 'abc')
再次执行同样的 SQL,lucky
表没有新增记录,说明该 SQL 已实现了避免插入重复数据的功能。
上面的 SQL 也可以改成左连接的形式:
INSERT INTO lucky (address)
SELECT
'abc'
FROM
(SELECT 1) t
LEFT JOIN lucky
ON address = 'abc'
WHERE address IS NULL