何时在 MySQL 中使用单引号、双引号和反引号

When to use single quotes, double quotes, and backticks in MySQL

提问by Nate

I am trying to learn the best way to write queries. I also understand the importance of being consistent. Until now, I have randomly used single quotes, double quotes, and backticks without any real thought.

我正在尝试学习编写查询的最佳方法。我也明白保持一致的重要性。到现在为止，我没有任何真正的想法就随机使用了单引号、双引号和反引号。

Example:

例子：

$query = 'INSERT INTO table (id, col1, col2) VALUES (NULL, val1, val2)';

Also, in the above example, consider that table, col1, val1, etc. may be variables.

另外，在上述例子中，考虑到table，col1，val1等可以是变量。

What is the standard for this? What do you do?

这样做的标准是什么？你做什么工作？

_{I've been reading answers to similar questions on here for about 20 minutes, but it seems like there is no definitive answer to this question.}

_{我已经在这里阅读了大约 20 分钟的类似问题的答案，但似乎这个问题没有明确的答案。}

采纳答案by Michael Berkowski

Backticks are to be used for table and column identifiers, but are only necessary when the identifier is a MySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set (see below) It is often recommended to avoid using reserved keywords as column or table identifiers when possible, avoiding the quoting issue.

反引号将用于表和列标识符，但仅当标识符是MySQL 保留关键字时才需要，或者当标识符包含空格字符或超出有限集的字符时（见下文）通常建议避免使用保留关键字尽可能作为列或表标识符，避免引用问题。

Single quotes should be used for string values like in the VALUES()list. Double quotes are supported by MySQL for string values as well, but single quotes are more widely accepted by other RDBMS, so it is a good habit to use single quotes instead of double.

单引号应该用于VALUES()列表中的字符串值。MySQL 也支持字符串值的双引号，但单引号被其他 RDBMS 更广泛地接受，因此使用单引号代替双引号是一个好习惯。

MySQL also expects DATEand DATETIMEliteral values to be single-quoted as strings like '2001-01-01 00:00:00'. Consult the Date and Time Literalsdocumentation for more details, in particular alternatives to using the hyphen -as a segment delimiter in date strings.

MySQL 还期望DATE和DATETIME文字值被单引号作为字符串，如'2001-01-01 00:00:00'. 有关更多详细信息，请参阅日期和时间文字文档，特别是-在日期字符串中使用连字符作为段分隔符的替代方法。

So using your example, I would double-quote the PHP string and use single quotes on the values 'val1', 'val2'. NULLis a MySQL keyword, and a special (non)-value, and is therefore unquoted.

因此，使用您的示例，我会双引号 PHP 字符串并在 values 上使用单引号'val1', 'val2'。 NULL是一个 MySQL 关键字，并且是一个特殊的（非）值，因此不加引号。

None of these table or column identifiers are reserved words or make use of characters requiring quoting, but I've quoted them anyway with backticks (more on this later...).

这些表或列标识符都不是保留字或使用需要引用的字符，但我还是用反引号引用了它们（稍后会详细介绍......）。

Functions native to the RDBMS (for example, NOW()in MySQL) should not be quoted, although their arguments are subject to the same string or identifier quoting rules already mentioned.

NOW()不应引用RDBMS 原生的函数（例如，在 MySQL 中），尽管它们的参数受已提及的相同字符串或标识符引用规则的约束。

Backtick (`)
table & column ───────┬─────┬──┬──┬──┬────┬──┬────┬──┬────┬──┬───────┐
                      ↓     ↓  ↓  ↓  ↓    ↓  ↓    ↓  ↓    ↓  ↓       ↓
$query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`, `updated`) 
                       VALUES (NULL, 'val1', 'val2', '2001-01-01', NOW())";
                               ↑↑↑↑  ↑    ↑  ↑    ↑  ↑          ↑  ↑↑↑↑↑ 
Unquoted keyword          ─────┴┴┴┘  │    │  │    │  │          │  │││││
Single-quoted (') strings ───────────┴────┴──┴────┘  │          │  │││││
Single-quoted (') DATE    ───────────────────────────┴──────────┘  │││││
Unquoted function         ─────────────────────────────────────────┴┴┴┴┘    

Variable interpolation

变量插值

The quoting patterns for variables do not change, although if you intend to interpolate the variables directly in a string, it must be double-quoted in PHP. Just make sure that you have properly escaped the variables for use in SQL. (It is recommended to use an API supporting prepared statements instead, as protection against SQL injection).

变量的引用模式不会改变，但如果您打算直接在字符串中插入变量，则必须在 PHP 中使用双引号。只要确保您已正确转义在 SQL 中使用的变量。（建议改用支持预处理语句的 API，以防止 SQL 注入）。

// Same thing with some variable replacements
// Here, a variable table name $table is backtick-quoted, and variables
// in the VALUES list are single-quoted 
$query = "INSERT INTO `$table` (`id`, `col1`, `col2`, `date`) VALUES (NULL, '$val1', '$val2', '$date')";

Prepared statements

准备好的报表

When working with prepared statements, consult the documentation to determine whether or not the statement's placeholders must be quoted. The most popular APIs available in PHP, PDO and MySQLi, expect unquotedplaceholders, as do most prepared statement APIs in other languages:

使用准备好的语句时，请查阅文档以确定是否必须引用语句的占位符。PHP、PDO 和 MySQLi 中最流行的 API 需要不带引号的占位符，其他语言的大多数准备好的语句 API 也是如此：

// PDO example with named parameters, unquoted
$query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`) VALUES (:id, :col1, :col2, :date)";

// MySQLi example with ? parameters, unquoted
$query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`) VALUES (?, ?, ?, ?)";

Characters requring backtick quoting in identifiers:

在标识符中需要反引号引用的字符：

According to MySQL documentation, you do not need to quote (backtick) identifiers using the following character set:

根据 MySQL 文档，您不需要使用以下字符集引用（反引号）标识符：

ASCII: [0-9,a-z,A-Z$_](basic Latin letters, digits 0-9, dollar, underscore)

ASCII：（[0-9,a-z,A-Z$_]基本拉丁字母、数字 0-9、美元、下划线）

You can use characters beyond that set as table or column identifiers, including whitespace for example, but then you mustquote (backtick) them.

您可以使用超出该设置的字符作为表或列标识符，例如包括空格，但您必须引用（反引号）它们。

回答by Salman A

There are two types of quotes in MySQL:

MySQL中有两种类型的引号：

1. 'for enclosing string literals
2. `for enclosing identifiers such as table and column names

1. '用于封闭字符串文字
2. `用于封闭标识符，例如表名和列名

And then there is "which is a special case. It could be used for oneof above-mentioned purposes at a time depending on MySQL server's sql_mode:

然后"是一种特殊情况。它可以一次用于上述目的之一，具体取决于 MySQL 服务器的sql_mode：

1. By defaultthe "character can be used to enclose string literals just like '
2. In ANSI_QUOTESmode the "character can be used to enclose identifiers just like `

1. 默认情况下，该"字符可用于包含字符串文字，就像'
2. 在ANSI_QUOTES模式下，"字符可用于包含标识符，就像`

The following query will produce different results (or errors) depending on SQL mode:

以下查询将根据 SQL 模式产生不同的结果（或错误）：

SELECT "column" FROM table WHERE foo = "bar"

ANSI_QUOTES disabled

ANSI_QUOTES 已禁用

The query will select the string literal "column"where column foois equal to string "bar"

查询将选择"column"列foo等于字符串的字符串文字"bar"

ANSI_QUOTES enabled

ANSI_QUOTES 已启用

The query will select the column columnwhere column foois equal to column bar

查询将选择column列foo等于列的列bar

When to use what

什么时候用什么

• I suggest that you avoid using "so that your code becomes independent of SQL modes
• Always quote identifiers since it is a good practice (quite a few questions on SO discuss this)

• 我建议您避免使用，"以便您的代码独立于 SQL 模式
• 总是引用标识符，因为这是一个很好的做法（关于 SO 的很多问题都在讨论这个）

回答by Chris Trahey

(There are good answers above regarding the SQL nature of your question, but this may also be relevant if you are new to PHP.)

（上面有关于您问题的 SQL 性质的很好的答案，但如果您不熟悉 PHP，这也可能相关。）

Perhaps it is important to mention that PHP handles single and double quoted strings differently...

也许重要的是要提到 PHP 以不同的方式处理单引号和双引号字符串......

Single-quoted strings are 'literals' and are pretty much WYSIWYG strings. Double-quoted strings are interpreted by PHP for possible variable-substitution (backticks in PHP are not exactly strings; they execute a command in the shell and return the result).

单引号字符串是“文字”，几乎是 WYSIWYG 字符串。双引号字符串被 PHP 解释为可能的变量替换（PHP 中的反引号不完全是字符串；它们在 shell 中执行命令并返回结果）。

Examples:

例子：

$foo = "bar";
echo 'there is a $foo'; // There is a $foo
echo "there is a $foo"; // There is a bar
echo `ls -l`; // ... a directory list

回答by Starx

Backticks are generally used to indicate an identifierand as well be safefrom accidentally using the Reserved Keywords.

反引号通常用于指示identifier和不失为安全的意外使用保留的关键字。

For example:

例如：

Use `database`;

Here the backticks will help the server to understand that the databaseis in fact the name of the database, not the database identifier.

这里的反引号将帮助服务器理解database实际上是数据库的名称，而不是数据库标识符。

Same can be done for the table names and field names. This is a very good habitif you wrap your database identifier with backticks.

表名和字段名也可以这样做。如果你用反引号包裹你的数据库标识符，这是一个很好的习惯。

Check thisanswer to understand more about backticks.

检查此答案以了解有关反引号的更多信息。

---

---

Now about Double quotes & Single Quotes (Michael has already mentioned that).

现在关于双引号和单引号（迈克尔已经提到过）。

But, to define a value you have to use either single or double quotes. Lets see another example.

但是，要定义一个值，您必须使用单引号或双引号。让我们看另一个例子。

INSERT INTO `tablename` (`id, `title`) VALUES ( NULL, title1);

Here I have deliberately forgotten to wrap the title1with quotes. Now the server will take the title1as a column name (i.e. an identifier). So, to indicate that it's a value you have to use either double or single quotes.

这里我故意忘记title1用引号括起来。现在服务器将把title1当作列名（即标识符）。因此，要表明它是一个值，您必须使用双引号或单引号。

INSERT INTO `tablename` (`id, `title`) VALUES ( NULL, 'title1');

---

---

Now, in combination with PHP, double quotes and single quotes make your query writing time much easier. Let's see a modified version of the query in your question.

现在，结合 PHP，双引号和单引号使您的查询编写时间更加轻松。让我们看看您的问题中查询的修改版本。

$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, '$val1', '$val2')";

Now, using double quotes in the PHP, you will make the variables $val1, and $val2to use their values thus creating a perfectly valid query. Like

现在，在 PHP 中使用双引号，您将创建变量$val1，并$val2使用它们的值，从而创建一个完全有效的查询。喜欢

$val1 = "my value 1";
$val2 = "my value 2";
$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, '$val1', '$val2')";

will make

将使

INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, 'my value 1', 'my value 2')

回答by Kumar Rakesh

In MySQL, these symbols are used to delimit a query `,",'and ().

在MySQL中，这些符号是用来分隔查询`，"，'和()。

1. "or 'are used for enclosing string-like values "26-01-2014 00:00:00"or '26-01-2014 00:00:00'. These symbols are only for strings, not aggregate functions like now, sum, or max.

2. `is used for enclosing table or column names, e.g. select `column_name` from `table_name` where id='2'

3. (and )simply enclose parts of a query e.g. select `column_name` from `table_name` where (id='2' and gender='male') or name='rakesh'.

1. "或'用于封闭类似字符串的值"26-01-2014 00:00:00"或'26-01-2014 00:00:00'。这些符号仅用于字符串，而不是聚合函数一样now，sum或max。

2. `用于封闭表名或列名，例如 select `column_name` from `table_name` where id='2'

3. (并)简单地附上查询的一部分，例如 select `column_name` from `table_name` where (id='2' and gender='male') or name='rakesh'.

回答by xdazz

The string literals in MySQL and PHP are the same.

MySQL 和 PHP 中的字符串文字是相同的。

A string is a sequence of bytes or characters, enclosed within either single quote (“'”) or double quote (“"”) characters.

字符串是由单引号 (“'”) 或双引号 (“"”) 字符括起来的字节或字符序列。

So if your string contains single quotes, then you could use double quotes to quote the string, or if it contains double quotes, then you could use single quotes to quote the string. But if your string contains both single quotes and double quotes, you need to escape the one that used to quote the string.

所以如果你的字符串包含单引号，那么你可以使用双引号来引用字符串，或者如果它包含双引号，那么你可以使用单引号来引用字符串。但是，如果您的字符串同时包含单引号和双引号，则需要转义用于引用字符串的那个。

Mostly, we use single quotes for an SQL string value, so we need to use double quotes for a PHP string.

大多数情况下，我们对 SQL 字符串值使用单引号，因此我们需要对 PHP 字符串使用双引号。

$query = "INSERT INTO table (id, col1, col2) VALUES (NULL, 'val1', 'val2')";

And you could use a variable in PHP's double-quoted string:

你可以在 PHP 的双引号字符串中使用一个变量：

$query = "INSERT INTO table (id, col1, col2) VALUES (NULL, '$val1', '$val2')";

But if $val1or $val2contains single quotes, that will make your SQL be wrong. So you need to escape it before it is used in sql; that is what mysql_real_escape_stringis for. (Although a prepared statement is better.)

但是如果$val1或$val2包含单引号，那将使您的 SQL 出错。所以在sql中使用之前需要将其转义；这就是为什么mysql_real_escape_string。（虽然准备好的语句更好。）

回答by vipul sorathiya

In combination of PHP and MySQL, double quotes and single quotes make your query-writing time so much easier.

PHP 和 MySQL 的结合，双引号和单引号使您的查询编写时间变得更加容易。

$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, '$val1', '$val2')";

Now, suppose you are using a direct post variable into the MySQL query then, use it this way:

现在，假设你在 MySQL 查询中使用了一个直接的 post 变量，那么使用它：

$query = "INSERT INTO `table` (`id`, `name`, `email`) VALUES (' ".$_POST['id']." ', ' ".$_POST['name']." ', ' ".$_POST['email']." ')";

This is the best practice for using PHP variables into MySQL.

这是在 MySQL 中使用 PHP 变量的最佳实践。

回答by vipul sorathiya

There has been many helpful answers here, generally culminating into two points.

这里有很多有用的答案，通常可以归结为两点。

1. BACKTICKS(`) are used around identifier names.
2. SINGLE QUOTES(') are used around values.

1. BACKTICKS(`) 用于围绕标识符名称。
2. 单引号（'）用于值周围。

AND as @MichaelBerkowski said

并且正如@MichaelBerkowski 所说

Backticks are to be used for table and column identifiers, but are only necessary when the identifier is a MySQLreserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set (see below) It is often recommended to avoid using reserved keywords as column or table identifiers when possible, avoiding the quoting issue.

反引号用于表和列标识符，但仅当标识符是MySQL保留关键字时才需要，或者标识符包含空白字符或超出有限集的字符（见下文）通常建议避免使用保留关键字作为尽可能使用列或表标识符，避免引用问题。

There is a case though where an identifier can neither be a reserved keywordor contain whitespaceor characters beyond limited setbut necessarily require backticks around them.

但在某些情况下，标识符既不能是保留关键字，也不能包含空格或超出限制集的字符，但必须在它们周围加上反引号。

EXAMPLE

例子

123E10is a valid identifier name but also a valid INTEGERliteral.

123E10是一个有效的标识符名称，但也是一个有效的INTEGER文字。

[Without going into detail how you would get such an identifier name], Suppose I want to create a temporary table named 123456e6.

[没有详细说明如何获得这样的标识符名称]，假设我想创建一个名为123456e6.

No ERROR on backticks.

反引号没有错误。

DB [XXX]> create temporary table `123456e6` (`id` char (8));
Query OK, 0 rows affected (0.03 sec)

ERROR when not using backticks.

不使用反引号时出错。

DB [XXX]> create temporary table 123451e6 (`id` char (8));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '123451e6 (`id` char (8))' at line 1

However, 123451a6is a perfectly fine identifier name (without back ticks).

然而，123451a6是一个完美的标识符名称（没有反勾号）。

DB [XXX]> create temporary table 123451a6 (`id` char (8));
Query OK, 0 rows affected (0.03 sec)

This is completely because 1234156e6is also an exponential number.

这完全是因为1234156e6它也是一个指数数。

回答by diEcho

If table cols and values are variables then there are two ways:

如果表列和值是变量，则有两种方法：

With double quotes ""the complete query:

使用双引号""完成查询：

$query = "INSERT INTO $table_name (id, $col1, $col2)
                 VALUES (NULL, '$val1', '$val2')";

Or

或者

 $query = "INSERT INTO ".$table_name." (id, ".$col1.", ".$col2.")
               VALUES (NULL, '".$val1."', '".$val2."')";

With single quotes '':

带单引号''：

$query = 'INSERT INTO '.$table_name.' (id, '.$col1.', '.$col2.')
             VALUES (NULL, '.$val1.', '.$val2.')';

Use back ticks ``when a column/value name is similar to a MySQL reserved keyword.

``当列/值名称类似于 MySQL 保留关键字时，使用反勾号。

Note:If you are denoting a column name with a table name then use back ticks like this:

注意：如果您用表名表示列名，则使用反勾号，如下所示：

`table_name`. `column_name`<-- Note: exclude .from back ticks.

`table_name`. `column_name`<-- 注意：.从反勾号中排除。

回答by john igneel

Single quotes should be used for string values like in the VALUES() list.

单引号应该用于像 VALUES() 列表中的字符串值。

Backticks are generally used to indicate an identifier and as well be safe from accidentally using the reserved keywords.

反引号通常用于指示标识符，并且可以避免意外使用保留关键字。

In combination of PHP and MySQL, double quotes and single quotes make your query writing time so much easier.

PHP 和 MySQL 的结合，双引号和单引号使您的查询编写时间变得更加容易。