mysql 自定义变量自增_mysql自定义表自增id步长

今天有个兄弟有个需求，需要单个表的自增id，增长步长为4，

之前配置主主环境的时候有两个参数控制步长和偏移量auto_increment_increment=2，auto_increment_offset=1，但这个是全局的并写在配置文件里面

现用下面的方法，实现session级别的自增id步长自定义

先看到，系统全局环境的自增情况，默认为1

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> select * from test;

Empty set (0.00 sec)

mysql> insert into test values();

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

+----+

| id |

+----+

| 1 |

+----+

1 row in set (0.00 sec)

mysql> insert into test values();

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

+----+

| id |

+----+

| 1 |

| 2 |

+----+

2 rows in set (0.00 sec) 然后我们在当前命令行下设置auto_increment_increment变量并插入两条数据试试

mysql> SET auto_increment_increment=4;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'auto_inc%';

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

| Variable_name | Value |

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

| auto_increment_increment | 4 |

| auto_increment_offset | 1 |

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

2 rows in set (0.00 sec)

mysql> insert into test values();

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

+----+

| id |

+----+

| 1 |

| 2 |

| 5 |

+----+

3 rows in set (0.00 sec)

mysql> insert into test values();

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

+----+

| id |

+----+

| 1 |

| 2 |

| 5 |

| 9 |

+----+

4 rows in set (0.00 sec)

mysql>

看到步长已经变成4了

现在另外开一个命令窗口，测试其他session下步长增长情况

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

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> select * from test;

+----+

| id |

+----+

| 1 |

| 2 |

| 5 |

| 9 |

+----+

4 rows in set (0.00 sec)

mysql> insert into test values();

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

+----+

| id |

+----+

| 1 |

| 2 |

| 5 |

| 9 |

| 11 |

+----+

5 rows in set (0.00 sec)

mysql> insert into test values();

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

+----+

| id |

+----+

| 1 |

| 2 |

| 5 |

| 9 |

| 11 |

| 12 |

+----+

6 rows in set (0.00 sec)

mysql> insert into test values();

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

+----+

| id |

+----+

| 1 |

| 2 |

| 5 |

| 9 |

| 11 |

| 12 |

| 13 |

+----+

7 rows in set (0.00 sec)

看到步长还是默认的1,，插入的数据也验证了这一点。

现在退出一开始的session，重新进命令行，查看该参数情况

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 307981587

Server version: 5.5.8-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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>

参数又回到全局默认。

备注：该方法中，该session级别操作的所有表的id自增情况将为+4，如果操作的表对这方面有要求的，需谨慎！！！