pgsql 学习笔记(一):批量修改数据表 id 的自增序列

前言:

我学习 pgsql 没有多久,对于数据库方面一知半解。开始写这个系列只是为了锻炼自己,有出错之处,敬请谅解,并请告知。

正文:

首先讲一下 pgsql 和 mysql 在 id 自增这一点上的不同:在 mysql 中,实现 id 自增的方式是依靠加一个 auto_increment 标志,而在 pgsql 中,id 自增是通过序列 SEQUENCE。

CREATE TABLE "oc_cms_staff" (
    "id" int4 NOT NULL DEFAULT nextval('oc_cms_staff_id_seq'::regclass),
    ...
};

其中的 oc_cms_staff_id_seq 即为序列。我之所以要去学习这篇文章的主题,也正是因为错误的数据库操作,导致了其中的数据表 id 依赖的序列出现混乱。
序列名通常为表名后加 “_id_seq”。为了达到本文的目的,我们先需要得到数据库中所有的表名,对应的查询语句如下:

SELECT tablename FROM pg_tables where tableowner='postgres' and SCHEMANAME='public'

得到了表名,我们就可以得到序列名,并针对序列进行修改。这包括了几步,第一步应该是删除之前该序列。但直接删除会报错,因为之前已经有数据表的 id 依赖于它,不能强行删除,删除前应该先将依赖解除。删除之后再重新创建,创建时应该设置它的起始值为 id 目前的最大值,否则会冲突。最后是重新给 id 绑定序列。

// 删除前先解除 id 对该序列的依赖
ALTER TABLE tablename ALTER COLUMN id SET DEFAULT null;
DROP SEQUENCE IF EXISTS sequence_name;
// 这里的 id_max 即 id 目前的最大值,可通过 “SELECT MAX(id) FROM tablename” 得到
CREATE SEQUENCE sequence_name START WITH id_max;
ALTER TABLE tablename ALTER COLUMN id SET DEFAULT nextval('sequence_name'::regclass);

这样就修改了数据表 id 的自增序列。知道了可以修改数据表 id 的自增序列的查询语句,接下来我们就可以写一个 PHP 脚本来批量修改了。

<?php

$host = 'your_host';
$port = 'your_port';
$dbname = 'your_dbname';
$user = 'your_user';
$password ='your_password';

// 连接数据库
$dbconn = pg_connect('host='.$host.' port='.$port.' dbname='.$dbname.' user='.$user.' password='.$password);

if ($dbconn === false) {
    exit('connect error!');
}

// 查询所有的数据表名
$result = pg_query($dbconn, "SELECT tablename FROM pg_tables where tableowner='postgres' and SCHEMANAME='public'");
$tablename_list = pg_fetch_all($result);

// 根据表名依次修改序列
foreach ($tablename_list as $item) {
    // 获得 id 目前的最大值
    $id_max_list = pg_fetch_all(pg_query($dbconn, "SELECT MAX(id) FROM ".$item['tablename']));
    $id_max = $id_max_list[0]['max'] + 1;

    // 依据表名拼接序列名
    $sequence = $item['tablename']."_id_seq";

    // 依次执行结束绑定、删除序列、重建序列、绑定序列
    $alter1_res = pg_query($dbconn, "ALTER TABLE ".$item['tablename']." ALTER COLUMN id SET DEFAULT null;");
    $alter2_res = pg_query($dbconn, "DROP SEQUENCE IF EXISTS ".$sequence.";");
    $alter3_res = pg_query($dbconn, "CREATE SEQUENCE ".$sequence." START WITH ".$id_max.";");
    $alter4_res = pg_query($dbconn, "ALTER TABLE ".$item['tablename']." ALTER COLUMN id SET DEFAULT nextval('".$sequence."'::regclass);");

    // 打印每句查询语句成功或失败
    echo $item['tablename'].' '.($alter1_res === false ? 'error:ALTER TABLE pg_cms_map ALTER COLUMN id SET DEFAULT null;' : 'success').'<br>';
    echo $item['tablename'].' '.($alter2_res === false ? 'error:DROP SEQUENCE IF EXISTS pg_cms_map_id_seq;' : 'success').'<br>';
    echo $item['tablename'].' '.($alter3_res === false ? 'error:CREATE SEQUENCE pg_cms_map_id_seq START WITH 23;' : 'success').'<br>';
    echo $item['tablename'].' '.($alter4_res === false ? 'error:ALTER TABLE pg_cms_map ALTER COLUMN id SET DEFAULT nextval(\'pg_cms_map_id_seq\'::regclass);' : 'success').'<br>';
}

好了,大功告成!

推荐阅读更多精彩内容