SQL における TRUNCATE と DELETE: 違いを理解する

SQL での TRUNCATE ステートメントと DELETE ステートメントがどのように機能するか、それらの違い、およびどちらかを使用する方がよい場合について説明します。

データベース テーブルを操作する場合、行のサブセットまたはすべての行を削除する必要がある場合があります。データベーステーブルから行を削除するには、ユースケースに応じて SQL TRUNCATE または DELETE ステートメントを使用できます。

このチュートリアルでは、各ステートメントを詳しく見て、それらがどのように機能するかを理解し、DELETE よりも TRUNCATE を使用する場合とその逆の場合を決定します。

先に進む前に、次の SQL サブセットを確認しておくと役に立ちます。

• データ定義言語 (DDL) ステートメントは、テーブルなどのデータベース オブジェクトを作成および管理するために使用されます。 SQL CREATE 、 DROP 、および TRUNCATE ステートメントは、DDL ステートメントの例です。
• データ操作言語 (DML) ステートメントは、データベース オブジェクト内のデータを操作するために使用されます。 DML ステートメントは、レコードの作成、読み取り、更新、削除の操作を実行するために使用されます。
• データクエリ言語 (DQL) ステートメントは、データベース テーブルからデータを取得するために使用されます。すべての SELECT ステートメントは DQL サブセットに分類されます。

SQL TRUNCATE ステートメントの使用方法

SQL TRUNCATE ステートメントの構文

SQL TRUNCATE ステートメントを使用する構文は次のとおりです。

 TRUNCATE TABLE table_name;

上記の TRUNCATE コマンドを実行すると、 table_name で指定されたテーブル内の すべての 行が削除されますが、テーブルは削除されません。

truncate 操作では、テーブル内のすべてのレコードがスキャンされるわけではありません。したがって、大規模なデータベーステーブルを操作する場合はかなり高速になります。

SQL TRUNCATE の使用例

📑 注 : マシンに MySQL が インストールされている場合は、MySQL コマンドライン クライアントを使用してコーディングできます。 PostgreSQL など、選択した別の DBMS を使用することもできます。

まず、使用するデータベースを作成しましょう。

 mysql> CREATE DATABASE db1;
Query OK, 1 row affected (1.50 sec)

次に、作成したばかりのデータベースを選択します。

 mysql> use db1;
Database changed

次のステップは、データベース テーブルを作成することです。次の CREATE TABLE ステートメントを実行して、単純な tasks テーブルを作成します。

 -- Create the tasks table
CREATE TABLE tasks (
    task_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    due_date DATE,
    status ENUM('Pending', 'In Progress', 'Completed') DEFAULT 'Pending',
    assignee VARCHAR(100)
);

この例では、 tasks テーブルに次の列があります。

• task_id : タスクごとに自動的に増加する一意の識別子。
• title : タスクのタイトルまたは名前。255 文字以内に制限されます。
• due_date : タスクの期限。日付として表されます。
• status : タスクのステータス。「保留中」、「進行中」、「完了」のいずれかです。デフォルト値は「保留中」に設定されています。
• assignee : 特定のタスクの担当者。

tasks テーブルを作成したので、そこにレコードを挿入しましょう。

 -- Inserting multiple records into the tasks table
INSERT INTO tasks (title, due_date, status, assignee)
VALUES
    ('Task 1', '2023-08-10', 'Pending', 'John'),
    ('Task 2', '2023-08-11', 'In Progress', 'Jane'),
    ('Task 3', '2023-08-12', 'Completed', 'Mike'),
    ('Task 4', '2023-08-13', 'Pending', 'Alice'),
    ('Task 5', '2023-08-14', 'In Progress', 'Bob'),
    ('Task 6', '2023-08-15', 'Completed', 'Emily'),
    ('Task 7', '2023-08-16', 'Pending', 'David'),
    ('Task 8', '2023-08-17', 'In Progress', 'Olivia'),
    ('Task 9', '2023-08-18', 'Pending', 'Daniel'),
    ('Task 10', '2023-08-19', 'Completed', 'Sophia'),
    ('Task 11', '2023-08-20', 'Pending', 'Matthew'),
    ('Task 12', '2023-08-21', 'In Progress', 'Ava'),
    ('Task 13', '2023-08-22', 'Completed', 'William'),
    ('Task 14', '2023-08-23', 'Pending', 'Ella'),
    ('Task 15', '2023-08-24', 'In Progress', 'James'),
    ('Task 16', '2023-08-25', 'Completed', 'Lily'),
    ('Task 17', '2023-08-26', 'Pending', 'Benjamin'),
    ('Task 18', '2023-08-27', 'In Progress', 'Mia'),
    ('Task 19', '2023-08-28', 'Pending', 'Henry'),
    ('Task 20', '2023-08-29', 'Completed', 'Isabella');

insert ステートメントを実行すると、次のような出力が表示されるはずです。

 Query OK, 20 rows affected (0.18 sec)
Records: 20  Duplicates: 0  Warnings: 0

次に、TRUNCATE テーブル コマンドを実行して、 tasks テーブルからすべてのレコードを削除します。

 TRUNCATE TABLE tasks;
Query OK, 0 rows affected (0.72 sec)

これを行うと、テーブルではなくすべてのレコードが削除されます。これを確認するには、 SHOW TABLES; そのようです：

 SHOW TABLES;

 +---------------+
| Tables_in_db1 |
+---------------+
| tasks         |
+---------------+
1 row in set (0.00 sec)

また、タスク テーブルからデータを取得する SELECT クエリは空のセットを返します。

 SELECT * FROM tasks;
Empty set (0.00 sec)

SQL DELETE ステートメントの使用方法

SQL DELETE ステートメントの構文

SQL DELETE ステートメントを使用する一般的な構文は次のとおりです。

 DELETE FROM table_name 
WHERE condition;

WHERE 句の condition は、 どの 行を削除するかを決定する述語です。 DELETE ステートメントは、述語が True であるすべての行を削除します。

したがって、DELETE ステートメントを使用すると、どのレコードを削除するかをより詳細に制御できるようになります。

しかし、WHERE 句を指定せずに DELETE ステートメントを使用するとどうなるでしょうか?🤔

 DELETE FROM table_name;

図のように DELETE ステートメントを実行すると、データベース テーブル内の すべての 行が削除されます。

DELETE ステートメントまたは DELETE ステートメントのセットがコミットされていないトランザクションの一部である場合、変更をロールバックできます。ただし、データを別の場所にバックアップすることをお勧めします。

SQL DELETE の使用例

次に、SQL delete ステートメントの動作を見てみましょう。

tasks テーブルからすべてのレコードを削除しました。したがって、(前に実行した) INSERT ステートメントを再実行してレコードを挿入できます。

 -- Inserting multiple records into the tasks table
INSERT INTO tasks (title, due_date, status, assignee)
VALUES
    ('Task 1', '2023-08-10', 'Pending', 'John'),
    ('Task 2', '2023-08-11', 'In Progress', 'Jane'),
    ('Task 3', '2023-08-12', 'Completed', 'Mike'),
    ...
    ('Task 18', '2023-08-27', 'In Progress', 'Mia'),
    ('Task 19', '2023-08-28', 'Pending', 'Henry'),
    ('Task 20', '2023-08-29', 'Completed', 'Isabella');

まず、WHERE 句を指定した DELETE ステートメントを使用してみましょう。次のクエリは、ステータスが「完了」であるすべての行を削除します。

 DELETE FROM tasks WHERE status = 'Completed';
Query OK, 6 rows affected (0.14 sec)

次に、次の SELECT クエリを実行します。

 SELECT * FROM tasks;

現在 14 行があることがわかります。

 +---------+---------+------------+-------------+----------+
| task_id | title   | due_date   | status      | assignee |
+---------+---------+------------+-------------+----------+
|       1 | Task 1  | 2023-08-10 | Pending     | John     |
|       2 | Task 2  | 2023-08-11 | In Progress | Jane     |
|       4 | Task 4  | 2023-08-13 | Pending     | Alice    |
|       5 | Task 5  | 2023-08-14 | In Progress | Bob      |
|       7 | Task 7  | 2023-08-16 | Pending     | David    |
|       8 | Task 8  | 2023-08-17 | In Progress | Olivia   |
|       9 | Task 9  | 2023-08-18 | Pending     | Daniel   |
|      11 | Task 11 | 2023-08-20 | Pending     | Matthew  |
|      12 | Task 12 | 2023-08-21 | In Progress | Ava      |
|      14 | Task 14 | 2023-08-23 | Pending     | Ella     |
|      15 | Task 15 | 2023-08-24 | In Progress | James    |
|      17 | Task 17 | 2023-08-26 | Pending     | Benjamin |
|      18 | Task 18 | 2023-08-27 | In Progress | Mia      |
|      19 | Task 19 | 2023-08-28 | Pending     | Henry    |
+---------+---------+------------+-------------+----------+
14 rows in set (0.00 sec)

次の DELETE ステートメントを実行すると、テーブル内の残りの 14 レコードがすべて削除されます。

 DELETE FROM tasks;
Query OK, 14 rows affected (0.20 sec)

そして、タスクテーブルは空になりました。

 SELECT * FROM tasks;
Empty set (0.00 sec)

SQL DROP ステートメント

これまでのところ、次のことを学びました。

• TRUNCATE ステートメントは、テーブルからすべての行を削除します。
• WHERE 句を指定しない DELETE ステートメントは、テーブルからすべてのレコードを削除します。

ただし、TRUNCATE および DELETE ステートメントではテーブルは削除されません。データベースからテーブルを削除したい場合は、次のように DROP TABLE コマンドを使用できます。

 DROP TABLE table_name;

次に、データベースからタスク テーブルを削除しましょう。

 mysql> DROP TABLE tasks;
Query OK, 0 rows affected (0.43 sec)

SHOW TABLES; が表示されます。空のセットを返します (データベースに存在していた唯一のテーブルを削除したため)。

 mysql> SHOW TABLES;
Empty set (0.00 sec)

SQL で TRUNCATE と DELETE を使用する場合

特徴	切り詰める	消去
構文	TRUNCATE TABLE table_name;	WHERE 句を使用する場合: DELETE FROM table_name WHERE condition; WHERE 句なし: DELETE TABLE table_name;
SQLサブセット	データ定義言語 (DDL)	データ操作言語 (DML)
効果	データベーステーブル内のすべての行を削除します。	WHERE 句を指定せずに DELETE ステートメントを実行すると、データベース テーブル内のすべてのレコードが削除されます。
パフォーマンス	大きなテーブルを操作する場合は、DELETE ステートメントよりも効率的です。	TRUNCATE ステートメントよりも効率が劣ります。

総括する：

• 大規模なデータベース テーブルからすべての行を削除する必要がある場合は、TRUNCATE ステートメントを使用します。
• 特定の条件に基づいて行のサブセットを削除するには、DELETE ステートメントを使用します。

まとめ

要約してディスカッションを締めくくりましょう。

• データベース テーブルを操作する場合、特定のテーブル内の行のサブセットまたはすべての行を削除することが必要な場合があります。これを行うには、TRUNCATE または DELETE ステートメントを使用できます。
• TRUNCATE ステートメントの構文は次のとおりです。 TRUNCATE TABLE table_name; 。 table_name で指定されたテーブル内のすべての行が削除されますが、テーブル自体は削除されません。
• DELETE ステートメントの構文は次のとおりです。 DELETE FROM table_name WHERE condition; 。これにより、述語 condition が true である行が削除されます。
• WHERE 句を指定 せずに SQL DELETE ステートメントを実行すると、テーブル内のすべての行が削除されます。したがって、機能的には、これは SQL TRUNCATE ステートメントと同じ結果を実現します。
• TRUNCATE の実行は、テーブル全体をスキャンしないため、大きなテーブルを操作する場合に特に高速になります。したがって、大規模なデータベース テーブル内のすべての行を削除する必要がある場合は、truncate を実行する方が効率的です。
• 特定の条件に基づいて行のサブセットを削除する必要がある場合は、SQL DELETE ステートメントを使用できます。

一般的に使用される SQL コマンドを簡単に確認するには、この SQL チートシートを参照してください。