唯一索引和逻辑删除字段的互斥 2020-05-11 17:07 表中的唯一索引会和逻辑删除字段互斥,导致逻辑删除后的数据和要插入的数据出现重复索引时无法插入。 有如下表: ```sql CREATE TABLE `pata_biz` ( `ID` bigint(32) NOT NULL AUTO_INCREMENT COMMENT 'ID主键', `NAME` varchar(128) DEFAULT NULL COMMENT '业务线名称', `CODE` varchar(128) UNIQUE DEFAULT NULL COMMENT '业务线code', `BIZ_DESC` varchar(1024) DEFAULT NULL COMMENT '业务线描述' ... `ISACTIVE` tinyint(1) NOT NULL DEFAULT '1' COMMENT '逻辑删除', `INSERTTIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '插入时间', `UPDATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' ); ``` 其中code字段设置为了unique唯一属性,表中就会为此字段添加唯一索引。而表中又有逻辑删除字段ISACTIVE,当ISACTIVE为0时,此条数据就被认为已删除,永远不会再用到。在系统中所有条件只检索ISACTIVE为1的数据。 现在要求做插入操作之前,先查询表中有无name或者code相同的(name,code均不能相同),如果有相同的就不能插入,没有相同的,此sql会返回0表示可以插入。 查询的sql如下: ```sql <sql id="insertCondition"> <where> ISACTIVE = 1 AND (`code` = #{code} OR `name` = #{name}) </where> </sql> <select id="queryBizInfoCountByCodeNameForInsert" resultType="java.lang.Long" parameterType="com.ppd.bot.dao.entity.PataBiz"> select count(*) from pata_biz <include refid="insertCondition"></include> </select> ``` 在执行查询时,如果数据库存在某条数据:name:testname1 code:1001 ISACTIVE:0 ,这条数据是被认为删除了的。然后尝试向表中插入一条数据:name:testname2 code:1001 此时使用queryBizInfoCountByCodeNameForInsert这个sql查询返回结果是0,因为上述数据的ISACTIVE是0查询时不会判断进去。但是真正执行插入sql语句时,数据库的唯一索引不会排除掉ISACTIVE为0的数据,就会因为code重复而无法插入直接报错。 这样看来数据库中好多限制都是理论限制,在实际应用中要根据场景分辨是否应该使用这些限制,尽量不要为数据库添加很强的限制条件,数据库只是存数据的,不做数据计算和判断。 --END--
发表评论