sql - 解析带下划线的 SQL Server 数字文字

Question

我想知道它为什么有效以及为什么它不返回错误:

SELECT 2015_11

结果:

╔══════╗
║ _11  ║
╠══════╣
║ 2015 ║
╚══════╝

第二种情况:

SELECT 2.1_a

╔═════╗
║ _a  ║
╠═════╣
║ 2.1 ║
╚═════╝

检查元数据:

SELECT  name, system_type_name
FROM sys.dm_exec_describe_first_result_set(
N'SELECT 2015_11', NULL, 0) 
UNION ALL
SELECT  name, system_type_name
FROM sys.dm_exec_describe_first_result_set(
N'SELECT 3.2_a', NULL, 0) 

╔══════╦══════════════════╗
║ name ║ system_type_name ║
╠══════╬══════════════════╣
║ _11  ║ int              ║
║ _a   ║ numeric(2,1)     ║
╚══════╩══════════════════╝

虽然以字母开头的标识符的行为符合我的想法:

SELECT a_11
-- Invalid column name 'a_11'.

LiveDemo

Answer 1

SQL 将查询视为

SELECT 2015_11

作为

SELECT 2015 _11 

这是

的快捷方式

SELECT 2015 AS [_11]

SQL Server 希望列名称遵循一些命名约定规则，如本 MSDN link 中详述。

The names of variables, functions, and stored procedures must comply with the following rules for Transact-SQL identifiers. The first character must be one of the following:

1. A letter as defined by the Unicode Standard 3.2. The Unicode definition of letters includes Latin characters from a through z, from A through Z, and also letter characters from other languages.

2. The underscore (_), at sign (@), or number sign (#).

   Certain symbols at the beginning of an identifier have special meaning in SQL Server. A regular identifier that starts with the at sign always denotes a local variable or parameter and cannot be used as the name of any other type of object. An identifier that starts with a number sign denotes a temporary table or procedure. An identifier that starts with double number signs (##) denotes a global temporary object. Although the number sign or double number sign characters can be used to begin the names of other types of objects, we do not recommend this practice.

Some Transact-SQL functions have names that start with double at signs (@@). To avoid confusion with these functions, you should not use names that start with @@.

还有 SELECT 的语法，如 MSDN就像

SELECT [ ALL | DISTINCT ] [ TOP ( expression ) [ PERCENT ] [ WITH TIES ] ] ::= { * | { table_name | view_name | table_alias }.* | { [ { table_name | view_name | table_alias }. ] { column_name | $IDENTITY | $ROWGUID } | udt_column_name [ { . | :: } { { property_name | field_name } | method_name ( argument [ ,...n] ) } ] | expression [ [ AS ] column_alias ] } | column_alias = expression } [ ,...n ]

在这种情况下，SQL 解析器首先检查表名，然后检查列名、Identity 和 rowguid，依此类推，直到与

匹配

<强>|表达式[[AS]column_alias]

然后它读取文字值直到下划线字符，此时它意识到文字必须已经结束并开始将后面的字符解析为 Column_alias 而无需明确的 AS

要验证这一点，请尝试在 SQL Server 中执行以下代码

SELECT 2015AS _11

这将产生与

相同的结果

SELECT 2015_11

另外，为了验证我上面刚刚写的内容，请参阅 SSMS 的屏幕截图，它在 AS 上突出显示代码

在第一个示例中 2015 是整数文字，在第二个示例中 2.1 是十进制文字

在您的第三个示例中， a 不是有效的文字。如果你尝试

SELECT 'a'_8

这会给你这样的结果

╔═════╗
║ _8  ║
╠═════╣
║ a   ║
╚═════╝

PS:您会发现这与 # 的工作方式几乎相同

因此 SELECT 2015#11 将给出类似的结果

╔══════╗
║ #11  ║
╠══════╣
║ 2015 ║
╚══════╝