了解花括号和“OJ"的使用在 SQL 查询中 [英] Understanding the use of curly braces and "OJ" in a SQL query

问题描述

我办公室的一位前雇员构建了一个 php 脚本，该脚本在 SQL 查询中使用花括号.我知道它通常适用于封装数组变量之类的事情，但是这个查询在字符串中没有任何 PHP 变量.有人可以澄清查询的 FROM 部分中生成的表实际上需要花括号的内容，以及 OJ 代表什么?

A previous employee at my office constructed a php script that uses curly braces in the a SQL query. I understand that it would normally work for things like encapsulating array variables, but this query doesn't have any PHP variables in the string. Can someone clarify what the table being generated in the FROM section of the query is actually requiring the curly braces for, and what the OJ stands for?

SELECT
  DISTINCT ra.folder_id,
  pd.id,
  f.name,
  pd.descriptor_text
FROM
  { 
    OJ permission_descriptors pd
    LEFT JOIN permission_descriptor_users pdu
    ON pdu.descriptor_id = pd.id
  }
  role_allocations ra,
  folders f
WHERE
  pdu.descriptor_id IS NULL AND
  pd.id = ra.permission_descriptor_id AND
  pd.id != 1
  ra.folder_id = f.id
ORDER BY
  ra.folder_id

推荐答案

MySQL 支持这种用于外部联接的替代语法.
然而，这并不意味着它应该被使用.

MySQL supports this alternative syntax for an Outer Join.
However that does not mean that it should be used.

1. 如果在某些时候您需要切换到另一个 RDBMS，那么拥有特定于 RDBMS 的代码可能会出现问题.
2. 在谷歌上搜索，似乎 MySQL 不支持超过 2 个连接的这种语法.

旁白:
代码的另一个不符合 ANSI 标准的问题是后续的连接.

Aside:
Another non-ANSI-compliant issue with the code is the subsequent joins.

这是对符合 ANSI 标准的版本的快速测试(未经测试):

This is a quick stab at an ANSI-compliant version (not tested):

SELECT
  DISTINCT ra.folder_id,
  pd.id,
  f.name,
  pd.descriptor_text
FROM
  permission_descriptors pd
  LEFT JOIN permission_descriptor_users pdu
  ON pdu.descriptor_id = pd.id
  LEFT JOIN role_allocations ra
  ON pd.id = ra.permission_descriptor_id 
  LEFT JOIN folders f 
  ON ra.folder_id = f.id

WHERE
  pdu.descriptor_id IS NULL AND
  pd.id <> 1 
ORDER BY
  ra.folder_id;

其他注意事项:
对于不等式 != 将起作用，但 <> 是首选.

Other notes:
For inequality != will work, but <> is preferred.

这篇关于了解花括号和“OJ"的使用在 SQL 查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！