Changing range of identity column for future inserts
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]
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]
IDENTITY column
Hi, I am a beginner in using database. And I appreciate your support and help. When we first create a table, 1. is it possible to create a column that identifies each record that is to be inserted? 2. If we can create this IDENTITY column, how do we create it? Do we set a maximum to the value of this column? Or the value simply increases as the number of records get inserted into the table grows? 3. when we try to insert the first record to this table, does this record go to the first row in the table? And the value of the IDENTITY for this record is 1? When we try to insert the second record to this table, does the second record automatically go to the second row in the table? And the value of the IDENTITY is 2? __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IDENTITY column
Hi, I think what you need is an auto increment (primary key) column. When a record is inserted, it will automatically generate the next number in the sequence. Being a primary key, the column must be unique (i.e no two rows can be the same), so you can identify a specify record in the table. When you insert a record, you can run SELECT LAST_INSERT_ID(); to retrieve the id that was used. The LAST_INSERT_ID is determined on a per connection basis, so you are guaranteed to get the last id that _you_ inserted, even if there are other users inserting records, as long as you do not run another INSERT. As for 3, you should not worry about how MySQL stores the rows. You should specify an ORDER BY clause in your SELECT statements to get the order in which you want. If you want to know, MySQL stores the rows ascending (1 is first, 2 is second, ...). However, if a record gets deleted from the table, then MySQL will try to re-use the space that once held that row. Check out the following links: http://www.mysql.com/doc/en/CREATE_TABLE.html http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html HTH Matt At 01:14 PM 12/23/2003, you wrote: Hi, I am a beginner in using database. And I appreciate your support and help. When we first create a table, 1. is it possible to create a column that identifies each record that is to be inserted? 2. If we can create this IDENTITY column, how do we create it? Do we set a maximum to the value of this column? Or the value simply increases as the number of records get inserted into the table grows? 3. when we try to insert the first record to this table, does this record go to the first row in the table? And the value of the IDENTITY for this record is 1? When we try to insert the second record to this table, does the second record automatically go to the second row in the table? And the value of the IDENTITY is 2? __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- 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]
