Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use MySQL's LAST_INSERT_ID to determine the unique ID value of each sub-table, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

In the MySQL data table structure, in general, an ID' field with a 'AUTO_INCREMENT' extension property' is defined to ensure that each record of the data table can be uniquely identified with this ID

With the continuous expansion of data, in order to improve query performance and reduce query hotspots, a table is generally divided into multiple data tables according to certain rules, that is, the so-called sub-tables.

Except for the index of the table name, the table structure is the same, and if the 'ID' field' of each table is still in the way of 'AUTO_INCREMENT'', ID cannot determine only one record.

At this time, we need a mechanism outside each sub-table to generate ID. We generally use a separate data table (suppose the table is called 'ticket_mutex') to save the ID. No matter which sub-table has data to increase, we first go to the ticket_ mutex table to add 1 to the ID value, and then get the ID value.

The operation of fetching ID seems complicated, but fortunately, MySQL provides a LAST_INSERT_ID mechanism that allows us to do it in one step.

1. Create a new data table ticket_mutex

View sourceprint?CREATE TABLE ticket_mutex (

Name varchar (32) NOT NULL PRIMARY KEY COMMENT Business name

Value bigint (20) UNSIGNED NOT NULL COMMENT ID value

) Engine=InnoDB DEFAULT CHARSET=UTF8 COMMENT saves sub-table ID table

The field 'name'' is used to indicate which business the ID belongs to. For example, the ID of the 'user' can be defined as' USER''.

The field 'value' is the ID value of the service.

2. Initialize the business and its ID value

View sourceprint?INSERT INTO ticket_mutex (name, value) values (USER, 0), (POST, 0)

+-+ +

| | name | value |

+-+ +

| | POST | 0 | |

| | USER | 0 | |

+-+ +

We initialize two records, that is, there are two different services, which represent 'user information' and 'subject information' respectively, and their initial ID values are all'0'.

3. Get the unique ID of the sub-table

This is the time to take advantage of the LAST_INSERT_ID () mechanism provided by MySQL.

When adding a piece of data to the user table, get the 'user ID':

View sourceprint?UPDATE ticket_mutex SET value=LAST_INSERT_ID (value+1) WHERE name=USER;SELECT LAST_INSERT_ID ()

+-+

| | LAST_INSERT_ID () |

+-+

| | 1 |

+-+

After passing this statement, we get a result of 1, which is the value we need. Looking at the data records, we find that the total number of records has not changed, but the ID of the 'user' is already 1.

View sourceprint?+-+-+

| | name | value |

+-+ +

| | POST | 0 | |

| | USER | 1 | |

+-+ +

In the same way, we can get the ID of the 'topic':

View sourceprint?UPDATE ticket_mutex SET value=LAST_INSERT_ID (value+1) WHERE name=POST;SELECT LAST_INSERT_ID ()

+-+

| | LAST_INSERT_ID () |

+-+

| | 1 |

+-+

View all records:

View sourceprint?+-+-+

| | name | value |

+-+ +

| | POST | 1 | |

| | USER | 1 | |

+-+ +

As can be seen from the above, through the LAST_INSERT_ID mechanism of MySQL, we can ensure that the business ID is constantly increasing without increasing the total number of records, thus ensuring the uniqueness of the sub-table ID.

4. LAST_INSERT_ID description

As can be seen from the name, LAST_INSERT_ID is the last ID value inserted. According to the official manual of MySQL, there are two ways to use it.

First, there is no parameter: LAST_INSERT_ID (). This method is used with the AUTO_INCREMENT attribute. When a record is added to a table with a 'AUTO_INCREMENT' attribute field, LAST_INSERT_ID () returns the value of the field. You can try it (I have verified it).

The second is with an expression: LAST_INSERT_ID (value+1), as described above, which returns the value of the expression, that is, 'value+1'

The above is all the contents of the article "how to use MySQL's LAST_INSERT_ID to determine the unique ID value of each sub-table". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.