何时在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.

示例:

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

此外，在上面的示例中，请考虑table，col1，val1等可能是变量.

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

这是什么标准?你怎么办?

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

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

推荐答案

反引号用于表和列的标识符，但是仅当标识符是

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.

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

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.

MySQL还希望DATE和DATETIME文字值可以像'2001-01-01 00:00:00'这样被单引号括起来.查阅日期和时间文字文档有关更多详细信息，特别是在日期字符串中使用连字符-作为分段定界符的替代方法.

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.

因此，使用您的示例，我将PHP字符串双引号，并在值'val1', 'val2'上使用单引号. NULL是MySQL关键字，是一个特殊的(非)值，因此未加引号.

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.

这些表或列标识符都不是保留字或使用需要引号的字符，但是无论如何我都用反引号将它们引号(稍后会再介绍...).

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

RDBMS的本机功能(例如，MySQL中的NOW())不应该被引用，尽管它们的参数要遵循已经提到的相同的字符串或标识符引用规则.

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.

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         ─────────────────────────────────────────┴┴┴┴┘    

变量插值

变量的引用模式不会改变，尽管如果您打算直接在字符串中插入变量，则必须在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')";

准备好的声明

在使用准备好的语句时，请查阅文档以确定是否必须引用语句的占位符. PHP，PDO和MySQLi中提供的最流行的API期望未加引号的占位符，其他大多数语言的预准备语句API也是如此:

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:

// 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 (?, ?, ?, ?)";

需要在标识符中加上引号的字符:

根据MySQL文档，您不需要使用以下字符集引用(反引号)标识符:

Characters requring backtick quoting in identifiers:

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

ASCII:[0-9,a-z,A-Z$_](基本拉丁字母，数字0-9，美元，下划线)

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

您可以使用超出此范围的字符设置为表或列标识符，例如，包括空格，但是您必须必须引用(反引号).

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

这篇关于何时在MySQL中使用单引号，双引号和反引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！