mysql自定义表自增id步长
来源:互联网 发布:淘宝客源码开发 编辑:程序博客网 时间:2024/06/05 16:52
今天有个兄弟有个需求,需要单个表的自增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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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 307981587Server version: 5.5.8-log Source distributionCopyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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,如果操作的表对这方面有要求的,需谨慎!!!
- mysql自定义表自增id步长
- mysql id指定自增长步长
- mysql 自增步长设置
- mySQL之自增列起始值、步长
- mysql id 自增
- MySql id自增
- MySQL自增Id
- MySQL 修改自增长auto_increment步长
- mysql 修改 查询 表自增id
- Mysql 获取自增Id
- MySQL 自增ID生成器
- mysql返回自增id
- MYSQL的自增ID
- mysql 虚拟自增ID
- Mysql varchar自增ID
- 设置mysql中自增列的初始值和增加步长
- 自定义id自增的一种方式-辅助表
- MySQL中自定义函数实现id自增长
- [Urgent]Senior Technical Support Engineer - 2013/10/14
- DAO层的CRUD操作该使用getHibernateTemplate()下的方法
- 数据库索引优化
- Android入门-ListView-自定义adapter和滑动解锁
- Android中使用AlarmManager进程被删除的解决办法
- mysql自定义表自增id步长
- 利用AsyncUdpSocket实现局域网下的IM(含Demo)
- didFailWithError: Error Domain=kCLErrorDomain Code=0 “The operation couldn’t be completed. (kCLError
- 数字媒体技术揭秘
- (数组的应用三:多项式的表示与处理4.3.1)POJ 1555 Polynomial Showdown(多项式的输出)
- 没有收藏的功能很是郁闷啊,先在这里收藏吧
- java.lang.NoClassDefFoundError: com.mobclick.android.MobclickA
- 内核proc文件系统与seq接口(5)---通用proc接口与seq_file接口实验
- Windows下USB磁盘开发系列一:枚举系统中U盘的盘符