소프트 삭제

DROP TABLE IF EXISTS member_delete_bad;
CREATE TABLE member_delete_bad(
 member_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 email VARCHAR(255) NOT NULL,
 name VARCHAR(100) NOT NULL,
 is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
 CONSTRAINT uq_member_delete_bad_email UNIQUE(email)
) ENGINE=InnoDB;
INSERT INTO member_delete_bad(email,name) VALUES('reuse@example.com','첫 계정');
UPDATE member_delete_bad SET is_deleted=TRUE WHERE email='reuse@example.com';
INSERT INTO member_delete_bad(email,name) VALUES('reuse@example.com','재가입 계정');
-- ERROR 1062: uq_member_delete_bad_email
SELECT member_id FROM member_delete_bad
WHERE is_deleted=TRUE AND CURRENT_TIMESTAMP>=NULL;

deleted rows=1
deletion timestamp=unknown
deletion actor=unknown
same email re-registration=ERROR 1062
retention candidates=0

DROP PROCEDURE IF EXISTS soft_delete_member;
DROP VIEW IF EXISTS active_members_v;
DROP TABLE IF EXISTS member_delete_request;

ALTER TABLE members
  DROP INDEX uq_members_email,
  DROP CHECK ck_members_status,
  ADD COLUMN deleted_at DATETIME(6) NULL,
  ADD COLUMN deleted_by BIGINT UNSIGNED NULL,
  ADD COLUMN delete_reason VARCHAR(240) NULL,
  ADD COLUMN active_email VARCHAR(255)
    GENERATED ALWAYS AS (IF(deleted_at IS NULL,LOWER(email),NULL)) STORED,
  ADD CONSTRAINT uq_members_active_email UNIQUE(active_email),
  ADD CONSTRAINT ck_members_status
    CHECK(status IN('ACTIVE','SUSPENDED')),
  ADD CONSTRAINT fk_members_deleted_by FOREIGN KEY(deleted_by)
    REFERENCES members(id) ON DELETE RESTRICT,
  ADD CONSTRAINT chk_members_delete_bundle CHECK(
    (deleted_at IS NULL AND deleted_by IS NULL AND delete_reason IS NULL)
    OR
    (deleted_at IS NOT NULL AND deleted_by IS NOT NULL
      AND CHAR_LENGTH(TRIM(delete_reason))>=10)
  ),
  ADD INDEX ix_members_deleted_at(deleted_at,id);

CREATE TABLE member_delete_request(
 request_id BINARY(16) NOT NULL,
 member_id BIGINT UNSIGNED NOT NULL,
 deleted_at DATETIME(6) NOT NULL,
 PRIMARY KEY(request_id),
 CONSTRAINT fk_delete_request_member FOREIGN KEY(member_id)
   REFERENCES members(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE VIEW active_members_v AS
SELECT id AS member_id,email,name,created_at
FROM members WHERE deleted_at IS NULL;

DELIMITER //
CREATE PROCEDURE soft_delete_member(
 IN p_request BINARY(16),IN p_member BIGINT UNSIGNED,
 IN p_actor BIGINT UNSIGNED,IN p_reason VARCHAR(240),IN p_at DATETIME(6))
proc: BEGIN
 DECLARE v_existing BIGINT UNSIGNED; DECLARE v_deleted DATETIME(6);
 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
 START TRANSACTION;
 SELECT member_id INTO v_existing FROM member_delete_request
 WHERE request_id=p_request FOR UPDATE;
 IF v_existing IS NOT NULL THEN COMMIT; SELECT v_existing AS member_id,'REPLAY' outcome; LEAVE proc; END IF;
 SELECT deleted_at INTO v_deleted FROM members WHERE id=p_member FOR UPDATE;
 IF v_deleted IS NOT NULL THEN
   INSERT INTO member_delete_request VALUES(p_request,p_member,v_deleted);
   COMMIT; SELECT p_member AS member_id,'ALREADY_DELETED' outcome; LEAVE proc;
 END IF;
 IF CHAR_LENGTH(TRIM(p_reason))<10 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='delete reason too short'; END IF;
 UPDATE members SET deleted_at=p_at,deleted_by=p_actor,delete_reason=p_reason
 WHERE id=p_member;
 IF ROW_COUNT()<>1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='member not found'; END IF;
 INSERT INTO member_delete_request VALUES(p_request,p_member,p_at);
 COMMIT; SELECT p_member AS member_id,'DELETED' outcome;
END//
DELIMITER ;

SET @member_id = (
 SELECT id FROM members
 WHERE email='min@example.com' AND deleted_at IS NULL
 ORDER BY id LIMIT 1
);
SET @delete_request=UUID_TO_BIN('77777777-7777-4777-8777-777777777777');
CALL soft_delete_member(@delete_request,@member_id,@member_id,
 '사용자가 직접 탈퇴를 확정한 요청','2026-07-16 09:00:00');
CALL soft_delete_member(@delete_request,@member_id,@member_id,
 '재전송된 동일 탈퇴 요청','2026-07-16 09:01:00');

first outcome=DELETED
second outcome=REPLAY
active view rows for member=0
delete request rows=1
historical posts retained=yes

INSERT INTO members(email,password_hash,name,status,created_at)
VALUES('min@example.com','$2b$12$rejoined','재가입 회원','ACTIVE',CURRENT_TIMESTAMP(6));
SET @rejoined_id=LAST_INSERT_ID();

INSERT INTO members(email,password_hash,name,status,created_at)
VALUES('min@example.com','$2b$12$duplicate','중복 활성 계정','ACTIVE',CURRENT_TIMESTAMP(6));
-- ERROR 1062: uq_members_active_email

UPDATE members SET deleted_at='2026-07-16 10:00:00'
WHERE id=@rejoined_id;
-- ERROR 3819: chk_members_delete_bundle

SELECT id AS member_id FROM members
WHERE (deleted_at IS NULL)<>(deleted_by IS NULL)
   OR (deleted_at IS NULL)<>(delete_reason IS NULL);

SELECT active_email,COUNT(*) FROM members
WHERE active_email IS NOT NULL GROUP BY active_email HAVING COUNT(*)>1;

SELECT s.id AS post_id FROM posts s
LEFT JOIN members l ON l.id=s.author_id
WHERE l.id IS NULL;