Problem altering column order in MySQL 5

[nipponese]

Hi,

 

I just started learning MySQL last week, with no prior DB experience, but I found a good book on the subject but am getting unexpected results from MySQL.

 

The book claims that I can create columns in a specified order of an existing table.

For instance, if I wanted the new column to be the SECOND column in the table, I would use:

ALTER TABLE table_name ADD COLUMN col_name TYPE SECOND;

 

However, every time I try this, I get the error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SECOND' at line 1

 

The book also claims this works for THIRD, FOUTH, etc., and LAST, but I get the same error every time.

 

Is it possible the book is just wrong, or maybe this just doesn't work in MySQL 5?

 

Any help is appreciated.

 

I am using MySQL version: 5.0.51a Source distribution

 

Though I don't think this really helps, for my example, this is the SHOW CREATE TABLE:

CREATE TABLE `car_table` (
  `car_id` int(11) NOT NULL default '0',
  `color` varchar(15) default NULL,
  `year` int(4) default NULL,
  `make` varchar(15) default NULL,
  `model` varchar(15) default NULL,
  `howmuch` decimal(7,2) default NULL,
  `VIN` varchar(17) default NULL,
  PRIMARY KEY  (`car_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1

 

[fenway]

I've never seen such syntax... you can definitely create NEW columns BEFORE/AFTER existing ones.. but you can't move them at all (nor do you EVER have to).

[nipponese]

I've never seen such syntax... you can definitely create NEW columns BEFORE/AFTER existing ones.. but you can't move them at all (nor do you EVER have to).

 

Thanks for your fast reply! That cleared some things up.

 

Though it brings up one more question on the topic:

 

You said changing column order isn't allowed, but it does seems to work for me in this example:

 

mysql> SELECT * FROM car_table;
+-------------------+--------+--------+------+----------+----------+----------+
| VIN               | car_id | color  | year | make     | model    | howmuch  |
+-------------------+--------+--------+------+----------+----------+----------+
| F4G5T8H6S5E2X4A4E |      1 | silver | 1998 | Porsche  | Boxter   | 17992.54 | 
| S5E3F5V2G1H4W5E9E |      2 | NULL   | 2000 | Jaguar   | XJ       | 15995.00 | 
| B2C3A5E8W7C4V1A6E |      3 | red    | 2002 | Cadillac | Escalade | 40215.90 | 
+-------------------+--------+--------+------+----------+----------+----------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE car_table MODIFY COLUMN VIN VARCHAR(17) AFTER car_id;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM car_table;
+--------+-------------------+--------+------+----------+----------+----------+
| car_id | VIN               | color  | year | make     | model    | howmuch  |
+--------+-------------------+--------+------+----------+----------+----------+
|      1 | F4G5T8H6S5E2X4A4E | silver | 1998 | Porsche  | Boxter   | 17992.54 | 
|      2 | S5E3F5V2G1H4W5E9E | NULL   | 2000 | Jaguar   | XJ       | 15995.00 | 
|      3 | B2C3A5E8W7C4V1A6E | red    | 2002 | Cadillac | Escalade | 40215.90 | 
+--------+-------------------+--------+------+----------+----------+----------+
3 rows in set (0.00 sec)

 

Based on your experience, is this something I SHOULDN'T do?

[fenway]

No kidding! I've never even though of doing that... I guess that *does* work.  I didn't say it wasn't allowed, just that there wasn't a "move" option.  But it appears that you can simply move a column with really changing anything else about it.  I haven't delved to see whether or not any indexes involving this column are rebuilt, either.  BTW, this seems to work in v4.1 too.

 

You learn something new every day -- that being said, why bother?