表或列名不能以数字开头吗? [英] Table or column name cannot start with numeric?

问题描述

我尝试使用以下语法创建名为15909434_user的表:

I tried to create table named 15909434_user with syntax like below:

CREATE TABLE 15909434_user ( ... )

这当然会产生错误.然后，在尝试对Google进行研究后，我在此处上找到了一篇不错的文章描述:

It would produced error of course. Then, after I tried to have a bit research with google, I found a good article here that describe:

在PostgreSQL中创建对象时，请为该对象命名.每个表都有一个名称，每个列都有一个名称，依此类推. PostgreSQL使用一种数据类型来定义所有对象名称:name类型.

类型为name的值是63个或更少字符的字符串.名称必须以字母或下划线开头；字符串的其余部分可以包含字母，数字和下划线.

A value of type name is 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.

...

如果发现需要创建一个不满足这些规则的对象，则可以将该名称括在双引号中.将名称包裹在引号中会创建一个带引号的标识符.例如，您可以创建一个名称为"3.14159"的表，该表必须是双引号，但实际上并非双引号的一部分(也就是说，它们不存储且不计入63个字符的限制) ). ...

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). ...

好的，现在我知道如何使用这种语法(在表名上加上双引号)解决此问题:

Okay, now I know how to solve this by use this syntax (putting double quote on table name):

CREATE TABLE "15909434_user" ( ... )

您可以创建表名或列名，例如"15909434_user"以及user_15909434，但是如果不使用双引号，则不能创建以数字开头的表名或列名.

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.

那么，我对此背后的原因感到好奇(除非是约定).为什么要应用此约定?是否要避免语法限制或其他原因?

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?

提前感谢您的关注！

推荐答案

它来自原始的sql标准，该标准通过几层间接最终到达 identifier start 块，这是其中之一.几件事，但主要是简单的拉丁字母".也可以使用其他方法，但是如果您想查看所有详细信息，请访问 http://en. wikipedia.org/wiki/SQL-92 并点击指向实际标准的链接(第85页)

It comes from the original sql standards, which through several layers of indirection eventually get to an identifier start block, 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-92 and follow the links to the actual standard ( page 85 )

具有非数字标识符介绍程序使编写解析器来更轻松，更快捷地对sql进行解码以执行代码，但是带引号的形式也可以.

Having non numeric identifier introducers makes writing a parser to decode sql for execution easier and quicker, but a quoted form is fine too.

解析器的问题更多地是在SELECT -list子句中，而不是在FROM子句中. select-list是从表中选择的表达式列表，它非常灵活，允许使用简单的列名和数字表达式.请考虑以下内容:

The problem for a parser is more in the SELECT-list clause than the FROM clause. 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 2e2 + 3.4 FROM ...

如果表名和列名可以以数字开头，则为2e2列名或有效数字(数字文字中通常允许使用e格式)，并且为3.4表"3"和"4"列还是数字值3.4?

If table names, and column names could start with numerics, is 2e2 a column name or a valid number (e format is typically permitted in numeric literals) and is 3.4 the table "3" and column "4" or is it the numeric value 3.4 ?

具有标识符以简单的拉丁字母(以及其他一些特定内容)开头的规则，这意味着看到2e2的解析器可以快速辨别这将是一个数字表达式，与3.4

Having the rule that identifiers start with simple latin letters (and some other specific things) means that a parser that sees 2e2 can quickly discern this will be a numeric expression, same deal with 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'.

免责声明，我略微简化了上述内容，忽略了相关名称，以使其简短.我对Postgres并不完全熟悉，但是已经针对Oracle RDB文档和sql spec仔细检查了以上答案

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

这篇关于表或列名不能以数字开头吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！