database 我应该写表名和列名总是小写吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2029358/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Should I write table and column names ALWAYS lower case?
提问by openfrog
I wonder if it's a problem, if a table or column name contains upper case letters. Something lets me believe databases have less trouble when everything is kept lower case. Is that true? Which databases don't like any upper case symbol in table and column names?
我想知道这是否有问题，如果表名或列名包含大写字母。有些东西让我相信，当所有内容都保持小写时，数据库的麻烦会更少。真的吗？哪些数据库不喜欢表名和列名中的任何大写符号？
I need to know, because my framework auto-generates the relational model from an ER-model.
我需要知道，因为我的框架从 ER 模型自动生成关系模型。
(this question is not about whether it's good or bad style, but only about if it's a technical problem for any database)
（这个问题不是关于它的风格是好是坏，而是关于它是否是任何数据库的技术问题）
采纳答案by danben
It is not a technical problem for the database to have uppercase letters in your table or column names, for any DB engine that I'm aware of. Keep in mind many DB implementations use case sensitive names, so always refer to tables and columns using the same case with which they were created (I am speaking very generally since you didn't specify a particular implementation).
对于我所知道的任何数据库引擎，数据库在表名或列名中包含大写字母都不是技术问题。请记住，许多数据库实现使用区分大小写的名称，因此始终使用创建它们时使用的相同大小写来引用表和列（因为您没有指定特定的实现，所以我说得非常笼统）。
For MySQL, here is some interesting information about how it handles identifier case. There are some options you can set to determine how they are stored internally. http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html
对于 MySQL，这里有一些关于它如何处理标识符大小写的有趣信息。您可以设置一些选项来确定它们在内部的存储方式。 http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitive.html
回答by rosscj2533
As far as I know there is no problem using either uppercase and lowercase. One reason for the using lower case convention is so that queries are more readable with lowercase table and column names and upper case sql keywords:
据我所知，使用大写和小写都没有问题。使用小写约定的原因之一是使用小写表名和列名以及大写 sql 关键字使查询更具可读性：
SELECT column_a, column_b FROM table_name WHERE column_a = 'test'
回答by kdgregory
The SQL-92 standard specifies that identifiers and keywords are case-insensitive (per A Guide to the SQL Standard4th edition, Date / Darwen)
SQL-92 标准指定标识符和关键字不区分大小写（根据SQL 标准第 4 版指南，日期 / Darwen）
That's not to say that a particular DBMS isn't either (1) broken, or (2) configurable (and broken)
这并不是说特定的 DBMS 没有 (1) 损坏，或 (2) 可配置（和损坏）
From a programming style perspective, I suggest using different cases for keywords and identifiers. Personally, I like uppercase identifiers and lowercase keywords, because it highlights the data that you're manipulating.
从编程风格的角度来看，我建议对关键字和标识符使用不同的情况。就个人而言，我喜欢大写标识符和小写关键字，因为它突出显示了您正在处理的数据。
回答by tillinberlin
As far as I know for a common L.A.M.P. setup it won't really matter - but be aware that MySQL hosted on Linux is case sensitive!
据我所知，对于常见的 LAMP 设置，这并不重要 - 但请注意，托管在 Linux 上的 MySQL 区分大小写！
To keep my code tidy I usually stick to lower case names for tables and colums, uppercase MySQL-Code and mixed Upper-Lower-Case variables - like this:
为了保持我的代码整洁，我通常坚持使用小写的表和列名称，大写的 MySQL-Code 和混合的大写-小写变量 - 像这样：
SELECT * FROM my_table WHERE id = '$myNewID'
SELECT * FROM my_table WHERE id = '$myNewID'
回答by Basil Bourque
SQL standard requires names stored in uppercase
SQL 标准要求名称以大写形式存储
The SQL standard requires identifiers be storedin all-uppercase. See section 5.2.13 of the SQL-92 as quoted from a draft copy in this Answeron another Question. The standard allows you use undelimited identifiers in lowercase or mixed case, as the SQL processor is required to convert as needed to convert to the uppercase version.
SQL 标准要求标识符以全大写形式存储。请参阅 SQL-92 的第 5.2.13 节，该部分引用自本回答关于另一个问题的草稿副本。该标准允许您使用小写或混合大小写的无分隔标识符，因为 SQL 处理器需要根据需要进行转换以转换为大写版本。
This requirement presumably dates back to the early days of SQL when mainframe systems were limited to uppercase English characters only.
这一要求大概可以追溯到 SQL 的早期，当时大型机系统仅限于大写英文字符。
Non-issue
没问题
Many database ignore this requirement by the standard.
许多数据库根据标准忽略了这一要求。
For example, Postgres does just the opposite, converting all unquoted (“undelimited”) identifiers to lowercase — this despite Postgres otherwise hewing closer to the standard than any other system I know of.
例如，Postgres 正好相反，将所有未加引号（“未定界”）标识符转换为小写——尽管 Postgres 比我所知道的任何其他系统更接近标准。
Some databases may store the identifier in the case you specified.
某些数据库可能会在您指定的情况下存储标识符。
Generally this is a non-issue. Virtually all databases do a case-insensitive lookup from the case used by an identifier to the case stored by the database.
一般来说，这不是问题。几乎所有数据库都会从标识符使用的案例到数据库存储的案例进行不区分大小写的查找。
There are occasional oddball cases where you may need to specify an identifier in its stored case or you may need to specify all-uppercase. This may happen with certain utilities where you must pass an identifier as a string outside the usual SQL processor context. Rare, but tuck this away in the back of your head in case you encounter some mysterious "cannot find table" kind of error message someday when using some unusual tool/utility. Has happened to me once.
偶尔有一些奇怪的情况，您可能需要在其存储的大小写中指定一个标识符，或者您可能需要指定全大写。某些实用程序可能会发生这种情况，在这些实用程序中，您必须将标识符作为字符串传递到通常的 SQL 处理器上下文之外。很少见，但是如果有一天您在使用一些不寻常的工具/实用程序时遇到一些神秘的“找不到表”类型的错误消息，请将其隐藏在脑后。曾经发生在我身上。
Snake case
蛇盒
Common practice nowadays seems to be to use all lowercase with underscore separating words. This style is known as Snake case.
现在的普遍做法似乎是使用所有小写字母和下划线分隔单词。这种款式被称为Snake case。
The use of underscore rather than Camel casehelps if your identifiers are ever presented as all uppercase (or all lowercase) and thereby lose readability without the word separation.
如果您的标识符以全大写（或全小写）形式呈现，从而在没有单词分隔的情况下失去可读性，则使用下划线而不是驼峰式大小写会有所帮助。
Bonus Tip: The SQL standard (SQL-92 section 5.2.11) explicitly promises to never use a trailing underscore in a keyword. So append a trailing underscore to all your identifiers to eliminate all worry of accidentally colliding.
额外提示：SQL 标准（SQL-92 第 5.2.11 节）明确承诺永远不会在关键字中使用尾随下划线。因此，在所有标识符后附加一个尾随下划线，以消除所有意外碰撞的担忧。
回答by Samuel Fullman
I use pascal case for field names lower case for table names (usually) as follows:
我将 pascal 大小写用于字段名称小写的表名称（通常）如下：
students
--------
ID
FirstName
LastName
Email
HomeAddress
courses
-------
ID
Name
Code
[etc]
Why is this cool? because it's readable, and because I can parse it as:
为什么这很酷？因为它是可读的，并且因为我可以将其解析为：
echo preg_replace('/([a-z])([A-Z])/',' ',$field); //insert a space
NOW, here's the fun part for tables:
现在，这是表格的有趣部分：
StudentsCourses
--------------
Students_ID
Courses_ID
AcademicYear
Semester
notice I capitalized S and C? That way they point back to the primary table(s). You could even write a routine to logically parse db structure this way and build queries automatically. So I use caps in tables when they are JOIN tables as in this case.
请注意我将 S 和 C 大写？这样他们就指向回主表。您甚至可以编写一个例程以这种方式从逻辑上解析 db 结构并自动构建查询。因此，当它们是 JOIN 表时，我在表中使用大写字母，就像在这种情况下一样。
Similarly, think of the _ as a -> in this table as: Students->ID and Courses->ID Not student_id - instead Students_ID - the cognate of the field matches the exact name of the table.
类似地，将此表中的 _ 视为 -> 为： Students->ID and Courses->ID Not student_id - 而不是 Students_ID - 该字段的同源词与表的确切名称匹配。
Using these simple conventions produces a readable protocol which handles about 70% of your typical relational structure.
使用这些简单的约定会产生一个可读的协议，它可以处理大约 70% 的典型关系结构。
回答by latika bhurani
Whatever you use, keep in mind the MySQL on Linux is case sensitive, while on Windows it is case insensitive .
无论您使用什么，请记住 Linux 上的 MySQL 区分大小写，而在 Windows 上则不区分大小写。
回答by Fred Wilson
If you're using postgresql and PHP, for instance, you'd have to write your query like this:
例如，如果您使用 postgresql 和 PHP，则必须像这样编写查询：
$sql = "SELECT somecolumn FROM \"MyMixedCaseTable\" where somerow= '$somevar'";
"Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)" http://www.postgresql.org/docs/8.4/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
"引用标识符也使其区分大小写，而未引用的名称总是折叠为小写。例如，标识符 FOO、foo 和 "foo" 被 PostgreSQL 认为是相同的，但 "Foo" 和 "FOO" 是不同于这三个并且彼此不同。（PostgreSQL 中将未加引号的名称折叠为小写与 SQL 标准不兼容，即未加引号的名称应折叠为大写。因此，foo 应等同于“FOO”而不是“ foo" 根据标准。如果您想编写便携式应用程序，建议您始终引用特定名称或从不引用它。)" http://www.postgresql.org/docs/8.4/static/sql-syntax- lexical.html#SQL-SYNTAX-IDENTIFIERS
So, sometimes, it depends on what you are doing...
所以，有时，这取决于你在做什么......
回答by techvslife
Think this is worth emphasizing: If a binary or case-sensitive collation is in effect, then (at least in Sql Server and other databases with rich collation features) identifiers and variable names WILL be case sensitive. You can even create tables whose names differ only in case. (—I am not sure the info above about the sql-92 standard is correct—if so, this part of the standard is not widely followed.)
认为这值得强调：如果二进制或区分大小写的排序规则有效，那么（至少在 Sql Server 和其他具有丰富排序规则功能的数据库中）标识符和变量名称将区分大小写。您甚至可以创建名称仅在大小写不同的表。（——我不确定上面关于 sql-92 标准的信息是否正确——如果是这样，这部分标准没有被广泛遵循。）
回答by Yevgeniy Afanasyev
The column names which are mixed case or uppercase have to be double quoted in PostgreSQL. If you don't want to worry about it in the future, name it in the lower case.
混合大小写或大写的列名必须在PostgreSQL 中用双引号引起来。如果您以后不想担心它，请将其命名为小写。
MySQL- the columns are absolutely case insensitive. And it can lead to problems. Say someone has written "mynAme" instead of "myName". The system would work fine, but once some developer would go searching for it through the source code, they might overlook it, and you all get in trouble.
MySQL- 列绝对不区分大小写。它可能会导致问题。假设有人写了“mynAme”而不是“myName”。该系统可以正常工作，但是一旦某些开发人员通过源代码搜索它，他们可能会忽略它，而你们都会遇到麻烦。
