前言：
我學(xué)習(xí) pgsql 沒有多久第岖，對(duì)于數(shù)據(jù)庫方面一知半解。開始寫這個(gè)系列只是為了鍛煉自己，有出錯(cuò)之處，敬請(qǐng)諒解付鹿，并請(qǐng)告知。
正文：
首先講一下 pgsql 和 mysql 在 id 自增這一點(diǎn)上的不同：在 mysql 中，實(shí)現(xiàn) id 自增的方式是依靠加一個(gè) auto_increment 標(biāo)志谎替，而在 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 即為序列杠河。我之所以要去學(xué)習(xí)這篇文章的主題券敌，也正是因?yàn)殄e(cuò)誤的數(shù)據(jù)庫操作熊镣，導(dǎo)致了其中的數(shù)據(jù)表 id 依賴的序列出現(xiàn)混亂。
序列名通常為表名后加 “_id_seq”。為了達(dá)到本文的目的文捶，我們先需要得到數(shù)據(jù)庫中所有的表名，對(duì)應(yīng)的查詢語句如下：
SELECT tablename FROM pg_tables where tableowner='postgres' and SCHEMANAME='public'
得到了表名，我們就可以得到序列名膝迎，并針對(duì)序列進(jìn)行修改。這包括了幾步卖漫，第一步應(yīng)該是刪除之前該序列突委。但直接刪除會(huì)報(bào)錯(cuò)勾笆，因?yàn)橹耙呀?jīng)有數(shù)據(jù)表的 id 依賴于它，不能強(qiáng)行刪除，刪除前應(yīng)該先將依賴解除。刪除之后再重新創(chuàng)建，創(chuàng)建時(shí)應(yīng)該設(shè)置它的起始值為 id 目前的最大值，否則會(huì)沖突状您。最后是重新給 id 綁定序列。
// 刪除前先解除 id 對(duì)該序列的依賴
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);
這樣就修改了數(shù)據(jù)表 id 的自增序列先改。知道了可以修改數(shù)據(jù)表 id 的自增序列的查詢語句貌嫡，接下來我們就可以寫一個(gè) PHP 腳本來批量修改了。
<?php
$host = 'your_host';
$port = 'your_port';
$dbname = 'your_dbname';
$user = 'your_user';
$password ='your_password';
// 連接數(shù)據(jù)庫
$dbconn = pg_connect('host='.$host.' port='.$port.' dbname='.$dbname.' user='.$user.' password='.$password);
if ($dbconn === false) {
exit('connect error!');
}
// 查詢所有的數(shù)據(jù)表名
$result = pg_query($dbconn, "SELECT tablename FROM pg_tables where tableowner='postgres' and SCHEMANAME='public'");
$tablename_list = pg_fetch_all($result);
// 根據(jù)表名依次修改序列
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;
// 依據(jù)表名拼接序列名
$sequence = $item['tablename']."_id_seq";
// 依次執(zhí)行結(jié)束綁定、刪除序列、重建序列荒给、綁定序列
$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>';
}
好了夺脾，大功告成菲茬！
