[MySQL]Updateしたデータをselectしたい

Question

題名の通りです。

update の際の where句の条件では 複数が該当するのですが、
limit 1 で 1件しか更新していません。

更新されたデータを取得したいのです。

###自分なりにしてみたこと
最終更新日時を記録したカラムを作って それが最も古いデータを select した。
→ 同じ時刻に update した人がいたらおかしくなる。

insert の last_insert_id(); にあたる方法みたいなのはないのでしょうか？
それとも update と select を1文にして 同時にする方法など...

お回答お待ちしております。

Answer 1

既に「解決済み」となっていますが、、

ご想像の通り、SELECT ... FOR UPDATE 構文を使うことで、望む動作を実現できます。
（ただし、操作対象のテーブルのエンジンが InnoDB の場合に限ります）
https://dev.mysql.com/doc/refman/5.6/ja/innodb-locking-reads.html

具体的には、以下のように使用します。

mysql> CREATE TABLE test (
    ->   id INT PRIMARY KEY AUTO_INCREMENT,
    ->   value INT
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO test (value) VALUES (1), (2), (2), (4), (7);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  3 |     2 |
|  4 |     4 |
|  5 |     7 |
+----+-------+
5 rows in set (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id FROM test WHERE value = 2 LIMIT 1 FOR UPDATE;
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)


#1 
#   ここで、別のターミナルなどから testテーブルの id = 2 を含むレコードに対して
#   UPDATE/DELETE または SELECT ... FOR UPDATE 文を実行


mysql> UPDATE test SET value = 10 WHERE id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |    10 |
|  3 |     2 |
|  4 |     4 |
|  5 |     7 |
+----+-------+
5 rows in set (0.00 sec)

上述の#1部分で、別のターミナルなどから同じ MySQL にログインし、testテーブルの id = 2 を含むレコードに対して UPDATE/DELETE または SELECT ... FOR UPDATE 文を実行してみてください。
COMMITするまで、SQL文の実行がブロックされる（応答が返ってこない）ことが確認できると思います。

Answer 2

主キー が割り当てられているので まずは updateしたい条件で select し、
主キーで該当データを削除、
変更を加えたデータを insert しました。

しかし この方法では 同時にアクセスがあった場合に 不具合が起きます。

select for update を使用して 先に情報取得→更新 とすればいけるかも...?

Answer 3

複数条件をlimitで処理しているならorder byしているはずですから
おなじorder byをかけてlimit 1でデータをとればよいのでは？

sample

timestampで管理

create table tbl(id int not null primary key,val int ,ts timestamp);
insert into tbl(id,val) values(1,100),(2,150),(3,300);
update tbl set val=400 where id=2;
select * from tbl order by ts desc limit 1;

Comments

[pac894398]

データが他から書き換えられたりしたら 困るんですね...。

[pac894398]

[追記] where 句で 検索条件にしているパラメータも update しております。

[yambejp]

まぁ普通最新データをとりたいならtimestampカラムを作っておくような気がしますけどね

[KiyoshiMotoki]

その"sample"だと、全てのカラム ts の値は NULL にしかなりませんよ。

mysql> create table tbl(id int not null primary key,val int ,ts timestamp);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into tbl(id,val) values(1,100),(2,150),(3,300);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from tbl;
+----+------+------+
| id | val | ts |
+----+------+------+
| 1 | 100 | NULL |
| 2 | 400 | NULL |
| 3 | 300 | NULL |
+----+------+------+
3 rows in set (0.00 sec)

[KiyoshiMotoki]

失礼しました。

私の環境では、システム変数 explicit_defaults_for_timestamp が ON になっていました。
（デフォルトは OFF）

この変数の値が OFF だと、自動的に現在時刻が格納されるのですね。

https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvar_explicit_defaults_for_timestamp

mysql> set @@local.explicit_defaults_for_timestamp = 0;
Query OK, 0 rows affected (0.06 sec)

mysql> create table tbl(id int not null primary key,val int ,ts timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tbl(id,val) values(1,100),(2,150),(3,300);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> update tbl set val=400 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from tbl;
+----+------+---------------------+
| id | val | ts |
+----+------+---------------------+
| 1 | 100 | 2016-11-28 12:29:41 |
| 2 | 400 | 2016-11-28 12:29:45 |
| 3 | 300 | 2016-11-28 12:29:41 |
+----+------+---------------------+
3 rows in set (0.00 sec)

[yambejp]

KiyoshiMotokiさん、フォローありがとうございます

explicit_defaults_for_timestampの設定があるのですね
今後はユーザー環境を考慮して、timestampについて記載をするときには
設定についても補足するようにします。