何时在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分钟,但看起来这个问题没有明确的答案。

12 个解决方案

#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还希望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'. 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. ` for enclosing identifiers such as table and column names

`用于包含表和列名称等标识符

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. In ANSI_QUOTES mode the " character can be used to enclose identifiers just like `

在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"

查询将选择字符串文字“列”,其中列foo等于字符串“bar”

ANSI_QUOTES enabled

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

查询将选择列foo等于列栏的列列

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)

总是引用标识符,因为这是一个很好的做法(关于SO的很多问题讨论这个)

#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 $fooecho "there is a $foo"; // There is a barecho `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).

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

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.

在这里,我故意忘记用引号包装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 $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

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.

   “或'用于包含类似字符串的值”26-01-2014 00:00:00“或'26 -01-2014 00:00:00'。这些符号仅用于字符串,而不是像现在这样的聚合函数,总和,或最大

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

   `用于封闭表名或列名,例如从`table_name`中选择`column_name`,其中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' .

   (和)简单地包含查询的部分,例如从`table_name`中选择`column_name`,其中(id ='2'和gender ='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.

这是将PHP变量用于MySQL的最佳实践。

#8

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

这里有很多有用的答案,通常最终分为两点。

1. BACKTICKS(`) are used around identifier names.

BACKTICKS(`)用于标识符名称。

2. SINGLE QUOTES(') are used around values.

单引号(')用于值。

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是有效的标识符名称,但也是有效的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, 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也是一个指数。

#9

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

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

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` < - 注意:排除。从后面的滴答声。

#10

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

#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认为你想在自己的表/列上进行数学运算,并将诸如“电子邮件”之类的连字符解释为e减去邮件。

---

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.

免责声明:所以我认为我会将此作为“FYI”类型的答案添加到那些完全不熟悉数据库并且可能不理解已经描述的技术术语的人那里。

#12

SQL servers and MySQL, PostgreySQL, Oracle don't understand double quotes("). Thus your query should be free from double quotes(") and should only use single quotes(').

SQL服务器和MySQL,PostgreySQL,Oracle不理解双引号(“)。因此,您的查询应该没有双引号(”),并且应该只使用单引号(')。

Back-trip(`) is optional to use in SQL and is used for table name, db name and column names.

回溯(`)是在SQL中使用的可选项,用于表名,数据库名和列名。

If you are trying to write query in your back-end to call MySQL then you can use double quote(") or single quotes(') to assign query to a variable like:

如果您尝试在后端编写查询来调用MySQL,那么您可以使用双引号(“)或单引号(')将查询分配给变量,如:

let query = "select id, name from accounts";//Orlet query = 'select id, name from accounts';

If ther's a where statement in your query and/or trying to insert a value and/or an update of value which is string use single quote(') for these values like:

如果你的查询中有where语句和/或试图插入一个值和/或值的更新,这是字符串使用单引号(')这些值,如:

let querySelect = "select id, name from accounts where name = 'John'";let queryUpdate = "update accounts set name = 'John' where id = 8";let queryInsert = "insert into accounts(name) values('John')";//Please not that double quotes are only to be used in assigning string to our variable not in the query//All these below will generate errorlet querySelect = 'select id, name from accounts where name = "John"';let queryUpdate = 'update accounts set name = "John" where id = 8';let queryInsert = 'insert into accounts(name) values("John")';//As MySQL or any SQL doesn't understand double quotes("), these all will generate error.

If you want to stay out of this confusion when to use double quotes(") and single quotes('), would recommend to stick with single quotes(') this will include backslash() like:

如果你想在使用双引号(“)和单引号(')时避免这种混淆,建议坚持使用单引号(')这将包括反斜杠(),如:

let query = 'select is, name from accounts where name = \'John\'';

Problem with double(") or single(') quotes arise when we had to assign some value dynamic and perform some string concatenation like:

当我们必须为动态分配一些值并执行一些字符串连接时,会出现double(“)或single(')引号的问题:

let query = "select id, name from accounts where name = " + fName + " " + lName;//This will generate error as it must be like name = 'John Smith' for SQL//However our statement made it like name = John Smith//In order to resolve such errors uselet query = "select id, name from accounts where name = '" + fName + " " + lName + "'";//Or using backslash(\)let query = 'select id, name from accounts where name = \'' + fName + ' ' + lName + '\'';

If need further clearance do follow quotes in JavaScript

如果需要进一步清除,请遵循JavaScript中的引号