MySQLのdeleteに関する独自仕様についてまとめています。
以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。
MySQLのdeleteの構文
DELETE FROM テーブル名 [ WHERE 条件 ] [ ORDER BY ソート指定 ] [ LIMIT 件数制限 ] ※[]内は省略可能
insert文にて、下記のデータをemployees（社員）テーブルに挿入します。SQLコードは、下記の記事掲載のSQLサンプルをそのまま実行しました。
【関連記事】
▶MySQL Insert 3つの構文の違いとは？ 一文で一括挿入する方法
DELETE FROM `employees` WHERE emp_no < 3
上記のSQLは、employees（社員）テーブルから、emp_no（社員番号）が3より小さいレコードを削除します。
実行すると、2 行削除しました。 (Query took 0.0228 seconds.)と表示され、実行後のemployeesテーブルはこうなります。
MySQLのdelete独自仕様
SQL規格にない、MySQLの独自仕様について見て行きましょう。
【関連記事】
▶SQLとはどういうもの？ 独自拡張と標準SQLの大きな違いって、何？
複数テーブルの削除
MySQLでは、下記の構文で複数の異なるテーブルを同時にdelete可能です。
DELETE テーブル名1,テーブル名2[,…] FROM テーブルの結合条件 [ WHERE 条件 ] ※[]内は省略可能
オリジナルのテーブルを削除しないように、create tableでemployees（社員）テーブルと、dept_emp（部署・社員紐付けテーブル）、departments（部署）テーブルのコピーを作ります。
MySQLAdminの操作→テーブルを (データベース).(テーブル) にコピーするでテーブル構造とデータをコピーします。employees→employees_copy、dept_emp→dept_emp_copy、departments→departments_copyの３つのテーブルを作ります。
以下のSQLは、所属部署（departments_copyのdept_name）が「Marketing」の社員のemployees_copy（社員テーブルのコピー）と、dept_emp_copy（部署・社員紐付けテーブルのコピー）を削除するSQLです。
DELETE employees_copy, dept_emp_copy FROM employees_copy left join dept_emp_copy on employees_copy.emp_no = dept_emp_copy.emp_no left join departments_copy on dept_emp_copy.dept_no = departments_copy.dept_no WHERE departments_copy.dept_name = 'Marketing'
実行すると、「40422 行削除しました。 (Query took 0.3944 seconds.)」と表示され、departments_copyとemployees_copyの2テーブルから、条件にマッチした行が削除されます。
order byとlimitで削除する順番を指定する
削除対象のテーブルが1テーブルの場合に限り、order byとlimitが使用可能です。
下記のSQLは、employees_copy（社員テーブルのコピー）のうち、emp_no（社員番号）が20,000より大きいデータをbirth_date（誕生日）、emp_no（社員番号）を昇順にソートした結果を出力します。
SELECT * FROM `employees_copy` WHERE emp_no > 20000 order by birth_date,emp_no
結果はこうなります。
先頭から3件のみ（赤枠の部分）のみ削除してみます。
DELETE FROM `employees_copy` WHERE emp_no > 20000 order by birth_date,emp_no limit 3
実行後に、上記のselect文を実行するとこうなります。
狙い通り、ソート結果の先頭から3件のデータのみ削除できました。
自動インクリメントカラムは、削除後もリセットされない
動作を確認するため、新規にテーブルを作成しました。
テーブル作成用のSQLは以下の通りです。
int型のカラムno（プライマリキー）と、text型のカラムテキストがあり、noはAuto Incrementを指定しています。
CREATE TABLE `deltest` ( `no` int(11) NOT NULL, `txt` text NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; ALTER TABLE `deltest` ADD PRIMARY KEY (`no`); ALTER TABLE `deltest` MODIFY `no` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6;
5件のデータをinsertしておきます。テーブルの中身はこうなっています。
deltestテーブルの内容
このテーブルに対して、no=4、no=5の行を削除し、1行insertをおこなうとどうなるか試してみましょう。自動インクリメントカラムのリセットが行われないとすると、no=4、no=5の行が消え、no=6の行が挿入されるはずです。
以下のSQLは、deltestテーブルからno=4またはno=5の行を削除します。その後、no=指定なし、txt=tuikaでdeltestテーブルにinsertをおこないます。
DELETE FROM `deltest` WHERE no=4 or no=5; insert deltest set txt="tuika";
deltestテーブルの内容
想定通りに、Auto Incrementの値はリセットされず、no無指定で挿入すると、no=6の行が挿入されました。
エイリアスは、from内のtable参照部でのみ宣言できる
asを使ったエイリアスは、使用できる箇所が決まっていてtalbe参照部以外で使うとエラーになります。
delete employees_copy as a1, dept_emp_copy as a2 FROM a1 inner join a2 on a1.emp_no=a2.emp_no WHERE a1.emp_no > 30000
上記を実行すると、「#1064 – You have an error in your SQL syntax」と、シンタックスエラーになります。
下記のSQLは、正しく実行できます。
delete a1, a2 FROM employees_copy as a1 inner join dept_emp_copy as a2 on a1.emp_no=a2.emp_no WHERE a1.emp_no > 30000
まとめ
- MySQLのdelete文は、1クエリで複数テーブルの削除が可能
- 単テーブルに限り、order byとlimitが使用可能
- 自動インクリメントカラムのカウンタは、delete実行後でもリセットされない
- delete文では、エイリアス指定の箇所によってはSyntax errorとなる
