Alex Rivera | Logout

When to use single quotes, double quotes, and backticks in MySQL

Asked 2012-07-04T01:53:36.917
805

I am trying to learn the best way to write queries. I also understand the importance of being consistent. Until now, I have randomly used single quotes, double quotes, and backticks without any real thought.

Example:

$query = 'INSERT INTO table (id, col1, col2) VALUES (NULL, val1, val2)';

Also, in the above example, consider that table, col1, val1, etc. may be variables.

What is the standard for this? What do you do?

php sql mysql quotes
Edit
Report

2 Answers

173

In MySQL you have:

  • 'single quotes' for enclosing string literals
  • `Backticks` for enclosing identifiers such as table and column names

And then we have "double quotes" which could be used for one of above purposes at a time depending on MySQL server's sql_mode:

  • In stock configuration, the " character can be used to enclose string literals just like '
  • In ANSI_QUOTES mode the " character can be used to enclose identifiers just like `

The following query will produce different results (or errors) depending on SQL mode:

SELECT "column" FROM table WHERE foo = "bar"

ANSI_QUOTES disabled

The query will select the string literal "column" where column foo is equal to string "bar"

ANSI_QUOTES enabled

The query will select the column column where column foo is equal to column bar

When to use what

  • I suggest that you avoid using " so that your code becomes independent of SQL modes
  • Always quote identifiers since it is a good practice
answered 2013-01-02T14:17:35.703
36

(There are good answers above regarding the SQL nature of your question, but this may also be relevant if you are new to PHP.)

Perhaps it is important to mention that PHP handles single and double quoted strings differently...

Single-quoted strings are 'literals' and are pretty much WYSIWYG strings. Double-quoted strings are interpreted by PHP for possible variable-substitution (backticks in PHP are not exactly strings; they execute a command in the shell and return the result).

Examples:

$foo = "bar";
echo 'there is a $foo'; // There is a $foo
echo "there is a $foo"; // There is a bar
echo `ls -l`; // ... a directory list
answered 2012-07-04T02:03:01.137

Your Answer