将MySQL查询转换为PostgreSQL [英] Convert MySQL query into PostgreSQL

问题描述

我有这个查询：

DROP TABLE IF EXISTS tmp_table;
CREATE TEMPORARY TABLE tmp_table(id int primary key) 
IGNORE (
   SELECT user2role.userid AS userid
     FROM user2role 
    INNER JOIN users ON users.id=user2role.userid 
    INNER JOIN role ON role.roleid=user2role.roleid 
    WHERE role.parentrole like 'H1::H2::H3::H4::H5::%') 
UNION (
   SELECT groupid
     FROM groups
    WHERE groupid IN (2,3,4));

此查询最初是用MySQL编写的，而不是如果存在则删除表使用不存在。我更改了该部分，但是我不知道如何处理 IGNORE 。

This query was originally written in MySQL and instead of DROP TABLE IF EXISTS it used IF NOT EXISTS. I changed that part, but I don't know what to do about the IGNORE.

首先，要做什么 IGNORE 在做什么？

First off, what is IGNORE doing?

我尝试查找PostgreSQL等效项，但它们似乎都涉及复杂的过程。我是否需要为此编写程序？如果我必须写一个，那会是什么样？我可以使用一些PHP代码代替 IGNORE 吗？ （SQL查询由PHP生成。）

I tried looking for PostgreSQL equivalents, but they all seem to involve complicated procedures. Do I have to write a procedure for this? And if I have to write one, what would it look like? Could I just emulate IGNORE using some PHP code instead? (The SQL queries are generated by PHP.)

推荐答案

您将在postgres中这样编写。

IGNORE 在这里无关紧要，因为该表刚刚被重新创建并且保证为空。并且 UNION 保证没有重复行插入。

You would write like this in postgres.
IGNORE is irrelevant here, as the table has just been recreated and is guaranteed to be empty. And UNION guarantees there are no duplicate rows inserted.

DROP TABLE IF EXISTS tmp_table;

CREATE TEMP TABLE tmp_table(id int4 primary key);

INSERT INTO tmp_table
SELECT user2role.userid::int4 AS id
  FROM user2role 
  JOIN users ON users.id = user2role.userid 
  JOIN role ON role.roleid = user2role.roleid 
 WHERE role.parentrole like 'H1::H2::H3::H4::H5::%'
UNION
SELECT groupid::int4
  FROM groups
 WHERE groupid in (2,3,4);

如果SELECT中不能出现重复，则可以考虑使用 UNION ALL更快而不是 UNION 。否则，您需要 UNION 来消除可能的欺骗。在此处中阅读。

如果数据集很大，则可以考虑在INSERT之后 创建主键。这样更快。

If duplicates in the SELECT cannot occur, you might consider the faster UNION ALL instead of UNION. Otherwise you need UNION to eliminate possible dupes. Read here.
If your dataset is large you might consider creating the primary key after the INSERT. That's faster.

阅读 mySQL文档对 IGNORE 的影响。

在重新访问页面时，我意识到您在原始代码中提到了 IF NOT EXISTS 。
您不是这样说的，但这仅在原始代码仅在表不存在的情况下创建了表的情况下才有意义，这可能导致表在不为空之前插。在这种情况下， IGNORE 是相关的，并且需要与PostgreSQL中的等效项。

On revisiting the page I realized you mention IF NOT EXISTS in the original code. You don't say so, but that only makes sense if the original code created the table only if it didn't exist already, which introduces the possibility of it being not empty before the INSERT. In this case IGNORE is relevant and needs an equivalent in PostgreSQL.

所以这是 替代答案 来解释您的问题。

So here is alternative answer for that interpretation of your question.

如果不存在则创建温度表已在PostgreSQL 9.1 中实现。

对于较旧的版本，我发布了<最近在SO上进行href = https://stackoverflow.com/questions/1766046/postgresql-create-table-if-not-exists>解决方案。

CREATE TEMP TABLE IF NOT EXISTS tmp_table(id int4 primary key);

INSERT INTO tmp_table
SELECT x.id
  FROM (
    SELECT user2role.userid::int4 AS id
      FROM user2role 
      JOIN users ON users.id = user2role.userid 
      JOIN role ON role.roleid = user2role.roleid 
     WHERE role.parentrole like 'H1::H2::H3::H4::H5::%'
    UNION
    SELECT groupid::int4
      FROM groups
     WHERE groupid in (2,3,4)
        ) x
  LEFT JOIN tmp_table t USING (id)
 WHERE t.id IS NULL;

左联接... t.id为空排除 tmp_table 中可能已经存在的任何 id 。 UNION 进入子选择，因此该子句只需要应用一次。应该是最快的。

在此处详细了解LEFT JOIN 。

LEFT JOIN ... WHERE t.id IS NULL excludes any id that might already be present in tmp_table. UNION goes into a sub-select, so that clause needs only be applied once. Should be fastest.
More on LEFT JOIN here.

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