SQL语法区分大小写吗?
SQL 区分大小写吗? 我使用过 MySQL 和 SQL Server 两者似乎都不区分大小写。 情况总是如此吗? 该标准是否定义了区分大小写?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
SQL 区分大小写吗? 我使用过 MySQL 和 SQL Server 两者似乎都不区分大小写。 情况总是如此吗? 该标准是否定义了区分大小写?
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
接受
或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
发布评论
评论(11)
不。MySQL 不区分大小写,SQL 标准也不区分大小写。 将命令写成大写是常见的做法。
现在,如果您正在谈论表/列名称,那么是的,它们是,但不是命令本身。
所以
是相同
但不一样
No. MySQL is not case sensitive, and neither is the SQL standard. It's just common practice to write the commands upper-case.
Now, if you are talking about table/column names, then yes they are, but not the commands themselves.
So
is the same as
but not the same as
我发现这篇博文非常有帮助(我不是作者)。 总结一下(请阅读):
他发现 DB2、Oracle 和 Interbase/Firebird 100% 兼容:
I found this blog post to be very helpful (I am not the author). Summarizing (please read, though):
He found DB2, Oracle and Interbase/Firebird are 100% compliant:
我认为 SQL Server 不区分大小写,至少默认情况下不区分大小写。
当我通过 SQL Server Management Studio 手动查询时,我总是把大小写搞乱,它高兴地接受了:
I don't think SQL Server is case sensitive, at least not by default.
When I'm querying manually via SQL Server Management Studio, I mess up case all the time and it cheerfully accepts it:
SQL 关键字本身不区分大小写。
表、列等的名称区分大小写,这取决于数据库 - 您可能应该假设它们是区分大小写的,除非您知道其他情况(在许多数据库中并非如此;在 MySQL 中,表名称有时是 区分大小写,但大多数其他名称不区分大小写)。
使用 =、>、< 等比较数据具有区分大小写的能力,这取决于在相关的单个数据库、表甚至列上使用的排序规则设置。 然而,在数据库中保持排序规则相当一致是正常的。 我们有一些列需要存储区分大小写的值; 他们有专门设置的排序规则。
SQL keywords are case insensitive themselves.
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).
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.
两全其美
如今,您只需用小写形式编写所有 SQL 语句,如果您需要对其进行格式化,则只需安装一个插件即可为您完成此操作。 仅当您的代码编辑器具有可用的这些插件时,这才适用。 Visual Studio Code 有许多扩展可以做到这一点。
您可以使用以下几个: vscode-sql-formatter 和 SqlFormatter-VSCode
Have the best of both worlds
These days you can just write all your SQL statements in lowercase and if you ever need to have it formatted then just install a plugin that will do it for you. This is only applicable if your code editor has those plug-ins available. Visual Studio Code has many extensions that can do this.
Here's a couple you can use: vscode-sql-formatter and SqlFormatter-VSCode
SQL-92 规范 规定可以引用标识符,或不加引号。 如果双方都未加引号,则它们始终不区分大小写,例如,
table_name == TAble_nAmE
。但是,带引号的标识符区分大小写,例如,
"table_name" != "TAble_naME"
。 同样基于规范,如果您希望将不带引号的标识符与带引号的标识符进行比较,则不带引号的标识符和带引号的标识符可以被认为是相同的,如果不带引号的字符是大写的,例如TABLE_NAME == "TABLE_NAME"
,但是TABLE_NAME != "table_name"
或TABLE_NAME != "TAble_NaMe"
。以下是规范的相关部分(第 5.2.13 节):
请注意,就像 SQL 标准的其他部分一样,并非所有数据库都完全遵循此部分。例如,PostgreSQL 将所有不带引号的标识符存储为小写而不是大写,因此
table_name == "table_name"
(这与标准完全相反)。此外,有些数据库始终不区分大小写,或者区分大小写取决于数据库中的某些设置或取决于系统的某些属性,通常是文件系统。 )请注意,某些数据库工具可能会始终发送带引号的标识符,因此在混合由某些工具生成的查询的情况下(例如由 Liquibase 或其他数据库迁移工具),带有手工查询(例如简单的 JDBC 在您的应用程序中选择)您必须确保大小写一致,特别是在带引号和不带引号的标识符不同的数据库上(DB2, PostgreSQL等)
The SQL-92 specification states that identifiers might be quoted, or unquoted. If both sides are unquoted then they are always case insensitive, e.g.,
table_name == TAble_nAmE
.However, quoted identifiers are case sensitive, e.g.,
"table_name" != "TAble_naME"
. Also based on the specification if you wish to compare unquoted 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"
, butTABLE_NAME != "table_name"
orTABLE_NAME != "TAble_NaMe"
.Here is the relevant part of the specification (section 5.2.13):
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 file system is case sensitive or not.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.)
这不是严格的 SQL 语言,但在 SQL Server 中,如果数据库排序规则区分大小写,则所有表名都区分大小写。
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 Server 中这是一个选项。 打开它很糟糕。
我不确定MySQL。
In SQL Server it is an option. Turning it on sucks.
I'm not sure about MySQL.
标识符和保留字不应区分大小写,尽管许多标识符和保留字遵循使用大写字母的约定,并且大写驼峰标识符的大小写。
请参阅 SQL-92 秒。 5.2
Identifiers and reserved words should not be case sensitive, although many follow a convention to use capitals for reserved words and upper camel case for identifiers.
See SQL-92 Sec. 5.2
我的理解是 SQL 标准要求不区分大小写。 不过,我不相信任何数据库完全遵循该标准。
MySQL 有一个配置设置作为其“严格模式”的一部分(使 MySQL 更符合标准的几个设置的抓包),用于区分大小写或不敏感的表名。 无论此设置如何,列名称仍然不区分大小写,尽管我认为它会影响列名称的显示方式。 我相信此设置是实例范围的,跨 RDBMS 实例中的所有数据库,尽管我'我今天进行研究以证实这一点(并希望答案是否定的)。
我喜欢 Oracle 更好地处理这个问题的方式。 在直接 SQL 中,表名和列名等标识符不区分大小写。 但是,如果出于某种原因您确实希望获得显式大小写,则可以将标识符括在双引号中(这与 Oracle SQL 中用于括起字符串数据的单引号有很大不同)。 因此:
将从tablename查询fieldname,但
将从tableName查询fieldName。
我非常确定您甚至可以使用此机制将空格或其他非标准字符插入标识符中。
在这种情况下,如果由于某种原因您发现需要显式大小写的表和列名称,那么您可以使用它,但我仍然会高度警告不要这样做。
当我每天使用 Oracle 时,我的惯例是在代码中将所有 Oracle SQL 关键字都大写,所有标识符都小写。 在文档中,我会将所有表名和列名都大写。 能够做到这一点非常方便且可读(尽管有时在代码中输入如此多的大写字母很痛苦——我确信我可以在这里找到一个编辑器功能来提供帮助)。
在我看来,MySQL 尤其糟糕,因为它在不同平台上对此存在差异。 我们需要能够在 Windows 上转储数据库并将它们加载到 Unix 中,如果 Windows 上的安装程序忘记将 RDBMS 置于区分大小写的模式,那么这样做将是一场灾难。 (公平地说,这是一场灾难的部分原因是我们的编码人员很久以前做出了错误的决定,依赖于 UNIX 上 MySQL 的区分大小写。)编写 Windows MySQL 安装程序的人使它变得非常方便和与 Windows 类似,并且为人们提供一个复选框来表示“您想打开严格模式并使 MySQL 更符合标准吗?”,这真是太棒了。 但MySQL很容易与标准有如此明显的差异,然后在不同平台上转身与自己事实上的标准不同,从而使事情变得更糟。 我确信在不同的 Linux 发行版上,这可能会进一步复杂化,因为不同发行版的打包者有时可能会合并自己首选的 MySQL 配置设置。
这是另一个需要讨论的堆栈溢出问题如果 RDBMS 需要区分大小写。
My understanding is that the SQL standard calls for case-insensitivity. I don't believe any databases follow the standard completely, though.
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).
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:
will query fieldname from tablename, but
will query fieldName from tableName.
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).
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 significantly 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.
Here's another Stack Overflow question that gets into discussing if case-sensitivity is desirable in an RDBMS.
SQL 关键字不区分大小写(
SELECT
、FROM
、WHERE
等),但它们通常全部大写。 但是,在某些设置中,表名和列名区分大小写。MySQL 有一个配置选项来启用/禁用它。 通常区分大小写的表名和列名是 Linux MySQL 上的默认设置,并且不区分大小写是默认设置在 Windows 上,但现在安装程序在安装过程中询问了这一点。 对于SQL Server,它是数据库排序规则设置的函数。
这是关于名称区分大小写的MySQL页面
这是MSDN 中有关 SQL Server 排序规则的文章< /a>
The SQL keywords are case insensitive (
SELECT
,FROM
,WHERE
, etc), but they 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 SQL Server it is a function of the database's collation setting.
Here is the MySQL page about name case-sensitivity
Here is the article in MSDN about collations for SQL Server