MySQLの論理削除と物理削除：deleted_atの実装と注意点

black laptop computer turned-on displaying source code on table SQL

データを削除するとき、行をテーブルから取り除く「物理削除（hard delete）」と、削除フラグや削除日時を列に記録して行を残す「論理削除（soft delete）」の2種類があります。論理削除はデータの復元や監査ログに向いている半面、すべてのクエリへの条件追加、インデックスの設計、UNIQUE 制約との衝突など考慮点も増えます。この記事では deleted_at と is_deleted それぞれの実装パターン、2 つのアプローチのメリット・デメリット、そして UNIQUE 制約問題の解決策を MySQL の具体例で整理します。

このシナリオで考える

ECサイトのユーザーテーブルを使います。ユーザーが退会したとき、そのデータをどう扱うかが今回のテーマです。

CREATE TABLE users (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email      VARCHAR(255) NOT NULL,
  name       VARCHAR(100) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (email, name) VALUES
  ('alice@example.com', 'Alice'),
  ('bob@example.com',   'Bob'),
  ('carol@example.com', 'Carol');

このテーブルに「退会」機能を追加するとき、物理削除と論理削除の2つの方針が生まれます。

物理削除と論理削除の基本

物理削除は DELETE 文で行をテーブルから消します。論理削除は行を残しつつ「削除済み」を示す列を更新します。

-- 物理削除：行がテーブルから消える
DELETE FROM users WHERE id = 2;

-- 論理削除：deleted_at に削除日時を記録（行は残る）
UPDATE users SET deleted_at = NOW() WHERE id = 2;

実行結果のイメージは次のとおりです。

-- 論理削除後の状態
SELECT id, email, name, deleted_at FROM users;
id email name deleted_at
1 alice@example.com Alice NULL
2 bob@example.com Bob 2026-05-31 03:00:00
3 carol@example.com Carol NULL

Bob の行は残っていますが deleted_at に値が入っています。有効ユーザーだけを取得するには WHERE deleted_at IS NULL を付けます。

2 つのアプローチの違いを表にまとめます。

観点 物理削除 論理削除
データの残存 行が消える（復元不可） 行が残る（後から参照・復元できる）
削除の取り消し バックアップが必要 列をリセットするだけ
外部キーへの影響 参照先を消すとエラーの可能性 行が残るためエラーなし
テーブルサイズ 削除後に減る 増え続ける（定期アーカイブが必要）
クエリの複雑さ そのまま SELECT できる 毎回 WHERE deleted_at IS NULL が必要
監査・履歴要件 別途履歴テーブルが必要 削除日時が列に残る

監査ログの義務がある金融・医療系システムや、ユーザーが誤削除の取り消しを要求しやすいサービスでは論理削除が適します。逆に、キャッシュデータや一時作業データなど復元の必要がないものは物理削除で十分です。

論理削除の2つの実装パターン

論理削除を実現する列の型には代表的な2つのパターンがあります。

パターン1：deleted_at（DATETIME）

NULL なら有効、値があれば削除済みと判断します。削除日時が列に記録されるため、「いつ削除されたか」が追跡できます。Laravel の SoftDeletes トレイトや Rails の paranoia gem など、主要 ORM の標準実装もこの方式を採用しています。

ALTER TABLE users
  ADD COLUMN deleted_at DATETIME NULL DEFAULT NULL;

-- 削除
UPDATE users SET deleted_at = NOW() WHERE id = 2;

-- 有効ユーザーだけ取得
SELECT id, email, name
FROM   users
WHERE  deleted_at IS NULL;

-- 削除済みユーザーだけ取得
SELECT id, email, name, deleted_at
FROM   users
WHERE  deleted_at IS NOT NULL;

-- 復元（削除を取り消す）
UPDATE users SET deleted_at = NULL WHERE id = 2;

パターン2：is_deleted（TINYINT）

0 が有効、1 が削除済みです。意味がわかりやすい反面、削除日時を残したい場合は別途 deleted_at 列を追加する必要があります。また、0 と 1 の2値しかないためインデックスの選択性が低く、大量データでの性能に注意が必要です。

ALTER TABLE users
  ADD COLUMN is_deleted TINYINT(1) NOT NULL DEFAULT 0;

-- 削除
UPDATE users SET is_deleted = 1 WHERE id = 2;

-- 有効ユーザーだけ取得
SELECT * FROM users WHERE is_deleted = 0;

2つの方式を比較します。

比較項目 deleted_at（DATETIME） is_deleted（TINYINT）
削除日時の記録 ○（列の値が削除日時） ×（別途列が必要）
インデックス選択性 NULL が多い場合でも使いやすい 0/1 の2値で選択性が低い
UNIQUE 制約との相性 NULL を活かした回避策が使える（後述） 値ベースの工夫が必要
ORM サポート Laravel・Rails・Django ORM など多数 独自実装が多い
ストレージ 8バイト（DATETIME） 1バイト

迷ったときは deleted_at を選んでおくのが無難です。UNIQUE 制約との相性も良く、ORM のサポートも手厚いためです。

UNIQUE制約との衝突問題と解決策

論理削除でもっとも注意が必要なのが UNIQUE 制約との衝突です。email に UNIQUE 制約があるテーブルで Alice を論理削除した後、同じメールアドレスで再登録しようとするとエラーになります。

ALTER TABLE users ADD UNIQUE KEY uq_email (email);

-- Alice を論理削除
UPDATE users SET deleted_at = NOW()
WHERE  email = 'alice@example.com';

-- 同じ email で再登録を試みる
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice New');
-- ERROR 1062 (23000): Duplicate entry 'alice@example.com' for key 'uq_email'

論理削除した行が物理的に残っているため、UNIQUE 制約が「重複」と判断します。解決策は以下の2つです。

解決策1：複合UNIQUEインデックスに変更する

MySQL では NULL 同士は UNIQUE 制約上の重複とみなしません。(email, deleted_at) の複合 UNIQUE インデックスにすると、deleted_at IS NULL（有効行）の email だけが重複チェックされます。

-- 単体の UNIQUE キーを削除
ALTER TABLE users DROP KEY uq_email;

-- (email, deleted_at) の複合 UNIQUE インデックスを追加
ALTER TABLE users
  ADD UNIQUE KEY uq_email_deleted (email, deleted_at);

-- 論理削除後に再登録できる
UPDATE users SET deleted_at = NOW()
WHERE  email = 'alice@example.com';

INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice New');
-- OK（deleted_at = NULL なので (alice@example.com, NULL) は重複扱いにならない）

ただしこの方法では「同じ email を持つ削除済み行が複数」存在できてしまいます。削除済みデータ同士の重複は防げない点に注意してください。

解決策2：生成列（Generated Column）を使う

MySQL 5.7 以降の生成列（Generated Column）を使うと、より意図が明確になります。有効行のみを表す列を自動生成し、そこに UNIQUE 制約を付けます。

ALTER TABLE users
  ADD COLUMN active_marker TINYINT
    AS (IF(deleted_at IS NULL, 1, NULL)) STORED,
  ADD UNIQUE KEY uq_email_active (email, active_marker);

有効行では active_marker = 1 となり (email, 1) の組み合わせが一意に保たれます。削除済み行では active_marker = NULL になるため UNIQUE チェックをすり抜けます。生成列は deleted_at の更新だけで自動的に再計算されるため、アプリ側での追加処理は不要です。

よくある落とし穴 / 注意点

WHERE条件の付け忘れ

論理削除テーブルを参照するすべてのクエリに WHERE deleted_at IS NULL を付け忘れると、削除済みデータが表示されます。ORM のグローバルスコープや VIEW で封じておくのが安全です。

-- 有効ユーザーだけ返す VIEW を用意する
CREATE VIEW active_users AS
  SELECT * FROM users WHERE deleted_at IS NULL;

インデックスの設計

deleted_at 単体のインデックスは、ほとんどの行が NULL（有効）の場合に選択性が低くなりがちです。実際の検索条件になるカラムと deleted_at を組み合わせた複合インデックスを設計してください。

-- email で有効ユーザーを検索するクエリ向け
CREATE INDEX idx_email_active ON users (email, deleted_at);

テーブルの肥大化

論理削除したデータが蓄積し続けると、テーブルが肥大化してフルスキャンのコストが増えます。「N か月以上前の削除済みレコードはアーカイブテーブルへ移す」または「物理削除する」バッチを定期実行するポリシーを最初から決めておきましょう。

外部キーとの整合性

親テーブルを論理削除しても、子テーブルの行は有効なまま残ります。退会したユーザーの注文レコードが「有効な注文」として参照され続ける、といった問題が起きます。親を論理削除するときは、子テーブルも連動して論理削除する処理を明示的に実装してください。

まとめ

物理削除はシンプルで性能が高く、復元の必要がないデータに適しています。論理削除は削除の取り消しや監査ログが必要な場面で有効ですが、クエリへの条件追加、インデックス設計、UNIQUE 制約との相性まで考慮が必要です。採用の判断軸は「削除後にデータを参照・復元する必要があるか」の一点に尽きます。論理削除を選ぶなら、生成列を活用した UNIQUE 制約の設計と、定期的なアーカイブポリシーをあらかじめ決めておくことが重要です。

参考リンク

アイキャッチ画像: Photo by Jantine Doornbos on Unsplash

タイトルとURLをコピーしました