数据库-为什么不区分大小写？ [英] Databases - Why case insensitive?
问题描述
我看到一个或两个线程在全球范围内讨论区分大小写，但是我的问题更具体。
例如，我了解对文本值进行不区分大小写搜索的兴趣
但是为什么我们要使用不区分大小写的数据库名称，表和列？
不是吗？会导致错误？使用数据库的脚本语言都是区分大小写的，因此例如，如果我们没有为字段使用正确的大小写，则将找不到...
SQL：2008 和 SQL-99 标准将数据库定义为对标识符不区分大小写，除非使用引号引起来。我发现大多数 ORM 都会在生成的SQL中引用标识符。
但是，您可能知道并非所有关系数据库都严格遵守这些标准。 DB2和Oracle 100％兼容。 PostgreSQL基本上是合规的，除了它会自动将未引用的内容小写（我个人更喜欢）。
mySQL有点奇怪，因为它存储每个表都作为文件系统上的文件。因此，它取决于文件系统的区分大小写。在Windows上：
创建表对象（整数）；
创建表 Foo（整数）； -错误已经存在
在Linux上的位置：
创建表对象（一个整数）； -创建FOO表
CREATE TABLE'Foo'（一个整数）； -创建Foo表
SQL Server甚至更陌生。它会保留创建时的大小写，但是无论如何，即使您引用了该名称，您也可以在以后以任何方式引用它。您不能创建两个表，它们的大小写唯一不同。注意：SQL Server确实具有控制此内容的配置选项，因为标识符将取决于数据库实例的默认排序规则。
在大多数情况下，我都同意计算机（编程语言，数据库，文件系统，URL，密码等），这真令人困惑！ ）应区分大小写，所有系统均独立实施，并且可能会或可能不会遵守可能存在或可能不存在的标准。如果您了解特定数据库系统的来龙去脉以及它的工作方式，那么肯定会实现区分大小写的数据库。
这确实是您的 负责以适用于您的方式实施您的系统，而不是整个技术行业以一致的方式实施一切以简化您的生活。
I saw one or two threads talking globally about case sensitivity, but my question is more specific.
I understand the interest of case insensitive searches on text values for example.
But why would we use case-insensitive database names, tables and columns?
Isn't it going to lead to mistakes? Script languages that use databases are all case-sensitive, so for example if we didn't use the right case for a field it will not be found...
The SQL:2008 and SQL-99 standards define databases to be case-insensitive for identifiers unless they are quoted. I've found most ORMs will quote identifiers in the SQL they generate.
However, as you probably know not all relational databases strictly adhere to the standards. DB2 and Oracle are 100% compliant. PostgreSQL is mostly compliant, except for the fact that it automatically lowercases anything that isn't quoted (which personally I prefer.)
mySQL gets a bit weird, since it stores each table as a file on the file system. For this reason, it's subject to the case sensitivity of the file system. On Windows:
CREATE TABLE FOO (a INTEGER);
CREATE TABLE 'Foo' (a INTEGER); -- Errors out, already exists
Where-as on Linux:
CREATE TABLE FOO (a INTEGER); -- Creates FOO table
CREATE TABLE 'Foo' (a INTEGER); -- Creates Foo table
SQL Server is even stranger. It will preserve the case on creation, however let you refer to it in any way after (even if you quote the name!) You can't create two tables whose only difference is their casing. Note: SQL Server does have configuration options that control this stuff though, as case-sensitivity of identifiers will depend on the default collation of the database instance. How confusing!
While for the most part I agree with you that computers (programming languages, databases, file systems, URLs, passwords, etc) should be case-sensitive, all systems are implemented independently and may or may not adhere to standards that may or may not exist. Implementing a case-senstive database is definitely possible, if you know the ins and outs of your particular database system and how it behaves.
It's really your responsibility to implement your system in a way that works for you, and not the entire technology industry to implement everything in a consistent way to make your life easier.
这篇关于数据库-为什么不区分大小写？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
