[原创]MySQL在用户标识字段、状态字段操作的问题

以下内容默认数据库为MySQL,存储引擎为INNODB

如何保证用户名的唯一?

在用户系统设计中,通常会指定某一个字段作为用户的唯一标识,如用户名、email、数字编号等。

那么,如何保证标识字段的唯一性,是每个用户系统必须解决的问题。比较常见的做法是先查询,如果存在该用户名就提示用户更换,如果不存在就写入数据库。在MySQL中就是先select,拿到select结果判断后,再决定是否insert。这种情况下,少量用户可能正常运行,但在大量用户时必然会出现标识字段重复的情况。

比如A与B两个用户,都选择tom作为自己的登录名,同时发送了注册请求。这时A与B用户的select都查到tom这个名称未被使用,然后都执行了insert,就会造成AB两个用户都注册成功的结果,引发的混乱。

一般常用的解决办法是利用唯一索引来约束标识字段,保证DB层面的正确性。

如果保证状态、库存等类似数据的更新的正确?

在用户系统和其他系统中,一般都会有改变状态字段的操作。比如user表中有status字段,0、1、2代表用户的三种不同状态,并且这三种状态有特定的转换反向,只能由0到1,0到2,不能有其他改变方式。与上面的用户注册类似,一般遇到这种情况,都会先去select查询状态,然后根据结果执行update。坑就在这里了,用户连续请求了2个不同的接口,第一个接口与第二个接口执行select,查询到的status都是0。然后第一个接口认为0到1操作合法,把status update为1,第二个接口认为0到2操作合法,把status update为2。最终status为2,而按照设计status先被update为1,1到2的操作不被允许,所以最终应该为1。

解决这个问题的方法是:开启事务,在第一次select时使用select … for update,在innodb中把要进行update的行设置锁,之后update上面的行,提交事务。这里在select … for update的时候,一定要设置合适的where条件,确保只锁要update的行,而不是锁全表数据。

Select … for update nowait的用法会在select行被锁时直接返回错误,而不是一直等到到locktimeout。

如何插入,在遇到重复字段时更新数据?

在MySQL中并没有直接提供类似Mongodb插入更新的支持,但是这又是一个常见的需求,如何实现呢?

  • 使用replace into方式,在遇到主键or唯一索引冲突时,删除原有数据,插入新行
  • 使用insert into … on duplicate key update的方式,在遇到主键or唯一索引冲突时,更新数据

通常设计中innodb主键为自增,所以正常只有唯一索引可以触发以上两种方式,注意两种方式区别。

留言

comments powered by Disqus