如何使用数据将插入的ID创建数据摘要

Question

I've a MySQL function that would generate the identifier-ID for given table. The table have composite key;

id - Auto_increment
act_id - BigInt(16). Its the ID obtained from the function below.

This function returns BigInt(16)

BEGIN 
 DECLARE qid integer;
 SELECT max(id)+1 into qid from wi_activity;
 IF(qid IS NULL) THEN
    SET qid=1;
END IF;
RETURN convert(concat(6,DATE_FORMAT(CURDATE(),'%d%m%Y'),lpad(qid,7,'0')), unsigned integer);
END

Problem 1:

The website has multiple data-entry user who enter data in given table. There might be the case when multiple user tries to enter data simultaneously. This would read same value of ID that would generate same value of act_id.

Problem 2:

Before inserting to table, it is required to display all the data entered including the act_id key that it would acquire after insert and it also must prevent from duplicate act_id key generation.

How can these problem be solved?

Answer 1

You cannot reliably predict the ID value a record is going to have, except by actually inserting it into the database.

The best way to do it is to actually insert empty (or full, with some sort of status attached) rows into the database, and display their IDs in your page. Then later you can update those rows to be "accepted".

Any other approach will get you in trouble because only an actually inserted row has a reliable ID value.