在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 back ticks without any real thought.

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

Example:

例子:

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

Also, in the above example, consider that "table," "col[n]," and "val[n]" may be variables.

同样，在上面的例子中，考虑“table”、“col[n]”和“val[n]”可能是变量。

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

11 个解决方案

#1

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 DATE and DATETIME literal values to be single-quoted as strings like '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.

MySQL还希望日期和DATETIME文字值作为“2001-01-01 00:00”之类的字符串被单独引用。有关更多细节，请参阅日期和时间文本文档，特别是使用连字符作为日期字符串的段分隔符的替代方法。

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

因此，使用您的示例，我将双引号PHP字符串并在值'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.

不应该引用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. 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 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,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 must quote (backtick) them.

您可以将该设置之外的字符用作表或列标识符，例如，包括空格，但是必须对它们进行引号(反引号)。

#2

There are two types of quotes in MySQL:

在MySQL中有两种引用类型:

1. ' for enclosing string literals
2. “用于封闭字符串文字。
3. ` for enclosing identifiers such as table and column names
4. 用于封装标识符，如表和列名

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

还有一个特殊的情况。根据MySQL服务器的sql_mode，可以将其用于上述目的之一:

1. By default the " character can be used to enclose string literals just like '
2. 默认情况下，“字符可以被用来包围字符串文字，就像”
3. In ANSI_QUOTES mode the " character can be used to enclose identifiers just like `
4. 在ANSI_QUOTES模式中，“字符可以用来包含标识符，就像'

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

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

ANSI_QUOTES disabled

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

查询将选择string literal“column”，其中foo列等于string "bar"

ANSI_QUOTES enabled

The query will select the column column where column foo is equal to column bar

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

When to use what

• I suggest that you avoid using " so that your code becomes independent of SQL modes
• 我建议您避免使用“以便您的代码独立于SQL模式”
• Always quote identifiers since it is a good practice (quite a few questions on SO discuss this)
• 总是引用标识符，因为这是一个很好的实践(相当多的问题，所以讨论这个)

#3

(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

#4

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

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

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.

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

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. Now the server will take the title1 as a column name (i.e. an identifier). So, to indicate that it's a value you have to use either double or single quotes.

这里我故意忘记在标题1后面加上引号。现在服务器将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 $val2 to 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')

#5

Basically in Mysql, There are these kinds of identifier are used in query ` ," ,' and () .

基本上在Mysql中，查询'、'、'和()都使用这些标识符。

1. " or ' use for enclosing the string like values "26-01-2014 00:00:00" or '26-01-2014 00:00:00' . These identifier use only for string not aggregate function like now() or sum ,max etc.

   或'26-01-2014 00:00'或'26-01-2014 00:00'这些标识符只用于字符串而不是聚合函数now()或sum、max等。

2. ` use for enclosing table or table column e.g. select column_name from table_name where id='2'

   '用于封装表或表列，例如，从表_name中选择column_name，其中id='2'

3. () are use only for just enclose parts of query e.g. select column_name from table_name where (id='2' and gender='male') or name='rakesh' .

   ()只用于包含查询的部分，例如从table_name中选择column_name，其中(id='2'和性别='male')或name='rakesh'。

#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. So you need to escape it before it is used in sql; that is what mysql_real_escape_string is for. (Although a prepared statement is better.)

但是，如果$val1或$val2包含单引号，那么SQL就会出错。所以在sql中使用之前需要转义;这就是mysql_real_escape_string的用途。(尽管事先准备好的陈述更好。)

#7

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:

现在，假设您正在使用一个直接post变量到MySQL查询中，那么使用它:

$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变量的最佳实践。

#8

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，双引号和单引号使您的查询编写时间更容易。

#9

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

如果表cols和值是变量，那么有两种方法:

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”。<——注意:排除。从蜱虫。

#10

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

这里有很多有用的答案，通常达到两点。

1. BACKTICKS()` are used around identifier names.
2. 反勾号()'用于标识符名称。
3. QUOTES(') are used around values.
4. 引号(')用于值。

AND as @MichaelBerkowski said

@MichaelBerkowski说过

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保留关键字时,或当标识符包含空格字符或字符之外的一组有限(见下文)通常建议避免使用保留字作为列或表标识符在可能的情况下,避免引用问题。

There is a case though where an identifier can neither be a reserved keyword or contain whitespace or characters beyond limited set but necessarily require backticks around them.

有一种情况，标识符既不能是保留的关键字，也不能包含有限集之外的空格或字符，但必须在它们周围加上反勾。

EXAMPLE

例子

123E10 is a valid identifier name but also a valid INTEGER literal.

123E10是一个有效的标识符名称，也是一个有效的整数文本。

[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, 123451a6 is 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 1234156e6 is also an exponential number.

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

#11

Besides all of the (well-explained) answers, there hasn't been the following mentioned and I visit this Q&A quite often.

除了所有的(详细解释的)答案之外，没有提到以下内容，我经常访问这个问答。

In a nutshell; MySQL thinks you want to do math on its own table/column and interprets hyphens such as "e-mail" as e minus mail.

总而言之;MySQL认为您想在它自己的表/列上做算术，并将诸如“电子邮件”这样的连字符解释为电子邮件。

---

Disclaimer: So I thought I would add this as an "FYI" type of answer for those who are completely new to working with databases and who may not understand the technical terms described already.

免责声明:因此，我认为对于那些对使用数据库完全陌生、可能不理解所描述的技术术语的人，我将把这作为一种“参考答案”。