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

Reprint http://blog.csdn.net/fwkjdaghappy1/article/details/7663331auto_increment Basic Features

The AUTO_INCREMENT attribute in MySQL is used to automatically generate ID functions for records in a table, which can replace sequence in Oracle, PostgreSQL and other databases to a certain extent.

In database applications, we often use unique numbers to identify records. In MySQL, it can be generated automatically by the AUTO_INCREMENT attribute of the data column.

You can specify a self-increasing initial value with the AUTO_INCREMENT=n option when creating a table.

The alter table_name AUTO_INCREMENT=n command can be used to reset the starting value of the increment.

When inserting records, if a value is explicitly specified for the AUTO_INCREMENT data column, two things happen:

Case 1: If the inserted value is a duplicate of an existing number, an error message will appear, because the value of the AUTO_INCREMENT data column must be unique;

Case two, if the inserted value is greater than the numbered value, it is inserted into the data column so that the next number will increment from this new value. That is, some numbers can be skipped.

If the maximum value of the self-increasing sequence is deleted, that value is reused when inserting new records.

If you update a self-increasing column with the UPDATE command, an error occurs if the column value is a duplicate of an existing value. If greater than an existing value, the next number increments from that value.

When using AUTO_INCREMENT, note the following:

AUTO_INCREMENT is an attribute of a data column that applies only to integer type data columns.

The data column for which the AUTO_INCREMENT attribute is set should be a sequence of positive numbers, so declare the data column as UNSIGNED so that the sequence number doubles.

AUTO_INCREMENT data columns must have unique indexes to avoid duplicate ordinal numbers (i.e., primary keys or part of primary keys).

AUTO_INCREMENT data columns must have the NOT NULL attribute.

The maximum number of AUTO_INCREMENT data column numbers is constrained by the data type of the column. For example, the maximum number of TINYINT data columns is 127, and if UNSIGNED is added, the maximum number is 255. AUTO_INCREMENT expires once the upper limit is reached.

MySQL AUTO_INCREMENT restarts numbering from 1 when a full table deletion occurs. Full table deletion means when issuing the following two statements:

[php]view plaindelete from table_name; or truncate table_name This is because MySQL(the best combination with PHP) actually does such an optimization operation when performing full table operations: first delete all data and indexes in the data table, and then rebuild the data table.

If you want to delete all rows but want to preserve sequence numbering information, you can suppress MySQL(the best combination of MySQL and PHP) optimization by using a delete command with where:

[php] view plaindelete from table_name where 1;

You can use last_insert_id () to get the value you just incremented.

Prior to mysql 5.1.22, mysql's "INSERT-like" statements (packages INSERT, INSERT…SELECT, REPLACE,REPLACE…SELECT, and LOAD DATA) locked tables with an AUTO-INC lock throughout the statement's execution until the end of the statement (rather than the end of the transaction).

Therefore, when using INSERT…SELECT, INSERT…values(…),values(…), LOAD DATA and other time-consuming operations, the entire table will be locked, while blocking other statements such as "INSERT-like" and Update. It is recommended to use programs to divide these statements into multiple statements and insert them one by one to reduce the locking time of a single time table.

After mysql 5.1.22, mysql was improved by introducing the parameter innodb_autoinc_lock_mode, which controls mysql lock table logic.

Before introducing this, let's introduce a few terms to facilitate innodb_autoinc_lock_mode.

1. "INSERT-like"：

INSERT, INSERT … SELECT, REPLACE, REPLACE … SELECT, and LOAD DATA, INSERT … VALUES(),VALUES()

2. "Simple inserts"：

insert statement, INSERT, INSERT … VALUES(),VALUES()

3. "Bulk inserts"：

insert statement, INSERT … SELECT, REPLACE … SELECT, LOAD DATA

4. "Mixed-mode inserts"：

Not sure if auto_increment id needs to be allocated, generally the following two cases

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

INSERT … ON DUPLICATE KEY UPDATE

innodb_autoinc_lock_mode = 0 ("traditional" lock mod).

This pattern is retained for backward compatibility, as before mysql 5.1.22, and, as described earlier, is characterized by "table-level locking" and poor concurrency.

innodb_autoinc_lock_mode = 1 ("consecutive" lock mode).

This mode is the default mode in the new version, recommended to use, relatively high concurrency, characterized by "consecutive", that is, to ensure that the newly inserted auto_increment id in the same insert statement is consecutive.

In this mode:

"Simple inserts": Directly parse the statement to get the number to insert, and then allocate enough auto_increment ids at once, only locking the entire allocation process.

"Bulk inserts": Because the number of inserts cannot be determined, table-level locking is used as in the previous schema.

"Mixed-mode inserts": Parse the statement directly to get the worst-case number of inserts, and then allocate enough auto_increment ids at once to lock the entire allocation process.

It should be noted that in this way, too many ids will be assigned, resulting in "waste."

For example INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,' b'), (5,'c'), (NULL,' d'); assigns 5 ids at once, regardless of whether the user specifies a partial id;

INSERT... ON DUPLICATE KEY UPDATE A one-time allocation regardless of whether future insertions will only perform update operations due to duplicate key.

Note: When master mysql version =5.1.22, slave needs to set innodb_autoinc_lock_mode to 0, because the default innodb_autoinc_lock_mode is 1. The execution results of INSERT … ON DUPLICATE KEY UPDATE and INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,' b'), (5,'c'), (NULL,' d'); are different. In real life, INSERT … ON DUPLICATE KEY UPDATE is generally used.

innodb_autoinc_lock_mode = 2 ("interleaved" lock mode)

This mode is to allocate one by one, without locking the table, only locking the process of allocating ids. The difference between innodb_autoinc_lock_mode = 1 is that multiple will not be pre-allocated. This mode has the highest concurrency.

However, when binlog_format is statement-based in replication (SBR statement-based replication for short), there is a problem, because one is allocated one by one, so that when concurrent execution is performed,"Bulk inserts" will be allocated to other INSERT at the same time, and master-slave inconsistency will occur (the execution result of the slave library is different from that of the master library), because binlog will only record the initial insert id.

Test SBR, execute begin;insert values(),();insert values(),();commit; will add SET INSERT_ID=18/*!*/; before each insert value (),(); in binlog.

But there is no problem with row-based replication RBR.

In addition, the main disadvantage of RBR is that when the number of logs includes a large number of update delete (update multiple statements, delete multiple statements), the log will be much larger than SBR; if there are not many such statements in the actual statement (there are many such cases in reality), it is recommended to use RBR with innodb_autoinc_lock_mode, but then again,"Bulk inserts" are very few in real production, so innodb_autoinc_lock_mode = 1 should be enough.

Finally, I encountered this problem today. LOCK_MODE is AUTO_INC, and things roll back very slowly. I had to stop the database restart.

mysql> select * from innodb_locks;

+-------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+

| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |

+-------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+

| 2954466:518 | 2954466 | AUTO_INC | TABLE | `test`.` kkkm` | NULL | NULL | NULL | NULL | NULL |

| 2954465:518 | 2954465 | AUTO_INC | TABLE | `test`.` kkkm` | NULL | NULL | NULL | NULL | NULL |

+-------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+

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.