In MySQL 5.0, can one insert into a table with an auto_increment column without updating the auto_increment value?

Question

I have a MySQL database, containing a table with an auto_increment primary key.

Consider the following definition as sufficient for this example:

create table test(id integer not null auto_increment primary key) engine=innodb;

Now, typically I would insert into this table using the following syntax:

insert into test() values();

This would appropriately update the auto_increment value, and that is fine.

However, I would like to insert a high value into this table (say, 1000000) but not have it update the auto_increment ID.

Is it possible to insert high values into this table in such a way as to not have an effect on the value of the auto_increment ID?

I know this is a terrible practice, but it would significantly simplify some things I have to do.

Thanks in advance.

Answer 1

I don't believe so - auto incremented fields have to be primary keys and, as such, are not null.

Answer 2

MySQL 5.0 lets you insert an arbitrary value into an auto increment field, but this does alter the next value used for auto incrementing. And although you can alter the "next number" an auto increment field will use (with ALTER TABLE t AUTO_INCREMENT = number), this has to be greater than the maximum value currently in that column.

So, no, it doesn't appear you can achieve what you asked.

Answer 3

This is not possible. To have things behave the way you're suggesting would introduce a duplicate auto_increment value when the counter eventually does get up to that very high number you've inserted.

Answer 4

You can just give your row the id you like. For example, given the following table definition:

CREATE TABLE IF NOT EXISTS `stack-test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255),
  PRIMARY KEY  (`id`)
);

The following query will insert a row with a automatically generated id:

INSERT INTO `stack-test` (name) VALUES ('Peter');

And this query will insert a row with a user-defined id:

INSERT INTO `stack-test` (id, name) VALUES (5, 'Joe');

Verify by querying all rows:

SELECT * FROM `stack-test`;

Output:

+----+-------+
| id | name  |
+----+-------+
|  1 | peter |
|  5 | Joe   |
+----+-------+

Update: Just read, that you don't want to influence the AUTO_INCREMENT value. As staticsan mentioned, the AUTO_INCREMENT field automatically uses the highest value plus one. There is a statement which resets the counter to a certain value, but this only works, if there is no higher value present in the table:

ALTER TABLE `stack-test` AUTO_INCREMENT = 2;