何时在 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 分钟的类似问题的答案，但这个问题似乎没有明确的答案.
推荐答案
反引号用于表和列标识符，但仅当标识符是 MySQL 保留关键字，或者当标识符包含空白字符或超出限制集的字符时(见下文)通常建议尽可能避免使用保留关键字作为列或表标识符，避免引用问题.
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.
此外，尽管数字是标识符的有效字符，但标识符不能仅由数字组成.如果他们这样做，他们必须用反引号包裹起来.
Also, although numbers are valid characters for identifiers, identifiers cannot consist solely of numbers. If they do they must be wrapped in backticks.
相关文章