Geek asks and answers

Is it possible to add a late auto-increment primary index column in a full mysql table, late?

Assuming this table contains nearly 5,000,000 rows

CREATE TABLE `author2book` (
  `author_id` int(11) NOT NULL,
  `book_id` int(11) NOT NULL,
  KEY `author_id_INDEX` (`author_id`),
  KEY `paper_id_INDEX` (`book_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Is it possible to add a primary index column idwith autoincrementas first place? I expect something like this:

CREATE TABLE `author2book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,  <<<<  This is what I try to achieve!
  `author_id` int(11) NOT NULL,
  `book_id` int(11) NOT NULL,
  KEY `author_id_INDEX` (`author_id`),
  KEY `paper_id_INDEX` (`book_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Is it possible?

Edit: I have to mention that I would like the added column to be populated.

+3
source share
3 answers

You can use ALTER TABLE to add a column and index to the same command. i.e:.

ALTER TABLE author2book ADD id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id);

See the MySQL Docs for ALTER TABLE for more information .

+5
source

, ... ( ) .

insert into newTable (author_id, book_id) 
select * from author2book

newTable .

+1
ALTER TABLE author2book
ADD COLUMN `id` int(11) NOT NULL FIRST;

Fill in the id field manually (using a script perhaps?), And then:

ALTER TABLE author2book
MODIFY COLUMN `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY;
0
source

More articles:


All Articles