When creating MySQL sql scripts, is it necessary to use quote column name in ` `?

Databases
1

when creating MySQL sql scripts, is it necessary to use quote column name in , like:

DROP TABLE IF EXISTS `qs_admin_log`;
CREATE TABLE `qs_admin_log` (
  `log_id` int(10) unsigned NOT NULL auto_increment,
  `admin_name` varchar(20) NOT NULL,
  `add_time` int(10) NOT NULL,
  `log_value` varchar(255) NOT NULL,
  `log_ip` varchar(20) NOT NULL,
  `log_type` tinyint(1) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY  (`log_id`)
) TYPE=MyISAM ;

?

I saw many scripts use this , is it necessary or is it just a good practice to use?

But if I execute such sql statements in phpMyAdmin, it shows syntax error. Also when I convert MySQL database to MS SQL Server database, those becomes dot sign and MSSQL reports syntax error when executing.

So is it a good MySQL practice to use when writing SQL statements?

2

neither

don’t use them, ever

this means you should not create table or column names which contain spaces or special characters or are reserved words

but that’s easy to do, yes?

3

The short answer, is yes, it’s good practice to escape your column and table names with backticks in MySQL. This allows you to use spaces (but you shouldn’t use spaces) and words that would otherwise be reserved (either existing reserved words, or new reserved words that appear in later or future versions of the database engine–but you should try to avoid doing this).

However, as you’ve discovered, not all database engines use the same method of escaping table and column names–only MySQL (as far as I’m aware) uses backticks.

However, using double-quotes to escape table and column names are generally cross-compatible. Oracle, PostgreSQL, MySQL, MSSQL, and SQLite all support the use of double-quotes to escape table and column names. There are a couple caveats to this, though.

In MSSQL, the default method for escaping table and column names is to use square brackets. MSSQL requires QUOTED_IDENTIFIER to be set to ON in order to be able to use double-quotes.

MySQL requires ANSI mode to be turned on in order to be able to use double-quotes.

If you find yourself needing to port queries from one engine to another, you will likely need to perform a find and replace search for the escape symbols. However, if you are going between MySQL and MSSQL, there are quite a few differences beyond just the usage of the escape symbols.

4

I agree with Rudy. You should never need to use backticks in your SQL commands where you have control of the column names - you should always pick column names that don’t need them. Since none of your column names need them you can then consistently NOT use backticks.

The only reason some software such as phpmyadmin uses them is because it doesn’t have control over what column names you choose and so has to allow for all the stupid things like spaces and reserved words.

5

thanks for all your advice!