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]
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)
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.