MySQL Forums
Forum List  »  InnoDB

Re: alter multiple tables
Posted by: Rick James
Date: February 14, 2013 10:02PM

alter table tbl
modify column ID bigint(20) NOT NULL,
DROP PRIMARY KEY,
add column rowid int(11) NOT NULL AUTO_INCREMENT primary key,
ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

Note: they all must refer to the same table.
There are certain exceptions, mostly in PARTITION maintenance.
Also some newer versions of MySQL speed up certain operations so that they are better done by themselves -- DROP INDEX is a notable example.

However, since InnoDB _must_ have a PRIMARY KEY, your DROP PRIMARY KEY effectively removes the PK you had, but then builds a new, hidden, PK. Then adding rowid as a PK, has to drop that PK and add your new one. Twice as much work as it would be if the DROP and ADD were in the same ALTER.

Options: ReplyQuote


Subject
Views
Written By
Posted
13834
February 12, 2013 06:02AM
4003
February 13, 2013 07:40PM
2904
February 14, 2013 02:23AM
Re: alter multiple tables
2984
February 14, 2013 10:02PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.