mysql导入postgresql_MYSQL数据库迁移到POSTGRESQL数据库（人工迁移）

系统管理员 2023-01-11 08:27 296阅读 0赞

利用Navicat for Mysql工具(其它工具也可以，比如mysqldump)将数据库模式导出(只导表结构不含数据)方法略，生成的文件名为mysql.sql

利用linux sed工具把导出的脚本中的所有COMMENT及mysql的独有原素替换掉，并生成新文件table\_new.sql，见以下脚本

cat mytab.sql  | sed 's/^.\*ENGINE=.\*$/);/g' | sed 's/COMMENT.\*$/,/g' | sed 's/\`//g' > table\_new.sql

将所有的INDEX语句单独生成一个文件(此步也可以省略，后面有其它方法)

cat table\_new.sql | egrep '\\-|^\\s\*KEY|^\\s\*UNIQUE' > key.sql

将所有的外键语句单独生成一个文件 (此步可以省略，后面有其它方法)

cat table\_new.sql | egrep '\\-|^\\s\*CONSTRAINT' > constraint.sql

去掉KEY和CONSTRAINT的语句，并生成新文件 table\_last.sql

cat table\_new.sql | sed  's/^\\s\*KEY.\*$//' | sed 's/^\\s\*CONSTRAINT.\*$//' | sed 's/^\\s\*UNIQUE.\*$//'  | sed '/^$/d'> table\_last.sql

转换换行符

unix2dos table\_last.sql

把文件COPY到WINDOWS中，用记事本工具(或其它工具)对一些特殊值进行查找、替换操作(此步的意思就是把mysql中的类型对应到postgresql中的类型)，比如：

查找 ID int(12) NOT NULL AUTO\_INCREMENT 替换 ID bigserial NOT NULL (自增长类型字段)

查找 datetime 替换 timestamp

……

重新整理一些特殊表，因为第5步中去掉了所有KEY和CONSTRAINT的语句，所以如果表字段中包括KEY开头或是CONSTRAINT开头的字段时(比如字段 KEYCD，KEYTTL等)要把这些特殊的表的建表语句手工制作一变，并在table\_last.sql中替换掉原来的语句。

在Postgresql中执行table\_last.sql语句(方法略)，如果有错误进行微调，直到所有表建立成功。

导入数据(先在mysql中导出，导出后的文件要替换换行符不然报错，然后再导入到postgresql)导出语句(在MYSQL中执行以下语句，并复制结果后再次执行，便会在服务器的目录下生成以每个表为单位的数据文件,我用的工具是Navicat for Mysql。注意替换table\_schema，这里生成数据文件的目录为：/usr/local/mysql/outfile/，也可换成其它目录)

SELECT CONCAT('select \* from ',

table\_name,

" into outfile '/usr/local/mysql/outfile/",

table\_name ,

'.dat' ,"'"

" fields terminated by '|' ;")

FROM information\_schema.tables

WHERE table\_schema='DEVELOP';

替换换行符后将生成的文件COPY到postgresql服务器的目录下

sed -i 's/\\r//g' outfile/\*

导入语句(在postgresql中执行，得到查询结果后复制查询结果，并再次执行,注意一定要用有superuser权限的用户执行，这里用的是pgadmin工具，目录是/tmp/data,注意替换table\_catalog中的值，我的数据名为DEVELOP)

select 'copy necsl.'||table\_name|| ' from ' || chr(39)||'/tmp/data/'

||upper(table\_name)||

'.dat' || chr(39) ||

' with DELIMITER ' || chr(39) || '|' || chr(39) ||';'

from information\_schema.tables

where table\_schema='necsl' and

table\_catalog='DEVELOP' ;

数据导入后可以用以下方法验证是否导入正确

\--在mysql及PG中分别建立下表

create table table\_count(tblname varchar(100) primary key not null,tblrecorder integer);

/\*在mysql中执行以下语句，并将结果复制后再次执行，将会生成在表table\_count中生成每张表有多少记录\*/

SELECT CONCAT('insert into table\_count select ' ,"'",table\_name,"'",' ,count(\*) from ',

table\_name ,";")

FROM information\_schema.tables

WHERE table\_schema='DEVELOP';

\--在postgresql中执行以下语句，并将结果复制后再次执行，将会生成在表table\_count中生成每张表有多少记录,然后与mysql中table\_count的表比较即可

select 'insert into necsl.table\_count select '|| quote\_literal(table\_name) ||',count(\*) from   necsl.'||table\_name|| ';'

from information\_schema.tables

where table\_schema='necsl' and

table\_catalog='DEVELOP' ;

\--如果导入有问题，可以在PG中执行以下句语生成清空所有表的语句，将执行结果复制再执行即可清空所有表

PG truncate

select 'truncate table  necsl.'||table\_name|| ';'

from information\_schema.tables

where table\_schema='necsl' and

table\_catalog='DEVELOP' ;

/\*生成索引信息及外键，在mysql中分别执行以下语句，并将结果复制到POSTGRESQL中执行\*/

/\*生成唯一索引\*/

select

CONCAT (

'CREATE UNIQUE INDEX ' ,

table\_name,'\_IDX\_',index\_name,

' ON ',

table\_name,

'(',

GROUP\_CONCAT(column\_name order by seq\_in\_index),

');'

)

from statistics

where table\_schema='DEVELOP'

AND INDEX\_NAME <>'PRIMARY'

AND INDEX\_SCHEMA='DEVELOP'

AND NON\_UNIQUE=0

GROUP BY index\_name,table\_name

ORDER BY TABLE\_NAME,INDEX\_NAME,seq\_in\_index asc

/\*生成btree索引\*/

select

CONCAT (

'CREATE INDEX ' ,

table\_name,'\_IDX\_',index\_name,

' ON ',

table\_name,

'(',

GROUP\_CONCAT(column\_name order by seq\_in\_index),

');'

)

from statistics

where table\_schema='DEVELOP'

AND INDEX\_NAME <>'PRIMARY'

AND INDEX\_SCHEMA='DEVELOP'

AND NON\_UNIQUE=1

GROUP BY index\_name,table\_name

ORDER BY TABLE\_NAME,INDEX\_NAME,seq\_in\_index asc

/\*生成外键 \*/

select

concat('alter table ',

c.TABLE\_NAME,

' add constraint ',

c.CONSTRAINT\_NAME,

' foreign key(',

c.COLUMN\_NAME,

') references ',

c.REFERENCED\_TABLE\_NAME,

'( ',

c.REFERENCED\_COLUMN\_NAME,

');')

from TABLE\_CONSTRAINTS t,KEY\_COLUMN\_USAGE c

where t.CONSTRAINT\_SCHEMA='DEVELOP'

AND t.CONSTRAINT\_TYPE='FOREIGN KEY'

AND t.TABLE\_SCHEMA='DEVELOP'

AND c.REFERENCED\_TABLE\_SCHEMA='DEVELOP'

AND t.CONSTRAINT\_NAME =c.CONSTRAINT\_NAME

and t.table\_name=c.table\_name;

mysql导入postgresql_MYSQL数据库迁移到POSTGRESQL数据库（人工迁移）

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 postgresql数据库路径迁移

相关 PostgreSQL教程：数据迁移（MySQL数据迁移到PostgreSQL）

相关 mysql——数据库迁移

相关 mysql迁移数据库

相关数据库迁移:从MySQL到PostgreSQL

相关 mysql 迁移导入到指定数据库

相关 postgresql 数据库迁移

相关 mysql导入postgresql_MYSQL数据库迁移到POSTGRESQL数据库（人工迁移）

相关 postgresql数据库路径迁移

相关 kettle7.0数据库迁移(MySQL迁移到Postgresql，迁移过程中自动创建表结构)