List:General Discussion« Previous MessageNext Message »
From:list Date:April 4 1999 11:22pm
Subject:auto_increment
View as plain text  
This has probably been asked before, but is it possible to not make a
REPLACE use a new value for an auto_increment field when that field is not
supplied in the query.. so:

CREATE TABLE test (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(32) NOT NULL PRIMARY KEY,
    KEY (id)
);

REPLACE INTO test (NAME) VALUES ('test');

results in the following table:

+----+------+
| id | name |
+----+------+
| 0  | test |
+----+------+

when doing another replace:

REPLACE INTO test (NAME) VALUES ('test');

the result is:

+----+------+
| id | name |
+----+------+
| 1  | test |
+----+------+

and when I got

+----+------+
| id | name |
+----+------+
| 1  | test |
| 2  | abcd |
+----+------+

and do a 

REPLACE INTO test (NAME) VALUES ('test');

I end up with

+----+------+
| id | name |
+----+------+
| 3  | test |
| 2  | abcd |
+----+------+

...

But this is probably according to SQL standards, but due to this, I am
required to do an select + insert/update instead of only 1 replace, which
probable is slower.

Thomas
-- 
Thread
auto_incrementlist5 Apr
  • auto_incrementMichael Widenius5 Apr