将 mysql 脚本转换为 postgresql 脚本 [英] converting mysql scripts to postgresql script

问题描述

我在来自 mysql 数据库的 .sql 文件中有以下行:

I have the following line in a .sql file from a mysql db:

  ALTER TABLE lcr_gw ALTER COLUMN ip_addr TYPE VARCHAR(50) DEFAULT NULL;

我想把它转换成 postgresql 能理解的语法.在我的个人测试中，我只能通过将其分解为两个单独的语句来使其工作，如下所示:

I would like to convert it into syntax that postgresql would understand. In my personal tests, I was only able to get it to work by breaking it down into two separate statements, like so:

ALTER TABLE lcr_gw ALTER COLUMN ip_addr TYPE VARCHAR(50);
ALTER TABLE lcr_gw ALTER COLUMN ip_addr SET DEFAULT NULL;

只是想知道是否有一种方法可以将两个语句合并为一个，但是 postgresql 会喜欢的一个?

Just wondering if there's a way to consolidate the two statements back into one, but one that postgresql will be happy with?

谢谢！

推荐答案

您发布的声明语法根本无效:
SQL 小提琴

要更改 MySQL 中的类型，您可以使用 CHANGE 或 MODIFY.
要更改默认值，您可以使用 DROP DEFAULT 或 SET DEFAULT NULL.

To change the type in MySQL, you would use CHANGE or MODIFY.
To change the default you would use DROP DEFAULT or SET DEFAULT NULL.

如果意图是更改类型并重置列默认值:

If the intention was to change the type and reset the column default:

就像在 MySQL 中一样，可以打包多个将操作转换为单个 ALTER TABLE 在 Postgres 中的语句 .

Like in MySQL, you can pack multiple actions into a single ALTER TABLEstatement in Postgres .

ALTER TABLE lcr_gw ALTER COLUMN ip_addr SET DEFAULT NULL
                  ,ALTER COLUMN ip_addr TYPE VARCHAR(50);

根据文档:

提供指定多个更改的选项的主要原因在单个 ALTER TABLE 中是多个表扫描或重写可以从而被合并成一个单一的遍历表.

The main reason for providing the option to specify multiple changes in a single ALTER TABLE is that multiple table scans or rewrites can thereby be combined into a single pass over the table.

但是如果列上存在与新类型不兼容的DEFAULT，则必须运行两个单独的语句:

But if there was a DEFAULT on the column that is incompatible with the new type, you have to run two separate statements:

ALTER TABLE lcr_gw ALTER COLUMN ip_addr SET DEFAULT NULL;
ALTER TABLE lcr_gw ALTER COLUMN ip_addr TYPE VARCHAR(50);

无论如何在这种情况下都没有关系.

Doesn't matter in this case anyway.

这篇关于将 mysql 脚本转换为 postgresql 脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！