非自增ID场景下，如何获取INSERT...SELECT语句插入的ID值？

Since you're using a custom bit-shift logic to generate IDs instead of relying on auto-increment columns, cur.lastrowid won't work— that method is specifically tied to database-generated auto-increment values. Here are three reliable approaches to get your inserted ID safely:

1. Use the RETURNING Clause (MySQL 8.0.19+) #

If you're running a recent enough MySQL version, this is the cleanest and most efficient solution. Modify your insert statement to return the generated ID directly in the same operation:

INSERT INTO global_ids (`id`)
SELECT (((max(id)>>4)+1)<<4)+1 FROM global_ids
RETURNING id;

After executing this query in your code, you can fetch the returned id value straight from the result set—no extra queries needed.

2. Calculate and Reuse the ID with User Variables #

For older MySQL versions that don't support RETURNING, you can compute the ID first using a user variable, insert it, then select the variable. This keeps everything in a single atomic batch to avoid race conditions:

SET @new_id = (((SELECT MAX(id) FROM global_ids) >> 4) + 1) << 4 + 1;
INSERT INTO global_ids (`id`) VALUES (@new_id);
SELECT @new_id;

Run these statements as a single batch in your code, then retrieve the value of @new_id from the final select result.

3. Pre-Calculate the ID with Transactional Locking #

If you need to split the calculation and insertion into separate steps, you must handle concurrency to prevent duplicate ID collisions. Wrap the operations in a transaction and use a row lock to block other sessions while you work:

BEGIN;
-- Lock the table to prevent other writes during ID calculation
SELECT (((max(id)>>4)+1)<<4)+1 AS new_id FROM global_ids FOR UPDATE;
-- Insert the pre-calculated ID
INSERT INTO global_ids (`id`) VALUES (@new_id);
COMMIT;

In your code, first run the select to get new_id, then use that value for the insert. The FOR UPDATE lock ensures no other session can modify the global_ids table during your transaction, so your calculated ID stays valid.

Critical Note #

Never split the MAX(id) calculation and insert without locking—if multiple processes run the MAX(id) query at the same time, they'll generate identical IDs, leading to a primary key violation. Always prioritize atomic operations or transactional locking for concurrency safety.

内容的提问来源于stack exchange，提问作者Evg