- No value was specified for the AUTO_INCREMENT
column, so MySQL assigned sequence numbers automatically. You can also explicitly assign 0 to the column to generate sequence numbers, unless the NO_AUTO_VALUE_ON_ZERO
SQL mode is enabled. If the column is declared NOT NULL
, it is also possible to assign NULL
to the column to generate sequence numbers. When you insert any other value into an AUTO_INCREMENT
column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value.
auto_increment字段没有指定值，mysql会自动生成顺序值。你可以明确的指定0去生成顺序值，除非NO_AUTO_VALUE_ON_ZERO模式被禁止了。如果这个字段被指定非NULL，那么插入一个NULL值也可以生成顺序值。当你插入其它指定的值的时候，这个列就被设置成你指定的值，而下次自动生成的顺序会被重置为跟随最大的字段值。
- For a multiple-row insert, LAST_INSERT_ID()
and mysql_insert_id()
actually return the AUTO_INCREMENT
key from the first of the inserted rows. This enables multiple-row inserts to be reproduced correctly on other servers in a replication setup.
多行的插入情况，LAST_INSERT_ID()或者mysql_insert_id()会返回第一行插入生成的自增值。这保证了其它从库正确复制这个操作的正确。
- For MyISAM
tables, you can specify AUTO_INCREMENT
on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT
column is calculated as [MAX(*auto_increment_column
) + 1 WHERE prefix=given-prefix
](https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_max). This is useful when you want to put data into ordered groups.
翻译：
对于MyISAM引擎的表，你可以在一个多列索引的第二列指定一个AUTO_INCREMENT。这种情况下，生成的自增值按照如下计算：
MAX(*auto_increment_column
) + 1 WHERE prefix=given-prefix
这在当你想把数据放入有顺序的组内时很有用。
CREATE TABLE animals (
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 (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;
Which returns:
+--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+
In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.
翻译：这中情况（当AUTO_INCREMENT列是多列索引的一部分），当你删除任意一个组的最大的自增值的时候，自增值会被以后重复利用的。这种情况只发生在MyISAM 表，通常情况下AUTO_INCREMENT值是不会被重复利用的。
If the AUTO_INCREMENT column is part of multiple indexes, MySQL generates sequence values using the index that begins with the AUTO_INCREMENT column, if there is one. For example, if the animals table contained indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would ignore the PRIMARY KEY for generating sequence values. As a result, the table would contain a single sequence, not a sequence per grp value.
翻译：如果自增列是多个索引的其中一部分，mysql会使用以自增列开头的索引来生成自增值。例如，如果动物表包含索引PRIMARY KEY (grp, id) 和 INDEX (id)，mysql会忽略用主键生成自增值。结果，表会包含一个单独的顺序，而不是每一个grp一个顺序。
