数据版本和去重的数据库设计
2018-05-03 更新: MySQL 中为 NULL 的值不计入索引,所以 delete_time 列不能设置为 NULL,推荐设置为特殊值 python 代码: datetime.datetime.min
现在的问题
- 古典数据库设计不支持逻辑删除
- 添加逻辑删除列后不支持唯一约束
表设计模式
id 必选、自增、可重复
ver 必选
(id 和 ver 两列相组合形成联合索引)
delete_time 必选
name
show me the code
不需要版本的例子:
CREATE TABLE `auth_user_role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id`int(11) NOT NULL,
`role_id`int(11) NOT NULL,
`delete_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
UNIQUE KEY `unique_key` (`user_id`, `role_id`, `delete_time`)
);
需要版本的例子:
CREATE TABLE `tbl_spu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ver` int(11) NOT NULL DEFAULT 1,
`name` varchar(30) NOT NULL,
`station_id` varchar(30) NOT NULL,
`delete_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`, `ver`), -- 联合主键
UNIQUE KEY `unique_key1` (`id`, `delete_time`), -- (未删除的)id 不允许重复
UNIQUE KEY `unique_key2` (`name`, `station_id`, `delete_time`) -- 每个站点下的(未删除的)spu 名字不允许重复
);
操作举例
以 tbl_spu 为例
INSERT
INSERT INTO `tbl_spu` (`partner_id`, `name`) VALUES ('AAA', 5);
注意这里不需要指定 id 和 ver,因为 id 是自增的、ver 是有 default 值的
id | ver | name | partner_id | delete_time |
---|---|---|---|---|
1 | 1 | AAA | 5 | NULL |
UPDATE
SELECT ver FROM `tbl_spu` WHERE `id` = '1' AND delete_time = NULL; -- 获取 ver
UPDATE `tbl_spu` SET `delete_time` = NOW() WHERE `id` = '1' AND ver = 1;
INSERT INTO `tbl_spu` (`id`, `ver`, `name`, `partner_id`) VALUES (1, 2, 'AAA2', 5);
update 的时候需要指定 id 和 ver
id | ver | name | partner_id | delete_time |
---|---|---|---|---|
1 | 1 | AAA | 5 | 2000-01-01 |
1 | 2 | AAA2 | 5 | NULL |
(如果没有 ver 列的话,就不需要先 UPDATE 再 INSERT 了,可以直接 UPDATE)
DELETE
UPDATE `tbl_spu` SET `delete_time` = NOW() WHERE `id` = '1' AND ver = 2;
UPDATE `tbl_spu` SET `delete_time` = NOW() WHERE `id` = '1' AND delete_time = NULL; -- 如果不知道 ver
id | ver | username | partner_id | delete_time |
---|---|---|---|---|
1 | 1 | AAA | 5 | 2000-01-01 |
1 | 2 | AAA2 | 5 | 2000-01-02 |
INSERT anthor spu called AAA
INSERT INTO `tbl_spu` (`name`, `partner_id`) VALUES ('AAA', 5);
注意这里也不需要指定 id 和 ver
id | ver | name | partner_id | delete_time |
---|---|---|---|---|
1 | 1 | AAA | 5 | 2000-01-01 |
1 | 2 | AAA2 | 5 | 2000-01-02 |
2 | 1 | AAA | 5 | NULL |
select
不考虑是否删除(通常在被引用的数据里):
SELECT * FROM tbl_user WHERE id = 20 -- 没有版本
SELECT * FROM tbl_spu WHERE id = 20 AND ver = 30 -- 有版本
根据 id 寻找有效记录:
SELECT * FROM tbl_user WHERE id = 20 AND delte_time = NULL -- 有版本和没有版本都是这样
根据全局唯一字段寻找有效记录:
SELECT * FROM tbl_user WHERE username = "XYZ" AND delte_time = NULL -- 有版本和没有版本都是这样
根据普通的字段寻找有效记录
SELECT * FROM tbl_user WHERE partner_id = 5 AND is_staff = false AND delte_time = NULL