Contents
  1. 1. 新建数据库
  2. 2. 增删查减
  3. 3. 主键自增
  4. 4. 总结

平常在Django项目中大量使用自增这个键,平常都是使用ORM,很少去了解这个东西在数据库中具体使用,最近遇到要备份和复原数据的事情,趁着这次好好探索一下这个自增键的使用

Django里面大部分都是将其作为Int自增主键来使用,第一个不需要维护一个唯一值,第二个使用Int作为主键的话,搜索和外键关联速度比较快。

我们这次从原生SQL出发,探索一下这个自增主键在数据库中的具体使用

新建数据库

我们先新建一个数据库

create table inc(
id serial not null,
name text
);

PG里面简单的使用serial关键字就会生成一个自增键,默认会在数据库新建一个索引表,例如上面就会新建一个inc_id_seq的索引表,这个字段类型为int,如果数据库很大,我们可以使用BIGSERIAL键申请一个bigint类型的字段

我们可以看一下这个索引表里面有什么

         Sequence "public.inc_id_seq"
    Column     |  Type   |        Value        
---------------+---------+---------------------
 sequence_name | name    | inc_id_seq
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 30
 is_cycled     | boolean | f
 is_called     | boolean | t

我们可以这个索引表其实就是维护了一个参数,通过字段我们可以知道,这是一个自增为1的键,下一个值为2,目前没有插入一个值

增删查减

我们通过一些基本操作来看看这个自增键的作用

  • 首先是插入
insert into inc (name) values ('1'),('2'), ('3');

我们插入三个值,我们再查看索引表,发现last_value变成了3

这个是没有指定id的值插入,我们试试显式声明插入

insert into inc  values (1, '1'),(2, '2'), (3, '3'), (4, '4');

我们惊奇的发现,在我们显式声明自增键的值的时候,索引表并没有变化,last_value还是3,这说明只有在不声明自增键,让数据库自己新建的时候,索引表才会更新

我们可以把自增键看做一个默认值,当没有给自增键赋值的时候,这个自增键会从这个键的索引表中得到下一次自增的值

所以我们再尝试使用不声明自增键值的方法插入一个新值

insert into inc (name) values ('4')

我们发现索引表中last_value变成了4

主键自增

由于我们在Django里面使用自增,一般都是将其声明为主键,设为唯一值,所以如果我们将声明表的结构变成

    create table inc(
id serial not null PRIMARY KEY,
name text
    );

上面的情况就不可能发生了,因为我们把自增键声明为主键,不过有意思的事就是如果你像上面一样指定了一个自增主键值为4,然后不指定再插入4,你会发现第一次会报主键不允许重复的错误,第二次则会成功插入,而且索引表的last_value变成了5

看来并不是每次成功的时候才会更新last_value值,只要让系统自己去申请自增值就会更新索引表,我尝试了对表的增删查改,发现只有insert并且申请自增值的时候才会更新索引表,而且这个索引表之后增加,不会减少,所以有时候你删掉最大的值,自增键默认又从最后一次开始更新

总结

在对单个数据表备份还原的时候,由于简单的使用了COPY命令进行备份还原,通过上面的探索我们发现如果涉及到自增主键的导入导出,在新表导入旧数据是不会出错的,但是由于我们没有考虑自增键的影响(我们导入自增键是显示赋值),在后面插入数据的时候有可能会报主键重复的错误

为了避免以后插入入数据出现这样的错误,我们有两种措施

  1. 使用COPY命令导入导出时候不获取自增键值
  2. COPY导入新表后自己更新索引表

第一种的话SQL比较繁琐(必须写出表所有字段值),推荐使用第二种

我们可以简单的使用

SELECT MAX(id) FROM your_table;

先获取自增键最大值,然后更新索引值(999为上面你获取的最大值)

SELECT setval('your_table_id_seq', 999, false);

当然我们可以将这条语句合正一句话

SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);

这样我们就可以开心的完成单表导入导出了

ps:
在使用COPY命令时必须是superuser才能从文件中读取和导入数据,最简单的方法是用superuser账号加权使用alter user xxx superuser,待倒完数据后再降权alter user xxx nosuperuser

Contents
  1. 1. 新建数据库
  2. 2. 增删查减
  3. 3. 主键自增
  4. 4. 总结