mysql的AUTO_INCREMENT

最新推荐文章于 2022-04-22 15:05:03 发布

知~仁~勇

最新推荐文章于 2022-04-22 15:05:03 发布

阅读量565

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/lgq421033770/article/details/50252433

版权

mysql 专栏收录该内容

42 篇文章 0 订阅

订阅专栏

来自手册

You can retrieve the most recent automatically generated AUTO_INCREMENT value with the
LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. These functions
are connection-specific, so their return values are not affected by another connection which is also
performing inserts

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.Using AUTO_INCREMENT
267
This enables multiple-row inserts to be reproduced correctly on other servers in
a replication setup.

注：

1.可以使用 sql函数 LAST_INSERT_ID() 或者 C API函数 mysql_insert_id()来获取最近插入的值的id,这种方法是线程安全的，所以不会被其他的插入影响到。

2.一次插入多条记录时，获取到的是第一条插入的自增id

3.修改自增id的方法：ALTER TABLE tbl_name AUTO_INCREMENT=100; 注：AUTO_INCREMENT需要大写，当修改的值小于现有id时，修改无效。

4.可以使用UNSIGNED来增加可用数量

5.可以使用 SHOW CREATE TABLE tbl_name；来查看自增id的当前值

6.当AUTO_INCREMENT超过范围时，insert会提示错误：

Failed to read auto-increment value from storage engine

更多待续：

InnoDB Notes
For InnoDB tables, be careful if you modify the column containing the auto-increment value in the
middle of a sequence of INSERT statements. For example, if you use an UPDATE statement to put a
new, larger value in the auto-increment column, a subsequent INSERT could encounter a “Duplicate
entry” error. The test whether an auto-increment value is already present occurs if you do a DELETE
followed by more INSERT statements, or when you COMMIT the transaction, but not after an UPDATE
statement.
For more information about AUTO_INCREMENT and InnoDB, see Section 14.5.5, “AUTO_INCREMENT
Handling in InnoDB”.
MyISAM Notes
• 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. This is useful when you
want to put data into ordered groups.

在MyISAM引擎中，可以使用自增字段和另外一个字段联合做主键，这样自增字段会根据另一个字段的值，来做分组的自增，如：

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 |
+--------+----+---------+

Further Reading
More information about AUTO_INCREMENT is available here:
• How to assign the AUTO_INCREMENT attribute to a column: Section 13.1.14, “CREATE TABLE
Syntax”, and Section 13.1.6, “ALTER TABLE Syntax”.
• How AUTO_INCREMENT behaves depending on the NO_AUTO_VALUE_ON_ZERO SQL mode:
Section 5.1.7, “Server SQL Modes”.
• How to use the LAST_INSERT_ID() function to find the row that contains the most recent
AUTO_INCREMENT value: Section 12.14, “Information Functions”.
• Setting the AUTO_INCREMENT value to be used: Section 5.1.4, “Server System Variables”.
• AUTO_INCREMENT and replication: Section 17.4.1.1, “Replication and AUTO_INCREMENT”.
• Server-system variables related to AUTO_INCREMENT (auto_increment_increment and
auto_increment_offset) that can be used for replication: Section 5.1.4, “Server System
Variables”