69
The SQL standard (current version is ISO/IEC 9075:2011, in multiple parts) says nothing about the 'back-tick' or 'back-quote' symbol (Unicode U+0060 or GRAVE ACCENT); it doesn't recognize it as a character with special meaning that can appear in SQL.
SQL标准(目前的版本是ISO/IEC 9075:2011,有很多部分)没有提到“后勾”或“后引号”符号(Unicode U+0060或庄重的重音);它不承认它是一个具有特殊意义的字符,可以出现在SQL中。
The Standard SQL mechanism for quoting identifiers is with delimited identifiers enclosed in double quotes:
引用标识符的标准SQL机制是用带分隔符的双引号括起来的:
SELECT "select" FROM "from" WHERE "where" = "group by";
In MySQL, that might be written:
在MySQL中,可以这样写:
SELECT `select` FROM `from` WHERE `where` = `group by`;
In MS SQL Server, that might be written:
在MS SQL Server中,可以这样写:
SELECT [select] FROM [from] WHERE [where] = [group by];
The trouble with the SQL Standard notation is that C programmers are used to enclosing strings in double quotes, so most DBMS use double quotes as an alternative to the single quotes recognized by the standard. But that then leaves you with a problem when you want to enclose identifiers.
SQL标准表示法的问题在于,C程序员习惯于将字符串括在双引号中,因此大多数DBMS都使用双引号作为标准识别的单引号的替代。但当您想要包含标识符时,这会给您带来一个问题。
Microsoft took one approach; MySQL took another; Informix allows interchangeable use of single and double quotes, but if you want delimited identifiers, you set an environment variable and then you have to follow the standard (single quotes for strings, double quotes for identifiers); DB2 only follows the standard, AFAIK; SQLite appears to follow the standard; Oracle also appears to follow the standard; Sybase appears to allow either double quotes (standard) or square brackets (as with MS SQL Server — which means SQL Server might allow double quotes too). This page documents all these servers (and was helpful filling out the gaps in my knowledge), and notes whether the strings inside delimited identifiers are case-sensitive or not.
微软的一种方法;MySQL又;Informix允许使用单引号和双引号,但如果您想要分隔标识符,您需要设置一个环境变量,然后必须遵循标准(字符串的单引号、标识符的双引号);DB2只遵循标准AFAIK;SQLite似乎遵循标准;甲骨文似乎也遵循这一标准;Sybase似乎允许双引号(标准)或方括号(就像MS SQL Server一样——这意味着SQL Server也可能允许双引号)。这个页面记录了所有这些服务器(这有助于填补我所知道的空白),并注意分隔符内的字符串是否区分大小写。
As to when to use a quoting mechanism around identifiers, my attitude is 'never'. Well, not quite never, but only when absolutely forced into doing so.
至于何时在标识符周围使用引用机制,我的态度是“从不”。好吧,不是完全没有,但只有在绝对被迫这么做的时候。
Note that delimited identifiers are case-sensitive; that is, "from"
and "FROM"
refer to different columns (in most DBMS — see URL above). Most of SQL is not case-sensitive; it is a nuisance to know which case to use. (The SQL Standard has a mainframe orientation — it expects names to be converted to upper-case; most DBMS convert names to lower-case, though.)
注意分隔标识符是区分大小写的;也就是说,“from”和“from”指的是不同的列(在大多数DBMS中——见上面的URL)。大多数SQL不是大小写敏感的;知道使用哪种情况是令人讨厌的。(SQL标准有大型机方向——它希望将名称转换为大写;不过,大多数DBMS都会将名称转换为小写。
In general, you must delimit identifiers which are keywords to the version of SQL you are using. That means most of the keywords in Standard SQL, plus any extras that are part of the particular implementation(s) that you are using.
通常,您必须将标识符分隔开,这些标识符是您正在使用的SQL版本的关键字。这意味着标准SQL中的大多数关键字,以及您正在使用的特定实现的一部分。
One continuing source of trouble is upgrades, where a column name that was not a keyword in release N becomes a keyword in release N+1. Existing SQL that worked before the upgrade stops working afterwards. Then, at least as a short-term measure, you may be forced into quoting the name. But in the ordinary course of events, you should aim to avoid needing to quote identifiers.
一个持续的麻烦来源是升级,其中不是关键字的列名在N+1版本中成为关键字。在升级之前工作的现有SQL之后将停止工作。然后,至少作为短期措施,你可能会被迫引用这个名字。但是在一般情况下,您应该避免引用标识符。
Of course, my attitude is coloured by the fact that Informix (which is what I work with mostly) accepts this SQL verbatim, whereas most DBMS would choke on it:
当然,我的态度受到Informix(我主要使用Informix)逐字接受这个SQL的影响,而大多数DBMS都会对它产生瓶颈:
CREATE TABLE TABLE
(
DATE INTEGER NOT NULL,
NULL FLOAT NOT NULL,
FLOAT INTEGER NOT NULL,
NOT DATE NOT NULL,
INTEGER FLOAT NOT NULL
);
Of course, the person who produces such a ridiculous table for anything other than demonstration purposes should be hung, drawn, quartered and then the residue should be made to fix the mess they've created. But, within some limits which customers routinely manage to hit, keywords can be used as identifiers in many contexts. That is, of itself, a useful form of future-proofing. If a word becomes a keyword, there's a moderate chance that the existing code will continue to work unaffected by the change. However, the mechanism is not perfect; you can't create a table with a column called PRIMARY, but you can alter a table to add such a column. There is a reason for the idiosyncrasy, but it is hard to explain.
当然,除了为了演示目的而制作如此可笑的桌子的人应该被挂起来,画出来,分成四等分,然后剩下的部分应该被用来修复他们造成的混乱。但是,在一些客户经常遇到的限制范围内,关键字可以在许多上下文中用作标识符。这本身就是一种有用的未来防护形式。如果一个单词变成了关键字,那么现有的代码将继续运行,而不受更改的影响。然而,这种机制并不完善;不能使用名为PRIMARY的列创建表,但可以修改表以添加此类列。这种癖好是有原因的,但很难解释。