MySQL データ削除編

データの削除

削除する場合は、WHERE で条件を絞って対象となるデータを自動で抽出するにせよ、事前にデータを確認してからやるのが基本。削除コマンドは DELETE である。

mysql> SELECT * FROM cocktail WHERE id = 1;
+----+-------------+-------+-------------------------------+
| id | name        | base  | source                        |
+----+-------------+-------+-------------------------------+
|  1 | Bloody Mary | Vodka | Vodka 45ml, some Tomato Juice |
+----+-------------+-------+-------------------------------+
1 row in set (0.07 sec)

mysql> DELETE FROM cocktail WHERE id = 1;
Query OK, 1 row affected (0.18 sec)

mysql> SELECT * FROM cocktail WHERE id = 1;
Empty set (0.00 sec)

私的に覚えづらいのが WHERE というオプション。もうちょっと何とかならないのか・・・という気もするが、これはこれでアリのような気もするし。まあ覚えてしまえばいいだけの話か。ちなみに WHERE オプションは SELECT などにも用いることが可能。

上記のコマンドは、cocktail というテーブルの、id が 1 のデータを削除する、という意味だ。「DELETE FROM cocktail WHERE name = 'Bloody Mary';」なんてのも OK。

その際気をつけなければならないのが、「Bloody Mary」というデータが複数あった場合、皆消されてしまうということだ。だから確実にひとつだけ削除したい場合は、ユニークなキーである id で消すことをお勧めする。

SELECT コマンドで cocktail を見ると、データがなくなっているのが確認できる。

なお、データベースから特定のレコードを削除すると、オーバーヘッド（未使用領域、ゴミ）が作られてしまう。そのため、DELETE したら最適化を行う必要がある。

mysql> OPTIMIZE TABLE cocktail;
+-----------------+----------+----------+----------+
| Table           | Op       | Msg_type | Msg_text |
+-----------------+----------+----------+----------+
| bloody.cocktail | optimize | status   | OK       |
+-----------------+----------+----------+----------+
1 row in set (0.14 sec)

id が歯抜けの場合、新しいデータはどこに入るか？

mysql> SELECT * FROM cocktail ORDER BY id;
+----+-------------+-----------+---------------------------------+
| id | name        | base      | source                          |
+----+-------------+-----------+---------------------------------+
|  6 | Bloody Mary | Vodka     | Vodka 45ml, some Tomato Juice   |
|  7 | Shandy Gaff | Beer      | Beer Beer 50%, Ginger ale 50%   |
|  8 | Mimosa      | Champagne | Champagne 50%, Orange Juice 50% |
+----+-------------+-----------+---------------------------------+
3 rows in set (0.00 sec)

このようなデータが入っていたとする。 id = 7 を削除してから、 Gin Tonic を追加すると、 id 何番目に入るかどうか検証してみよう。

mysql> DELETE FROM cocktail WHERE id = 7;
Query OK, 1 row affected (0.00 sec)

OPTIMIZE TABLE cocktail;
+-----------------+----------+----------+----------+
| Table           | Op       | Msg_type | Msg_text |
+-----------------+----------+----------+----------+
| bloody.cocktail | optimize | status   | OK       |
+-----------------+----------+----------+----------+
1 row in set (0.07 sec)

mysql> INSERT INTO cocktail VALUES(NULL, 'Gin Tonic', 'Gin', 'Gin 45ml, some Tonic Water');
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM cocktail ORDER BY id;
+----+-------------+-----------+---------------------------------+
| id | name        | base      | source                          |
+----+-------------+-----------+---------------------------------+
|  6 | Bloody Mary | Vodka     | Vodka 45ml, some Tomato Juice   |
|  8 | Mimosa      | Champagne | Champagne 50%, Orange Juice 50% |
|  9 | Gin Tonic   | Gin       | Gin 45ml, some Tonic Water      |
+----+-------------+-----------+---------------------------------+
3 rows in set (0.00 sec)

データは、id = 7 を飛び越え、9 番目に入った。

連番でデータが入っていないと嫌だ、という几帳面な人もいるだろう。だが、そういうことは気にしてはいけない。削除したデータも id で管理しているのだから ( id は PRIMARY_KEY で 1 ずつ AUTO_INCREMENT する ) 。

どうしても気になる人は、 id の Field を作るときに PRIMARY_KEY にしないで、 id も単なるカラムとして ( name や base , source と同じように ) 扱えばよい。

AUTO_INCREMENT の値をリセットする方法

どうしても ID が連続してないとイヤという几帳面な人もいるだろう。 上記の方法 ( id を PRIMARY_KEY にしない。 AUTO_INCREMENT もしない ) が採用できないなら、全てのデータをいったん削除した上で、「AUTO_INCREMENT の値をリセットする方法」を実行し、新たに連続してデータを書き込む必要がでてくる。データ数が少ないうちはいいが、100 件 ( 数はテキトー ) を越えてくると処理的に厳しくなる。第一ログたまりまくり。

mysql> SELECT * FROM cocktail;
+----+-------------+-----------+---------------------------------+
| id | name        | base      | source                          |
+----+-------------+-----------+---------------------------------+
|  1 | Bloody Mary | Vodka     | Vodka 45ml, some Tomato Juice   |
|  2 | Shandy Gaff | Beer      | Beer Beer 50%, Ginger ale 50%   |
|  3 | Mimosa      | Champagne | Champagne 50%, Orange Juice 50% |
+----+-------------+-----------+---------------------------------+
3 rows in set (0.00 sec)

mysql> DELETE FROM cocktail WHERE id = 2;
Query OK, 1 row affected (0.00 sec)

この状態で次のデータを入力すると、 id = 4 から入ってしまい、 id = 2 が歯抜けになる。それを回避するために、いったん全データを削除する。

mysql> DELETE FROM cocktail;
Query OK, 2 rows affected (0.07 sec)

次に、 ALTER TABLE を実行して、 id の値をリセットしてやる。

mysql> ALTER TABLE cocktail PACK_KEYS = 0 CHECKSUM = 0 DELAY_KEY_WRITE = 0 AUTO_INCREMENT = 1;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

その後、全データを書き込む。

mysql> INSERT INTO cocktail VALUES(NULL, 'Bloody Mary', 'Vodka', 'Vodka 45ml, some Tomato Juice');
mysql> INSERT INTO cocktail VALUES(NULL, 'Mimosa', 'Champagne', 'Champagne 50%, Orange Juice 50%');
mysql> INSERT INTO cocktail VALUES(NULL, 'Gin Tonic', 'Gin', 'Gin 45ml, some Tonic Water');

mysql> SELECT * FROM cocktail;
+----+-------------+-----------+---------------------------------+
| id | name        | base      | source                          |
+----+-------------+-----------+---------------------------------+
|  1 | Bloody Mary | Vodka     | Vodka 45ml, some Tomato Juice   |
|  2 | Mimosa      | Champagne | Champagne 50%, Orange Juice 50% |
|  3 | Gin Tonic   | Gin       | Gin 45ml, some Tonic Water      |
+----+-------------+-----------+---------------------------------+
3 rows in set (0.00 sec)

できたはできたが、ご覧の通り、非常に効率が悪い。