我一直在从MySQL导入一些数据到Postgres,该计划应该很简单-手动重新创建具有相同数据类型的表,划分一种输出为CSV的方式,将数据传输,然后将其复制到Postgres。做完了

mysql -u whatever -p whatever -d the_database

SELECT * INTO OUTFILE '/tmp/the_table.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' FROM the_table;


发送并导入到postgres

psql -etcetc -d other_database

COPY the_table FROM '/csv/file/location/the_table.csv' WITH( FORMAT CSV, DELIMITER ',', QUOTE '"', ESCAPE '\', NULL '\N' );


太久了,我已经忘记了'0000-00-00'是一件事情...
因此,首先我必须想出一种方法来处理奇怪的数据类型,最好是在MySQL端,因此为我计划导入的20个左右的表编写了此脚本,以解决任何不兼容问题并相应地列出各列

with a as (
    select
        'the_table'::text as tblname,
        'public'::text as schname
), b as (
    select array_to_string( array_agg( x.column_name ), ',' ) as the_cols from (
        select
            case
                when udt_name = 'timestamp'
                then 'NULLIF('|| column_name::text || ',''0000-00-00 00:00:00'')'
                when udt_name = 'date'
                then 'NULLIF('|| column_name::text || ',''0000-00-00'')'
                else column_name::text
            end as column_name
        from information_schema.columns, a
        where table_schema = a.schname
        and table_name = a.tblname
        order by ordinal_position
    ) x
)
select 'SELECT '|| b.the_cols ||' INTO OUTFILE ''/tmp/'|| a.tblname ||'.csv'' FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''"'' ESCAPED BY ''\\'' FROM '|| a.tblname ||';' from a,b;


生成CSV,好的。传输,确定-结束...

BEGIN;
ALTER TABLE the_table SET( autovacuum_enabled = false, toast.autovacuum_enabled = false );
COPY the_table FROM '/csv/file/location/the_table.csv' WITH( FORMAT CSV, DELIMITER ',', QUOTE '"', ESCAPE '\', NULL '\N' ); -- '
ALTER TABLE the_table SET( autovacuum_enabled = true, toast.autovacuum_enabled = true );
COMMIT;


一切顺利,直到我遇到以下消息:

ERROR:  invalid byte sequence for encoding "UTF8": 0xed 0xa0 0xbd
CONTEXT:  COPY new_table, line 12345678


第二张表也遇到相同的错误,但是每隔一张成功导入。
现在,MySQL数据库中的所有列和表都设置为utf8,第一个包含消息的令人讨厌的表是

CREATE TABLE whatever(
col1 int(11) NOT NULL AUTO_INCREMENT,
col2 date,
col3 int(11),
col4 int(11),
col5 int(11),
col6 int(11),
col7 varchar(64),
PRIMARY KEY(col1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


所以大概数据应该是utf ...对吗?为确保没有重大错误,我编辑了my.cnf以确保我能想到的所有内容都包括编码

[character sets]
default-character-set=utf8
default-character-set=utf8
character-set-server = utf8
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'


为了进行转换,我更改了最初的“查询生成查询”案例语句以转换列

        case
            when udt_name = 'timestamp'
            then 'NULLIF('|| column_name::text || ',''0000-00-00 00:00:00'')'
            when udt_name = 'date'
            then 'NULLIF('|| column_name::text || ',''0000-00-00'')'
            when udt_name = 'text'
            then 'CONVERT('|| column_name::text || ' USING utf8)'
            else column_name::text
        end as column_name


仍然没有运气。谷歌搜索“ 0xed 0xa0 0xbd”之后,我还是一个聪明人,字符集并不是我真正的事情。
我什至将3 gig csv文件打开到其提到的行,并且似乎没有任何异常,用十六进制编辑器查看时,我看不到那些字节值(编辑:也许我看起来不够努力)所以我开始耗尽想法。我是否错过了一些非常简单的东西,令人担忧的是,其他一些表是否也可能被“更安静地”破坏了?

在ubuntu 14.04操作系统上,MySQL版本是5.5.44,而Postgres是9.4。

最佳答案

没有其他尝试,我尝试了最简单的解决方案,只需更改文件

iconv -f utf-8 -t utf-8 -c the_file.csv > the_file_iconv.csv


在新文件和原始文件之间大约有100个字节,因此在我看不见的地方一定有无效字节,它们“正确地”导入了,所以我想那很好,但是知道是否可以在导入之前发现文件之前,有一些方法可以在创建文件时强制执行正确的编码。

10-06 10:12
查看更多