[pcductape] Re: MySql

  • From: "Pam" <ltf01@xxxxxxxxxx>
  • To: <pcductape@xxxxxxxxxxxxx>
  • Date: Wed, 17 Sep 2003 00:01:25 -0500

Hi Vic,
Thanks for the info.  After a lot of trial and error and by process of
elimination, I learned a hard earned lesson about reserved words.  I thought
at first it was because I was updating a table using fresh input and another
table's data.  I'm building a search engine. =)

Pam
  -----Original Message-----
  From: pcductape-bounce@xxxxxxxxxxxxx
[mailto:pcductape-bounce@xxxxxxxxxxxxx]On Behalf Of Victor Firestone
  Sent: Wednesday, September 17, 2003 12:40 AM
  To: pcductape@xxxxxxxxxxxxx
  Subject: [pcductape] Re: MySql


  Pam - a tad long and quite explicit

  As you can see - desc and describe are not allowed. There is a workaround
for this that not always works which is to use ANSI standard
double-quotesdouble-quotes. Also, if you have a double-quote in the variable
name, you just use two double-quotes, e.g. Annoying "variable name" becomes
"Annoying ""variable name""". But, and there are always lots of buts
especially in programs like MySQL - Backticks are the MySQL style of quoting
table and column names. Only if you are running in ANSI mode should you use
double-quotes.


   MySQL Picky About Reserved Words

  A common problem stems from trying to create a table with column names
that use the names of datatypes or functions built into MySQL, such as
TIMESTAMP or GROUP. You're allowed to do it (for example, ABS is allowed as
a column name). However, by default, in function invocations no whitespace
is allowed between the function name and the following `(' character, so
that a function call can be distinguished from a reference to a column name.

  If you start the server with the --ansi or --sql-mode=IGNORE_SPACE option,
the server allows function invocations to have whitespace between a function
name and the following `(' character. This causes function names to be
treated as reserved words; as a result, column names that are the same as
function names must be quoted.

  The following words are explicitly reserved in MySQL. Most of them are
forbidden by SQL-92 as column and/or table names (for example, GROUP). A few
are reserved because MySQL needs them and is (currently) using a yacc
parser:

        Word  Word  Word
        ADD  ALL  ALTER
        ANALYZE  AND  AS
        ASC  ASENSITIVE  AUTO_INCREMENT
        BDB  BEFORE  BERKELEYDB
        BETWEEN  BIGINT  BINARY
        BLOB  BOTH  BTREE
        BY  CALL  CASCADE
        CASE  CHANGE  CHAR
        CHARACTER  CHECK  COLLATE
        COLUMN  COLUMNS  CONNECTION
        CONSTRAINT  CREATE  CROSS
        CURRENT_DATE  CURRENT_TIME  CURRENT_TIMESTAMP
        CURSOR  DATABASE  DATABASES
        DAY_HOUR  DAY_MICROSECOND  DAY_MINUTE
        DAY_SECOND  DEC  DECIMAL
        DECLARE  DEFAULT  DELAYED
        DELETE  DESC  DESCRIBE
        DISTINCT  DISTINCTROW  DIV
        DOUBLE  DROP  ELSE
        ELSEIF  ENCLOSED  ERRORS
        ESCAPED  EXISTS  EXPLAIN
        FALSE  FIELDS  FLOAT
        FOR  FORCE  FOREIGN
        FROM  FULLTEXT  GRANT
        GROUP  HASH  HAVING
        HIGH_PRIORITY  HOUR_MICROSECOND  HOUR_MINUTE
        HOUR_SECOND  IF  IGNORE
        IN  INDEX  INFILE
        INNER  INNODB  INOUT
        INSENSITIVE  INSERT  INT
        INTEGER  INTERVAL  INTO
        IO_THREAD  IS  ITERATE
        JOIN  KEY  KEYS
        KILL  LEADING  LEAVE
        LEFT  LIKE  LIMIT
        LINES  LOAD  LOCALTIME
        LOCALTIMESTAMP  LOCK  LONG
        LONGBLOB  LONGTEXT  LOOP
        LOW_PRIORITY  MASTER_SERVER_ID  MATCH
        MEDIUMBLOB  MEDIUMINT  MEDIUMTEXT
        MIDDLEINT  MINUTE_MICROSECOND  MINUTE_SECOND
        MOD  MRG_MYISAM  NATURAL
        NOT  NO_WRITE_TO_BINLOG  NULL
        NUMERIC  ON  OPTIMIZE
        OPTION  OPTIONALLY  OR
        ORDER  OUT  OUTER
        OUTFILE  PRECISION  PRIMARY
        PRIVILEGES  PROCEDURE  PURGE
        READ  REAL  REFERENCES
        REGEXP  RENAME  REPEAT
        REPLACE  REQUIRE  RESTRICT
        RETURN  RETURNS  REVOKE
        RIGHT  RLIKE  RTREE
        SECOND_MICROSECOND  SELECT  SENSITIVE
        SEPARATOR  SET  SHOW
        SMALLINT  SOME  SONAME
        SPATIAL  SPECIFIC  SQL_BIG_RESULT
        SQL_CALC_FOUND_ROWS  SQL_SMALL_RESULT  SSL
        STARTING  STRAIGHT_JOIN  STRIPED
        TABLE  TABLES  TERMINATED
        THEN  TINYBLOB  TINYINT
        TINYTEXT  TO  TRAILING
        TRUE  TYPES  UNION
        UNIQUE  UNLOCK  UNSIGNED
        UNTIL  UPDATE  USAGE
        USE  USER_RESOURCES  USING
        UTC_DATE  UTC_TIME  UTC_TIMESTAMP
        VALUES  VARBINARY  VARCHAR
        VARCHARACTER  VARYING  WARNINGS
        WHEN  WHERE  WHILE
        WITH  WRITE  XOR
        YEAR_MONTH  ZEROFILL

  The following symbols (from the table above) are disallowed by SQL-99 but
allowed by MySQL as column/table names. This is because some of these names
are very natural names and a lot of people have already used them.

    a.. ACTION
    b.. BIT
    c.. DATE
    d.. ENUM
    e.. NO
    f.. TEXT
    g.. TIME
    h.. TIMESTAMP




  ~~~~~~~~~~~~~~~~~~~

  TTFN - Vic

  "To laugh often and much; to win the respect of intelligent people and
  the affection of children; to earn the appreciation of honest critics
  and endure the betrayal  of false friends; to appreciate beauty, to
find the best in others; to leave the world a little better; whether by a
healthy child, a garden patch or a redeemed social condition; to know even
one life has breathed easier because you have lived.

  ~~~~~~~~~~~~~~~~~~






----------------------------------------------------------------------------
--
  From: pcductape-bounce@xxxxxxxxxxxxx
[mailto:pcductape-bounce@xxxxxxxxxxxxx] On Behalf Of Pam
  Sent: Wednesday, 17 September, 2003 06:14
  To: pcductape@xxxxxxxxxxxxx
  Subject: [pcductape] MySql


  Does anyone know if "description" or any form of that word is a reserved
word in MySql?

  Pam

Other related posts: