[Mysql] mysql-AUTO_INCREMENT参数详解

1、auto_increment的性质就是为新行生成一个唯一标识,例如

#创建测试表
  CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);
#插入测试数据:
INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');
#查看测试结果：
SELECT * FROM animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)

2、上面的auto_increment列没有指定插入的值，mysql会自动序列数。也可以显示指定0或者null值来生成序列值，但是当sql_mode中no_auto_value_on_zero被启用时，如果auto_increment列被指定为0，则该值按0处理。

#查看sql_mode模式
show variables like '%sql_mode%';
+---------------+------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                  |
+---------------+------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
#插入数据
insert into animals(id,name) values(0,'pig');
insert into animals(id,name) values(null,'monkey');
 [aaaa]> SELECT * FROM animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
|  7 | pig     |
|  8 | monkey  |
+----+---------+
8 rows in set (0.00 sec)
#自动序列依次递增
#修改sql_mode模式
set @@sql_mode='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
show variables like '%sql_mode%';
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                        |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#再次插入数据
insert into animals(id,name) values(0,'lion');
insert into animals(id,name) values(null,'tiger');
root@db 01:38:  [aaaa]> SELECT * FROM animals;
+----+---------+
| id | name    |
+----+---------+
|  0 | lion    |
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
|  7 | pig     |
|  8 | monkey  |
|  9 | tiger   |
+----+---------+
10 rows in set (0.00 sec)

当sql_mode中NO_AUTO_VALUE_ON_ZERO为启用状态，插入的数值为0时，按0处理。

#再次插入数据提示主键重复。
root@db 01:41:  [aaaa]> insert into animals(id,name) values(0,'giraffe');
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

#修改sql_mode为原来配置
set @@sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
show variables like '%sql_mode%';
+---------------+------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                  |
+---------------+------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

3、当向自动增长列插入其他数据，序列值被重置，以便下一个自动生成的值从auto_increment对应列的最大值开始，例如：

INSERT INTO animals (id,name) VALUES(100,'rabbit');
INSERT INTO animals (id,name) VALUES(NULL,'mouse');
SELECT * FROM animals;
+-----+---------+
| id  | name    |
+-----+---------+
|   0 | lion    |
|   1 | dog     |
|   2 | cat     |
|   3 | penguin |
|   4 | lax     |
|   5 | whale   |
|   6 | ostrich |
|   7 | pig     |
|   8 | monkey  |
|   9 | tiger   |
| 100 | rabbit  |
| 101 | mouse   |
+-----+---------+
12 rows in set (0.00 sec)

当手动插入id为100的值之后，再次默认插入自动增长的值变为101。测试插入位于最小值和最大值之间的值：

INSERT INTO animals (id,name) VALUES(50,'giraffe');
INSERT INTO animals (id,name) VALUES(NULL,'tortoise');
SELECT * FROM animals;
+-----+----------+
| id  | name     |
+-----+----------+
|   0 | lion     |
|   1 | dog      |
|   2 | cat      |
|   3 | penguin  |
|   4 | lax      |
|   5 | whale    |
|   6 | ostrich  |
|   7 | pig      |
|   8 | monkey   |
|   9 | tiger    |
|  50 | giraffe  |
| 100 | rabbit   |
| 101 | mouse    |
| 102 | tortoise |
+-----+----------+
14 rows in set (0.00 sec)

手动插入位于最小值和最大值之间的值，对生成的序列值没有任何影响。

4、更新innodb类型的表中auto_increment的列值，不会重置auto_increment序列，但是myisam和ndb类型的表会重置。

先以innodb类型表为例：

#更新了auto_increment中列的值小于当前最大值
update animals set id=10 where id=2;
SELECT * FROM animals;
+-----+----------+
| id  | name     |
+-----+----------+
|   0 | lion     |
|   1 | dog      |
|   3 | penguin  |
|   4 | lax      |
|   5 | whale    |
|   6 | ostrich  |
|   7 | pig      |
|   8 | monkey   |
|   9 | tiger    |
|  10 | cat      |
|  50 | giraffe  |
| 100 | rabbit   |
| 101 | mouse    |
| 102 | tortoise |
+-----+----------+
14 rows in set (0.00 sec)

INSERT INTO animals (id,name) VALUES(NULL,'donkey');
SELECT * FROM animals;
+-----+----------+
| id  | name     |
+-----+----------+
|   0 | lion     |
|   1 | dog      |
|   3 | penguin  |
|   4 | lax      |
|   5 | whale    |
|   6 | ostrich  |
|   7 | pig      |
|   8 | monkey   |
|   9 | tiger    |
|  10 | cat      |
|  50 | giraffe  |
| 100 | rabbit   |
| 101 | mouse    |
| 102 | tortoise |
| 103 | donkey   |
+-----+----------+
15 rows in set (0.00 sec)

#更新了auto_increment中列的值大于当前最大值
update animals set id=110 where id=3;
SELECT * FROM animals;
+-----+----------+
| id  | name     |
+-----+----------+
|   0 | lion     |
|   1 | dog      |
|   4 | lax      |
|   5 | whale    |
|   6 | ostrich  |
|   7 | pig      |
|   8 | monkey   |
|   9 | tiger    |
|  10 | cat      |
|  50 | giraffe  |
| 100 | rabbit   |
| 101 | mouse    |
| 102 | tortoise |
| 103 | donkey   |
| 110 | penguin  |
+-----+----------+
15 rows in set (0.00 sec)

INSERT INTO animals (id,name) VALUES(NULL,'cow');
 SELECT * FROM animals;
+-----+----------+
| id  | name     |
+-----+----------+
|   0 | lion     |
|   1 | dog      |
|   4 | lax      |
|   5 | whale    |
|   6 | ostrich  |
|   7 | pig      |
|   8 | monkey   |
|   9 | tiger    |
|  10 | cat      |
|  50 | giraffe  |
| 100 | rabbit   |
| 101 | mouse    |
| 102 | tortoise |
| 103 | donkey   |
| 104 | cow      |
| 110 | penguin  |
+-----+----------+
16 rows in set (0.00 sec)

auto_increment序列值没有更新，这里面有个大坑，随着序列不断增长，当增长到110是，就会报逐渐重复的错误了，因此自动增长列中的值不要更改。

root@db 02:33:  [aaaa]> INSERT INTO animals (id,name) VALUES(NULL,'cow');
Query OK, 1 row affected (0.00 sec)
root@db 02:36:  [aaaa]> INSERT INTO animals (id,name) VALUES(NULL,'cow');
Query OK, 1 row affected (0.00 sec)
root@db 02:36:  [aaaa]> INSERT INTO animals (id,name) VALUES(NULL,'cow');
Query OK, 1 row affected (0.01 sec)
root@db 02:36:  [aaaa]> INSERT INTO animals (id,name) VALUES(NULL,'cow');
Query OK, 1 row affected (0.01 sec)
root@db 02:36:  [aaaa]> INSERT INTO animals (id,name) VALUES(NULL,'cow');
Query OK, 1 row affected (0.00 sec)
root@db 02:36:  [aaaa]> INSERT INTO animals (id,name) VALUES(NULL,'cow');
ERROR 1062 (23000): Duplicate entry '110' for key 'PRIMARY'
root@db 02:36:  [aaaa]> INSERT INTO animals (id,name) VALUES(NULL,'cow');
Query OK, 1 row affected (0.00 sec)
SELECT * FROM animals;
+-----+----------+
| id  | name     |
+-----+----------+
|   0 | lion     |
|   1 | dog      |
|   4 | lax      |
|   5 | whale    |
|   6 | ostrich  |
|   7 | pig      |
|   8 | monkey   |
|   9 | tiger    |
|  10 | cat      |
|  50 | giraffe  |
| 100 | rabbit   |
| 101 | mouse    |
| 102 | tortoise |
| 103 | donkey   |
| 104 | cow      |
| 105 | cow      |
| 106 | cow      |
| 107 | cow      |
| 108 | cow      |
| 109 | cow      |
| 110 | penguin  |
| 111 | cow      |
+-----+----------+
22 rows in set (0.00 sec)

下面以myisam类型表为例：

#创建测试表
CREATE TABLE animals_myisam (
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=MyISAM;
#插入测试数据
INSERT INTO animals_myisam (name) VALUES
    ('dog'),('cat'),
    ('penguin'),('lax'),('whale'),
    ('ostrich');

SELECT * FROM animals_myisam;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)
#更新了auto_increment中列的值小于当前最大值
update animals_myisam set id=10 where id=3;
INSERT INTO animals_myisam (name) VALUES ('mouse');
SELECT * FROM animals_myisam;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
| 10 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
| 11 | mouse   |
+----+---------+
7 rows in set (0.00 sec)
update animals_myisam set id=8 where id=4;
INSERT INTO animals_myisam (name) VALUES ('donkey');
root@db 03:51:  [aaaa]> SELECT * FROM animals_myisam;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
| 10 | penguin |
|  8 | lax     |
|  5 | whale   |
|  6 | ostrich |
| 11 | mouse   |
| 12 | donkey  |
+----+---------+
8 rows in set (0.00 sec)
#更新了auto_increment中列的值大于当前最大值
update animals_myisam set id=51 where id=4;
INSERT INTO animals_myisam (name) VALUES ('horse');
root@db 02:41:  [aaaa]> SELECT * FROM animals_myisam;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
| 10 | penguin |
| 51 | lax     |
|  5 | whale   |
|  6 | ostrich |
| 11 | mouse   |
| 52 | horse   |
+----+---------+
8 rows in set (0.00 sec)

对于myisam类型的表，当对auto_increment列更新时，无论更新后的值大于或者小于当前自增列的值，都按当前列的最大值处理。

5、可以通过mysql函数last_insert_id查看最后插入的值：

select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               53 |
+------------------+
1 row in set (0.00 sec)

6、当auto_increment列值达到最大值后，再也无法生成后面的值，当再次插入的时候将会报错，提示主键重复。比如MEDIUMINT类型的最大值为8388607

INSERT INTO animals (id,name) VALUES(8388606,'whale');
root@db 02:54:  [aaaa]> INSERT INTO animals (id,name) VALUES(null,'elephant');
Query OK, 1 row affected (0.00 sec)
root@db 02:55:  [aaaa]> INSERT INTO animals (id,name) VALUES(null,'rhinoceros');
ERROR 1062 (23000): Duplicate entry '8388607' for key 'PRIMARY'
root@db 02:55:  [aaaa]> INSERT INTO animals (id,name) VALUES(null,'rhinoceros');
ERROR 1062 (23000): Duplicate entry '8388607' for key 'PRIMARY'
root@db 02:55:  [aaaa]>  SELECT * FROM animals;
+---------+----------+
| id      | name     |
+---------+----------+
|       0 | lion     |
|       1 | dog      |
|       4 | lax      |
|       5 | whale    |
|       6 | ostrich  |
|       7 | pig      |
|       8 | monkey   |
|       9 | tiger    |
|      10 | cat      |
|      50 | giraffe  |
|     100 | rabbit   |
|     101 | mouse    |
|     102 | tortoise |
|     103 | donkey   |
|     104 | cow      |
|     105 | cow      |
|     106 | cow      |
|     107 | cow      |
|     108 | cow      |
|     109 | cow      |
|     110 | penguin  |
|     111 | cow      |
| 8388606 | whale    |
| 8388607 | elephant |
+---------+----------+

当插入的值达到8388607值时，没有办法再插入数据了，这时需要修改数据类型

alter table animals modify id int(10) NOT NULL AUTO_INCREMENT;
INSERT INTO animals (id,name) VALUES(null,'rhinoceros');
SELECT * FROM animals;
root@db 02:57:  [aaaa]> INSERT INTO animals (id,name) VALUES(null,'rhinoceros');
Query OK, 1 row affected (0.00 sec)

root@db 02:58:  [aaaa]> SELECT * FROM animals;
+---------+------------+
| id      | name       |
+---------+------------+
|       0 | lion       |
|       1 | dog        |
|       4 | lax        |
|       5 | whale      |
|       6 | ostrich    |
|       7 | pig        |
|       8 | monkey     |
|       9 | tiger      |
|      10 | cat        |
|      50 | giraffe    |
|     100 | rabbit     |
|     101 | mouse      |
|     102 | tortoise   |
|     103 | donkey     |
|     104 | cow        |
|     105 | cow        |
|     106 | cow        |
|     107 | cow        |
|     108 | cow        |
|     109 | cow        |
|     110 | penguin    |
|     111 | cow        |
| 8388606 | whale      |
| 8388607 | elephant   |
| 8388608 | rhinoceros |
+---------+------------+

修改完成之后，再次插入成功,下面是关于数据类型的最小值和最大值的对比

Type   Storage(Bytes) MinimumValueSigned	MinimumValueUnsigned MaximumValueSigned	MaximumValueUnsigned
TINYINT	    1	           -128	                   0	                127	              255
SMALLINT	2	           -32768	               0	                32767	          65535
MEDIUMINT	3	           -8388608	               0	                8388607	          16777215
INT	        4	           -2147483648	           0	               2147483647	      4294967295
BIGINT	    8	           -2^63	               0	                2^63-1	          2^64-1

7、想要修改auto_increment的起始值，可以找alter table，比如：

ALTER TABLE animals AUTO_INCREMENT = 100;

先使用truncate清除表数据,truncate会初始化起始值,delete语句不会初始化,先执行delete操作看一下

root@db 02:58:  [aaaa]> delete from animals;
Query OK, 25 rows affected (0.01 sec)
root@db 03:04:  [aaaa]> SELECT * FROM animals;
Empty set (0.00 sec)
root@db 03:04:  [aaaa]> insert into animals (name) values('dog');
Query OK, 1 row affected (0.00 sec)
root@db 03:04:  [aaaa]> SELECT * FROM animals;
+---------+------+
| id      | name |
+---------+------+
| 8388609 | dog  |
+---------+------+
1 row in set (0.00 sec)

auto_increment的值继续增长，没有从1开始。
执行truncate语句

root@db 03:04:  [aaaa]> truncate table animals;
Query OK, 0 rows affected (0.01 sec)
root@db 03:05:  [aaaa]> SELECT * FROM animals;
Empty set (0.00 sec)
root@db 03:05:  [aaaa]> insert into animals (name) values('dog');
Query OK, 1 row affected (0.01 sec)
root@db 03:05:  [aaaa]> SELECT * FROM animals;
+----+------+
| id | name |
+----+------+
|  1 | dog  |
+----+------+
1 row in set (0.00 sec)

auto_increment序列值从1重新开始计算。
下面使用alter table修改auto_increment的起始值：

root@db 03:05:  [aaaa]> truncate table animals;
Query OK, 0 rows affected (0.05 sec)
root@db 03:06:  [aaaa]> alter table animals auto_increment=99;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
root@db 03:07:  [aaaa]> insert into animals (name) values('dog');
Query OK, 1 row affected (0.01 sec)
root@db 03:07:  [aaaa]> insert into animals (name) values('cat');
Query OK, 1 row affected (0.01 sec)
root@db 03:08:  [aaaa]> SELECT * FROM animals;
+-----+------+
| id  | name |
+-----+------+
|  99 | dog  |
| 100 | cat  |
+-----+------+
2 rows in set (0.00 sec)

auto_increment起始值从99开始计算。
另外使用alter table设置的auto_increment列值，要大于auto_increment列中的最大值才会有效，如果设置的值小于auto_increment列中的最大值，该值会被重置为当前列的最大值+1。
如果设置系统级的步长(auto_increment_increment)和初始值(auto_increment_offset)
但是auto_increment_offset一定不能大于auto_increment_increment的值，否则auto_increment_offset会被忽略：

root@db 04:18:  [aaaa]> truncate table animals;
Query OK, 0 rows affected (0.02 sec)

root@db 04:18:  [aaaa]> set @@auto_increment_offset=5;
Query OK, 0 rows affected (0.00 sec)

root@db 04:18:  [aaaa]> set @@auto_increment_increment=7;
Query OK, 0 rows affected (0.00 sec)

root@db 04:19:  [aaaa]> 
root@db 04:20:  [aaaa]> 
root@db 04:20:  [aaaa]> INSERT INTO animals (name) VALUES
    ->     ('dog'),('cat'),('penguin'),
    ->     ('lax'),('whale'),('ostrich');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

root@db 04:20:  [aaaa]> select * from animals;
+----+---------+
| id | name    |
+----+---------+
|  5 | dog     |
| 12 | cat     |
| 19 | penguin |
| 26 | lax     |
| 33 | whale   |
| 40 | ostrich |
+----+---------+
6 rows in set (0.00 sec)

8、对于myisam中的自增列,如果自增列在复合索引中,自增列会先匹配前缀,根据前缀条件搜索到的结果在自增,计算方法：s MAX(auto_increment_column) + 1 WHERE prefix=given-prefix。例如：

drop table animals_myisam;
CREATE TABLE animals_myisam (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals_myisam (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

root@db 03:12:  [aaaa]> SELECT * FROM animals_myisam ORDER BY grp,id;
+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+
6 rows in set (0.00 sec)

root@db 03:12:  [aaaa]> INSERT INTO animals_myisam (grp,name) VALUES
    ->     ('mammal','dog'),('mammal','cat'),
    ->     ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ->     ('bird','ostrich');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

root@db 03:13:  [aaaa]> INSERT INTO animals_myisam (grp,name) VALUES
    ->     ('mammal','dog'),('mammal','cat'),
    ->     ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ->     ('bird','ostrich');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

root@db 03:13:  [aaaa]> SELECT * FROM animals_myisam ORDER BY grp,id;
+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| fish   |  2 | lax     |
| fish   |  3 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| mammal |  4 | dog     |
| mammal |  5 | cat     |
| mammal |  6 | whale   |
| mammal |  7 | dog     |
| mammal |  8 | cat     |
| mammal |  9 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
| bird   |  3 | penguin |
| bird   |  4 | ostrich |
| bird   |  5 | penguin |
| bird   |  6 | ostrich |
+--------+----+---------+
18 rows in set (0.00 sec)

在这种情况下，删除带有包括最大值的增长列，auto_increment序列值会被复用。

delete from animals_myisam where grp='mammal' and id > 2;
root@db 03:23:  [aaaa]> SELECT * FROM animals_myisam ORDER BY grp,id;
+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| fish   |  2 | lax     |
| fish   |  3 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
| bird   |  3 | penguin |
| bird   |  4 | ostrich |
| bird   |  5 | penguin |
| bird   |  6 | ostrich |
+--------+----+---------+
11 rows in set (0.00 sec)
#再次插入数据
INSERT INTO animals_myisam (grp,name) VALUES ('mammal','dog'),('mammal','cat');

root@db 03:23:  [aaaa]> SELECT * FROM animals_myisam ORDER BY grp,id;
+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| fish   |  2 | lax     |
| fish   |  3 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | dog     |
| mammal |  4 | cat     |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
| bird   |  3 | penguin |
| bird   |  4 | ostrich |
| bird   |  5 | penguin |
| bird   |  6 | ostrich |
+--------+----+---------+
13 rows in set (0.00 sec)

以上情况是在id没有单独索引的情况下发生的，如果id单独的索引，那么插入的值会变成单一的值，不会跟grp列有任何关系。

create index id_ind on animals_myisam(id);
INSERT INTO animals_myisam (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');
select * from animals_myisam order by mammal;
root@db 03:29:  [aaaa]> select * from animals_myisam order by grp;
+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   | 10 | lax     |
| fish   |  1 | lax     |
| fish   |  2 | lax     |
| fish   |  3 | lax     |
| mammal |  2 | cat     |
| mammal | 11 | whale   |
| mammal |  8 | cat     |
| mammal |  7 | dog     |
| mammal |  3 | dog     |
| mammal |  4 | cat     |
| mammal |  1 | dog     |
| bird   |  5 | penguin |
| bird   |  6 | ostrich |
| bird   |  4 | ostrich |
| bird   |  3 | penguin |
| bird   |  2 | ostrich |
| bird   |  9 | penguin |
| bird   |  1 | penguin |
| bird   | 12 | ostrich |
+--------+----+---------+