java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'xxx' for key 'xxx'

public void insertUser(User user) {
    try {
        userMapper.insert(user);
        log.info("用戶數(shù)據(jù)插入成功");
    } catch (org.springframework.dao.DuplicateKeyException e) {
        // Spring框架對(duì)SQLIntegrityConstraintViolationException的封裝
        log.warn("用戶數(shù)據(jù)已存在: {}", user.getUsername());
        // 可以選擇忽略或更新現(xiàn)有數(shù)據(jù)
    } catch (java.sql.SQLIntegrityConstraintViolationException e) {
        // 使用JDBC直接操作時(shí)可能遇到的原生異常
        log.warn("用戶數(shù)據(jù)已存在(JDBC原生異常): {}", user.getUsername());
        // 同樣可以處理重復(fù)數(shù)據(jù)
    }
}

public void insertUserWithCheck(User user) {
    User existingUser = userMapper.selectByUsername(user.getUsername());
    if (existingUser == null) {
        userMapper.insert(user);
    } else {
        // 處理重復(fù)數(shù)據(jù)，比如更新或忽略
    }
}

@Transactional(rollbackFor = Exception.class)
public void insertUserWithLock(User user) {
    // 獲取分布式鎖(采用Redisson實(shí)現(xiàn)自動(dòng)續(xù)租)
    String lockKey = "user_register:" + user.getUsername();
    RLock lock = redissonClient.getLock(lockKey);

    try {
        // 嘗試獲取鎖，設(shè)置自動(dòng)續(xù)租（看門狗機(jī)制）
        boolean locked = lock.tryLock(5, 30, TimeUnit.SECONDS);
        if (locked) {
            User existingUser = userMapper.selectByUsername(user.getUsername());
            if (existingUser == null) {
                userMapper.insert(user);
            } else {
                // 處理重復(fù)數(shù)據(jù)
            }
        } else {
            throw new BusinessException("操作頻繁，請(qǐng)稍后重試");
        }
    } catch (InterruptedException e) {
        Thread.currentThread().interrupt();
        throw new BusinessException("操作被中斷");
    } finally {
        // 確保鎖釋放
        if (lock.isHeldByCurrentThread()) {
            lock.unlock();
        }
    }
}

@Insert("INSERT IGNORE INTO user(username, email, password) VALUES(#{username}, #{email}, #{password})")
int insertIgnore(User user);

@Insert("INSERT INTO user(username, email, password, login_count) " +
        "VALUES(#{username}, #{email}, #{password}, #{loginCount}) " +
        "ON DUPLICATE KEY UPDATE " +
        "email = IF(email = VALUES(email), email, VALUES(email)), " + // 僅當(dāng)值變化時(shí)更新，避免無謂更新
        "login_count = login_count + 1")                              // 累加操作必然更新
int insertOrUpdateLoginCount(User user);

@Insert("REPLACE INTO user(id, username, email, password) VALUES(#{id}, #{username}, #{email}, #{password})")
int replaceUser(User user);

@Mapper
public interface UserMapper {

    @Insert("<script>" +
            "INSERT INTO user(username, email, password) VALUES " +
            "<foreach collection='users' item='user' separator=','>" +
            "(#{user.username}, #{user.email}, #{user.password})" +
            "</foreach>" +
            " ON DUPLICATE KEY UPDATE " +
            "email = VALUES(email), " +
            "password = VALUES(password)" +
            "</script>")
    int batchInsertOrUpdate(@Param("users") List<User> users);

    @Insert("<script>" +
            "INSERT IGNORE INTO user(username, email, password) VALUES " +
            "<foreach collection='users' item='user' separator=','>" +
            "(#{user.username}, #{user.email}, #{user.password})" +
            "</foreach>" +
            "</script>")
    int batchInsertIgnore(@Param("users") List<User> users);
}

public int batchInsertWithJdbcTemplate(List<User> users) {
    String sql = "INSERT INTO user(username, email, password) VALUES (?, ?, ?) " +
                 "ON DUPLICATE KEY UPDATE email = VALUES(email)";

    return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            User user = users.get(i);
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getEmail());
            ps.setString(3, user.getPassword());
        }

        @Override
        public int getBatchSize() {
            return users.size();
        }
    }).length;
}

@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    /**
     * 插入用戶數(shù)據(jù)，遇到重復(fù)則更新
     */
    @Transactional(rollbackFor = Exception.class)
    public boolean insertOrUpdateUser(User user) {
        return userMapper.insertOrUpdate(user) > 0;
    }

    /**
     * 批量插入用戶數(shù)據(jù)，忽略重復(fù)
     */
    @Transactional(rollbackFor = Exception.class)
    public int batchInsertIgnore(List<User> users) {
        if (CollectionUtils.isEmpty(users)) {
            return 0;
        }

        // 大批量數(shù)據(jù)，分批處理避免事務(wù)過大
        int batchSize = 500; // 根據(jù)實(shí)際數(shù)據(jù)大小和數(shù)據(jù)庫配置調(diào)整
        int totalInserted = 0;

        for (int i = 0; i < users.size(); i += batchSize) {
            List<User> batch = users.subList(i, Math.min(users.size(), i + batchSize));
            totalInserted += userMapper.batchInsertIgnore(batch);
        }

        return totalInserted;
    }
}

// UserMapper接口
public interface UserMapper {

    @Insert("INSERT INTO user(username, email, password) " +
            "VALUES(#{username}, #{email}, #{password}) " +
            "ON DUPLICATE KEY UPDATE " +
            "email = VALUES(email), password = VALUES(password)")
    int insertOrUpdate(User user);

    @Insert("<script>INSERT IGNORE INTO user(username, email, password) VALUES " +
            "<foreach collection='list' item='user' separator=','>" +
            "(#{user.username}, #{user.email}, #{user.password})" +
            "</foreach></script>")
    int batchInsertIgnore(@Param("list") List<User> users);
}

public class MySqlExceptionHelper {

    // MySQL錯(cuò)誤碼常量
    public static final int ER_DUP_ENTRY = 1062; // 重復(fù)鍵錯(cuò)誤碼

    /**
     * 執(zhí)行可能出現(xiàn)重復(fù)鍵的數(shù)據(jù)庫操作
     * @param operation 數(shù)據(jù)庫操作
     * @param duplicateKeyHandler 重復(fù)鍵處理器
     * @return 處理結(jié)果
     */
    public static <T> T executeWithDuplicateKeyHandling(
            Supplier<T> operation,
            Function<Exception, T> duplicateKeyHandler) {

        try {
            return operation.get();
        } catch (DataAccessException e) {
            // 提取原始異常
            Throwable cause = e.getCause();
            if (cause instanceof SQLException) {
                SQLException sqlEx = (SQLException) cause;
                // 通過錯(cuò)誤碼判斷，而非不可靠的字符串匹配
                if (sqlEx.getErrorCode() == ER_DUP_ENTRY) {
                    // 調(diào)用重復(fù)鍵處理器
                    return duplicateKeyHandler.apply((Exception) cause);
                }
            }
            // 重新拋出其他異常
            throw e;
        }
    }
}

public boolean insertUserSafely(User user) {
    return MySqlExceptionHelper.executeWithDuplicateKeyHandling(
        // 正常插入邏輯
        () -> {
            int rows = userMapper.insert(user);
            return rows > 0;
        },
        // 重復(fù)鍵處理邏輯
        ex -> {
            log.warn("用戶{}已存在，嘗試更新", user.getUsername());
            int rows = userMapper.updateByUsername(user);
            return rows > 0;
        }
    );
}

@Service
public class UserRegistrationService {

    @Autowired
    private UserMapper userMapper;

    public RegisterResult register(RegisterRequest request) {
        User user = new User();
        user.setUsername(request.getUsername());
        user.setEmail(request.getEmail());
        user.setPassword(encryptPassword(request.getPassword()));
        user.setCreateTime(new Date());

        try {
            // 使用INSERT IGNORE插入
            int result = userMapper.insertIgnore(user);

            if (result > 0) { // 成功插入新用戶
                return RegisterResult.success();
            } else { // 用戶名已存在
                // 查詢是否是用戶名沖突
                User existingUser = userMapper.selectByUsername(user.getUsername());
                if (existingUser != null) {
                    return RegisterResult.usernameExists();
                } else {
                    // 可能是郵箱沖突
                    return RegisterResult.emailExists();
                }
            }
        } catch (Exception e) {
            log.error("注冊(cè)異常", e);
            return RegisterResult.error("系統(tǒng)異常");
        }
    }
}

@Service
public class DataImportService {

    @Autowired
    private UserMapper userMapper;
    @Autowired
    private MetricsService metricsService; // 監(jiān)控服務(wù)

    @Transactional(rollbackFor = Exception.class)
    public ImportResult importUsers(List<UserDTO> userDTOs) {
        ImportResult result = new ImportResult();

        // 數(shù)據(jù)預(yù)處理和驗(yàn)證
        List<User> validUsers = userDTOs.stream()
                .filter(this::isValidUserData)
                .map(this::convertToUser)
                .collect(Collectors.toList());

        if (validUsers.isEmpty()) {
            result.setMessage("沒有有效數(shù)據(jù)");
            return result;
        }

        // 分批處理，每批500條
        int batchSize = 500;
        List<List<User>> batches = new ArrayList<>();
        for (int i = 0; i < validUsers.size(); i += batchSize) {
            batches.add(validUsers.subList(i, Math.min(validUsers.size(), i + batchSize)));
        }

        int totalImported = 0;
        int totalDuplicated = 0;
        List<String> errors = new ArrayList<>();

        for (List<User> batch : batches) {
            try {
                int batchCount = batch.size();
                int imported = userMapper.batchInsertIgnore(batch);
                totalImported += imported;
                totalDuplicated += (batchCount - imported);

                // 記錄監(jiān)控指標(biāo)
                metricsService.recordMetrics(
                    "user_import_success", imported,
                    "user_import_duplicate", batchCount - imported,
                    "user_import_duplicate_ratio", (batchCount - imported) * 100.0 / batchCount
                );

            } catch (Exception e) {
                log.error("導(dǎo)入批次異常", e);
                errors.add("批次導(dǎo)入錯(cuò)誤: " + e.getMessage());
            }
        }

        result.setTotalProcessed(validUsers.size());
        result.setSuccessCount(totalImported);
        result.setDuplicateCount(totalDuplicated);
        result.setErrors(errors);

        return result;
    }
}

@Service
public class SequenceGenerator {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 獲取指定業(yè)務(wù)類型的ID序列段
     * @param type 業(yè)務(wù)類型
     * @param step 步長(zhǎng)(一次獲取多少個(gè)ID)
     * @return 起始ID，應(yīng)用可在內(nèi)存中遞增使用
     */
    public long getNextIdBatch(String type, int step) {
        // 使用悲觀鎖確保并發(fā)安全
        String selectSql = "SELECT current_id FROM id_generator WHERE type = ? FOR UPDATE";
        Long currentId = jdbcTemplate.queryForObject(selectSql, Long.class, type);

        if (currentId == null) {
            // 首次使用，初始化序列
            String insertSql = "INSERT INTO id_generator(type, current_id, step) VALUES(?, 0, ?)";
            jdbcTemplate.update(insertSql, type, step);
            currentId = 0L;
        }

        // 更新序列值
        String updateSql = "UPDATE id_generator SET current_id = current_id + ? WHERE type = ?";
        jdbcTemplate.update(updateSql, step, type);

        // 返回當(dāng)前批次的起始ID
        return currentId;
    }
}

// ID生成器表結(jié)構(gòu)
/*
CREATE TABLE id_generator (
    type VARCHAR(50) PRIMARY KEY,
    current_id BIGINT NOT NULL,
    step INT NOT NULL DEFAULT 1000,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
*/

@Service
public class PaymentService {

    @Autowired
    private TransactionMapper transactionMapper;
    @Autowired
    private AccountMapper accountMapper;

    /**
     * 執(zhí)行支付交易（冪等操作）
     * 通過訂單號(hào)+交易類型作為唯一鍵，確保同一筆交易只執(zhí)行一次
     */
    @Transactional(rollbackFor = Exception.class)
    public PaymentResult pay(String orderNo, String accountId, BigDecimal amount) {
        // 創(chuàng)建交易記錄（使用唯一約束確保冪等）
        TransactionDO transaction = new TransactionDO();
        transaction.setOrderNo(orderNo);
        transaction.setType("PAYMENT");
        transaction.setAccountId(accountId);
        transaction.setAmount(amount);
        transaction.setStatus("PROCESSING");
        transaction.setCreateTime(new Date());

        // 嘗試插入交易記錄，如果已存在則返回0
        int affected = transactionMapper.insertIgnore(transaction);

        if (affected == 0) {
            // 交易已存在，查詢狀態(tài)返回
            TransactionDO existingTx = transactionMapper.selectByOrderNoAndType(orderNo, "PAYMENT");
            return new PaymentResult(existingTx.getStatus(), "交易已處理");
        }

        try {
            // 執(zhí)行實(shí)際扣款邏輯（此部分必須保證原子性）
            boolean success = accountMapper.deductBalance(accountId, amount) > 0;

            // 更新交易狀態(tài)
            if (success) {
                transactionMapper.updateStatus(orderNo, "PAYMENT", "SUCCESS");
                return new PaymentResult("SUCCESS", "支付成功");
            } else {
                transactionMapper.updateStatus(orderNo, "PAYMENT", "FAILED");
                return new PaymentResult("FAILED", "余額不足");
            }
        } catch (Exception e) {
            // 異常情況更新交易狀態(tài)
            transactionMapper.updateStatus(orderNo, "PAYMENT", "ERROR");
            throw e; // 向上拋出異常觸發(fā)事務(wù)回滾
        }
    }
}

@Mapper
public interface TransactionMapper {

    @Insert("INSERT IGNORE INTO transactions(order_no, type, account_id, amount, status, create_time) " +
            "VALUES(#{orderNo}, #{type}, #{accountId}, #{amount}, #{status}, #{createTime})")
    int insertIgnore(TransactionDO transaction);

    @Select("SELECT * FROM transactions WHERE order_no = #{orderNo} AND type = #{type}")
    TransactionDO selectByOrderNoAndType(@Param("orderNo") String orderNo, @Param("type") String type);

    @Update("UPDATE transactions SET status = #{status}, update_time = NOW() " +
            "WHERE order_no = #{orderNo} AND type = #{type}")
    int updateStatus(@Param("orderNo") String orderNo, @Param("type") String type, @Param("status") String status);
}

@Service
public class DeviceMetricsService {

    @Autowired
    private MetricsMapper metricsMapper;

    /**
     * 記錄設(shè)備實(shí)時(shí)指標(biāo)數(shù)據(jù)
     * 使用device_id+timestamp作為唯一鍵，確保同一時(shí)間點(diǎn)只保留最新數(shù)據(jù)
     */
    public void recordMetric(String deviceId, Date timestamp, Double value, String metricType) {
        DeviceMetric metric = new DeviceMetric();
        metric.setDeviceId(deviceId);
        metric.setTimestamp(timestamp);
        metric.setValue(value);
        metric.setMetricType(metricType);
        metric.setCreateTime(new Date());

        // 使用REPLACE INTO確保只保留最新值
        metricsMapper.replaceMetric(metric);
    }

    /**
     * 批量記錄設(shè)備指標(biāo)（高性能版本）
     */
    public void batchRecordMetrics(List<DeviceMetric> metrics) {
        if (CollectionUtils.isEmpty(metrics)) {
            return;
        }

        // 分批處理，每批200條
        int batchSize = 200;
        for (int i = 0; i < metrics.size(); i += batchSize) {
            List<DeviceMetric> batch = metrics.subList(i, Math.min(metrics.size(), i + batchSize));
            metricsMapper.batchReplaceMetrics(batch);
        }
    }
}

@Mapper
public interface MetricsMapper {

    @Insert("REPLACE INTO device_metrics(device_id, timestamp, metric_type, value, create_time) " +
            "VALUES(#{deviceId}, #{timestamp}, #{metricType}, #{value}, #{createTime})")
    int replaceMetric(DeviceMetric metric);

    @Insert("<script>" +
            "REPLACE INTO device_metrics(device_id, timestamp, metric_type, value, create_time) VALUES " +
            "<foreach collection='metrics' item='metric' separator=','>" +
            "(#{metric.deviceId}, #{metric.timestamp}, #{metric.metricType}, " +
            "#{metric.value}, #{metric.createTime})" +
            "</foreach>" +
            "</script>")
    int batchReplaceMetrics(@Param("metrics") List<DeviceMetric> metrics);
}

@Service
public class ShardingUserService {

    @Autowired
    private List<UserMapper> shardedMappers; // 不同分片的mapper
    @Autowired
    private ConsistentHash consistentHash; // 一致性哈希服務(wù)

    /**
     * 跨分片用戶注冊(cè)，確保用戶名全局唯一
     */
    public RegisterResult registerWithSharding(RegisterRequest request) {
        // 1. 先查詢?nèi)治ㄒ凰饕恚_認(rèn)用戶名不存在
        String username = request.getUsername();

        // 使用分布式鎖防止并發(fā)插入
        String lockKey = "user:register:" + username;
        try (RedisLockWrapper lock = new RedisLockWrapper(redissonClient, lockKey)) {
            if (!lock.tryLock(5, TimeUnit.SECONDS)) {
                return RegisterResult.busy();
            }

            // 2. 檢查全局用戶名索引
            if (usernameIndexMapper.exists(username)) {
                return RegisterResult.usernameExists();
            }

            // 3. 生成全局唯一用戶ID
            String userId = SnowflakeIdGenerator.nextId();

            // 4. 確定分片（使用一致性哈希算法）
            int shardIndex = consistentHash.getShardIndex(username);
            UserMapper targetMapper = shardedMappers.get(shardIndex);

            // 5. 插入用戶數(shù)據(jù)到對(duì)應(yīng)分片
            User user = createUserFromRequest(request, userId);
            targetMapper.insert(user);

            // 6. 插入全局用戶名索引（使用INSERT IGNORE防止并發(fā)）
            UserNameIndex index = new UserNameIndex(username, userId, shardIndex);
            usernameIndexMapper.insertIgnore(index);

            return RegisterResult.success(userId);
        } catch (Exception e) {
            log.error("分片用戶注冊(cè)異常", e);
            return RegisterResult.error("系統(tǒng)異常");
        }
    }
}

/**
 * 基于虛擬節(jié)點(diǎn)的一致性哈希實(shí)現(xiàn)
 */
@Component
public class ConsistentHash {

    private final TreeMap<Long, Integer> virtualNodes = new TreeMap<>();
    private final int numberOfReplicas; // 虛擬節(jié)點(diǎn)數(shù)量
    private final int shardCount; // 實(shí)際分片數(shù)

    public ConsistentHash(@Value("${sharding.virtual-nodes:160}") int numberOfReplicas,
                          @Value("${sharding.shard-count:4}") int shardCount) {
        this.numberOfReplicas = numberOfReplicas;
        this.shardCount = shardCount;

        // 初始化虛擬節(jié)點(diǎn)
        for (int i = 0; i < shardCount; i++) {
            addShard(i);
        }
    }

    private void addShard(int shardIndex) {
        for (int i = 0; i < numberOfReplicas; i++) {
            String nodeKey = shardIndex + "-" + i;
            long hash = hash(nodeKey);
            virtualNodes.put(hash, shardIndex);
        }
    }

    public int getShardIndex(String key) {
        if (virtualNodes.isEmpty()) {
            return 0;
        }

        long hash = hash(key);
        // 找到第一個(gè)大于等于hash的節(jié)點(diǎn)
        Map.Entry<Long, Integer> entry = virtualNodes.ceilingEntry(hash);

        // 如果沒有找到，則取第一個(gè)節(jié)點(diǎn)
        if (entry == null) {
            entry = virtualNodes.firstEntry();
        }

        return entry.getValue();
    }

    private long hash(String key) {
        // 使用MurmurHash獲得更均勻的哈希分布
        return Hashing.murmur3_128().hashString(key, StandardCharsets.UTF_8).asLong();
    }
}