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

问题描述

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

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使用方括号(以及MS Access)，而MySQL使用反引号.

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

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

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.

来自文档:

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

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

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

如果启用了ANSI_QUOTES SQL模式，则也可以在双引号中引起标识符的引用:

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屋！