Shulou(Shulou.com)06/01 Report--

This article mainly introduces the method of mysql search self-increasing column, which has a certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

Mysql search self-increment method: 1, use [select max (id) from tablename]; 2, use [SELECT LAST_INSERT_ID ()]; 3, use [select @ @ IDENTITY]; 4, [SHOW TABLE].

Mysql searches for self-incrementing columns:

1. Select max (id) from tablename

2. SELECT LAST_INSERT_ID ()

LAST_INSERT_ID is independent of table. If you insert data into table an and then insert data into table b, the LAST_INSERT_ID will change.

Max (id) obviously does not work when multiple users insert data alternately. It's time to use LAST_INSERT_ID, because LAST_INSERT_ID is Connection-based, and as long as each thread uses a separate Connection object, the LAST_INSERT_ID function returns the ID of the first record generated by that Connection on the latest insert or update operation of the AUTO_INCREMENT column. This value cannot be affected by other clients (Connection), ensuring that you can retrieve your ID without worrying about the activities of other clients, and without locking. Multiple records are inserted using a single INSERT statement, and LAST_INSERT_ID returns a list.

3. Select @ @ IDENTITY

@ @ identity represents the value of the self-incrementing column corresponding to the last time data was inserted into a table with the identity attribute (that is, a self-incrementing column). It is a system-defined global variable. In general, system-defined global variables start with @ @, while user-defined variables start with @.

For example, there is a table A whose self-incrementing column is id. If the value of the self-incrementing column is automatically increased to 101after inserting a row of data into table A, the value obtained by select @ @ identity is 101. The premise of using @ @ identity is that the connection is not closed when select @ @ identity is executed after the insert operation, otherwise you will get a null value.

4 、 SHOW TABLE STATUS

The result shows that there is an Auto_increment field in the table name record, and the value of the next self-increment ID is the current maximum self-increment ID of the table.

Thank you for reading this article carefully. I hope the editor will share the content of the self-adding method of mysql search is helpful to everyone. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

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.