===============MySQL部分=============
创建带自增主键id的表test_serial:
mysql> show create table test_serial\G
*************************** 1. row ***************************
Table: test_serial
Create Table: CREATE TABLE `test_serial` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`addr` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
准备数据：
insert into test_serial(id,addr) values(1,'北京');
insert into test_serial(id,addr) values(2,'上海');
insert into test_serial(id,addr) values(3,'广州');
insert into test_serial(id,addr) values(4,'深圳');
mysql> select * from test_serial;
+----+--------+
| id | addr |
+----+--------+
| 1 | 北京 |
| 2 | 上海 |
| 3 | 广州 |
| 4 | 深圳 |
+----+--------+
4 rows in set (0.00 sec)
1、先删除id=4的记录：
mysql> delete from test_serial where id = 4;
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_serial;
+----+--------+
| id | addr |
+----+--------+
| 1 | 北京 |
| 2 | 上海 |
| 3 | 广州 |
+----+--------+
3 rows in set (0.00 sec)
再次插入,此时不用显式定义id字段：
mysql> insert into test_serial(addr) values('深圳');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_serial;
+----+--------+
| id | addr |
+----+--------+
| 1 | 北京 |
| 2 | 上海 |
| 3 | 广州 |
| 5 | 深圳 |
+----+--------+
4 rows in set (0.00 sec)
结果发现id从5开始，此时id列产生了空洞。
2、truncate后再观察：
mysql> truncate table test_serial;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test_serial(addr) values('深圳');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_serial;
+----+--------+
| id | addr |
+----+--------+
| 1 | 深圳 |
+----+--------+
1 row in set (0.00 sec)
结果发现自增列id从1开始，也就是说truncate会清空自增值又从1开始。
3、插入最大值,int的取值范围是1到2147483647,所以插入大于2147483647值会报错。
mysql> insert into test_serial(id,addr) values(2147483647,'unknow');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_serial(id,addr) values(2147483648,'unknow');
ERROR 1264 (22003): Out of range value for column 'id' at row 1
===========PostgreSQL部分=========
同样创建带自增主键id的表test_serial，PostgreSQL中是以SERIAL作为自增序列的关键字，
和MySQL中auto_increment类似：
tt=# create table test_serial(id SERIAL not null primary key,addr varchar(10));
CREATE TABLE
tt=# \d test_serial;
Table "public.test_serial"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+-----------------------------------------
id | integer | | not null | nextval('test_serial_id_seq'::regclass)
addr | character varying(10) | | |
Indexes:
"test_serial_pkey" PRIMARY KEY, btree (id)
准备数据：
insert into test_serial(id,addr) values(1,'北京');
insert into test_serial(id,addr) values(2,'上海');
insert into test_serial(id,addr) values(3,'广州');
insert into test_serial(id,addr) values(4,'深圳');
tt=# select * from test_serial;
id | addr
----+------
1 | 北京
2 | 上海
3 | 广州
4 | 深圳
(4 rows)
1、先删除id=4的记录：
tt=# delete from test_serial where id = 4;
DELETE 1
tt=# select * from test_serial;
id | addr
----+------
1 | 北京
2 | 上海
3 | 广州
(3 rows)
再次插入,此时不用显式定义id字段：
tt=# insert into test_serial(addr) values('深圳');
ERROR: duplicate key value violates unique constraint "test_serial_pkey"
DETAIL: Key (id)=(1) already exists. -- 第一次插入报错提示说id=1已经存在
tt=# insert into test_serial(addr) values('深圳');
ERROR: duplicate key value violates unique constraint "test_serial_pkey"
DETAIL: Key (id)=(2) already exists. -- 第二次插入报错提示说id=2已经存在
tt=# insert into test_serial(addr) values('深圳');
ERROR: duplicate key value violates unique constraint "test_serial_pkey"
DETAIL: Key (id)=(3) already exists. -- 第三次插入报错提示说id=3已经存在
tt=# insert into test_serial(addr) values('深圳');
INSERT 0 1 -- 第四次插入成功
查询结果和上面报错信息说明：id=4的记录已经插入成功，id并不是从4直接开始的，而是从1开始计算的。
tt=# select * from test_serial;
id | addr
----+------
1 | 北京
2 | 上海
3 | 广州
4 | 深圳
(4 rows)
再次删除，再次插入，此时插入的是显式定义id列为6的记录：
tt=# delete from test_serial where id = 4;
DELETE 1
tt=# select * from test_serial;
id | addr
----+------
1 | 北京
2 | 上海
3 | 广州
(3 rows)
tt=# insert into test_serial values(6,'杭州');
INSERT 0 1
tt=# select * from test_serial;
id | addr
----+------
1 | 北京
2 | 上海
3 | 广州
6 | 杭州
(4 rows) -- 插入成功
此时不显式定义id列再次插入：
tt=# insert into test_serial(addr) values('深圳');
INSERT 0 1
tt=# select * from test_serial;
id | addr
----+------
1 | 北京
2 | 上海
3 | 广州
6 | 杭州
5 | 深圳
(5 rows) -- 测试再次插入，id=5的会被使用
tt=# insert into test_serial(addr) values('南京');
ERROR: duplicate key value violates unique constraint "test_serial_pkey"
DETAIL: Key (id)=(6) already exists. -- 测试再次插入，提示id=6的已存在（不应该从7开始吗？），
显然不是通过计算id最大值加1得来的，和MySQL不一样。
以上说明：显示定义id列值和不显示定义列值，PostgreSQL计算自增值得算法不一样。
pg赋值插入的时候是改变了字段的值但是没有改变序列的值，所以再次缺省赋值
插入时依旧是顺序获取对应序列的下一个值
tt=# insert into test_serial(addr) values('南京');
INSERT 0 1 -- 测试再次插入成功id为7
tt=# select * from test_serial;
id | addr
----+------
1 | 北京
2 | 上海
3 | 广州
6 | 杭州
5 | 深圳
7 | 南京
(6 rows)
此时再次插入指定id为4的记录，插入成功：
tt=# insert into test_serial(id,addr) values(4,'成都');
INSERT 0 1
tt=# select * from test_serial;
id | addr
----+------
1 | 北京
2 | 上海
3 | 广州
6 | 杭州
5 | 深圳
7 | 南京
4 | 成都
(7 rows)
2、再看pg的truncate
tt=# truncate table test_serial;
TRUNCATE TABLE
tt=# insert into test_serial(addr) values('西安');
INSERT 0 1
tt=# select * from test_serial;
id | addr
----+------
9 | 西安
(1 row)
结果发现自增列id不是从1开始，也就是说truncate不会清空自增值。
3、再看pg的插入最大值和MySQL一样，插入最大值,
int的取值范围是1到2147483647,所以插入大于2147483647值会报错。
tt=# insert into test_serial(id,addr) values(2147483647,'unknow');
INSERT 0 1
tt=# insert into test_serial(id,addr) values(2147483648,'unknow');
ERROR: integer out of range
结论：
1、mysql在字段上加AUTO_INCREMENT属性来实现自增，pg使用serial类型，序列号类型其实不是真正的类型，当声明一个字段为序列号类型时其实是创建了一个序列。
2、mysql插入的是字段的值，pg执行插入的时候变化的是字段值和序列的值，只不过在缺省插入的时候二者相等。
所以，INSERT时如果没有给该字段赋值会默认获取下一个值或者对应序列的下一个值。
但是在该字段有赋值插入之后，就有区别了，pg赋值插入的时候是改变了字段的值但是没有改变序列的值，所以再次缺省赋值插入时依旧是顺序获取对应序列的下一个值。
3、mysql和pg在delete之后插入是同样的，但是truncate操作在pg上是清空了表数据，但是没有清空对应的序列，所以在执行truncate之后，再次插入会获取者对应序列的下一个值，而在mysql上是从1开始。
4、达到规定最大值之后都不能继续插入，所以不能循环使用。
备注：结论是完全参考[PostgreSQL中文社区]微信公众号,本文是记录验证原文结论实验步骤，仅供学习
网友评论