Skip to content

数据版本和去重的数据库设计

2018-05-03 更新: MySQL 中为 NULL 的值不计入索引,所以 delete_time 列不能设置为 NULL,推荐设置为特殊值 python 代码: datetime.datetime.min


现在的问题

  1. 古典数据库设计不支持逻辑删除
  2. 添加逻辑删除列后不支持唯一约束

表设计模式

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