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

本文翻译自：When to use single quotes, double quotes, and backticks in MySQL

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分钟的类似问题的答案，但是似乎没有确切的答案。}

---

#1楼

参考：https://stackoom.com/question/lVEh/何时在MySQL中使用单引号-双引号和反引号

---

#2楼

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. 单引号应用于VALUES()列表中的字符串值。 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. MySQL还为字符串值支持双引号，但是单引号已被其他RDBMS广泛接受，因此使用单引号而不是双引号是一个好习惯。

MySQL also expects DATE and DATETIME literal values to be single-quoted as strings like '2001-01-01 00:00:00' . MySQL还希望DATE和DATETIME文字值DATETIME引号括起来，如'2001-01-01 00:00:00'类'2001-01-01 00:00:00'字符串。 Consult the Date and Time Literals documentation for more details, in particular alternatives to using the hyphen - as a segment delimiter in date strings. 咨询日期和时间文字的更多详细信息的文件，特别是替代使用连字符-在日期字符串段分隔符。

So using your example, I would double-quote the PHP string and use single quotes on the values 'val1', 'val2' . 因此，使用您的示例，我将对PHP字符串加双引号，并对值'val1', 'val2'使用单引号。 NULL is a MySQL keyword, and a special (non)-value, and is therefore unquoted. 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. RDBMS的本机函数（例如，MySQL中的NOW() ）不应被引用，尽管它们的参数要遵循已经提到的相同的字符串或标识符引用规则。

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. 变量的引用模式不会改变，尽管如果您打算直接在字符串中插入变量，则必须在PHP中将其双引号。 Just make sure that you have properly escaped the variables for use in SQL. 只要确保您已正确转义了要在SQL中使用的变量即可。 ( It is recommended to use an API supporting prepared statements instead, as protection against SQL injection ). （ 建议使用支持预处理语句的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 unquoted placeholders, 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,az,AZ$_] (basic Latin letters, digits 0-9, dollar, underscore) ASCII： [0-9,az,AZ$_] （基本拉丁字母，数字0-9，美元，下划线）

You can use characters beyond that set as table or column identifiers, including whitespace for example, but then you must quote (backtick) them. 您可以使用超出设置范围的字符作为表或列标识符，例如，包括空格，但是必须将其引号（反引号）。

---

#3楼

Backticks are generally used to indicate an identifier and as well be safe from accidentally using the Reserved Keywords . 反引号通常用于表示一个identifier和以及从偶然使用安全 保留的关键字 。

For example: 例如：

Use `database`;

Here the backticks will help the server to understand that the database is 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 habit if you wrap your database identifier with backticks. 如果用反引号包装数据库标识符，这是一个很好的习惯 。

Check this answer to understand more about backticks. 检查此答案以了解有关反引号的更多信息。

---

Now about Double quotes & Single Quotes (Michael has already mentioned that). 现在有关双引号和单引号（Michael已经提到过）。

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 title1 with quotes. 在这里，我故意忘记了将title1用引号引起来。 Now the server will take the title1 as a column name (ie an identifier). 现在，服务器将title1作为列名（即标识符）。 So, to indicate that it's a value you have to use either double or single quotes. 因此，要表明它是一个值，您必须使用双引号或单引号。

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. 现在，与PHP结合使用，双引号和单引号使您的查询编写时间变得更加轻松。 Let's see a modified version of the query in your question. 让我们看看问题中查询的修改版本。

$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 $val2 to use their values thus creating a perfectly valid query. 现在，在PHP中使用双引号，您将使变量$val1和$val2使用它们的值，从而创建一个完全有效的查询。 Like 喜欢

$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')

---

#4楼

If table cols and values are variables then there are two ways: 如果表cols和value是变量，则有两种方法：

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` . `table_name` `column_name` <-- Note: exclude . `column_name` <-注意：排除. from back ticks. 从后壁虱。

---

#5楼

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

---

#6楼

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 $val1 or $val2 contains single quotes, that will make your SQL be wrong. 但是，如果$val1或$val2包含单引号，则会使您的SQL错误。 So you need to escape it before it is used in sql; 因此，您需要先对其进行转义，然后才能在sql中使用它； that is what mysql_real_escape_string is for. 这就是mysql_real_escape_string的用途。 (Although a prepared statement is better.) （尽管准备好的陈述会更好。）