SQL 表名或列名不能以数字开头？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15917064/
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
Table or column name cannot start with numeric?
提问by Wayan Wiprayoga
I tried to create table named 15909434_userwith syntax like below:
我尝试创建以15909434_user如下语法命名的表：
CREATE TABLE 15909434_user ( ... )
It would produced error of course. Then, after I tried to have a bit research with google, I found a good article herethat describe:
它当然会产生错误。然后，在我尝试用谷歌进行一些研究之后，我在这里找到了一篇很好的文章，描述了：
When you create an object in PostgreSQL, you give that object a name. Every table has a name, every column has a name, and so on. PostgreSQL uses a single data type to define all object names: the
nametype.A value of type
nameis a string of 63 or fewer characters. A name must start with a letter or an underscore; the rest of the string can contain letters, digits, and underscores....
If you find that you need to create an object that does not meet these rules, you can enclose the name in double quotes. Wrapping a name in quotes creates a quoted identifier. For example, you could create a table whose name is "
3.14159"—the double quotes are required, but are not actually a part of the name (that is, they are not stored and do not count against the 63-character limit). ...
当你在 PostgreSQL 中创建一个对象时，你给那个对象一个名字。每个表都有一个名称，每个列都有一个名称，等等。PostgreSQL 使用单一数据类型来定义所有对象名称：
name类型。type 的值
name是一个包含 63 个或更少字符的字符串。名称必须以字母或下划线开头；字符串的其余部分可以包含字母、数字和下划线。...
如果您发现需要创建不符合这些规则的对象，可以将名称用双引号括起来。将名称用引号括起来会创建一个带引号的标识符。例如，您可以创建一个名称为“
3.14159”的表——双引号是必需的，但实际上并不是名称的一部分（即，它们不被存储并且不计入 63 个字符的限制）。...
Okay, now I know how to solve this by use this syntax (putting double quote on table name):
好的，现在我知道如何使用这种语法来解决这个问题（在表名上加上双引号）：
CREATE TABLE "15909434_user" ( ... )
You can create table or column name such as "15909434_user"and also user_15909434, but cannot create table or column name begin with numeric without use of double quotes.
您可以创建表名或列名，例如"15909434_user"和user_15909434，但不能在不使用双引号的情况下创建以数字开头的表名或列名。
So then, I am curious about the reason behind that (except it is a convention). Why this convention applied? Is it to avoid something like syntax limitation or other reason?
那么，我很好奇这背后的原因（除了它是一个约定）。为什么应用这个约定？是为了避免语法限制还是其他原因？
Thanks in advance for your attention!
预先感谢您的关注！
回答by rlb
It comes from the original sql standards, which through several layers of indirection eventually get to an identifier startblock, which is one of several things, but primarily it is "a simple latin letter". There are other things too that can be used, but if you want to see all the details, go to http://en.wikipedia.org/wiki/SQL-92and follow the links to the actual standard ( page 85 )
它来自最初的sql标准，它通过几层间接最终得到一个标识符起始块，这是几件事之一，但主要是“一个简单的拉丁字母”。还有其他东西可以使用，但如果您想查看所有详细信息，请访问http://en.wikipedia.org/wiki/SQL-92并按照实际标准的链接（第 85 页）
Having non numeric identifier introducers makes writing a parser to decode sql for execution easier and quicker, but a quoted form is fine too.
拥有非数字标识符引入器使得编写解析器来解码 sql 以更容易和更快地执行，但引用形式也很好。
Edit: Why is it easier for the parser?
编辑：为什么解析器更容易？
The problem for a parser is more in the SELECT-list clause than the FROMclause. The select-list is the list of expressions that are selected from the tables, and this is very flexible, allowing simple column names and numeric expressions. Consider the following:
解析器的问题在SELECT-list 子句中比在FROM子句中更多。select-list 是从表中选择的表达式列表，它非常灵活，允许简单的列名和数字表达式。考虑以下：
SELECT 2e2 + 3.4 FROM ...
If table names, and column names could start with numerics, is 2e2a column name or a valid number (eformat is typically permitted in numeric literals) and is 3.4the table "3" and column "4" or is it the numeric value 3.4?
如果表名和列名可以以数字开头，是2e2列名还是有效数字（e格式通常允许在数字文字中），是3.4表“ 3”和列“ 4”还是数值3.4？
Having the rule that identifiersstart with simple latin letters (and some other specific things) means that a parser that sees 2e2can quickly discern this will be a numeric expression, same deal with 3.4
标识符以简单的拉丁字母（和其他一些特定的东西）开头的规则意味着2e2可以快速识别这将是一个数字表达式的解析器，同样的处理3.4
While it would be possible to devise a scheme to allow numeric leading characters, this might lead to even more obscure rules (opinion), so this rule is a nice solution. If you allowed digits first, then it would always need quoting, which is arguably not as 'clean'.
虽然设计一个允许数字前导字符的方案是可能的，但这可能会导致更模糊的规则（意见），所以这个规则是一个很好的解决方案。如果您首先允许数字，那么它总是需要引用，这可以说不是“干净”。
Disclaimer, I've simplified the above slightly, ignoring corelation names to keep it short. I'm not totally familiar with postgres, but have double checked the above answer against Oracle RDB documentation and sql spec
免责声明，我已经稍微简化了上面的内容，忽略了相关名称以使其简短。我对 postgres 并不完全熟悉，但已经根据 Oracle RDB 文档和 sql 规范仔细检查了上述答案
回答by LoztInSpace
I'd imagine it's to do with the grammar.
我想这与语法有关。
SELECT 24*DAY_NUMBER as X from MY_TABLE
SELECT 24*DAY_NUMBER as X from MY_TABLE
is fine, but ambiguous if 24 was allowed as a column name.
很好，但如果允许 24 作为列名，则不明确。
Adding quotes means you're explicitly referring to an identifier not a constant. So in order to use it, you'd always have to escape it anyway.
添加引号意味着您明确引用标识符而不是常量。所以为了使用它，你无论如何都必须逃避它。
