CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(255) NOT NULL,
  `is_deleted` TINYINT(1) NOT NULL DEFAULT 0,
  `email` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_username_unique` (`username`)
);

-- 創(chuàng)建聯(lián)合唯一索引
ALTER TABLE user ADD UNIQUE INDEX idx_username_del_flag (username, del_flag);

-- 邏輯刪除時(shí)設(shè)置del_flag為NULL
UPDATE user SET del_flag = NULL WHERE username = '張三' AND del_flag = 0;

/**
 * 是否刪除
 * 為解決'邏輯刪除'和'唯一索引'沖突問(wèn)題，將邏輯刪除字段設(shè)置為NULL
 */
@TableLogic(value = "0", delval = "NULL")
private Boolean deleteFlag;

-- 修改表結(jié)構(gòu)，將刪除標(biāo)志改為時(shí)間戳
ALTER TABLE user 
ADD COLUMN delete_time BIGINT DEFAULT 0 COMMENT '刪除時(shí)間，0表示未刪除';

-- 創(chuàng)建聯(lián)合唯一索引
ALTER TABLE user ADD UNIQUE INDEX idx_username_delete_time (username, delete_time);

-- 邏輯刪除時(shí)設(shè)置delete_time為當(dāng)前時(shí)間戳
UPDATE user SET delete_time = UNIX_TIMESTAMP() WHERE username = '張三' AND delete_time = 0;

-- 新增del_unique_key字段
ALTER TABLE user 
ADD COLUMN del_unique_key INT DEFAULT 0 COMMENT '用于唯一索引的邏輯刪除字段';

-- 創(chuàng)建聯(lián)合唯一索引
ALTER TABLE user ADD UNIQUE INDEX idx_username_del_unique (username, del_unique_key);

-- 邏輯刪除時(shí)設(shè)置del_unique_key為主鍵值
UPDATE user SET del_unique_key = id, is_deleted = 1 WHERE username = '張三' AND is_deleted = 0;

-- 添加虛擬生成列
ALTER TABLE user
  ADD COLUMN username_visible VARCHAR(255)
    GENERATED ALWAYS AS (IF(is_deleted = 0, username, NULL)) VIRTUAL,
  ADD COLUMN email_visible VARCHAR(255)
    GENERATED ALWAYS AS (IF(is_deleted = 0, email, NULL)) VIRTUAL;

-- 在虛擬列上創(chuàng)建唯一索引
CREATE UNIQUE INDEX idx_user_unique
  ON user(username_visible, email_visible);

-- 插入第一條數(shù)據(jù)
INSERT INTO user (username, is_deleted, email) VALUES ('張三', 0, 'zhangsan@example.com');

-- 邏輯刪除該數(shù)據(jù)
UPDATE user SET is_deleted = 1 WHERE username = '張三';

-- 再次插入相同用戶(hù)名，成功！
INSERT INTO user (username, is_deleted, email) VALUES ('張三', 0, 'new_zhangsan@example.com');

-- 創(chuàng)建歷史表
CREATE TABLE user_history LIKE user;
ALTER TABLE user_history ADD COLUMN deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- 刪除操作（事務(wù)中執(zhí)行）
START TRANSACTION;
INSERT INTO user_history SELECT *, NOW() FROM user WHERE id = 1;
DELETE FROM user WHERE id = 1;
COMMIT;

// 偽代碼示例
public boolean insertUser(User user) {
    String key = "user:unique:" + user.getUsername();
    // 使用SETNX原子操作
    boolean success = redis.setnx(key, user.getId(), expiration);
    if (!success) {
        throw new BusinessException("用戶(hù)名已存在");
    }
    // 插入數(shù)據(jù)庫(kù)
    return userMapper.insert(user) > 0;
}

// 刪除時(shí)
public boolean deleteUser(Long userId) {
    User user = userMapper.selectById(userId);
    String key = "user:unique:" + user.getUsername();
    redis.delete(key);
    return userMapper.logicDelete(userId);
}

-- 創(chuàng)建表
CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(255) NOT NULL,
  `is_deleted` TINYINT(1) NOT NULL DEFAULT 0,
  `email` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 添加虛擬生成列
ALTER TABLE user
  ADD COLUMN username_visible VARCHAR(255)
    GENERATED ALWAYS AS (IF(is_deleted = 0, username, NULL)) VIRTUAL,
  ADD COLUMN email_visible VARCHAR(255)
    GENERATED ALWAYS AS (IF(is_deleted = 0, email, NULL)) VIRTUAL;

-- 創(chuàng)建唯一索引
CREATE UNIQUE INDEX idx_user_unique
  ON user(username_visible, email_visible);

-- 測(cè)試數(shù)據(jù)操作
-- 1. 插入第一條數(shù)據(jù)
INSERT INTO user (username, is_deleted, email) VALUES ('zhangsan', 0, 'zhangsan@example.com');

-- 2. 邏輯刪除第一條數(shù)據(jù)
UPDATE user SET is_deleted = 1 WHERE username = 'zhangsan';

-- 3. 插入同用戶(hù)名的新數(shù)據(jù)（應(yīng)該成功）
INSERT INTO user (username, is_deleted, email) VALUES ('zhangsan', 0, 'new_zhangsan@example.com');

-- 4. 查詢(xún)驗(yàn)證
SELECT * FROM user WHERE is_deleted = 0;