mysql插入前查询重复问题解决方案

需求

在插入数据前先查询是否已经存在该数据,存在则不插入,不存在再插入

使用场景

用户注册前查询是否重名

举例的表结构

表名	userInfo
主键	id	无符号自增
其他	userName	用户名,不允许重名
	password	密码

解决方案

1.添加唯一约束

​ 将userName设为唯一约束,此时插入前不需要判断是否重名,直接插入,插入失败则重名

​ 缺点：不适用与约束可以null的情况,
      举例:若某表内的每一行数据的关系逻辑结构是一颗树,所有子节点的pid是父节点的id,存在根节点的pid为null,此时若是将pid设为唯一约束,当pid为null的时候唯一约束失效

2.分两条sql语句,先查询再判断是否插入

​    这种写法其实存在问题,当这条接口被客户端短时间内多次调用时,在多线程环境下是有可能产生脏数据(重名)的,参照单例模式和操作系统的进程理解这种情况。

3.insert语句里加入not exists解决

  从网上查到的写法,但是实际测试中发现会执行一次sql语句,插入了多行

Insert into userinfo(userName, password) 
select #{userName},#{password} from userinfo
   where not exists 
	(select u.id from userinfo u where u.userName=#{userName}) 

   可以加入limit 1只取第一行,不过在测试上面的sql语句时发现存在一个问题:当表内无数据时没有插入数据

Insert into userinfo(userName, password) 
select #{userName},#{password} from userinfo
   where not exists 
	(select u.id from userinfo u where u.userName=#{userName}) 
   limit 1
#exist:存在,对于外层的每一条数据,判断exist()里面有没有查到数据,查到返回true,并把外层select查到是此条数据插入结果集中,返回false则跳过此条数据,不插入
#not exist:不存在,与exist()相反,查不到再插入到结果集中
#limit 1 只取第一行数据,防止重复插入多次

原因:

​ select #{userName},#{password} from userinfo没有查到数据,后面的exists只是起到过滤前面已经查询到的数据的效果,当表内无数据时,select没查到,也就无法insert

解决方法:删掉from userInfo和limit 1

Insert into userinfo(userName, password) 
select #{userName},#{password}
   where not exists 
	(select u.id from userinfo u where u.userName=#{userName}) 

继续优化:

Insert into userinfo(userName, password) 
select #{userName},#{password}
   where not exists 
	(select 1 from userinfo u where u.userName=#{userName} limit 1) 

原因:

​ from userInfo是根据表内的数据查询,如果你知道select 'Hello'这种写法,很容易理解为什么这样写

​ 当删掉from userInfo时,select #{userName},#{password}只返回一条结果,此时limit 1也就没必要写了

4.使用insert ignore into

    表⽰如果中已经存在相同的记录，则忽略当前新数据，但是对于部分重复的数据无效。例如userName相同但password不同，使用insert ignore into会成功插入
　　当插⼊数据时，如出现错误时，如重复数据，将不返回错误，只以警告形式返回。所以使⽤ignore请确保语句本⾝没有问题，否则也会被忽略掉。例如：

INSERT IGNORE INTO books (name) VALUES ('MySQL Manual')

5.使用触发器

​ 编写触发器,在插入前判断是否重名,重名则阻止插入,否则放行