前提
采用navicat,将mysql的表及数据迁入pgsql,但是自增字段及索引没有迁移过去,所以需要自己写脚本加上
–增加自增序列
SELECT
concat('create sequence ',a.name,'_seq',' INCREMENT 1 START 1 NO MINVALUE NO MAXVALUE;')
FROM
(SELECT table_name as name FROM
information_schema.columns WHERE
table_schema = 'aml_payment' -- schema的名称
and COLUMN_NAME = 'id' -- 字段为id
)a;
–修改键id为自增序列
SELECT
concat('alter table ',a.name,' alter column id set default nextval(''',a.name,'_seq'');')
FROM
(SELECT table_name as name FROM
information_schema.columns WHERE
table_schema = 'aml_payment'
and COLUMN_NAME = 'id')a;
原表数量太多,在此干脆将字段为id的表拎出来,拼接成pgsql更改自增的语法,而后在pgsql执行
添加索引
SELECT
CONCAT('CREATE ',
if(a.non_unique = 1,'INDEX ','UNIQUE INDEX '),
a.INDEX_NAME,
' ON ',
a.TABLE_NAME,
' using btree',
' (',
a.index_column,
');'
)
FROM
(select TABLE_NAME, non_unique,INDEX_NAME, GROUP_CONCAT(COLUMN_NAME) as INDEX_COLUMN
from
information_schema.statistics
where
table_schema='aml_payment' -- 数据库名
AND index_name <> 'PRIMARY' -- 将主键去掉
GROUP BY TABLE_NAME, INDEX_NAME,non_unique) a
在mysql环境下运行,而后将检索出来的内容,即pgsql增加索引的语句,在pgsql的环境下运行