关于mysql的自增id测试,innodb和myisam下的不同表现
来源:互联网 发布:unity3d vr 室内场景 编辑:程序博客网 时间:2024/05/21 09:55
innodb引擎下的自增id测试
1 innodb引擎下,如果显示insert了最大值,那么下次的AUTO_INCREMENT值就是这个最大值+1
mysql> select * from innodb_test;+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
mysql> insert into innodb_test(id) values(100);
Query OK, 1 row affected (0.00 sec)
mysql> show create table innodb_test\G
*************************** 1. row ***************************
Table: innodb_test
Create Table: CREATE TABLE `innodb_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
2 如果这时候再把其中一个id值update成105,那么下次的AUTO_INCREMENT却还是不变
mysql> update innodb_test set id=105 where id=1;Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from innodb_test;
+-----+
| id |
+-----+
| 2 |
| 3 |
| 100 |
| 105 |
+-----+
4 rows in set (0.00 sec)
mysql> show create table innodb_test\G
*************************** 1. row ***************************
Table: innodb_test
Create Table: CREATE TABLE `innodb_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
3 如果这时再利用自增段去插入,到了105的时候是会报错的
mysql> insert into innodb_test values();Query OK, 1 row affected (0.00 sec)
mysql> insert into innodb_test values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into innodb_test values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into innodb_test values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into innodb_test values();
ERROR 1062 (23000): Duplicate entry '105' for key 'PRIMARY'
4 这时继续插入,不会报错,因为刚才即使报错了,AUTO_INCREMENT值依旧会增加
mysql> insert into innodb_test values();Query OK, 1 row affected (0.00 sec)
mysql> show create table innodb_test\G
*************************** 1. row ***************************
Table: innodb_test
Create Table: CREATE TABLE `innodb_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from innodb_test;
+-----+
| id |
+-----+
| 2 |
| 3 |
| 100 |
| 101 |
| 102 |
| 103 |
| 104 |
| 105 |
| 106 |
+-----+
9 rows in set (0.00 sec)
5 如果db重启了,那么innodb表在启动后,AUTO_INCREMENT值会自动检测出当前表中的最大值+1
测试5.1 手工修改AUTO_INCREMENT值为110,重启db,发现AUTO_INCREMENT还是原来的没有修改前的AUTO_INCREMENT,因为我只修改了但并没有插入数据,顺便提一句,AUTO_INCREMENT值只能设置得比当前值大,不能比当前值小,如果设置得比当前值小,它会默认调整到当前的最大值。
mysql> alter table innodb_test AUTO_INCREMENT=110;Query OK, 9 rows affected (0.01 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> show create table innodb_test\G
*************************** 1. row ***************************
Table: innodb_test
Create Table: CREATE TABLE `innodb_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=110 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> alter table innodb_test AUTO_INCREMENT=10;
Query OK, 9 rows affected (0.00 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> show create table innodb_test\G
*************************** 1. row ***************************
Table: innodb_test
Create Table: CREATE TABLE `innodb_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> alter table innodb_test AUTO_INCREMENT=110;
Query OK, 9 rows affected (0.00 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> show create table innodb_test\G
*************************** 1. row ***************************
Table: innodb_test
Create Table: CREATE TABLE `innodb_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=110 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
这时重启db发现又变回了107,符合刚才的猜测
[root@10-10-175-111 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
mysql> show create table innodb_test\G
*************************** 1. row ***************************
Table: innodb_test
Create Table: CREATE TABLE `innodb_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
测试5.2 插入一条111的记录,重启db,这时AUTO_INCREMENT应该不变,符合猜测
mysql> select * from innodb_test;+-----+
| id |
+-----+
| 2 |
| 3 |
| 100 |
| 101 |
| 102 |
| 103 |
| 104 |
| 105 |
| 106 |
+-----+
9 rows in set (0.00 sec)
mysql> insert into innodb_test(id) values(111);
Query OK, 1 row affected (0.00 sec)
mysql> show create table innodb_test\G
*************************** 1. row ***************************
Table: innodb_test
Create Table: CREATE TABLE `innodb_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> Ctrl-C -- exit!
Aborted
[root@10-10-175-111 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
mysql> show create table innodb_test\G
*************************** 1. row ***************************
Table: innodb_test
Create Table: CREATE TABLE `innodb_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
测试5.3,删除该表的所有记录,不重启db的话AUTO_INCREMENT不变,如果重启db则AUTO_INCREMENT会置空,重新计数,符合猜测
mysql> show create table innodb_test\G*************************** 1. row ***************************
Table: innodb_test
Create Table: CREATE TABLE `innodb_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> delete from innodb_test;
Query OK, 10 rows affected (0.01 sec)
mysql> Ctrl-C -- exit!
Aborted
[root@10-10-175-111 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
mysql> show create table innodb_test\G
*************************** 1. row ***************************
Table: innodb_test
Create Table: CREATE TABLE `innodb_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
mysql> insert into innodb_test values();
Query OK, 1 row affected (0.01 sec)
mysql> select * from innodb_test;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
myisam引擎下自增id
1 myisam引擎下,如果显示insert了最大值,那么下次的AUTO_INCREMENT值就是这个最大值+1
mysql> insert into myisam_test values();Query OK, 1 row affected (0.00 sec)
mysql> insert into myisam_test values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into myisam_test(id) values(5);
Query OK, 1 row affected (0.00 sec)
mysql> show create table myisam_test\G
*************************** 1. row ***************************
Table: myisam_test
Create Table: CREATE TABLE `myisam_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
2 如果这时候再把其中一个id值update成105,那么下次的AUTO_INCREMENT就会变成106(这和innodb是不同的!)
mysql> update myisam_test set id=105 where id=1;Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from myisam_test;
+-----+
| id |
+-----+
| 2 |
| 5 |
| 105 |
+-----+
3 rows in set (0.00 sec)
mysql> show create table myisam_test\G
*************************** 1. row ***************************
Table: myisam_test
Create Table: CREATE TABLE `myisam_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=106 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
3 当db重启后,myisam引擎的AUTO_INCREMENT值不变
测试3.1 手工修改AUTO_INCREMENT值为110,重启db,发现AUTO_INCREMENT还是110,符合猜测
mysql> show create table myisam_test\G*************************** 1. row ***************************
Table: myisam_test
Create Table: CREATE TABLE `myisam_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=106 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> alter table myisam_test AUTO_INCREMENT=110;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show create table myisam_test\G
*************************** 1. row ***************************
Table: myisam_test
Create Table: CREATE TABLE `myisam_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=110 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
[root@10-10-175-111 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
mysql> show create table myisam_test\G
*************************** 1. row ***************************
Table: myisam_test
Create Table: CREATE TABLE `myisam_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=110 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
测试3.2 删除该表下的所有记录,重启db,AUTO_INCREMENT值不变
mysql> show create table myisam_test\G*************************** 1. row ***************************
Table: myisam_test
Create Table: CREATE TABLE `myisam_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=110 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> delete from myisam_test;
Query OK, 3 rows affected (0.00 sec)
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
mysql> show create table myisam_test\G
*************************** 1. row ***************************
Table: myisam_test
Create Table: CREATE TABLE `myisam_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=110 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from myisam_test;
Empty set (0.00 sec)
mysql> insert into myisam_test values();
Query OK, 1 row affected (0.00 sec)
mysql> select * from myisam_test;
+-----+
| id |
+-----+
| 110 |
+-----+
1 row in set (0.00 sec)
得到的启示
1 现在都提倡都使用innodb引擎,如果将myisam引擎转换过来的时候,一定要小心这两个引擎在自增id上的不同表现2 自增id的列不要随意显示指定自增id值,myisam还好些,innodb是可能引起重复主键错误
3 有些主从使用不同引擎的要注意了,同样也是两种引擎不同表现会带来一些意想不到的问题
2 0
- 关于mysql的自增id测试,innodb和myisam下的不同表现
- mysql的cardinality在myisam和innodb中的不同
- MySql存储引擎MyISAM和Innodb的不同
- MySQL原理研究:myisam和innodb索引实现的不同
- MYSQL的InnoDB和MyISAM
- Mysql 的 Innodb和MyISAM
- MyISAM和InnoDB的不同优化
- MyISAM和InnoDB索引实现的不同
- myisam和innodb索引实现的不同
- myisam和innodb索引实现的不同
- myisam和innodb索引实现的不同
- myisam和innodb索引实现的不同
- MyISAM和InnoDB的不同优化
- 关于MySQL的Myisam和Innodb的一些比较总结
- 关于mysql纯insert时候InnoDB和MyISAM的区别
- MyISAM和InnoDB通过主键自增ID排序区别
- MyISAM和InnoDB的插入性能测试
- MYSQL 的 MyISAM 和 InnoDB 的困惑
- 检查em集群配置报错:ORA-28000
- Notification(一)
- 出来混总是会忘记的,不过偶尔能记起来就好了,大家还记得当初敲HelloWorld 时的心情吗?
- 【蓝桥第二周】和尚挑水
- GiftGenerator:标准可读格式选择题解析为GIFT格式(用于Moodle)
- 关于mysql的自增id测试,innodb和myisam下的不同表现
- HBase集群安装-0 环境安装
- 同时在工程中引入了多个第三方jar包,导致的DEX错误
- Scala 正则表达式
- 创建SQLAlchemy的ORM类的基类(二)
- 晓月_【C#异步服务器】第一天 服务器源代码
- 简单工厂模式
- TRS WCM 几处突破点
- Notification(二)