mysql - 将 MySQL 查询转换为 PostgreSQL

Question

我有这个问题:

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 编写的，它使用 IF NOT EXISTS 而不是 DROP TABLE IF EXISTS。我更改了那部分，但我不知道如何处理 IGNORE。

首先，IGNORE 在做什么？

我试着寻找 PostgreSQL 的等价物，但它们似乎都涉及复杂的过程。我必须为此编写程序吗？如果我必须写一个，它会是什么样子？我可以只使用一些 PHP 代码来模拟 IGNORE 吗？ (SQL 查询由 PHP 生成。)

Answer 1

你会在 postgres 中这样写。
IGNORE 在这里无关紧要，因为该表刚刚重新创建并且保证为空。 UNION 保证没有插入重复的行。

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 来消除可能的欺骗。阅读here .
如果您的数据集很大，您可以考虑在 INSERT 之后创建主键。那更快。

阅读mySQL docs关于 IGNORE 的影响。

---

在重新访问该页面时，我发现您在原始代码中提到了 IF NOT EXISTS。你没有这么说，但这只有在原始代码仅在表不存在的情况下创建表时才有意义，这引入了它在 INSERT 之前不为空的可能性。在这种情况下，IGNORE 是相关的，需要 PostgreSQL 中的等效项。

因此，这是对您问题的解释的替代答案。

CREATE TEMP TABLE IF NOT EXISTS 已经是 implemented in PostgreSQL 9.1 .
对于旧版本，我发布了 solution最近在 SO 上。

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;

LEFT JOIN ... WHERE t.id IS NULL 排除可能已经存在于 tmp_table 中的任何 id。 UNION 进入子选择，因此该子句只需应用一次。应该是最快的。
More on LEFT JOIN here .