MYSQL LAST_INSERT_ID is not working properly. How do i fix it?

Question

I wrote MySQL StoredProcedure to create and return a new identifier for each table value, however it gets the wrong value for last_insert_id () from the MySQL WorkBench and Java applications.

This procedure will be called from several sessions.

CALL `GET_NEW_ID`('test', @test); select @test; 

It gives me "141215000000", and that means last_insert_id () returns 0 all the time. I see that it correctly inserts new data into seq_data as intended.

 CREATE PROCEDURE `GET_NEW_ID`(IN V_TABLE VARCHAR(10), OUT V_ID VARCHAR(12)) BEGIN INSERT INTO seq_data ( id, `name`, `stat_date`) SELECT IFNULL(MAX(id), 0)+1, V_TABLE, DATE_FORMAT(NOW(),'%Y%m%d') FROM seq_data WHERE name = V_TABLE AND stat_date = DATE_FORMAT(NOW(),'%Y%m%d'); SET V_ID = concat(DATE_FORMAT(NOW(),'%y%m%d'),LPAD(LAST_INSERT_ID(), 6, '0')); END 

The table looks like this.

 CREATE TABLE `seq_data` ( `id` int(6) NOT NULL AUTO_INCREMENT, `name` varchar(20) COLLATE utf8_bin NOT NULL, `stat_date` varchar(8) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`,`name`,`stat_date`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; 

My goal is like ...

 CALL `GET_NEW_ID`('test', @test); select @test; 

return 141215000001

 CALL `GET_NEW_ID`('test', @test); select @test; 

return 141215000002

 CALL `GET_NEW_ID`('hello', @test); select @test; 

return 141215000001

Answer 1

As pointed out in the MySQL documentation , LAST_INSERT_ID () returns a BIGINT value (64-bit) representing the first automatically generated value that was set for the AUTO_INCREMENT column using the most recent INSERT statement executed to affect that column .

In your case, you are inserting an identifier, so the AUTO_INCREMENT value is not generated, so LAST_INSERT_ID returns 0.

You can try something like:

 CREATE PROCEDURE `GET_NEW_ID`(IN V_TABLE VARCHAR(10), OUT V_ID VARCHAR(12)) BEGIN INSERT INTO seq_data (`name`, `stat_date`) SELECT V_TABLE, DATE_FORMAT(NOW(),'%Y%m%d') FROM seq_data WHERE name = V_TABLE AND stat_date = DATE_FORMAT(NOW(),'%Y%m%d'); SET V_ID = concat(DATE_FORMAT(NOW(),'%y%m%d'),LPAD(LAST_INSERT_ID(), 6, '0')); END