MySQL in Auto_increment and auto_increment_offset aspects of the introduction

In a database query, there are two parameter variables involved in auto_increment

[[email protected]][(None)]>Show variables like 'auto_inc%';+--------------------------+-------+|Variable_name|Value|+--------------------------+-------+|Auto_increment_increment| 1     ||Auto_increment_offset| 1     |+--------------------------+-------+2Rowsinch Set(0.00Sec

Auto_increment_increment: Self-increment

Auto_increment_offset: Drift value, i.e. step

Since auto_increment_increment is a globally variable variable, it is possible to modify the self-increment to achieve the test purpose

[[email protected]][(None)]> Create TableBoss.autoinc1 (colint  not NULLAuto_incrementPrimary Key); Query OK,0Rows Affected (1.03sec)[[email protected]][(None)]> Set @ @auto_increment_increment=Ten; Query OK,0Rows Affected (0.00sec)[[email protected]][(None)]>Show variables like 'auto_inc%';+--------------------------+-------+|Variable_name|Value|+--------------------------+-------+|Auto_increment_increment| Ten    ||Auto_increment_offset| 1     |+--------------------------+-------+2Rowsinch Set(0.00Sec

As you can see from above, the increment starts from 10, so what is the result of inserting the data at this point?

[[email protected]][(None)]> Insert  intoBoss.autoinc1Values(NULL),(NULL),(NULL),(NULL); Query OK,4Rows Affected (0.29sec) Records:4Duplicates:0Warnings:0[[email protected]][(None)]> SelectCol fromboss.autoinc1;+-----+|Col|+-----+|   1 ||   One ||   + ||   to |+-----+4Rowsinch Set(0.00Sec

From the result set, the increment of auto_increment_increment is 10 for the next one, that is, 11->21->31->41 and so on.

At this point, we set the offset value, then the data will

[[email protected]][(None)]> Create TableBoss.autoinc2 (colint  not NULLAuto_incrementPrimary Key); Query OK,0Rows Affected (1.31sec)[[email protected]][(None)]> Insert  intoBoss.autoinc2Values(NULL),(NULL),(NULL),(NULL); Query OK,4Rows Affected (0.14sec) Records:4Duplicates:0Warnings:0[[email protected]][(None)]> SelectAo. fromBoss.autoinc2;+-----+|Ao.|+-----+|   5 ||   the ||   - ||   * |+-----+4Rowsinch Set(0.00Sec

As you can see, the first one is offset from base 1 to 5 values (1,2,3,4,5) and then automatically added value, which is handled every time you go to 10

The intrinsic logic for Auto_increment_offset + Nxauto_increment_increment N represents the first few times, starting from 0 of the technology to calculate

such as 5+0*10,5+1*10,

[[email protected]][MySQL]> Set @ @auto_increment_offset=5; Query OK,0Rows Affected (0.00sec)[[email protected]][MySQL]> Create TableBoss.autoinc6 (colint  not NULLAuto_incrementPrimary Key); Query OK,0Rows Affected (0.36sec)[[email protected]][MySQL]> Set @ @auto_increment_increment=Ten; Query OK,0Rows Affected (0.00sec)[[email protected]][MySQL]>Show variables like 'auto_inc%';+--------------------------+-------+|Variable_name|Value|+--------------------------+-------+|Auto_increment_increment| Ten    ||Auto_increment_offset| 5     |+--------------------------+-------+2Rowsinch Set(0.00sec)[[email protected]][MySQL]> Insert  intoBoss.autoinc6Values(NULL),(NULL),(NULL),(NULL); Query OK,4Rows Affected (0.08sec) Records:4Duplicates:0Warnings:0[[email protected]][MySQL]> SelectCol fromBoss.autoinc6;+-----+|Col|+-----+|   5 ||   the ||   - ||   * |+-----+4Rowsinch Set(0.00Sec

MySQL in Auto_increment and auto_increment_offset aspects of the introduction