How to kill two birds with one mysql query?

Question

PHP

Hello!
There are two mysql queries:

SELECT * FROM emails WHERE status=0 order by rand() limit 1
UPDATE emails SET status='1' WHERE id=5111

That is, the first request retrieves a random entry from the database, and the second one blocks it by changing the status
id field in the second request is substituted using php
. Is it possible to somehow combine this into one request?
At the same time, we should still receive a random entry in response

Answer 1

If you need to select data by parallel scripts, then this option works reliably:

UPDATE `emails` SET `status` = 1 WHERE @id := `id` AND `status` = 0 ORDER BY RAND() LIMIT 1;
SELECT * FROM `emails` WHERE `id` = @id;

But sorting by RAND () is very slow, it is better to refuse it if possible.

Answer 2

UPDATE emails SET status='1' WHERE id=(SELECT id FROM emails WHERE status=0 order by rand() limit 1)

Answer 3

1. Set the proccess field of the double type to 0 in the table by default.
2. In the PHP script, set $process_id = microtime(TRUE) + getmypid(); to get a unique process ID for each script call.
3. UPDATE `emails` SET `process` = $process_id WHERE `process` = 0 LIMIT 1
4. SELECT * FROM `emails` WHERE `process` = $
process_id one data for processing. And you, as I understand it, solve just such a problem.

Answer 4

why would you want to block it. It seems to me that you want to solve your problem initially in the wrong way.

Answer 5

UPDATE emails SET status=1, id=LAST_INSERT_ID(id) order by RAND() LIMIT 1

You can then select the updated record via SELECT LAST_INSERT_ID() or mysql_last_insert_id()

Answer 6

to wrap a call of requests in transaction will not approach?