– 准备工作
CREATE TABLE `t_ivan_innodb` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `f_c` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT charset=latin1;

CREATE TABLE `t_ivan_myisam` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `f_c` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=Myisam DEFAULT charset=latin1;

INSERT INTO t_ivan_innodb(f_c) VALUES(”);
INSERT INTO t_ivan_innodb(f_c) VALUES(”);
INSERT INTO t_ivan_innodb(f_c) VALUES(”);
DELETE FROM t_ivan_innodb WHERE id=1;

INSERT INTO t_ivan_myisam(f_c) VALUES(”);
INSERT INTO t_ivan_myisam(f_c) VALUES(”);
INSERT INTO t_ivan_myisam(f_c) VALUES(”);
DELETE FROM t_ivan_myisam WHERE id=1;

– ——————————————-

root:ivan> UPDATE t_ivan_innodb SET id=id-1 WHERE id>1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

root:ivan> UPDATE t_ivan_myisam SET id=id-1 WHERE id>1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

– 可见默认在物理记录数序没有打乱的时候，默认是asc的，更新都正常
– ——————————————–

DROP TABLE t_ivan_innodb;
DROP TABLE t_ivan_myisam;
– 重新创建和插入记录

root:ivan> UPDATE t_ivan_innodb SET id=id-1 WHERE id>1 ORDER BY id DESC;
ERROR 1062 (23000): Duplicate entry ’2′ FOR KEY ‘PRIMARY’

root:ivan> UPDATE t_ivan_myisam SET id=id-1 WHERE id>1 ORDER BY id DESC;
ERROR 1062 (23000): Duplicate entry ’2′ FOR KEY ‘PRIMARY’

– desc排序的时候肯定错误
– ———————————————

– 下面插入数据故意打乱

INSERT INTO t_ivan_innodb(id) VALUES(2);
INSERT INTO t_ivan_innodb(id) VALUES(3);
INSERT INTO t_ivan_innodb(id) VALUES(4);
INSERT INTO t_ivan_innodb(id) VALUES(7);
INSERT INTO t_ivan_innodb(id) VALUES(6);
INSERT INTO t_ivan_innodb(id) VALUES(5);

INSERT INTO t_ivan_myisam(id) VALUES(2);
INSERT INTO t_ivan_myisam(id) VALUES(3);
INSERT INTO t_ivan_myisam(id) VALUES(4);
INSERT INTO t_ivan_myisam(id) VALUES(7);
INSERT INTO t_ivan_myisam(id) VALUES(6);
INSERT INTO t_ivan_myisam(id) VALUES(5);

root:ivan> UPDATE t_ivan_innodb SET id=id-1 WHERE id>1;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

root:ivan> UPDATE t_ivan_myisam SET id=id-1 WHERE id>1;
ERROR 1062 (23000): Duplicate entry ’6′ FOR KEY ‘PRIMARY’