由于是入门的小白,在项目中遇到了类似的问题,做如下记录。
1、问题场景
开始本篇之前,先说下我遇到的问题场景,实际开发的场景为例:
开发中有一张用户表tb_device_user , 用来记录设备上的用户名和密码,由于底层设备的用户ID使用的是自增整数,而且是在各自的设备中保持唯一,通信时候服务器下发token到设备,作为全局唯一的设备识别码,因此至少两个唯一字段。遇到的需求是在数据库中只能保存唯一的用户密码,也就是说同一个token下的同一个user_id对应只能有一个密码,设备会不定期的上传更新用户密码,要求数据库中如果存在就更新,不存在就插入。
以上是一大段的需求场景描述,也是我实际开发中遇到的需求,为简便起见,我用图形来表示
图形表达的话会更形象些。
2、解决思路和操作步骤
上面也提到了解决思路,那就是使用 联合唯一索引 来解决插入或更新的问题,我使用的是可视化工具Navicat,就给大家演示下如何操作
2.1)建表
建表的SQL语句:
DROP TABLE IF EXISTS `tb_device_user`;
CREATE TABLE `tb_device_user` (
`id` varchar(32) CHARACTER SET utf8 NOT NULL,
`token` varchar(32) CHARACTER SET utf8 DEFAULT NULL COMMENT '设备识别码',
`user_id` smallint(4) DEFAULT NULL,
`user_pwd` varchar(10) CHARACTER SET utf8 DEFAULT NULL COMMENT '设备的用户密码',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.2)新建联合索引
3、效果展示
3.1)插入数据操作
附上我的SQL语句
INSERT INTO `tb_device_user` (
`id`,
`token`,
`user_id`,
`user_pwd`
)
VALUES
(
'002ecf0dfc404906958add9650ebe5cb',
'94107B5203B14FF389F465E0195AD37B',
'1',
'00452132'
),
(
'00f7616d7a4a4ef6bce7a86bf61dcbdd',
'94107B5203B14FF389F465E0195AD37B',
'2',
'1111111'
),
(
'01b24b7ec2f649838689d93a0dd51e9f',
'94107B5203B14FF389F465E0195AD37B',
'3',
'142536'
),
(
'2955ec1b2c384c42997762462257ffdd',
'AD71F1BD6E9F4721B61986EB13246E53',
'1',
'123123123'
),
(
'01bbdd754343457086b044cacbf16c0b',
'AD71F1BD6E9F4721B61986EB13246E53',
'3',
'7894561'
);
看下表中的数据:
插入操作刚刚已经做完了,使用的是标准的SQL语句,但是现在需求又不一样了。
3.2)更新表中已有用户密码的操作,如果存在就更新,没有就插入一条新记录
现在的需求如题所描述,更新表中已有的数据,如果该设备下的用户ID下已经有了密码,就更新原来的密码,如果没有,就插入一条新的记录。我就不绕弯子了,直接给出SQL语句来。使用了 ON DUPLICATE KEY UPDATE
下面看下,如果是一条新的记录的插入,也即token和uid中只要有一个不一样的情况下,数据如何插入
类似的,如果token相同,但是user_id不一样,那么也会作为一条新数据插入数据表中,如果联合索引一样,也即token和user_id一样的情况下,会执行更新操作。
这个就是单张数据表中有多个“唯一约束”的情况下的处理方式,其实可以将多个唯一约束做联合索引来插入或更新数据。