List:General Discussion« Previous MessageNext Message »
From:chas Date:May 10 1999 3:07pm
Subject:column names starting with numbers causing a problem.
View as plain text  
First off, I'll say that I've changed my column names so 
that they don't start with a number - it seems to be safer
(for the long term).... so I've circumvented the problem 
outlined below.  But I'm curious as to the reason for
the following error since the MySQL docs say that column names
can start with a numeral :


Consider two tables "features" and "filter" :

mysql> explain features;
+--------------+------------+------+-----+---------+-------
| Field        | Type       | Null | Key | Default | Extra
+--------------+------------+------+-----+---------+-------
| placeid      | int(11)    |      | PRI | 0       |
| 20wordstitle | char(32)   | YES  |     | NULL    |
| 20wordsbody  | char(128)  | YES  |     | NULL    |
| 60wordstitle | char(55)   | YES  |     | NULL    |
| 60wordsbody  | char(255)  | YES  |     | NULL    |
+--------------+------------+------+-----+---------+-------
5 rows in set (0.00 sec)

mysql> explain filter;
+------------+------------+------+-----+---------+-------+
| Field      | Type       | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| placeid    | int(11)    |      | PRI | 0       |       |
| region     | char(12)   |      | MUL |         |       |
| ourrating  | tinyint(1) |      |     | -1      |       |
| costrating | tinyint(1) |      |     | -1      |       |
| fhomepage  | tinyint(1) |      | MUL | 0       |       |
| fcategory  | tinyint(1) |      | MUL | 0       |       |
| fregion    | tinyint(1) |      |     | 0       |       |
| fcolumn    | tinyint(1) |      |     | 0       |       |
+------------+------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

As you can see, it was no problem using column names like
"60wordsbody" and I loaded data into these tables fine. 
Howvever, queries involving the column names that start 
with a  numeral gave errors. For example :

mysql> select a.60wordstitle, a.placeid
    -> from features as a, filter as b
    -> where (b.placeid = 1)
    -> and (a.placeid = b.placeid)
    -> ;
ERROR 1064: parse error near '.60wordstitle, a.placeid


Compare with :

mysql> select b.region, a.placeid
    -> from features as a, filter as b
    -> where (b.placeid = 1)
    -> and (a.placeid = b.placeid)
    -> ;
+---------+---------+
| region  | placeid |
+---------+---------+
| Bangsar |       1 |
+---------+---------+
1 row in set (0.01 sec)


Is my SQL just screwed or is there another explanation for this ?

chas

Thread
A good database design bookIsabelle Poueriet9 May
  • Re: A good database design bookSasha Pachev9 May
  • Re: A good database design bookDavor Cengija9 May
  • Re: A good database design bookJules Bean9 May
    • Re: A good database design bookMartin Ramsch9 May
      • column names starting with numbers causing a problem.chas10 May
Re: A good database design bookbjorn10 May
  • Re: A good database design bookJules Bean10 May