DELETE文
既存の行を削除する場合にはDELETE文を使用します。UPDATE文と同様、一部の行だけを削除することができます。
DELETE文の基本構文
DELETE FROM テーブル名 [WHERE 条件] [その他の条件];
「CityテーブルのIDを降順に並べ替えた時の上位1件を削除する」
mysql> DELETE FROM City ORDER BY ID DESC LIMIT 1; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM City ORDER BY ID DESC LIMIT 3; +------+----------+-------------+------------+------------+ | ID | Name | CountryCode | District | Population| +------+----------+-------------+------------+------------+ | 4079 | Rafah | PSE | Rafah | 92020 | | 4078 | Nablus | PSE | Nablus | 100231 | | 4077 | Jabaliya | PSE | North Gaza | 113901 | +------+----------+-------------+------------+------------+ 3 rows in set (0.00 sec)
テーブルの全件を削除する場合には、通常TRUNCATE文を削除します。TRUNCATE文は条件の指定はできませんが、テーブルのデータを全て削除するにはTRUNCATE文はDELETE文よりも圧倒的に高速です。
TRUNCATE文の基本構文
TRUNCATE [TABLE] テーブル名;
以下ではCityテーブルのコピーを作って、DELETE文とTRUNCATE文の性能比較を行っています。
mysql> CREATE TABLE CityCopy_T SELECT * FROM City; Query OK, 4079 rows affected (0.09 sec) Records: 4079 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE CityCopy_D SELECT * FROM City; Query OK, 4079 rows affected (0.08 sec) Records: 4079 Duplicates: 0 Warnings: 0 mysql> TRUNCATE CityCopy_T; Query OK, 0 rows affected (0.02 sec) mysql> DELETE FROM CityCopy_D; Query OK, 4079 rows affected (0.11 sec)
あまりスペックの良くないノートPC上のVM上でテストしているので、処理時間の絶対値はあまり重要ではありませんが、4,000行ちょっとの小さめのテーブルでも約20ミリ秒対約110ミリ秒と処理時間に明確な差が出てきます。
コラム：テーブルのコピーの作り方
今回のDELETE文とTRUNCATE文の性能比較のサンプルで、テーブルをコピーしてテストを行っていますが、テーブルのコピーには2つの方法があります。
テーブルの構成をコピーする
CRETE TABEL テーブル名 LIKE コピー元のテーブル名
テーブルのデータごとコピーする
CRETE TABEL テーブル名 SELECT * FROM コピー元のテーブル名
後者の方がテーブルの作成時にデータもコピーしてくれるので便利ですが、テーブルの構成を厳密にはコピーしてくれないので注意が必要です。以下では元のテーブル構成と、それぞれの構成を使ってコピーされたテーブルの構成を、SHOW CREATE TABLE文を使って確認しています。
SELECT文を使ったコピーではインデックスの定義や列のオプション(ここではID列のAUTO_INCREMENT、エンジンなどの各種設定が抜け落ちてしまうため、使用には注意が必要です。テーブルの構成をそのままコピーし、さらにデータを全てコピーする場合には、LIKEを使って構成をコピーし、INSERT ... SELECT ...で全てデータをコピーする必要があります。
ただし、LIKEを使用している場合でも、AUTO_INCREMENT=4081のようにコピーされない属性情報もあるので注意してください。
