SQL SQL中的单引号和双引号有什么区别？

What is the difference between single and double quotes in SQL?

提问by Vineet

What is the difference between single quotes and double quotes in SQL?

SQL中的单引号和双引号有什么区别？

采纳答案by OMG Ponies

Single quotes are used to indicate the beginning and end of a string in SQL. Double quotes generally aren't used in SQL, but that can vary from database to database.

单引号用于在 SQL 中表示字符串的开头和结尾。双引号通常不在 SQL 中使用，但可能因数据库而异。

Stick to using single quotes.

坚持使用单引号。

That's the primary use anyway. You can use single quotes for a column alias — where you want the column name you reference in your application code to be something other than what the column is actually called in the database. For example: PRODUCT.idwould be more readable as product_id, so you use either of the following:

无论如何，这是主要用途。您可以对列别名使用单引号 — 您希望在应用程序代码中引用的列名不是该列在数据库中实际调用的名称。例如：PRODUCT.id将更具可读性product_id，因此您可以使用以下任一项：

• SELECT PRODUCT.id AS product_id
• SELECT PRODUCT.id 'product_id'

• SELECT PRODUCT.id AS product_id
• SELECT PRODUCT.id 'product_id'

Either works in Oracle, SQL Server, MySQL… but I know some have said that the TOAD IDE seems to give some grief when using the single quotes approach.

要么在 Oracle、SQL Server、MySQL 中工作……但我知道有些人说 TOAD IDE 在使用单引号方法时似乎有些麻烦。

You do have to use single quotes when the column alias includes a space character, e.g., product id, but it's not recommended practice for a column alias to be more than one word.

当列别名包含空格字符时，您确实必须使用单引号，例如product id，，但建议不要将列别名设置为多个单词。

回答by Please_Dont_Bully_Me_SO_Lords

A simple rule for us to remember what to use in which case:

我们记住在这种情况下使用什么的简单规则：

• [S]ingle quotes are for [S]trings ; [D]ouble quotes are for [D]atabase identifiers;

• [ S] 单引号用于 [ S] 字符串；[ D] 双引号用于 [ D] 数据库标识符；

In MySQL and MariaDB, the `(backtick) symbol is the same as the "symbol. You can use "when your SQL_MODEhas ANSI_QUOTESenabled.

在MySQL和MariaDB的，在`（反引号）符号是一样的“符号，你可以用”当你SQL_MODE已经ANSI_QUOTES启用。

回答by Bill Karwin

Single quotes delimit a string constant or a date/time constant.

单引号分隔字符串常量或日期/时间常量。

Double quotes delimit identifiers for e.g. table names or column names. This is generally only necessary when your identifier doesn't fit the rules for simple identifiers.

双引号分隔标识符，例如表名或列名。这通常仅在您的标识符不符合简单标识符规则时才需要。

See also:

也可以看看：

• Do different databases use different name quote?

• 不同的数据库使用不同的名称引用吗？

You can make MySQL use double-quotes per the ANSI standard:

您可以让 MySQL 根据 ANSI 标准使用双引号：

SET GLOBAL SQL_MODE=ANSI_QUOTES

You can make Microsoft SQL Server use double-quotes per the ANSI standard:

您可以让 Microsoft SQL Server 根据 ANSI 标准使用双引号：

SET QUOTED_IDENTIFIER ON

回答by MarkR

In ANSI SQL, double quotes quote object names (e.g. tables) which allows them to contain characters not otherwise permitted, or be the same as reserved words (Avoid this, really).

在 ANSI SQL 中，双引号引用对象名称（例如表），允许它们包含不允许的字符，或者与保留字相同（避免这种情况，真的）。

Single quotes are for strings.

单引号用于字符串。

However, MySQL is oblivious to the standard (unless its SQL_MODE is changed) and allows them to be used interchangably for strings.

然而，MySQL 不遵守标准（除非它的 SQL_MODE 被改变）并允许它们可互换地用于字符串。

Moreover, Sybase and Microsoft also use square brackets for identifier quoting.

此外，Sybase 和 Microsoft 也使用方括号进行标识符引用。

So it's a bit vendor specific.

所以它有点特定于供应商。

Other databases such as Postgres and IBM actually adhere to the ansi standard :)

其他数据库，如 Postgres 和 IBM 实际上都遵循 ansi 标准:)

回答by guettli

I use this mnemonic:

我使用这个助记符：

• Single quotes are for strings (one thing)
• Double quotes are for tables names and column names (two things)

• 单引号用于字符串（一件事）
• 双引号用于表名和列名（两件事）

This is not 100% correct according to the specs, but this mnemonic helps me (human being).

根据规范，这不是 100% 正确，但这个助记符对我（人类）有帮助。

回答by Ayushh Srivastava

The difference lies in their usage. The single quotes are mostly used to refer a string in WHERE, HAVING and also in some built-in SQL functions like CONCAT, STRPOS, POSITION etc.

不同之处在于它们的用法。单引号主要用于在 WHERE、HAVING 以及一些内置 SQL 函数（如 CONCAT、STRPOS、POSITION 等）中引用字符串。

When you want to use an alias that has space in between then you can use double quotes to refer to that alias.

当您想使用中间有空格的别名时，您可以使用双引号来引用该别名。

For example

例如

(select account_id,count(*) "count of" from orders group by 1)sub 

Here is a subquery from an orders table having account_id as Foreign key that I am aggregating to know how many orders each account placed. Here I have given one column any random name as "count of" for sake of purpose.

这是来自以 account_id 作为外键的订单表中的子查询，我正在聚合它以了解每个帐户下的订单数量。在这里，出于目的，我给一列随机命名为“count of”。

Now let's write an outer query to display the rows where "count of" is greater than 20.

现在让我们编写一个外部查询来显示“count of”大于 20 的行。

select "count of" from 
(select account_id,count(*) "count of" from orders group by 1)sub where "count of" >20;

You can apply the same case to Common Table expressions also.

您也可以将相同的情况应用于公用表表达式。