转义 SQL 名称中的正斜杠?可以“转义"，但SQL认为是多列 [英] Escaping a forward slash in an SQL name? It can be "escaped", but SQL believes it to be multiple columns

问题描述

我工作中的最后一个人在列名中填充了特殊字符，例如 (?、! 和/)，并为列名使用了许多保留关键字(通常使用时间戳或用户).

The last person in my job has flooded column names with special characters such as (?,!, and /), as well as used many reserved keywords for column names (more often than not, timestamp or user is used).

通常，我通过使用双引号或括号来转义 SQL 对象来解决这个问题.完整列列表的子集如下:

Normally, I step around this by using double quotes or brackets to escape the SQL object. A subset of the full list of columns are below:

DriverID, 
Department, 
Odometer, 
MerchantState, 
MerchantCity, 
gallons/Units, 
timestamp, 
tax

在我的查询中，我将有问题的两列(加仑/单位和时间戳)括在双引号内.时间戳，因为它是一个保留关键字，而加仑/单位，因为没有引号，SQL 读取查询，停在斜杠处，并告诉我加仑"不是表中的列.

Inside my query, I wrap the two columns in question (gallons/units and timestamp) inside double quotes. Timestamp because it's a reserved keyword, and Gallons/units, because without the quotes, SQL reads the query, stops at the slash, and tells me "Gallons" is not a column inside the table.

如果我在列名周围加上双引号，SQL 会返回一个不同的错误:操作数应该包含 1 列".

If I do wrap double quotes around the column name, SQL returns a different error: "Operand should contain 1 column(s)".

我尝试了所有变体(仅捕获引号中的斜杠，同时引用两者，使用方括号，混合方括号和引号等，但都无济于事).

I've tried every variant (only capturing the slash in quotes, quoting both, using brackets, mixing brackets and quotes, etc. but with to no avail).

除了重命名列名并更改从中提取的程序中的关联代码之外，我还能做些什么来修复此查询?(我试图避免的非常乏味的任务).

Is there anything I can do to fix this query short of renaming the column name and changing the associated code in the program that pulls from it? (the really tedious task I'm trying to avoid).

推荐答案

在 SQL Server 中，标识符可以使用方括号分隔，例如

In SQL Server, identifiers can be delimited using square brackets, e.g.

SELECT [gallons/units] ...

在 MySQL 中，标识符可以使用反引号分隔，例如

In MySQL, identifiers can be delimited using backticks, e.g.

SELECT `gallons/units` ...

(注意:如果 MySQL SQL_MODE 包含 ANSI_QUOTES，那么双引号将被视为标识符的分隔符，类似于 Oracle 处理双引号的方式；如果没有该设置，double引号被处理为字符串文字的分隔符.使用 ANSI_QUOTES 包括 SQL_MODE，"gallons/unit" 将被解释为标识符(列名). 如果没有 ANSI_QUOTES，MySQL 会将其视为字符串文字，就像用单引号括起来一样.)

(NOTE: If MySQL SQL_MODE includes ANSI_QUOTES, then double quotes are treated as delimiters for identifiers, similar to the way Oracle handles double quotes; absent that setting, double quotes are handled as delimiters for string literals. With ANSI_QUOTES included SQL_MODE, "gallons/units" will be interpreted as an identifier (column name). Without ANSI_QUOTES, MySQL will see it as a string literal, as if it were enclosed in single quotes.)

跟进:

至于错误 操作数应该只包含 1 列"，这通常是查询语义的问题，而不是转义标识符的问题.

As far as an error "operand should contain only 1 column(s)", that's usually a problem with query semantics, not an issue with escaping identifiers.

SELECT 列表中的子查询只能返回一个表达式，例如，这将引发错误:

A subquery in the SELECT list can return only a single expression, for example, this would throw an error:

Query: SELECT 'foo' , ( SELECT 'fee' AS fee, 'fi' AS fi )

Error Code: 1241
Operand should contain 1 column(s)

这篇关于转义 SQL 名称中的正斜杠?可以“转义"，但SQL认为是多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！