如何保证db中只有一条具有某些特征的数据

如何保证db中只有一条具有某些特征的数据

需求

保证同一目录下相同类型的文件(文件夹)不重名

示例：文件表

CREATE TABLE `file_disk` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `pid` bigint(20) NOT NULL DEFAULT '0' COMMENT '父级ID',
  `file_name` varchar(125) NOT NULL DEFAULT '' COMMENT '名称',
  `structure_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '类型 1文件夹 2文件',
  `file_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '0' COMMENT '文件后缀',
  `deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除 0否 1是',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=893 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='网盘文件表';

实施方案

01 加唯一索引

给pid，file_name，structure_type，file_type添加联合唯一索引

优点：简单维护成本低

缺点：有局限,不适用于软删除（本需求中有回收站功能，不符）

 

02 先查后添加或更新

-- 先查
SELECT COUNT( 1 ) FROM file_disk WHERE deleted=0 AND (file_name = ? AND structure_type = ? AND pid = ? AND file_type = ?) 
-- 后添加
INSERT INTO file_disk ( file_name, structure_type, creator_id, file_type ) VALUES ( ?, ?, ?, ? ) 

缺点：只能用于单体架构,不适用集群部署,有线程安全性问题,可通过synchronized修饰解决

 

03 拼接sql.保证原子性

INSERT INTO file_disk ( file_name, structure_type, creator_id, file_type ) SELECT '唯一空间',1,1,1 FROM DUAL WHERE NOT EXISTS (SELECT id FROM file_disk WHERE deleted = 0 AND ( file_name = '唯一空间' AND file_type = 1 AND pid = 0 AND structure_type = 1 ) );

sq讲解

普通的insert语句

insert into 表名 (字段1,字段2,字段3) values(值1,值2,值3)

等效于

INSERT INTO 表名 (字段1,字段2,字段3) SELECT 值1,值2,值3 FROM DUAL 

select后面就是insert into 语句中的values后面的值；

DUAL(mysql中的关键字)是为了方便那些要求所有SELECT语句都应该具有FROM和其他子句的人

其中的 DUAL 是一个临时表

拼接sql格式

字段1和字段2不可重复

INSERT INTO 表1 (字段1,字段2,字段3) SELECT 值1,值2,值3 FROM DUAL WHERE NOT EXISTS (SELECT 任一字段 FROM 表1 WHERE 字段1 = 值1 AND 字段2 = 值2 ) 

 

04 分布式锁

复杂,成本高

 

参考文档1: insert时带有where条件的写法

参考文档2: mysql 带条件的添加语句(insert +where 条件)