At 23:56 +0200 6/16/02, Elizabeth Mattijsen wrote:
>$ mysql -V
>mysql Ver 11.17 Distrib 3.23.49, for pc-linux-gnu (i686)
>
>Situation: a table with two UNIQUE fields, one of them with AUTO_INCREMENT.
>
>Using REPLACE with a NULL value on the AUTO_INCREMENT field twice,
>with an identical value for the other UNIQUE field, causes the auto
>increment to be executed even though there already _is_ a record
>when the REPLACE is executed the second time.
>
>I would expect the second replace to not do an update of the
>AUTO_INCREMENT field because there is already a record available.
That's not how it works. You're replacing based on the name value, and
updating the other column. Updating an AUTO_INCREMENT column with NULL
causes the next sequence number to be generated and assigned to the column.
>
>Please check the following statements and their output.
>========================================================================
>mysql> CREATE TABLE _test (
> id INT UNSIGNED AUTO_INCREMENT,
> name VARCHAR(255),
> UNIQUE id (id),
> UNIQUE name (name)
>);
>Query OK, 0 rows affected (0.01 sec)
>
>mysql> DESCRIBE _test;
>+-------+------------------+------+-----+---------+----------------+
>| Field | Type | Null | Key | Default | Extra |
>+-------+------------------+------+-----+---------+----------------+
>| id | int(10) unsigned | | PRI | NULL | auto_increment |
>| name | varchar(255) | YES | MUL | NULL | |
>+-------+------------------+------+-----+---------+----------------+
>2 rows in set (0.00 sec)
>
>mysql> REPLACE INTO _test VALUES (NULL,'name');
>Query OK, 1 row affected (0.00 sec)
>
>mysql> SELECT * FROM _test;
>+----+------+
>| id | name |
>+----+------+
>| 1 | name |
>+----+------+
>1 row in set (0.01 sec)
>
>mysql> REPLACE INTO _test VALUES (NULL,'name');
>Query OK, 2 rows affected (0.00 sec)
>
>mysql> SELECT * FROM _test;
>+----+------+
>| id | name |
>+----+------+
>| 2 | name |
>+----+------+
>1 row in set (0.00 sec)
>========================================================================
>
>Is this behaviour correct? I would expect the result to be "1,name"
>instead of "2,name".
>
>I was hoping to not have to do a SELECT to see if there is already a
>record with the second UNIQUE field. It seems that this is needed
>after all and that REPLACE is not very useful in this situation.
>
>Elizabeth Mattijsen
