各数据库创建主键自增序列的方法及被truncate截断的表现

1 Mysql创建主键自增序列表

网上好多数据库创建主键自增序列都是比较散的，我利用半天时间去网上查了一下，把他们结合在了一起，方便查看阅读，主要包括mysql,oracle,还有pg的，把他们创建自增列表的方法集中了一下。

1.1 使用serial创建主键自增序列表

mysql> create table t1(a serial, b int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1(b) values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1(b) values(2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1(b) values(3);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1 order by a;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    2 |
| 3 |    3 |
+---+------+
3 rows in set (0.00 sec)

使用truncate截断后的表现

mysql> truncate t1;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from t1 order by a;
Empty set (0.00 sec)
mysql> insert into t1(b) values(4);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1 order by a;
+---+------+
| a | b    |
+---+------+
| 1 |    4 |
+---+------+
1 row in set (0.00 sec)

1.2 使用auto_increment创建主键自增序列表：

mysql> create table t3(a int primary key auto_increment,b int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t3(b) values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3(b) values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3 order by a;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    2 |
+---+------+
2 rows in set (0.00 sec)

使用truncate截断后

mysql> truncate t3;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from t3 order by a;
Empty set (0.00 sec)

mysql> insert into t3(b) values(3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3 order by a;
+---+------+
| a | b    |
+---+------+
| 1 |    3 |
+---+------+
1 row in set (0.00 sec)

1.3 使用sequence创建主键自增序列的表
由于mysql和oracle不太一样，不支持直接的sequence，需要自定义一个函数来处理要增长的值。才可以使用，比较麻烦，暂时先不写。

结论：在mysql中创建自增序列的表，自增序列必须设置主键，有主键的自增序列在截断后就会恢复默认值。所以mysql创建的主键自增序列的表一般被truncate截断后都会重置序列号。

2 Oracle创建主键自增序列的表

2.1 使用serial创建主键自增序列的表

Oracle不支持serial类型，所以不能使用serial创建表。

2.2 使用auto_increment创建主键自增序列表：

Oracle没有这个”auto_increment”属性，所以它没法像MySQL般在表内定义自增主键。

2.3 使用sequence创建主键自增序列的表

2.3.1 创建序列的语法

 create sequence [user.]sequence_name
    [increment by n]
    [start with n]
    [maxvalue n | nomaxvalue]
    [minvalue n | nominvalue];

2.3.2 修改序列的语法

alter sequence [user.]sequence_name
    [increment by n]
    [maxvalue n | nomaxvalue]
    [minvalue n | nominvalue];

2.3.3 序列参数说明

INCREMENT BY： 指定序列号之间的间隔，该值可为正的或负的整数，但不可为0。序列为升序。忽略 该子句时，缺省值为1。
START WITH：指定生成的第一个序列号。在升序时，序列可从比最小值大的值开始，缺省值为序列的最小值。对于降序，序列可由比最大值小的值开始，缺省值为序列的最大值。

SQL> create table t1(a int not null primary key,b int);
表已创建。
SQL> create sequence s1
  2  increment by 1
  3  start with 1;
序列已创建。
SQL> insert into t1(a,b) values(s1.nextval,1);
已创建 1 行。
SQL> insert into t1(a,b) values(s1.nextval,2);
已创建 1 行。
SQL> insert into t1(a,b) values(s1.nextval,3);
已创建 1 行。
SQL> select * from t1 order by a;
         A          B
---------- ----------
         1          1
         2          2
         3          3

被truncate截断后

SQL> truncate table t1;
表被截断。
SQL> select * from t1 order by a;
未选定行
SQL> insert into t1(a,b) values(s1.nextval,4);
已创建 1 行。
SQL> insert into t1(a,b) values(s1.nextval,5);
已创建 1 行。
SQL> select * from t1 order by a;
         A          B
---------- ----------
         4          4
         5          5

结论：oracle不可以使用serial和auto_increment创建主键自增序列的表，只能使用sequnece创建自增序列的表，表被截断后序列号不会自动重置。

3 PG创建主键自增序列的表
3.1 使用serial创建主键自增序列的表

postgres=# create table t1(a serial, b int);
NOTICE:  CREATE TABLE will create implicit sequence "t1_a_seq" for serial column "t1.a"
CREATE TABLE
postgres=# insert into t1(b) values(1);
INSERT 0 1
postgres=# insert into t1(b) values(2);
INSERT 0 1
postgres=# insert into t1(b) values(3);
INSERT 0 1
postgres=# select * from t1 order by a;
 a | b
---+---
 1 | 1
 2 | 2
 3 | 3
(3 rows)

表被truncate t1；截断后，序列号不会重置

postgres=# truncate t1;
TRUNCATE TABLE
postgres=# select * from t1 order by a;
 a | b
---+---
(0 rows)
postgres=# insert into t1(b) values(4);
INSERT 0 1
postgres=# insert into t1(b) values(5);
INSERT 0 1
postgres=# select * from t1 order by a;
 a | b
---+---
 4 | 4
 5 | 5
(2 rows)

表被truncate t1 restart identity;截断后序列号重置

postgres=# truncate t1 restart identity;
TRUNCATE TABLE
postgres=# select * from t1 order by a;
 a | b
---+---
(0 rows)
postgres=# insert into t1(b) values(6);
INSERT 0 1
postgres=# insert into t1(b) values(7);
INSERT 0 1
postgres=# select * from t1 order by a;
 a | b
---+---
 1 | 6
 2 | 7
(2 rows)

3.2 使用auto_increment创建主键自增序列的表

pgsql没有auto_increment

3.3 使用sequence创建主键自增序列表

postgres=# create table t2(a int primary key,b int);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2"
CREATE TABLE
postgres=# create sequence s2
postgres-# increment 1
postgres-# start with 1;
CREATE SEQUENCE
postgres=# insert into t2 values(nextval('s2'),1);
INSERT 0 1
postgres=# insert into t2 values(nextval('s2'),2);
INSERT 0 1
postgres=# insert into t2 values(nextval('s2'),3);
INSERT 0 1
postgres=# select * from t2 order by a;
 a | b
---+---
 1 | 1
 2 | 2
 3 | 3
(3 rows)

使用Truncate t2；截断后，序列号不会重置

postgres=# truncate t2;
TRUNCATE TABLE
postgres=# select * from t2 order by a;
 a | b
---+---
(0 rows)
postgres=# insert into t2 values(nextval('s2'),4);
INSERT 0 1
postgres=# insert into t2 values(nextval('s2'),5);
INSERT 0 1
postgres=# select * from t2 order by a;
 a | b
---+---
 4 | 4
 5 | 5
(2 rows)

使用truncate t2 restart identity;截断后，序列号不会重置

postgres=# truncate t2 restart identity;
TRUNCATE TABLE
postgres=# select * from t2 order by a;
 a | b
---+---
(0 rows)
postgres=# insert into t2 values(nextval('s2'),6);
INSERT 0 1
postgres=# insert into t2 values(nextval('s2'),7);
INSERT 0 1
postgres=# select * from t2 order by a;
 a | b
---+---
 6 | 6
 7 | 7
(2 rows)

结论：PG中可以使用serial和利用sequence创建自增序列的表，使用serial创建的自增序列的表使用truncate t2
restart identity;截断之后，序列号会重置。利用sequence创建的自增序列的表截断后，使用这两种截断都不会使序列号重置。