RE: Changing range of identity column for future inserts
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 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing range of identity column for future inserts
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=1300; 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 1300. The next regularly inserted row will get id = 1301. 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 1300. (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]
RE: Changing range of identity column for future inserts
Yes, I saw that tried it, but turned out I was just doing it wrong. I had an errant SET in my statement. What works is: ALTER TABLE event AUTO_INCREMENT = 1300 Thanks Dan At 2:59 PM + 11/15/05, mel list_php wrote: 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 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]