使mysqldump为psql输入转储适当的数据（转义单引号）

Question

使mysqldump为psql输入转储适当的数据（转义单引号）

4

我正试图将一个MySQL数据库转移到PostgreSQL。我已经在Postgres中重建了模式，所以我需要做的就是将数据传输过去，而不必重新创建表。

我可以使用遍历所有记录并逐个插入的代码来实现此操作，但我尝试过，对于我们的数据库大小来说太慢了，因此我尝试改用mysqldump和管道到psql（每个表一次），之后我可能会并行处理。

我已经不得不通过打开和关闭各种选项来跨越各种障碍才能走到这一步，以获取一个基本合理的转储。再次说明，这只转储了INSERT INTO，因为我已经准备好了空模式，以便将数据插入其中：

      /usr/bin/env \
      PGPASSWORD=mypassword \
      mysqldump \
      -h mysql-server \
      -u mysql-username \
      --password=mysql-password \
      mysql-database-name \
      table-name \
      --compatible=postgresql \
      --compact \
      -e -c -t \
      --default-character-set=utf8 \
      | sed "s/\\\\\\'/\\'\\'/g" \
      | psql \
      -h postgresql-server \
      --username=postgresql-username \
      postgresql-database-name

除了那个丑陋的sed命令之外，其他都可以管理。我正在尝试使用sed将MySQL对字符串中单引号的引用（'O\'Connor'）转换为PostgreSQL的引用要求（'O''Connor'）。它起作用，直到转储中出现这样的字符串：'以反斜杠结尾的字符串\ '...是的，似乎我们数据库中有一些用户输入具有这种格式，这是完全有效的，但不能通过我的sed命令。我可以添加一个lookbehind到sed命令中，但我感觉自己正在爬进一个兔子洞。是否有办法：

a）告诉mysqldump通过加倍引用单引号来引用单引号 b）告诉psql期望反斜杠被解释为引用转义符号？

我还有另一个与BINARY和bytea差异有关的问题，但我已经通过base64编码/解码阶段解决了这个问题。

编辑|看起来我可以使用“set backslash_quote = on; set standard_conforming_strings = off;”实现（b），但我不确定如何将其注入到管道输出的开头。

- d11wtq

3个回答

3

使用mysqldump的--tab选项将表导出为TSV格式，然后使用psql的COPY方法导入。

- eggyal

谢谢，我会看一下这个。我曾经试过CSV，但它在处理二进制数据和大型文本字段方面不够强大。想必TSV有应对这些问题的办法？ - d11wtq

@d11wtq：使用--hex-blob。大文本字段不应该是问题。 - eggyal

这看起来非常有前途！非常感谢 :) - d11wtq

我最终没有使用hex-blob，因为这意味着我需要再次使用sed，而这充满了边缘情况。相反，我在MySQL中执行了十六进制转换，然后将该字段导出为Postgres期望的文本格式：CREATE FUNCTION PG_BYTEA(s BLOB) RETURNS TEXT DETERMINISTIC RETURN CONCAT('\\x', HEX(s));。只要在Postgres中打开了backslash_quote并关闭了standard_conforming_strings，这个方法就可以完美地工作。 - d11wtq

1

试试这个：

sed -e "s/\\\\'/\\\\\\'/g" -e "s/\([^\\]\)\\\\'/\1\\'\\'/g"

是的，“倾斜牙签症”，我知道。

- Ansgar Wiechers

说实话，我想避免使用sed。它感觉不是正确的解决方案。我编写了一个小脚本，通过管道将数据传递到其中，以添加一些标题，使postgres处于较少ansi严格模式，并且现在正在工作，但是@eggyal的解决方案希望更加简洁。 - d11wtq

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- lee · Accepted Answer

文件psqlrc和~/.psqlrc可能包含要在客户端启动时执行的SQL命令。您可以将这三行或任何其他设置放入该文件中。

SET standard_conforming_strings = 'off';
SET backslash_quote = 'on';
SET escape_string_warning = 'off';

以下是psql的设置，结合以下的mysqldump命令，可以成功地将mysql 5.1中的仅数据迁移到具有UTF-8文本（在我的情况下为中文）的postgresql 9.1。如果创建一个中间文件太大或太耗时，那么这种方法可能是迁移大型数据库的唯一合理方式。由于两个数据库的数据类型差异很大，因此需要手动迁移架构。计划编写一些DDL以确保其正确性。

mysqldump \
--host=<hostname> \
--user=<username> \
--password=<password> \
--default-character-set=utf8 \
--compatible=postgresql \
--complete-insert \
--extended-insert \
--no-create-info \
--skip-quote-names \
--skip-comments \
--skip-lock-tables \
--skip-add-locks \
--verbose \
<database> <table> | psql -n -d <database>