Research on MySQL auto-growth primary key and MySQL growth primary key

Research on MySQL auto-growth primary key and MySQL growth primary key

The keyword used for automatic MySQL growth isAUTO_INCREMENTBecause it is DDL, It is case insensitive. Columns used must be defined as keys, such as primary keys and unique keys.

The database used in this article is MariaDB 5.5.5

The default transaction isolation sector is the REPEATABLE-READ

The client is the HeidiSQL installed with MariaDB for Windows.

Community Free Version Download Page: https://downloads.mariadb.org/mariadb/

Create test table

Use the client to connect to the server. The user is root and the password is also root, for example:

mysql -h localhost -P 3306 -u root -proot

Select to switch database first:

USE `test`;

Create a test table:

Drop table if exists 'test _ auto'; create table 'test _ auto' ('id' int not null AUTO_INCREMENT, primary key ('id ')) COMMENT = 'test auto-incrementing 'COLLATE = 'utf8 _ general_ci' ENGINE = InnoDB;

Operations on Client 1

Use the new client 1 to connect to the server. The user is root and the password is also root. For example:

mysql -h localhost -P 3306 -u root -proot

Switch database:

USE `test`;

Then, in client 1, start the transaction and insert some data without committing.

# Execute begin client 1; insert into test_auto () values (); insert into test_auto () values (); insert into test_auto () values ();

In this case, you can use the query statement

SELECT * FROM `test`.`test_auto`;

We can see that we have 6 data records with an id of 1-6, corresponding to the number of SQL statements for data insertion. Because we have not submitted the request, this result can only be seen in client 1.

Client 2 Operations

Use the new client 2 to connect to the server. The user is root and the password is also root. For example:

mysql -h localhost -P 3306 -u root -proot

Switch database:

USE `test`;

Then, in client 2, start the transaction, insert some data, and do not commit.

# Execute begin client 2; insert into test_auto () values (); insert into test_auto () values (); insert into test_auto () values ();

In this case, you can use the query statement

SELECT * FROM `test`.`test_auto`;

We can see that five pieces of data are obtained, with the id 7-11, corresponding to the number of SQL statements for inserting data. Because we have not submitted the file, this result can only be seen in client 2.

Here we can see that the auto-increment primary key is globally unique. If a transaction is rolled back, the auto-increment part will not be affected. The auto-increment primary keys of multiple transactions do not affect each other, and they can be unique, but cannot guarantee that the final record is continuous.

Note:

Through operations on Client 1 and client 2, you can find that uncommitted transaction operations cannot be seen by other clients.

This isREPEATABLE-READTransaction isolation level. before the transaction is started and submitted, the client displays a snapshot of the moment when the transaction is started, plus the execution result of the operation in this session. Ensure that the transactions committed by other sessions are not affected during transaction execution.

If the transaction isolation level isREAD COMMITtEDOnly records that have been successfully submitted can be seen.

Query transaction isolation level:select @@tx_isolation

Client 3 operations

Use the new client 3 to connect to the server. The user is root and the password is also root. For example:

mysql -h localhost -P 3306 -u root -proot

Switch database:

USE `test`;

Then, use the query statement in client 3:

SELECT * FROM `test`.`test_auto`;

We can see that there is no data, because no data has been submitted.

Client 1 Operations-continued

Return to the Client 1 window and execute the query statement:

SELECT * FROM `test`.`test_auto`;

We can see that it is still the original six records. Then submit the transaction

commit;

Client 2 operations-continued

Return to the Client 2 window and execute the query statement:

SELECT * FROM `test`.`test_auto`;

We can see that there are still 5 original records. NextRollbackTransactions

rollback;

Then execute the query statement:

SELECT * FROM `test`.`test_auto`;

We can see 6 records (ID: 1-6). Why are there 6 records? This transaction ends at rollback, And the snapshot version is not read, but all visible data is read, that is, the data submitted by client 1.

Other operations

Next, we can see six records in client 1.

You can also perform several insert or transaction operations to query data and analyze the results.

Summary

The automatic growth of MySQL columns ensures that there are no duplicates, and there is no skip sign in the middle (of course, there are only some special businesses that need not skip ). Especially in the transaction execution environment, this processing method can only be used in order not to affect the logic and performance.

GitHub: https://github.com/cncounter/translation/blob/master/tiemao_2015/16_MySQL_AUTO_INCREMENT/MySQL_AUTO_INCREMENT.md

Author: Tie an http://blog.csdn.net/renfufei.

Date: January 1, June 01, 2015

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