kavoir.com kavoir.com - 3 months ago 13
MySQL Question

Syntax error for multiple change column in alter table?

I'm trying to alter a table like this:

ALTER TABLE book
ADD COLUMN `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `id`,
CHANGE COLUMN `id` bigint unsigned NOT NULL AUTO_INCREMENT,
CHANGE COLUMN `author_id` bigint unsigned NOT NULL;


But it keeps giving this syntax error:

#1064 - 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 'bigint unsigned NOT NULL AUTO_INCREMENT, CHANGE COLUMN `author_id` bigint un' at line 3


I have no idea where I'm going wrong here. The SQL seems quite all right to me and each of the table alteration can be successfully executed individually:

ALTER TABLE book ADD COLUMN `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `id`;
ALTER TABLE book CHANGE COLUMN `id` bigint unsigned NOT NULL AUTO_INCREMENT;
ALTER TABLE book CHANGE COLUMN `author_id` bigint unsigned NOT NULL;


I can use the individual versions but there are quite some large tables that I need to alter so it would be better if I use a combined query which should be faster.

Why doesn't the combined SQL work?

Answer

Problem is in your below code line cause per syntax in documentation CHANGE expects a new column name like CHANGE [COLUMN] old_col_name new_col_name column_definition

CHANGE COLUMN `id` bigint unsigned NOT NULL AUTO_INCREMENT,

So you can either change this to

CHANGE COLUMN `id` `new_id` bigint unsigned NOT NULL AUTO_INCREMENT,

(OR) consider using MODIFY rather like MODIFY [COLUMN] col_name column_definition

MODIFY [COLUMN] `id` bigint unsigned NOT NULL AUTO_INCREMENT
Comments