Auto_increment
来源:互联网 发布:召唤师捏脸数据 编辑:程序博客网 时间:2024/06/14 06:24
1.Innodb表的自动增长列可以手工插入,但是插入的值如果是空或者0,则实际插入的将是自动增长后的值
mysql> create table t1(id int not null auto_increment primary key,name varchar(10));Query OK, 0 rows affected (0.06 sec)mysql> desc t1;+-------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | varchar(10) | YES | | NULL | |+-------+-------------+------+-----+---------+----------------+2 rows in set (0.01 sec)mysql> insert into t1 values(0,'fanboshi');Query OK, 1 row affected (0.00 sec)mysql> insert into t1 values(null,'duyalan');Query OK, 1 row affected (0.00 sec)mysql> select * from t1;+----+----------+| id | name |+----+----------+| 1 | fanboshi || 2 | duyalan |+----+----------+2 rows in set (0.00 sec)
2.可以通过alter table t1 auto_incremenrt=n 语句强制设置自动增长列的初始值,默认从1开始,但是该强制的默认值是保留在内存中的,如果该值在使用之前数据库重新启动,那么这个强制的默认值就会丢失,就需要数据库启动后重新设置
mysql> alter table t1 auto_increment=5;Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> insert into t1 values(null,'handudu');Query OK, 1 row affected (0.00 sec)mysql> select * from t1;+----+----------+| id | name |+----+----------+| 1 | fanboshi || 2 | duyalan || 5 | handudu |+----+----------+3 rows in set (0.00 sec)
3.可以是用last_insert_id()查询当前线程最后插入记录使用的值。如果一次插入多条记录,那么返回的是第一条记录使用的自动增长值。
mysql> select last_insert_id();+------------------+| last_insert_id() |+------------------+| 5 |+------------------+1 row in set (0.00 sec)注意last_insert_id()是所有表auto_increment的最新插入值,因此在并发的情况下,获取某表的最新插入auto_increment可能出现错误
4.对于innodb表,自动增长列必须是索引,且必须是组合索引的第一列,且一个表只能有一个auto_increment属性。
mysql> create table t2(id int not null auto_increment,name varchar(10));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
非主键mysql> create table t2(id int not null auto_increment,name varchar(10),index(id));Query OK, 0 rows affected (0.09 sec)mysql> mysql> show index from t2;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| t2 | 1 | id | 1 | id | A | 0 | NULL | NULL | | BTREE | | |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.00 sec)mysql> show index from t2;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| t2 | 1 | id | 1 | id | A | 0 | NULL | NULL | | BTREE | | |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.00 sec)不是主键,只是有索引mysql> insert into t2 values(1,'fan');Query OK, 1 row affected (0.00 sec)mysql> insert into t2 values(2,'fan');Query OK, 1 row affected (0.00 sec)mysql> select * from t2;+----+------+| id | name |+----+------+| 1 | fan || 2 | fan |+----+------+2 rows in set (0.00 sec)如果是组合索引,也必须是组合索引的第一列mysql> create table t3(id1 int not null auto_increment,id2 int,name varchar(10),index(id2,id1));ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
但是对于MyISAM表,自动增长列可以使组合索引的其他列,这样插入记录后,自动增长列是按照组合索引的前面几列进行排序后递增的。
mysql> create table t3_myisam(id1 int not null auto_increment,id2 int,name varchar(10),index(id2,id1)) engine=myisam;Query OK, 0 rows affected (0.04 sec)mysql> insert into t3_myisam(id2,name) values(3,'fanboshi'),(1,'duyalan'),(1,'daduzi'),(2,'fan'),(5,'hehe'),(6,'keke');Query OK, 6 rows affected (0.03 sec)Records: 6 Duplicates: 0 Warnings: 0mysql> select * from t3_myisam;+-----+------+----------+| id1 | id2 | name |+-----+------+----------+| 1 | 3 | fanboshi || 1 | 1 | duyalan || 2 | 1 | daduzi || 1 | 2 | fan || 1 | 5 | hehe || 1 | 6 | keke |+-----+------+----------+6 rows in set (0.00 sec)好像看不出啥规律再插入一次mysql> insert into t3_myisam(id2,name) values(3,'fanboshi'),(1,'duyalan'),(1,'daduzi'),(2,'fan'),(5,'hehe'),(6,'keke');Query OK, 6 rows affected (0.00 sec)Records: 6 Duplicates: 0 Warnings: 0mysql> select * from t3_myisam order by id2,id1;+-----+------+----------+| id1 | id2 | name |+-----+------+----------+| 1 | 1 | duyalan || 2 | 1 | daduzi || 3 | 1 | duyalan || 4 | 1 | daduzi || 1 | 2 | fan || 2 | 2 | fan || 1 | 3 | fanboshi || 2 | 3 | fanboshi || 1 | 5 | hehe || 2 | 5 | hehe || 1 | 6 | keke || 2 | 6 | keke |+-----+------+----------+12 rows in set (0.00 sec)id2=1有四个,所以id1有1,2,3,4id2=2有俩,id1=1,2自动增长列id1作为组合索引的第二列,对该表插入一些记录后,可以发现自动增长列是按照组合索引第一列id2进行排序后分组递增的
5.MyISAM 及INNODB表,表中auto_increment最大值被删除,将不会被重用。就是说会跳号
mysql> insert into t1(name) values('hehe');Query OK, 1 row affected (0.02 sec)mysql> select * from t1;+----+----------+| id | name |+----+----------+| 1 | fanboshi || 2 | duyalan || 5 | handudu || 6 | hehe |+----+----------+4 rows in set (0.00 sec)mysql> delete from t1 where id=6;Query OK, 1 row affected (0.08 sec)mysql> insert into t1(name) values('keke');Query OK, 1 row affected (0.00 sec)mysql> select * from t1;+----+----------+| id | name |+----+----------+| 1 | fanboshi || 2 | duyalan || 5 | handudu || 7 | keke |+----+----------+4 rows in set (0.00 sec)
6.用"WHERE auto_col IS NULL"条件选择出新插入的行,即在INSERT后马上用:
SELECT * FROM t4 WHERE id IS NULL;
选择出来的将是新插入的行,而非真正的满足"id IS NULL"条件的行。
但你要是再执行一次上述查询,则返回的又变成了真正的满足"a IS NULL"条件的行,
由于a是主键,因此肯定会返回空集。这看上去很诡异是吗,不过MySQL也不想这么干,为了支持 ODBC标准
不过可以将SQL_AUTO_IS_NULL设为0来禁止这一用法。
此方法获取last_insert_id不推荐
mysql> insert into t1(name) values('new');Query OK, 1 row affected (0.00 sec)mysql> select * from t1 where id is null;Empty set (0.00 sec)mysql> show variables like 'sql_auto_is_null';+------------------+-------+| Variable_name | Value |+------------------+-------+| sql_auto_is_null | OFF |+------------------+-------+1 row in set (0.00 sec)mysql> set session sql_auto_is_null=on;Query OK, 0 rows affected (0.02 sec)mysql> show variables like 'sql_auto_is_null';+------------------+-------+| Variable_name | Value |+------------------+-------+| sql_auto_is_null | ON |+------------------+-------+1 row in set (0.00 sec)mysql> select * from t1 where id is null;+----+------+| id | name |+----+------+| 8 | new |+----+------+1 row in set (0.01 sec)mysql> select * from t1 where id is null;Empty set (0.00 sec)
7.AUTO_INCREMENT属性也给复制带来了麻烦。一般情况下复制AUTO_INCREMENT属性能正确工作,但以下情况还是有问题:
- INSERT DELAYED ... VALUES(LAST_INSERT_ID())不能被正确复制
- 存储过程插入的使用AUTO_INCREMENT属性的记录不能被正确复制
- 通过"ALTER TABLE"命令增加AUTO_INCREMENT属性时在主从节点上产生的值可能是不一样的,因为这个各行AUTO_INCREMENT属性的值取决于物理上的存储顺序。
8.对于replication的master-master方式 为防止auto_increment字段的重复,可做如下设置
A服务器的my.cnf设置如下:
auto_increment_offset = 1
auto_increment_increment = 2
这样A的auto_increment字段产生的数值是:1, 3, 5, 7, ...
B服务器的my.cnf设置如下:
auto_increment_offset = 2
auto_increment_increment = 2
这样B的auto_increment字段产生的数值是:2, 4, 6, 8, ...
8.根据官方的说明:If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. (如果auto_increment_offset的值大于auto_increment_increment的值,则auto_increment_offset的值会被忽略)
mysql> show variables like 'auto_increment%';+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| auto_increment_increment | 1 || auto_increment_offset | 1 |+--------------------------+-------+2 rows in set (0.00 sec)mysql> set session auto_increment_offset=5;Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'auto_increment%';+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| auto_increment_increment | 1 || auto_increment_offset | 5 |+--------------------------+-------+2 rows in set (0.00 sec)mysql> create table t5 like t1;Query OK, 0 rows affected (0.07 sec)mysql> desc t5;+-------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | varchar(10) | YES | | NULL | |+-------+-------------+------+-----+---------+----------------+2 rows in set (0.00 sec)mysql> insert into t5(name) values('fanboshi');Query OK, 1 row affected (0.01 sec)mysql> select * from t5;+----+----------+| id | name |+----+----------+| 1 | fanboshi |+----+----------+1 row in set (0.00 sec)mysql> set session auto_increment_increment=5;Query OK, 0 rows affected (0.00 sec)mysql> insert into t5(name) values('duyalan');Query OK, 1 row affected (0.00 sec)mysql> insert into t5(name) values('heheda');Query OK, 1 row affected (0.02 sec)mysql> select * from t5;+----+----------+| id | name |+----+----------+| 1 | fanboshi || 5 | duyalan || 10 | heheda |+----+----------+3 rows in set (0.00 sec)
- auto_increment
- Auto_increment
- AUTO_INCREMENT
- mysql_ auto_increment
- MySQL AUTO_INCREMENT
- mysql AUTO_INCREMENT
- mysqldump AUTO_INCREMENT
- SQL auto_increment
- MySql: AUTO_INCREMENT
- MySQL的AUTO_INCREMENT
- MySQL - 使用AUTO_INCREMENT
- MySQL的AUTO_INCREMENT
- MySQL的AUTO_INCREMENT
- MySQL的AUTO_INCREMENT
- oracle 实现字段auto_increment
- mysql的auto_increment
- MySQL的AUTO_INCREMENT
- MySQL的AUTO_INCREMENT
- 【工作日志】elementUI学习-Layout布局-2
- Eclipse快捷键大全(史诗级)
- redux-applyMiddleware源码解读
- 对象关系映射(ORM)
- 指针练习
- Auto_increment
- String,StringBuffer,StringBuilder异同之比较观
- 简单的python爬虫
- codeforces 733 F. Drivers Dissatisfaction(最小生成树+lca+倍增去环)
- Slider滑块插件之Chrome下双击后的拖拽问题
- 深入理解HTTP Session
- 必须要知道对手怕什么,不能老是自己害怕
- MyBatis之传多个参数
- leetcode----Palindrome Number