mel list_php wrote:
From: Dan Buettner <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Subject: Changing range of identity column for future inserts
Date: Tue, 15 Nov 2005 08:50:03 -0600
I would like to change one of my tables so that future inserts use a
higher range of numbers for the primary key values.
I have a table 'event' with column:
id int unsigned not null auto_increment primary key
Currently new records are going into the table with id column values
in the 3,000,000-range. I'd like to change that so that new records
get id column values in the 13,000,000 range or so ... but without
changing the values of existing records.
This is so I can combine data into another, existing table with 12
million entries, without overlapping id numbers or assigning different
ones.
I can't find how to do this on the MySQL site.
Any suggestions?
Thanks,
Dan
I think you want that:
When you add an AUTO_INCREMENT column, column values are filled in with
sequence numbers for you automatically. For MyISAM tables, you can set
the first sequence number by executing SET INSERT_ID=value before ALTER
TABLE or by using the AUTO_INCREMENT=value table option.
From MySQL 5.0.3, you can use the ALTER TABLE ... AUTO_INCREMENT=value
table
option for InnoDB tables to set the sequence number for new rows if the
value is greater than the maximum value in the AUTO_INCREMENT column.
(from http://dev.mysql.com/doc/refman/5.0/en/alter-table.html)
hth,
melanie
The ALTER TABLE solution works in 4.1, as well
<http://dev.mysql.com/doc/refman/4.1/en/alter-table.html>:
ALTER TABLE event AUTO_INCREMENT=13000000;
Note, however, that any ALTER TABLE operation makes a new copy of your table and
replaces the old with the new when done. This can take a while with a large
table, and writes are locked out until it's finished.
You can also simply insert a dummy row with the id column explicitly named and
set to 13000000. The next regularly inserted row will get id = 13000001.
Subsequent rows will get higher numbers. You can verify that the next
Auto_increment value has been changed by looking at the output of
SHOW TABLE STATUS LIKE 'event';
You can delete the dummy row immediately with MyISAM tables. With InnoDB, you
can delete it as soon as you have one real row with an id > 13000000. (See the
manual for details
<http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-column.html>).
Michael
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
