When should we use `` or '' in mySQL?

Question

Hello all,

CREATE TABLE `counties` (
  `id` int(11) NOT NULL auto_increment,
  `county` tinytext NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;

-- 
INSERT INTO `counties` VALUES (1, 'Alachua County');

I have seen some patterns here. People uses `` to surround field names and use '' to surround values. Is that true?

Or the major reason to do so is because then we can put the code in a text file and import into the database.

thank you

Answer 1

Yes, you use backticks [`] to surround field, table, and database names. You don't really need it unless you are using spaces in your field/table names, or words that have special meaning in SQL (ie: from, where)

Single quotes ['] are use to surround strings.

Answer 2

Using `` to surround field names is specific to MySQL afaik; you might want to avoid it if you want your SQL to be portable. The table.column or schema.table.column notation usually works well unless you've done something silly like put spaces in your table/column names.

Using table.column also makes it much harder to get lost if you're looking at a large dtd with lots of tables and columns.

Answer 3

'' for strings is quite common - it applies for just about every programming language there is, and it is most definitely required in MySQL for string values. (You will sometimes see '1'. It works, but the single quotes are unnecessary, and actually are more work for the MySQL engine to work through for integer values.)

Backticks for table and field names, though, are optional. If you're not using any reserved keywords, they are unnecessary. If, however, you make the mistake of naming a column something like select, you will need the backticks to avoid syntax errors. Some programmers (and just about any automatic query-building process) use the backticks just to be safe.