SQL语句中的反引号和方括号有什么区别? [英] What is the difference between the backtick and the square bracket in SQL statements?

问题描述

我以为会有另一个问题，但我找不到.在 MySQL 和 PHP 中，我通常用反引号封装我的字段名称以屏蔽任何保留的名称或字符.但是，我的一位同事指出，这也可以使用方括号来实现.排除反引号与 SQL Server 不兼容的事实(显然)，有什么区别?我应该使用哪个?

SELECT `username` FROM `users`从 [用户] 中选择 [用户名]

解决方案

SQL Server/T-SQL 使用方括号(以及 MS Access)，而 MySQL 使用反引号.

据我所知，可以出现在文档中，或者在测试，方括号对 MySQL 无效.因此，如果您需要在 SQL Server 中将关键字作为表名括起来，请使用 []，而在 MySQL 中，当启用 ANSI_QUOTES 时，请使用反引号或双引号.

来自文档:

<块引用>

标识符引号字符是反引号(`"):

mysql>SELECT * FROM `select` WHERE `select`.id >100;

<块引用>

如果启用了 ANSI_QUOTES SQL 模式，也允许在双引号内引用标识符:

mysql>创建表测试"(col INT)；错误 1064:您的 SQL 语法有错误...mysql>设置 sql_mode='ANSI_QUOTES';mysql>创建表测试"(col INT)；查询正常，0 行受影响(0.00 秒)

I thought there would be another question about this but I was unable to find one. In MySQL with PHP I usually encapsulate my field names with backticks to mask any reserved names or characters. However, one of my colleagues has pointed out that this can also be achieved using square brackets. Excluding the fact that the backticks are not compatible with SQL server (apparently), what is the difference? Which should I use?

SELECT `username` FROM `users`
SELECT [username] FROM [users]

解决方案

SQL Server/T-SQL uses square brackets (as well as MS Access), while MySQL uses backticks.

As far as I know, can turn up in documentation, or use in testing, square brackets are not valid for MySQL. So if you need to enclose a keyword as a table name in SQL Server, use [], and in MySQL use backticks, or double-quotes when ANSI_QUOTES is enabled.

From the documentation:

The identifier quote character is the backtick ("`"):

mysql> SELECT * FROM `select` WHERE `select`.id > 100;

If the ANSI_QUOTES SQL mode is enabled, it is also permissible to quote identifiers within double quotation marks:

mysql> CREATE TABLE "test" (col INT);
ERROR 1064: You have an error in your SQL syntax...
mysql> SET sql_mode='ANSI_QUOTES';
mysql> CREATE TABLE "test" (col INT);
Query OK, 0 rows affected (0.00 sec)

这篇关于SQL语句中的反引号和方括号有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！