SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    25
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    when creating MySQL sql scripts, is it necessary to use quote column name in ` `, like:
    Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by blackswan View Post
    ... is it necessary or is it just a good practice to use?
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Barefoot on the Moon! silver trophy Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,606
    Mentioned
    56 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by blackswan View Post
    when creating MySQL sql scripts, is it necessary to use quote column name in ` `, like:

    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?
    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.
    Visit The Blog | Follow On Twitter
    301tool 1.1.5 - URL redirector & shortener (PHP/MySQL)
    Can be hosted on and utilize your own domain

  4. #4
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,807
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Force Flow View Post
    The short answer, is yes, it's good practice to escape your column and table names with backticks in MySQL.
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    25
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thanks for all your advice!


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •