Webサイト制作コースのお申し込みはこちら

SQLのtruncate tableについてまとめました。

deleteやdropとの比較、制限、主要DBMS間の差異について解説します。

truncateの構文

truncate table テーブル名

領域の割り当てを解除し一気にデータを削除します。

トランザクションログを作らずに削除するため、高速で大量データを削除できます。ただし、全件削除の機能のみで、where句での条件指定はできません。インデックスも削除されます。

なお、実行にはdrop table権限が必要です。delete権限では実行できません。

また、truncate実行時には、Deleteトリガーが設定されていても起動しません。

delete文との違い

delete文は、where句での条件指定ができ、limit句で削除件数の指定も可能です。

また、トランザクションログに「レコードを削除した」というログが残ります。実行後にロールバックが可能です。

大量削除をする場合は、トランザクションログ書き込みが起こるため速度が低下する点と、外部キー制約設定テーブルに対しても、制約に違反しない限り削除可能な点がtruncate文との違いです。

また、delete文の場合は削除件数が返り値として取得できますが、truncate文では返り値が空になります。

dropとの違い

drop文はテーブル構造ごと削除します。トランザクションログの書き出しがなく、実行後のロールバックができません。

テーブル自体が削除され、インデックスも削除されます。

テーブル構造を復活させるには、create tableを実行する必要があります。

大量データの入ったテーブルへのdrop tableの実行速度は、delete文よりは速く、truncate文よりは遅いため、データの削除だけが目的なら、drop文を使うメリットはありません。

truncate文とdelete、dropの実行速度　ダントツ速いのはtruncate

以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。

30万件のデータが入ったemployeesテーブルで削除にかかる時間を比較してみます。

delete from employees
truncate table employees

実行時間は、約1/6でした。

件数が増えるに従って、実行時間はどのように変化するか見るために280万件のデータが入ったsalariesテーブルの削除にかかる時間を比較してみます。

delete from salaries

削除後にロールバックをおこなった場合は以下の通り。

truncate table salaries
drop table salaries

truncateにかかった時間は、deleteにかかった時間の約1/47でした。

truncateにかかった時間は、deleteにかかった時間の約1/7でした。

件数が増えれば増えるほど、truncateとdeleteの実行時間差は大きくなると言えるでしょう。

truncate tableの制限

truncate tableで複数のテーブルを削除することはできない

truncate table文は、対象の1テーブルのデータを削除します。複数テーブルの指定はできません。

複数テーブルの削除を行いたい場合は、以下のようにtruncate table文を列挙したファイルを作成しておき、実行するなどの工夫が必要です。

truncate table departments
truncate table dept_emp;
truncate table dept_manager;
truncate table employees;
truncate table salaries;
truncate table titles;

外部キー制約が設定されていると、truncate tableが実行できない

外部キー制約が設定されているテーブルにtrancate tableを実行することはできません。

例えば、MySQLだと下記のエラーが出力されます。

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

以下のように、一時的に外部キー制約を無効化（MySQLの場合）してからtruncate tableする方法があります。

SET foreign_key_checks = 0;
truncate table テーブル名1;
truncate table テーブル名2;
truncate table テーブル名3;
SET foreign_key_checks = 1;

もしくは、delete文で削除を行いましょう。

テーブルロックされていると、truncate tableが実行できない

対象テーブルでトランザクションが実行中の場合や、テーブルがロックされている場合は、truncate tableが実行できません。

主要DBMSのtruncate table仕様の差異

Oracle

TRUNCATE TABLE（Oracle SQL 言語リファレンス）

MySQL

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.33 TRUNCATE TABLE 構文

SQL Server

TRUNCATE TABLE (Transact-SQL) – SQL Server | Microsoft Docs

truncate tableのまとめ

ポテパンダの一言メモ
  • 大量データ削除時には、deleteよりもtruncate tableの方が処理速度が速い
  • 複数テーブル削除は、truncate tableを複数実行する
  • 外部テーブル制約のあるテーブルに対しては、制約オフ後に実行するか、delete文を使用
  • 主要DBMSで、主にオプション指定でtruncate tableの仕様に差異あり

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか？

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。