为什么在SQL表名称中通常使用下划线而不是驼峰式大小写 [英] Why underline is usually used in the sql table names rather than camel case

问题描述

在我已经看到的所有应用程序/示例中（例如wordpress）。列表名称使用下划线而不是驼峰式大小写。我想知道是否存在一些技术上的不兼容问题或约定？它依赖于系统平台（Linux / Windows）还是sql方言（Mysql，postgreSQL，DB2，Oracle等）。例如，在下表中，我使用过骆驼保护套，但还没有任何问题/警告！如果我应该/必须重构我的表，为什么要/必须这样做？

In all the applications/examples I have already seen(eg. wordpress). The column table names use underline rather than camel case. I'd like to know if there are some technical incompatibility problems or it's a convention? Is it dependent of the system platform(Linux/Windows) or the sql dialect(Mysql, postgreSQL, DB2, Oracle, ...). For example in the following table I have used camel case and I haven't already had any problems/warnings about it! If I should/must refactor my table, why should/must I do it?

SQL对表/列名是否区分大小写？方言呢？

Is SQL case insensitive about the table/column names? What about the dialects?

CREATE TABLE `testuser` (
  `id` bigint(20) NOT NULL,
  `user_type` varchar(8) NOT NULL,
  `username` varchar(30) DEFAULT NULL,
  `password` varchar(128) DEFAULT NULL,
  `regDate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

推荐答案

一些背景信息：

（ANSI）SQL标准要求未引用的标识符以大写形式存储在系统目录中，并且未引用的标识符不区分大小写。

The (ANSI) SQL standard requires that non-quoted identifiers are stored in all uppercase in the system catalogs and that non-quoted identifiers are case-insensitive.

根据标准，以下未加引号的标识符引用同一对象（例如表）： FOOBAR ， foobar ， FooBar （所有这些都将作为 FOOBAR 存储在系统目录中）。

According to the standard the following non-quoted identifiers reference the same object (e.g. a table): FOOBAR, foobar, FooBar (and all would have been stored as FOOBAR in the system catalogs).

以下用引号的标识符引用了3个不同的对象： FOOBAR ， foobar ， FooBar 。

The following quoted identifiers reference 3 different objects: "FOOBAR", "foobar", "FooBar".

几乎所有DBMS至少符合未加引号的标识符不区分大小写的要求。据我所知，除了MySQL和SQL Server之外，两者都可以配置为区分大小写，即使对于未引用的标识符也是如此。我不确定SQL Server的默认行为是什么（正如Damien在他的评论中指出的那样，这取决于用于SQL Server的排序规则。）

Nearly all DBMS comply at least with the requirement that non-quoted identifiers are case insensitive. Except for MySQL and SQL Server as far as I know - both can be configured to be case-sensitive even for non-quoted identifiers. I'm not sure what the default behaviour of SQL Server is though (as Damien pointed out in his comment, this depends on the collation being used for SQL Server).

MySQL更加令人困惑，因为它的行为取决于几种配置设置，存储引擎和文件系统的组合。我所知道的所有其他DBMS在所有平台和安装上的行为都是一致的。

MySQL is even more confusing as its behaviour depends on the combination of several configuration settings, the storage engine and the filesystem. All other DBMS I know are consistent regarding their behaviour across all platforms and installations.

PostgreSQL遵循区分大小写的原则，但是将所有内容都折叠为小写。

PostgreSQL complies with the case-sensitivity but it folds everything to lowercase.

给定这些规则，我认为使用下划线的传统命名约定源于对象名称以大写形式存储的事实。获得可读名称的唯一方法是用下划线分隔名称的重要部分。

So given these rules, I think that the "traditional" naming convention using underscores stems from the fact that object names are stored in uppercase. The only way to get "readable" names is to separate important parts of the name with underscores.

SQL Server甚至保留了大小写，因此是非标准的（类似于Windows下NTFS的工作方式），因此它不会将名称折叠成任何东西。因此，当名称存储在系统目录中时，它不会更改名称的大小写（但默认情况下不区分大小写）。因此，您会发现使用CamelCase在Microsoft环境中工作的人员要多于例如在Oracle环境中。

SQL Server is even more non-standard as it is case-preserving (similar to the way NTFS under Windows works) so it does not fold the names to anything. So it does not change the case of the name when it's stored in the system catalog (but it is by default case insensitive). For that reason you will find people working in a Microsoft environment using CamelCase more often then e.g. in an Oracle environment.

这篇关于为什么在SQL表名称中通常使用下划线而不是驼峰式大小写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！