Mysql auto-incremental id acquisition and resetting

Mysql auto-incremental id acquisition and resetting

Reprinted please indicate the source: curtain roll west wind column (http://blog.csdn.net/ljxfblog)

Several Methods for mysql to obtain auto-increment IDS

• Use the max function: select max (id) from tablename
  
  • Advantage: easy to use.
  • Disadvantage: The obtained result is not the real auto-increment id, but the largest Id in the table. If data is deleted, the difference between the value and the auto-increment id is large. If the table data is connected, data disorder may occur.
• Use the LAST_INSERT_ID function: select LAST_INSERT_ID ()
  
  • Advantage: the real auto-increment id is obtained.
  • Disadvantage: this function is irrelevant to the table and always retains the id of the newly inserted auto-increment column. If you operate on multiple clients and cannot guarantee atomicity, this value may be incorrect. Therefore, it must be called after insertion.
• Use the @ IDENTITY variable: select @ IDENTITY
  
  • This method is similar to LAST_INSERT_ID () and has the same advantages and disadvantages. It must be called after insertion.
• Use the mysql query function: show table status;
  
  • Advantage: the auto-increment id can be found accurately. In addition, you can add the where statement or like statement after the statement to filter.
  • Disadvantage: This statement returns a record set and cannot return auto-increment values separately. Therefore, additional operations are required.
• Use the custom query method: mysql table information is stored in the information_schema table. Therefore, we can refer to show table status to construct query statements. For example:
  
  • Select Auto_increment into autoId from information_schema.tables where Table_Schema = 'dbname' and table_name = 'tablename ';
  • Advantage: It is highly customized and can accurately obtain any desired information.
  • OK: it is hard to remember. Haha.

Reset the mysql auto-increment id

• Use truncate: truncate table;
  
  • Note: Using truncate will delete the table data and release space, and reset the word auto-increment id, but will not delete the table definition.
  • Usage: it can be used only when the table needs to be cleared.
• Use the modified ID: dbcc checkident ('table _ name', reseed, new_reseed_value );
  
  • Note: new_reseed_value is the current identifier. The next insertion starts with new_reseed_value + 1. If new_reseed_value is smaller than the value currently identified in the table, duplicate keys may be generated and error 2627 may be generated.
  • Use: This method does not clear existing data. It is applicable to modifying new IDs after a large number of deletions, but cannot be smaller than the maximum value in the current table.
• Alter table tablename auto_increment = new
  
  • Note: This method is similar to the preceding two methods. The data is not cleared, and duplicate keys may occur. Therefore, this method is only applicable to Resetting the auto-increment id after clearing the table or modifying the auto-increment id after a large number of deletions.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.