我不确定我是否正确地将这个 MySQL 查询翻译成 Postgresql
不久前,我询问如何将 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.
IGNORE
表示不会插入表中已存在的行。由于您刚刚创建了表,因此还不能有任何行。因此,该选项在这种情况下是无关紧要的。MySQL
REPLACE
应该是一个事务。如果没有 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 ain 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.