Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry ‘xxx‘ for key ‘xxx‘错误的解决方法

1. 复现错误

今天写好导入hive表的接口，如下代码所示：

/**
 * hive表导入
 *
 * @author super先生
 * @datetime 2023/3/20:16:32
 * @return
 */
@ResponseBody
@PostMapping(value = "/xxx/importTables")
public ServiceStatusData localHiveImportTables(
    @RequestBody ImportHiveTableDto importTablesDto, @RequestHeader("x-userid") Long userId) {
  logger.info("入参记录：importTablesBo={},userId={}", importTablesDto, userId);
  if (isBlank(importTablesDto.getHiveDatabaseName())) {
    return new ServiceStatusData(ServiceStatusData.Status.Fail, "hive库名不能为空", null);
  }
  if (isBlank(importTablesDto.getHiveTableName())) {
    return new ServiceStatusData(ServiceStatusData.Status.Fail, "hive表名不能为空", null);
  }
  String tableImportType = importTablesDto.getTableImportType();
  if (isBlank(tableImportType)) {
    return new ServiceStatusData(ServiceStatusData.Status.Fail, "导表类型不能为空", null);
  }
  if (null == TableImportTypeEnum.getJsonObjectByCode(tableImportType)) {
    return new ServiceStatusData(ServiceStatusData.Status.Fail, "不支持当前的导表类型", null);
  }
  String decimationFrequency = importTablesDto.getDecimationFrequency();
  if (isBlank(decimationFrequency)) {
    return new ServiceStatusData(ServiceStatusData.Status.Fail, "抽取频率不能为空", null);
  }
  if (null == DecimationFrequencyEnum.getJsonObjectByCode(decimationFrequency)) {
    return new ServiceStatusData(ServiceStatusData.Status.Fail, "不支持当前的抽取频率", null);
  }
  importTablesDto.setCron(DecimationFrequencyEnum.toCron(decimationFrequency));
  if (null == importTablesDto.getDatasetId()) {
    return new ServiceStatusData(ServiceStatusData.Status.Fail, "工作表id不能为空", null);
  }
  return hiveImportTaskService.localHiveImportTables(importTablesDto, userId);
}

启动postman访问该接口，确报出如下错误：

错误信息比较多，我们就截取如上重要的信息：java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'missionMockId-\x01' for key 'idx_misstion_id_deleted'。

2. 分析错误

根据java.sql.SQLIntegrityConstraintViolationException异常信息可知，这是由sql报出的错误，也就是说我的数据表存在异常。

具体是什么异常，我们继续往下分析。

正赶上最近ChatGPT比较火，可以借助它帮助我分析错误，如下图所示：

ChatGPT说我尝试添加一个与现有记录重复的值，建议我查看重复记录的相关信息。

于是，查看mybatis打印出的SQL语句，如下图所示：

执行上述mybatis打印出的SQL语句，如下代码所示：

UPDATE 
	hive_import_task 
SET 
	deleted = 1 ,
	update_time = NOW() 
where 
	deleted = 0 and dataset_id = 2

ERROR 1062 (23000): Duplicate entry 'missionMockId-\x01' for key 'idx_misstion_id_deleted'

再次分析Duplicate entry 'missionMockId-\x01' for key 'idx_misstion_id_deleted'错误信息，从中可以得到idx_misstion_id_deleted键值。

idx_misstion_id_deleted是我创建的联合唯一索引，关联的字段为mission_id和deleted，不能有重复的记录。

比如只能存在一条mission_id = 'missionMockId' and deleted = 1的记录，但可以存在 mission_id = 'missionMockId' and 'deleted = 0'的记录。

根据update语句中的where条件，使用select查询，如下图所示：

因而，已经存在一条deleted = 1记录了，当我再把deleted = 0的记录更新为deleted = 1，就已经重复了，才会报出上述的错误。

总结，由于我设置了联合唯一索引(idx_misstion_id_deleted)，因而更新数据时会造成两条重复的数据。

3. 解决错误

既然我更新的数据命中了联合唯一索引，导致了该错误，那么，便可进行如下修改。

1. 使用如下命令删除索引

ALTER TABLE `hive_import_task` DROP INDEX `idx_misstion_id_deleted`;

2. 在HiveImportTaskService类的localHiveImportTables方法中做如下代码的限制

public ServiceStatusData localHiveImportTables(ImportHiveTableDto importTablesDto, Long userId) {
 ...
  // 判断是否抽取过
  HiveImportTask byDatasetId = hiveImportTaskMapper.queryByDatasetId(datasetId);
  if (null != byDatasetId) {
    JSONObject body = new JSONObject();
    body.put("isTaskExist", true);
    return new ServiceStatusData(ServiceStatusData.Status.Fail, "抽取失败：当前任务已存在", body);
  }
  ...
  return new ServiceStatusData(ServiceStatusData.Status.Success, "抽取结束", null);
}

3. HiveImportTaskMapper.java增加queryByDatasetId方法的代码：

/**
 * 根据datasetId查询
 *
 * @param datasetId
 * @return
 */
HiveImportTask queryByDatasetId(@Param("datasetId") Long datasetId);

4. 在hiveImportTaskMapper.xml中添加如下代码：

<select id="queryByDatasetId" parameterType="java.lang.Long"
        resultType="com.xxx.HiveImportTask">
    <include refid="selectCommon"/>
    AND dataset_id = #{datasetId}
    ORDER BY
    create_time DESC
    LIMIT 1
</select>

<sql id="selectCommon">
	SELECT
		id AS id,
		mission_id AS missionId,
		dataset_id AS datasetId,
		request_config AS requestConfig,
		mission_state AS missionState,
		user_id AS userId,
		create_time AS createTime,
		update_time AS updateTime,
		deleted,
		import_result AS importResult
	FROM
		hive_import_task
	WHERE
		deleted = 0
</sql>

4. 文末总结

通过对java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'missionMockId-\x01' for key 'idx_misstion_id_deleted'错误的分析，得知这是更新数据违反了数据库的唯一约束条件，也就是说违反了唯一索引或联合唯一索引。

一般报出java.sql.SQLIntegrityConstraintViolationException: Duplicate entry ‘xxx‘ for key ‘xxx‘这样的错误，其实就是违反了数据库的唯一约束条件！也就是插入数据或添加数据时，具有唯一约束条件的列值重复了。

如果报出这种错误，可有如下两种解决方法：

1. 取消唯一性约束的列；

2. 保证插入的数据或更新的数据，与表中已有的数据不重复！