Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is the difference between the backtick and the square bracket in SQL statements?

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]
like image 816
Ben Carey Avatar asked Mar 15 '12 12:03

Ben Carey


People also ask

What does square brackets mean in SQL?

On SQL Server and MS Access, square brackets have a special meaning when used in a query filter. The square brackets are used to specify a set or range of characters, as in "[A-Z]" which would match any single character from 'A' to 'Z'.

What does Backtick mean in SQL?

Backticks are used in MySQL to select columns and tables from your MySQL source. In the example below we are calling to the table titled Album and the column Title . Using backticks we are signifying that those are the column and table names.

What is the use of square brackets []?

Square brackets, often just called brackets in American English, are a set of punctuation marks that are most often used to alter or add information to quoted material. Square brackets come in pairs as [ and ].

Does brackets matter in SQL?

The brackets are required if you use keywords or special chars in the column names or identifiers. You could name a column [First Name] (with a space) – but then you'd need to use brackets every time you referred to that column.


2 Answers

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)
like image 78
Michael Berkowski Avatar answered Oct 07 '22 21:10

Michael Berkowski


Both are non-standard ways to quote object names that should either be case-sensitive, are a reserved word or contain special characters that are not allowed otherwise.

The standard quoting character for such an identifier is a double quote. To be ANSI SQL compatible, you should use them:

SELECT "username" FROM "users"

But note that quoted identifiers are case-sensitive as per ANSI SQL. However both mentioned products do not obey to this requirement. Whether such an identifier is case-sensitive or not depends on a several (different) configuration settings in MySQL and the database collation in MS SQL Server.

Both DBMS can (and in my opinion should) be configured to accept the ANSI standard quote characters as well.

I would strongly recommend to avoid any object name that requires quoting. Using identifiers that do not require quoting will save you a lot of trouble in the long run.

like image 21
a_horse_with_no_name Avatar answered Oct 07 '22 23:10

a_horse_with_no_name