What is the use of mysql auto-increment?

In database table design, we will struggle with the choice of "natural key" and "surrogate key". Natural keys are more complicated to implement when "soft deletion" of data. Some natural keys are caused by key values ​​that are too long or multiple column combinations. It is not suitable as a table primary key. The two more common surrogate keys are auto incremnet and globally unique identification columns (GUID). Next, I will talk to you about the auto-increment key.

Mysql uses the auto-increment numeric field provided by the database as the auto-increment primary key.

Advantages:

(1) The database is automatically numbered, fast, and grows incrementally. It is stored in order, which is very beneficial for retrieval;

(2) Numeric type, takes up little space, is easy to sort, and is convenient to transfer in the program;

(3) If you add records through a non-system, you don’t need to specify this field, and you don’t have to worry about duplicate primary keys.

After talking about the advantages, let’s talk about its shortcomings. In fact, its shortcomings come from its advantages.

Disadvantages:

(1) Because of automatic growth, it will be troublesome to manually insert records with specified IDs, especially when the system is integrated with other systems. When data needs to be imported, it is difficult to ensure that the IDs of the original system will not conflict with primary keys (provided that the old system is also digital). Especially when the new system goes online, the old and new systems exist in parallel, and the databases are heterogeneous. When two-way synchronization is required, auto-incrementing the primary key will be your nightmare;

(2) In the system During integration or cutover, if the primary key of the old and new systems is different in numeric type, the primary key data type will be modified, which will also lead to the modification of other tables associated with foreign keys, and the consequences will also be serious;

(3) If the system is also numeric, in order to distinguish old and new data when importing, you may want to add a character identifier (such as "o", old) before the primary key of the old data to indicate that this is old data, then the automatically growing numeric Another challenge.

The above is the detailed content of What is the use of mysql auto-increment?. For more information, please follow other related articles on the PHP Chinese website!