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

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.

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