MySQL 5.7 自增欄位相關引數說明
auto_increment_increment 和 auto_increment_offset引數用在主主複製中,用於控制AUTO_INCREMENT欄位的操作,在不同節點使用不同的生成規則,以避免生成的序列相同而產生衝突。這兩個引數可以分別設定全域性和會話的變數,每個引數的值的範圍是1~65535。將這兩個引數設定為0會導致實際上將這兩個引數的值設為1。
auto_increment_increment引數控制AUTO_INCREMENT欄位值的間隔數
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> CREATE TABLE autoinc1
-> (col int not null auto_increment primary key)
-> ;
Query OK, 0 rows affected (0.12 sec)
mysql> select @@auto_increment_increment;
+----------------------------+
| @@auto_increment_increment |
+----------------------------+
| 1 |
+----------------------------+
1 row in set (0.00 sec)
mysql> set @@auto_increment_increment=10;
Query OK, 0 rows affected (0.06 sec)
mysql> select @@auto_increment_increment;
+----------------------------+
| @@auto_increment_increment |
+----------------------------+
| 10 |
+----------------------------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> INSERT INTO autoinc1 VALUES(null),(null),(null);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
| 1 |
| 11 |
| 21 |
+-----+
3 rows in set (0.00 sec)
auto_increment_offset 引數決定AUTO_INCREMENT欄位的起始值
mysql> set @@auto_increment_offset=5;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 5 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> CREATE TABLE autoinc2
-> (col int not null auto_increment primary key);
Query OK, 0 rows affected (0.64 sec)
mysql> INSERT INTO autoinc2 VALUES (null),(null),(null);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT col FROM autoinc2;
+-----+
| col |
+-----+
| 5 |
| 15 |
| 25 |
+-----+
3 rows in set (0.00 sec)
auto_increment_increment引數控制AUTO_INCREMENT欄位值的間隔數
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> CREATE TABLE autoinc1
-> (col int not null auto_increment primary key)
-> ;
Query OK, 0 rows affected (0.12 sec)
mysql> select @@auto_increment_increment;
+----------------------------+
| @@auto_increment_increment |
+----------------------------+
| 1 |
+----------------------------+
1 row in set (0.00 sec)
mysql> set @@auto_increment_increment=10;
Query OK, 0 rows affected (0.06 sec)
mysql> select @@auto_increment_increment;
+----------------------------+
| @@auto_increment_increment |
+----------------------------+
| 10 |
+----------------------------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> INSERT INTO autoinc1 VALUES(null),(null),(null);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
| 1 |
| 11 |
| 21 |
+-----+
3 rows in set (0.00 sec)
auto_increment_offset 引數決定AUTO_INCREMENT欄位的起始值
mysql> set @@auto_increment_offset=5;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 5 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> CREATE TABLE autoinc2
-> (col int not null auto_increment primary key);
Query OK, 0 rows affected (0.64 sec)
mysql> INSERT INTO autoinc2 VALUES (null),(null),(null);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT col FROM autoinc2;
+-----+
| col |
+-----+
| 5 |
| 15 |
| 25 |
+-----+
3 rows in set (0.00 sec)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26506993/viewspace-2098997/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mysql innodb相關引數說明MySql
- MySQL引數說明MySql
- mysql relay log相關引數說明MySql
- 【MYSQL】MHA引數列表說明MySql
- Mysql JDBC Url引數說明MySqlJDBC
- 【MySQL】SemisynchronousReplication配置和引數說明MySql
- MySQL CMake引數說明手冊MySql
- Retrofit請求引數註解欄位說明
- Mysql my.cnf部分引數說明MySql
- MYSQL: Handler_read_%引數說明MySql
- 【MySQL】Semisynchronous Replication 配置和引數說明MySql
- mysql5.7 General tablespace使用說明MySql
- MySQL 5.7 mysqlpump 備份工具說明MySql
- MySQL 5.7 EXPLAIN EXTENDED語句說明MySqlAI
- 幾個和MySQL InnoDB相關的引數設定說明MySql
- MySQL Galera cluster叢集常用引數說明MySql
- MySQL mysqldump命令的引數詳細說明MySql
- mysql常用引數使用說明及查詢MySql
- TOP引數說明
- mysqldump引數說明MySql
- mysqldump 引數說明MySql
- 【AMM】關於ASM中AMM引數說明ASM
- statspack 安裝以及相關引數說明
- MySQL 8 和 MySQL 5.7 在自增計數上的區別MySql
- MySQL 5.5編譯安裝cmake引數說明MySql編譯
- Elasticsearch 引數配置說明Elasticsearch
- kafka 引數配置說明Kafka
- redis 3.0 引數說明Redis
- golden gate 引數說明Go
- oracle引數說明(zt)Oracle
- Oracle sessions,processes 和 transactions 引數 關係 說明OracleSession
- Kafka 配置引數彙總及相關說明Kafka
- mysql 變數說明MySql變數
- MySQL引數DELAY_KEY_WRITE的詳細說明MySql
- Oracle Table建立引數說明Oracle
- Oracle Table 建立引數 說明Oracle
- mysqldump引數詳細說明MySql
- mosquitto命令引數說明UI