MYSQL LAST_INSERT_ID not working as intended. How do I fix?

Question

I wrote MySQL StoredProcedure to create and return new ID for each table value, however, it gets wrong value on last_insert_id() from MySQL WorkBench and Java application.

This procedure will be called from multiple sessions.

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

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

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

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 stated in the MySQL documentation, LAST_INSERT_ID() returns a BIGINT (64-bit) value representing the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement to affect such a column.

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

You may 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