最进看了老王的文章 http://hi.baidu.com/thinkinginlamp/?page=1,写了关于批量insert的东西,想到了以前自己在批量插入数据时,如果中途某条记录插入失败了,会是什么效果。但是当时自己并没有去深入研究,看了老王的文章后,自己也做了一个测试,记录如下,和老王的文章差不多,仅仅是自己记录一下当作笔记,方便自己以后复习。
以下是测试流程:
mysql> CREATE TABLE test_myisam (
-> id INT(11) unsigned NOT NULL AUTO_INCREMENT,
-> textvalue VARCHAR(30),
-> PRIMARY KEY (id)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.51 sec)
mysql> CREATE TABLE test_innodb (
-> id INT(11) unsigned NOT NULL AUTO_INCREMENT,
-> textvalue VARCHAR(30),
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.69 sec)
mysql> INSERT INTO test_innodb
-> VALUES (0,'Zero'),(1,'One'),
-> (2,'Two'),(3,'Three');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> show create table test_innodb;
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_innodb | CREATE TABLE `test_innodb` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`textvalue` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.38 sec)
mysql> INSERT INTO test_myisam
-> VALUES (0,'Zero'),(1,'One'),
-> (2,'Two'),(3,'Three');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> show create table test_myisam;
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_myisam | CREATE TABLE `test_myisam` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`textvalue` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.61 sec)
mysql> select * from test_myisam;
+----+-----------+
| id | textvalue |
+----+-----------+
| 1 | Zero |
+----+-----------+
1 row in set (0.42 sec)
mysql> select * from test_innodb;
Empty set (0.00 sec)
可以看到,innodb在中途某条数据插入失败后,以前插入的数据不会生效,但是 AUTO_INCREMENT的值却增加到了记录的条数,虽然数据没有入库,myisam确不是,中途一条记录失败了,插入的数据会入库,但后面的没有,AUTO_INCREMENT的值也只是增加到了插入成功的记录数。
猜测:innodb是支持事务的引擎,事务是执行任务的最小单位,任务要么全成功,要么不执行,所以中途一条记录插入失败时,会回滚
但是myisam就不一样了,数据一条一条的插入,报错就直接退出
以上只是个人的理解。