MySQLのデータ更新(UPDATE)・削除(DELETE)を手動実行する際に、安全に実行する方法

つい先日、MySQLのデータ更新(UPDATE)する際に、where句の指定に誤りがあり、想定外のデータが更新されるというトラブルがあり….安全にデータ更新する方法を考えてみました。

ということで、MySQLのデータ更新(UPDATE)・削除(DELETE)を手動実行する際に、安全に実行する方法について書いていきます。

SQL文の記載を誤ると想定外のデータを更新・削除しちゃう怖い作業ですが、これをできるだけ安全にやる方法について記載していきます！

結論から書くとトランザクションを使って安全にデータ更新・削除していきます。

(トランザクションを使うのは基本ですが、、、改めて重要性に気づいたので記載していきます)

トランザクションの使い方

- トランザクションを始めるコマンド

begin;

ロールバックするコマンド(誤ったデータ更新をした際に、元に戻すコマンド)

rollback;

コミットするコマンド(正しくデータ更新をできた際に、実際にDBに反映するコマンド)

commit;

では次に、データ準備して、実際にロールバックとコミットをしていきます

データ準備

テーブル作成
- 以下のコマンドで、idとnameを持つ、userテーブルを作成

create table user (id int, name varchar(10));

データ作成
- 以下のコマンドで２レコード作成します

insert into user values (1, 'test_user1'),(2, 'test_user2');

トランザクションを使って安全にデータ更新(UPDATE)してみる

rollbackする場合


mysql> begin;
Query OK, 0 rows affected (0.00 sec)
### トランザクションをスタート
mysql> update user set name = 'test1';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0
### 更新件数を確認して、妥当性を確認

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
### ロールバックする

mysql> select name from user ;
+------------+
| name       |
+------------+
| test_user1 |
| test_user2 |
+------------+
2 rows in set (0.00 sec)
###ロールバックできたことを確認する

commitする場合


mysql> begin;
Query OK, 0 rows affected (0.00 sec)
### トランザクションをスタート

mysql> update user set name = 'test1' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
### 更新件数を確認して、妥当性を確認

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
### コミットする

mysql> select name from user ;
+------------+
| name       |
+------------+
| test1      |
| test_user2 |
+------------+
2 rows in set (0.00 sec)
###コミットできたことを確認する

トランザクションを使って安全にデータ削除(DELETE)してみる

rollbackする場合


mysql> begin;
Query OK, 0 rows affected (0.00 sec)
### トランザクションをスタート
mysql> delete from user;
Query OK, 2 rows affected (0.00 sec)
### 更新件数を確認して、妥当性を確認

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
### ロールバックする

mysql> select name from user ;
+------------+
| name       |
+------------+
| test_user1 |
| test_user2 |
+------------+
2 rows in set (0.00 sec)
###ロールバックできたことを確認する

commitする場合


mysql> begin;
Query OK, 0 rows affected (0.00 sec)
### トランザクションをスタート

mysql> delete from user where id = 1;
Query OK, 1 row affected (0.00 sec)
### 更新件数を確認して、妥当性を確認

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
### コミットする

mysql> select name from user ;
+------------+
| name       |
+------------+
| test_user2 |
+------------+
1 row in set (0.00 sec)
###コミットできたことを確認する