insert with select, get inserted value

Question

I am using specific query for generating ids without autoincrement:

'insert into global_ids (`id`) select (((max(id)>>4)+1)<<4)+1 from global_ids'


CREATE TABLE `global_ids` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I have problem of fetching last inserted row id (because i am not using autoincrement, cur.lastrowid is None)

How can i fetch result of inserted row id value in my case?

Answer 1

If you don't care about race conditions, you can get the highest id value in the table with SELECT MAX(id) AS id FROM global_ids.

But, you do care about race conditions. What happens if two copies of your python program are running? One may do an insert, then another may do an insert before the first one does the select I suggested. If that happens, the select will return a wrong value.

You could try

     START TRANSACTION;
     SELECT (((max(id)>>4)+1)<<4)+1 INTO @id FROM global_ids WITH UPDATE;
     INSERT INTO global_ids (id) VALUES (@id);
     COMMIT;
     SELECT @id AS id;

to serialize your id generation / retrieval operation with transactions. (This won't work in MyISAM tables, because they ignore transactions.)

But, your best bet to avoid race condition hassles in MySQL is to actually use the autoincrementing feature. You could try this.

CREATE TABLE global_ids (
     id INT NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (`id`)
) 

Then issue these queries one after the other:

INSERT INTO global_ids () VALUES ();
SELECT (((LAST_INSERT_ID()>>4)+1)<<4)+1 AS id;

That will use the auto-incrementing feature to generate your transformed id values without running the risk of race conditions.