我不确定我是否正确地将这个 MySQL 查询翻译成 Postgresql

发布于 12-10 07:24 字数 1403 浏览 0 评论 0原文

不久前,我询问如何将 MySQL 查询更改为 Postgresql。而且,我想我已经明白了,但是现在,我有另一个类似的查询,所以我做了同样的事情,但我不确定它是否按预期工作。 原始 MySQL 查询:

create temporary table IF NOT EXISTS temptable(id int(11) primary key, shared int(1) default 0) 
ignore (SELECT 6 as id) UNION (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 (3,4,2));

我的 Postgresql(9.1) 版本:

Create TEMP TABLE IF NOT EXISTS temptable(id int primary key, shared int default 0);
Insert into temptable SELECT x.id FROM ( SELECT user2role.userid AS id 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::%' 
UNION (SELECT groupid FROM groups where groupid in (2,3,4))) x 
LEFT JOIN temptable t USING (id) WHERE t.id IS NULL

现在,我最担心的是 ignore (SELECT 6 as id) UNION 位。我不太明白 SELECT 6 as id 的意义。

另外,这与此查询无关,但我有另一个类似的查询,它使用 replace 而不是忽略。我知道 replace 很像 insert ,只是它会替换旧值(如果它们具有相同的键)。我想不出更好的方法,所以我只是继续使用 insert,但我应该以不同的方式实现它吗?

谢谢你们所做的一切,伙计们。

A while back, I asked about changing a MySQL query into Postgresql. And, I think I got that down, but now, I have this other query which is similar, so I did the same sort of thing, but I am not sure if it's working as intended.
The Original MySQL query:

create temporary table IF NOT EXISTS temptable(id int(11) primary key, shared int(1) default 0) 
ignore (SELECT 6 as id) UNION (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 (3,4,2));

My Postgresql(9.1) version:

Create TEMP TABLE IF NOT EXISTS temptable(id int primary key, shared int default 0);
Insert into temptable SELECT x.id FROM ( SELECT user2role.userid AS id 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::%' 
UNION (SELECT groupid FROM groups where groupid in (2,3,4))) x 
LEFT JOIN temptable t USING (id) WHERE t.id IS NULL

Now, I am most worried about the ignore (SELECT 6 as id) UNION bit. I don't quite understand the point of SELECT 6 as id.

Also, this is irrelevant to this query, but I have another similar query that uses replace instead of ignore. I understand that replace is a lot like insert, just that it replaces old values if they have the same key. I couldn't think of a better way, so I just went ahead and used insert, but should I implement it in a different way instead?

Thanks for everything, guys.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

寂寞清仓2024-12-17 07:24:31
  • IGNORE 表示不会插入表中已存在的行。由于您刚刚创建了表,因此还不能有任何行。因此,该选项在这种情况下是无关紧要的。

  • MySQL REPLACE 应该是

    删除 .. 使用 insert_list .. WHERE ...;
    插入 .. ;
    

一个事务。如果没有 DELETE 的 INSERT 成功,则结果有效。没有存在冲突的行,因此首先没有必要进行 DELETE。但是,如果任何现有行与 INSERT 冲突,则存在操作回滚的风险。除了序列中的间隙之外,这不会造成任何损害,而序列永远不会回滚。

  • IGNORE means that rows already present in the table will not be inserted. As you have just created the table, there cannot be any rows yet. Therefore, the option is irrelevant in this case.

  • MySQL REPLACE should be a

    DELETE .. USING insert_list .. WHERE ...;
    INSERT .. ;
    

in one transaction. If an INSERT without DELETE succeeds, than the result is valid. There were no rows with conflicts, so the DELETE was not necessary in the first place. However, you risk that the operation is rolled back if any of the existing rows conflict with the INSERT. That does no harm other than gaps in sequences, which are never rolled back.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文