List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:April 4 1999 10:39pm
Subject:auto_increment
View as plain text  
>>>>> "list" == list  <list@stripped> writes:

list> This has probably been asked before, but is it possible to not make a
list> REPLACE use a new value for an auto_increment field when that field is not
list> supplied in the query.. so:

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

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

list> results in the following table:

list> +----+------+
list> | id | name |
list> +----+------+
list> | 0  | test |
list> +----+------+

list> when doing another replace:

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

list> the result is:

list> +----+------+
list> | id | name |
list> +----+------+
list> | 1  | test |
list> +----+------+

list> and when I got

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

list> and do a 

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

list> I end up with

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

list> ...

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

Hi!

The SQL standard doesn't have AUTO_INCREMENT or REPLACE INTO...

I agree that it would be nicer if REPLACE used an old auto_increment
value in this case;  I will put this on my TODO...

Regards,
Monty
Thread
auto_incrementlist5 Apr
  • auto_incrementMichael Widenius5 Apr