MySQL implements ID generator with last_insert_id

Learn last_insert_id First

• LAST_INSERT_ID has its own storage space, can save a number

• Returns the value of the self-increment field for the last insert row record without parameters. With parameters, the number you store will be brushed into the value given by the parameter.

In combination with the sample, see:

--This condition is normal, the LAST_INSERT_ID output value will increaseINSERT  into' Advertise_ipad ' (' Path ', ' status ')VALUES('TestValue',1);SELECTLAST_INSERT_ID ();--Output 1 --If the Auto_increment field is actively assigned, the output value does not increaseINSERT  into' Advertise_ipad ' (' id ', ' path ', ' status ')VALUES(' -','TestValue',1);SELECTLAST_INSERT_ID ();--Output 1INSERT  into' Advertise_ipad ' (' Path ', ' status ')VALUES('TestValue',1);--The auto_increment is then brushed to 100, and if you perform an insert without an ID, the next ID will become 101,LAST_INSERT_ID 101SELECTLAST_INSERT_ID ();--Output 101 --If the Auto_increment field is actively assigned, and the value is smaller than the current self-increment, the result of last_insert_id will not be changedINSERT  into' Advertise_ipad ' (' id ', ' path ', ' status ')VALUES(' -','TestValue',1);SELECTLAST_INSERT_ID ();--Output 101INSERT  into' Advertise_ipad ' (' Path ', ' status ')VALUES('TestValue',1);--At this time auto_increment is still 101, will not be affected by 60, and then do not have an ID of the insertion, the next ID and last_insert_id will be 102SELECTLAST_INSERT_ID ();--Output 102 --perform a BULK INSERT, although 103, 104, 105, three records are inserted, but last_insert_id will return the first, i.e. 103INSERT  into' Advertise_ipad ' (' Path ', ' status ')VALUES('TestValue',1), ('TestValue',1), ('TestValue',1);SELECTLAST_INSERT_ID ();--Output 103INSERT  into' Advertise_ipad ' (' Path ', ' status ')VALUES('TestValue',1);--insert a record again, since the increment has reached 105, the next line will become 106,last_insert_id and become 106SELECTLAST_INSERT_ID ();--Output 106 --if the last_insert_id method with parameters is executed, it will be savedSELECTLAST_INSERT_ID ( -);SELECTLAST_INSERT_ID ();--OutputINSERT  into' Advertise_ipad ' (' Path ', ' status ')VALUES('TestValue',1);--insert another record, since the increment or 106, the next line will become 107,last_insert_id also become 107SELECTLAST_INSERT_ID ();--Output 107

The function is simple.

To achieve an ID generator that facilitates different business access, we need a table that manages the different dimension IDs, such as

CREATE TABLE ' Tbl_id_factory ' (  tinyint(3notNULL,  bigint (notNULL,  PRIMARYKEY  (' Id_ Type ')) ENGINE=DEFAULT CHARSET=UTF8;

Create a MySQL function to implement

CREATE FUNCTION TINYINT RETURNS bigint (deterministic) BEGIN    UPDATE SET  last_insert_id (id_value+WHEREid_type_index;    RETURN last_insert_id (); END

Only SELECT next_id (business type) is called at the top level. Can

Reference Document: Https://dev.mysql.com/doc/refman/8.0/en/mysql-insert-id.html

MySQL implements ID generator with last_insert_id