SQL 语法是否区分大小写？

Is SQL syntax case sensitive?

提问by Steve Tranby

Is SQL case sensitive. I've used MySQL and SQL Server which both seem to be case in-sensitive. Is this always the case? Does the standard define case-sensitivity?

SQL 是否区分大小写。我使用过 MySQL 和 SQL Server，它们似乎都不区分大小写。总是这样吗？标准是否定义了区分大小写？

采纳答案by Stefan Rusek

The SQL Keywords are case-insensitive (SELECT, FROM, WHERE, etc), but are often written in all caps. However in some setups table and column names are case-sensitive. MySQL has a configuration option to enable/disable it. Usually case-sensitive table and column names are the default on Linux MySQL and case-insensitive used to be the default on Windows, but now the installer asked about this during setup. For MSSQL it is a function of the database's collation setting.

该SQL关键字是不区分大小写（SELECT，FROM，WHERE等），但往往写在全部大写。但是，在某些设置中，表和列名称区分大小写。MySQL 有一个配置选项来启用/禁用它。通常区分大小写的表和列名称是 Linux MySQL 上的默认值，不区分大小写曾经是 Windows 上的默认值，但现在安装程序在安装过程中询问了这一点。对于 MSSQL，它是数据库排序规则设置的函数。

Here is the MySQL page about name case-sensitivity

这是关于名称区分大小写的MySQL 页面

Here is the article in MSDN about collations for MSSQL

这是MSDN 中关于 MSSQL 排序规则的文章

回答by Cade Roux

This isn't strictly SQL language, but in SQL Server if your database collation is case-sensitive, then all table names are case-sensitive.

这不是严格的 SQL 语言，但在 SQL Server 中，如果您的数据库排序规则区分大小写，则所有表名都区分大小写。

回答by JosephStyons

In Sql Server it is an option. Turning it on sucks.

在 Sql Server 中，它是一个选项。打开它很糟糕。

I'm not sure about MySql.

我不确定 MySql。

回答by Turnkey

Identifiers and reserved words should not be case sensitive, although many follow a convention to use capitals for reserved words and Pascal case for identifiers.

标识符和保留字不应该区分大小写，尽管许多遵循约定，保留字使用大写，标识符使用 Pascal 大小写。

See SQL-92Sec. 5.2

请参阅SQL-92秒。5.2

回答by SztupY

The SQL92 specificationstates that identifiers might be quoted, or unquoted. If both sides are unquoted then they are always case-insensitive, e.g. table_name == TAble_nAmE.

在SQL92规范指出标识符可能会被引用，或不带引号。如果双方都没有被引用，那么它们总是不区分大小写的，例如table_name == TAble_nAmE.

However quoted identifiers are case-sensitive, e.g. "table_name" != "TAble_naME". Also based on the spec if you wish to compare unqouted identifiers with quoted ones, then unquoted and quoted identifiers can be considered the same, if the unquoted characters are uppercased, e.g. TABLE_NAME == "TABLE_NAME", but TABLE_NAME != "table_name"or TABLE_NAME != "TAble_NaMe".

然而，带引号的标识符区分大小写，例如"table_name" != "TAble_naME". 同样基于规范，如果您希望将未加引号的标识符与带引号的标识符进行比较，那么如果未加引号的字符是大写的，则可以将未加引号的标识符和带引号的标识符视为相同，例如TABLE_NAME == "TABLE_NAME", butTABLE_NAME != "table_name"或TABLE_NAME != "TAble_NaMe"。

Here is the relevant part of the spec (section 5.2.13):

这是规范的相关部分（第 5.2.13 节）：

     13)A <regular identifier> and a <delimited identifier> are equiva-
        lent if the <identifier body> of the <regular identifier> (with
        every letter that is a lower-case letter replaced by the equiva-
        lent upper-case letter or letters) and the <delimited identifier
        body> of the <delimited identifier> (with all occurrences of
        <quote> replaced by <quote symbol> and all occurrences of <dou-
        blequote symbol> replaced by <double quote>), considered as
        the repetition of a <character string literal> that specifies a
        <character set specification> of SQL_TEXT and an implementation-
        defined collation that is sensitive to case, compare equally
        according to the comparison rules in Subclause 8.2, "<comparison
        predicate>".

Note, that just like with other parts of the SQL standard, not all databases follow this section fully. PostgreSQL for example stores all unquoted identifiers lowercased instead of uppercased, so table_name == "table_name"(which is exactly the opposite of the standard). Also some databases are case-insensitive all the time, or case-sensitiveness depend on some setting in the DB or are dependent on some of the properties of the system, usually whether the filesystem is case-sensitive or not.

请注意，就像 SQL 标准的其他部分一样，并非所有数据库都完全遵循这一部分。例如，PostgreSQL 将所有未加引号的标识符存储为小写而不是大写，因此table_name == "table_name"（这与标准完全相反）。还有一些数据库一直不区分大小写，或者区分大小写取决于数据库中的某些设置或取决于系统的某些属性，通常文件系统是否区分大小写。

Note that some database tools might send identifiers quoted all the time, so in instances where you mix queries generated by some tool (like a CREATE TABLE query generated by Liquibase or other DB migration tool), with hand made queries (like a simple JDBC select in your application) you have to make sure that the cases are consistent, especially on databases where quoted and unquoted identifiers are different (DB2, PostgreSQL, etc.)

请注意，某些数据库工具可能会发送一直引用的标识符，因此在您将某些工具生成的查询（例如 Liquibase 或其他数据库迁移工具生成的 CREATE TABLE 查询）与手工查询（例如简单的 JDBC select在您的应用程序中）您必须确保情况一致，尤其是在带引号和不带引号的标识符不同的数据库（DB2、PostgreSQL 等）上

回答by skiphoppy

My understanding is that the SQL standard calls for case-insensitivity. I don't believe any databases follow the standard completely, though.

我的理解是 SQL 标准要求不区分大小写。不过，我不相信任何数据库都完全遵循标准。

MySQL has a configuration setting as part of its "strict mode" (a grab bag of several settings that make MySQL more standards-compliant) for case sensitive or insensitive table names. Regardless of this setting, column names are still case-insensitive, although I think it affects how the column-names are displayed. I believe this setting is instance-wide, across all databases within the RDBMS instance, although I'm researching today to confirm this (and hoping the answer is no).

MySQL 有一个配置设置作为其“严格模式”的一部分（一组使 MySQL 更符合标准的设置），用于区分大小写或不敏感的表名。不管这个设置如何，列名仍然不区分大小写，尽管我认为它会影响列名的显示方式。我相信这个设置是实例范围的，跨 RDBMS 实例中的所有数据库，尽管我今天正在研究以确认这一点（并希望答案是否定的）。

I like how Oracle handles this far better. In straight SQL, identifiers like table and column names are case insensitive. However, if for some reason you really desire to get explicit casing, you can enclose the identifier in double-quotes (which are quite different in Oracle SQL from the single-quotes used to enclose string data). So:

我喜欢 Oracle 如何更好地处理这个问题。在直接 SQL 中，诸如表名和列名之类的标识符不区分大小写。但是，如果出于某种原因您确实希望获得显式大小写，您可以将标识符括在双引号中（这在 Oracle SQL 中与用于将字符串数据括起来的单引号完全不同）。所以：

SELECT fieldName
FROM tableName;

will query fieldnamefrom tablename, but

将从tablename查询fieldname，但是

SELECT "fieldName"
FROM "tableName";

will query fieldNamefrom tableName.

将从tableName查询fieldName。

I'm pretty sure you could even use this mechanism to insert spaces or other non-standard characters into an identifier.

我很确定您甚至可以使用这种机制在标识符中插入空格或其他非标准字符。

In this situation if for some reason you found explicitly-cased table and column names desirable it was available to you, but it was still something I would highly caution against.

在这种情况下，如果由于某种原因您发现显式大小写的表名和列名是可取的，那么您可以使用它，但我仍然会高度警告它。

My convention when I used Oracle on a daily basis was that in code I would put all Oracle SQL keywords in uppercase and all identifiers in lowercase. In documentation I would put all table and column names in uppercase. It was very convenient and readable to be able to do this (although sometimes a pain to type so many capitals in code -- I'm sure I could've found an editor feature to help, here).

我每天使用 Oracle 时的惯例是，在代码中，我将所有 Oracle SQL 关键字大写，所有标识符都小写。在文档中，我会将所有表名和列名都大写。能够做到这一点非常方便和可读（尽管有时在代码中输入这么多大写字母很痛苦——我相信我可以在这里找到一个编辑器功能来提供帮助）。

In my opinion MySQL is particularly bad for differing about this on different platforms. We need to be able to dump databases on Windows and load them into UNIX, and doing so is a disaster if the installer on Windows forgot to put the RDBMS into case-sensitive mode. (To be fair, part of the reason this is a disaster is our coders made the bad decision, long ago, to rely on the case-sensitivity of MySQL on UNIX.) The people who wrote the Windows MySQL installer made it really convenient and Windows-like, and it was great to move toward giving people a checkbox to say "Would you like to turn on strict mode and make MySQL more standards-compliant?" But it is very convenient for MySQL to differ so signficantly from the standard, and then make matters worse by turning around and differing from its own de facto standard on different platforms. I'm sure that on differing Linux distributions this may be further compounded, as packagers for different distros probably have at times incorporated their own preferred MySQL configuration settings.

在我看来，MySQL 在不同平台上的差异尤其糟糕。我们需要能够在 Windows 上转储数据库并将它们加载到 UNIX 中，如果 Windows 上的安装程序忘记将 RDBMS 置于区分大小写的模式，那么这样做将是一场灾难。（公平地说，这是一场灾难的部分原因是我们的编码人员很久以前做出了错误的决定，依赖于 UNIX 上 MySQL 的区分大小写。）编写 Windows MySQL 安装程序的人使它非常方便和类似于 Windows，而且很高兴为人们提供一个复选框来说“你想打开严格模式并使 MySQL 更符合标准吗？” 但是对于 MySQL 来说与标准如此显着不同是非常方便的，然后通过在不同平台上扭转并与自己的事实上的标准不同而使事情变得更糟。我敢肯定，在不同的 Linux 发行版上，这可能会进一步复杂化，因为不同发行版的打包人员有时可能会合并他们自己首选的 MySQL 配置设置。

Here's another SO question that gets into discussing if case-sensitivity is desirable in an RDBMS.

这是另一个 SO 问题，该问题讨论了 RDBMS 中是否需要区分大小写。

回答by cmcculloh

No. MySQL is not case sensitive, and neither is the SQL standard. It's just common practice to write the commands upper-case.

不。MySQL 不区分大小写，SQL 标准也不区分大小写。将命令写成大写是常见的做法。

Now, if you are talking about table/column names, then yes they are, but not the commands themselves.

现在，如果您在谈论表/列名称，那么是的，它们是，但不是命令本身。

So

所以

SELECT * FROM foo;

is the same as

是相同的

select * from foo;

but not the same as

但不一样

select * from FOO;

回答by Matthew Cornell

I found this blog postto be very helpful (I am not the author). Summarizing (please read, though):

我发现这篇博文非常有帮助（我不是作者）。总结（但请阅读）：

...delimited identifiers are case sensitive ("table_name" != "Table_Name"), while non quoted identifiers are not, and are transformed to upper case (table_name => TABLE_NAME).

...分隔标识符区分大小写（“table_name”！=“Table_Name”），而非引用标识符不区分大小写，并转换为大写（table_name => TABLE_NAME）。

He found DB2, Oracle and Interbase/Firebird are 100% compliant:

他发现 DB2、Oracle 和 Interbase/Firebird 是 100% 兼容的：

PostgreSQL ... lowercases every unquoted identifier, instead of uppercasing it. MySQL ... file system dependent. SQLite and SQL Server ... case of the table and field names are preserved on creation, but they are completely ignored afterwards.

PostgreSQL ... 将每个未加引号的标识符小写，而不是大写。MySQL ...依赖于文件系统。SQLite 和 SQL Server ... 在创建时保留表和字段名称的大小写，但之后它们将被完全忽略。

回答by MarkR

SQL keywords are case insensitive themselves.

SQL 关键字本身不区分大小写。

Names of tables, columns etc, have a case sensitivity which is database dependent - you should probably assume that they are case sensitive unless you know otherwise (In many databases they aren't though; in MySQL table names are SOMETIMES case sensitive but most other names are not).

表、列等的名称具有与数据库相关的区分大小写的敏感性 - 您应该假设它们区分大小写，除非您知道其他情况（在许多数据库中它们不是；在 MySQL 中，表名有时区分大小写，但大多数其他名字不是）。

Comparing data using =, >, < etc, has a case awareness which is dependent on the collation settings which are in use on the individual database, table or even column in question. It's normal however, to keep collation fairly consistent within a database. We have a few columns which need to store case-sensitive values; they have a collation specifically set.

使用 =、>、< 等比较数据具有大小写意识，这取决于在单个数据库、表甚至相关列上使用的排序规则设置。然而，在数据库中保持排序规则相当一致是正常的。我们有几列需要存储区分大小写的值；他们有一个专门设置的排序规则。

回答by Dana

I don't think SQL Server is case-sensitive, at least not by default.

我认为 SQL Server 不区分大小写，至少默认情况下不是。

When I'm querying manually via Management Studio, I mess up case all the time and it cheerfully accepts it:

当我通过 Management Studio 手动查询时，我总是把案例弄得一团糟，它欣然接受：

select cOL1, col2 FrOM taBLeName WheRE ...