0

I'm trying to understand the best-practice for performing DML operations on multiple rows, then returning the updated rows in MySQL with the following criteria:

  • Works on MySQL 8.0, with 5.7 as nice-to-have
  • Cannot use a Stored Procedure or function, as the queries will be dynamically generated and not statically analyzable
  • Temporary Tables are okay
  • Issuing 2-3 queries to complete the statement is okay

What I understand about the limitations of MySQL:

  • No support for RETURNING clauses
  • Has a function, LAST_INSERT_ID() which can return the last inserted id for AUTO_INCREMENT PK columns

The following I believe works for AUTO_INCREMENT-containing tables. But how do you do this if a table does not have an AUTO_INCREMENT key?

LOCK TABLE my_table WRITE;
INSERT INTO my_table (col_a, col_b, col_c) VALUES (1,2,3), (4,5,6), (7,8,9);
SET @row_count = ROW_COUNT();
SET @last_insert_id = LAST_INSERT_ID();
UNLOCK TABLES;
SELECT id FROM my_table WHERE id >= @last_insert_id AND id <= @last_insert_id + (@row_count - 1);

EDIT: I believe this question should remain open for the following reasons:

  • The answer linked as a duplicate only talks about update statements
  • The code in the answer only appears to work for updating a single row, attempting to use that code to modify multiple rows results in a deprecation warning and a wrong-type failure:
SET @uids := null;
UPDATE Track
SET Name = 'New Name', Composer = 'New Composer'
WHERE TrackId > 5
  AND ( SELECT @uids := CONCAT_WS(',', TrackId, @uids) );
SELECT @uids;

[2023-01-27 12:55:56] [22001][1292] Data truncation: Truncated incorrect DOUBLE value: '7,6'
[2023-01-27 12:55:56] [HY000][1287] Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
[2023-01-27 12:55:56] [22007][1292] Truncated incorrect DOUBLE value: '7,6'

This question is asking about:

  • Code that works for generalized DML operations
  • Returning Multiple rows
  • The possibility that the table has no Primary Key
Gavin Ray
  • 595
  • 1
  • 3
  • 10
  • FWIW, you don't need LOCK TABLES in your example. the ROW_COUNT and LAST_INSERT_ID are scope to your session, so no other sessions can affect them. – Bill Karwin Jan 27 '23 at 17:33
  • Other possible duplicate: https://stackoverflow.com/questions/15190032/sql-returning-values-how-to-do-it – Bill Karwin Jan 27 '23 at 17:37
  • Doesn’t give you a direct solution, but maybe automatic created and updated at timestamps are good enough for your use case? https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html. You could also include a query or client ID column if there are concurrent operations. – hundredwatt Jan 28 '23 at 19:09

0 Answers0