Auto_increment详解

Auto_increment

Mysql AUTO_INCREMENT

1.Innodb表的自动增长列可以手工插入,但是插入的值如果是空或者0,则实际插入的将是自动增长后的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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开始,但是该强制的默认值是保留在内存中的,如果该值在使用之前数据库重新启动,那么这个强制的默认值就会丢失,就需要数据库启动后重新设置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> alter table t1 auto_increment=5;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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()查询当前线程最后插入记录使用的值。如果一次插入多条记录,那么返回的是第一条记录使用的自动增长值。


Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2020 Fan() All Rights Reserved.

访客数 : | 访问量 :