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
