MySQL REPLACE in an auto incremented row

Question

Let say I have a MySQL table which contains three columns: id, a and b and the column named id is an AUTO INCREMENT field. If I pass a query like the following to MySQL, it will works fine:

REPLACE INTO `table` (`id`, `a`, `b`) VALUES (1, 'A', 'B')

But if I skip the field id it will no longer works, which is expected.

I want to know if there is a way to ignore some fields in the REPLACE query. So the above query could be something like this:

REPLACE INTO `table` (`a`, `b`) VALUES ('A', 'B')

Why do I need such a thing?

Sometimes I need to check a database with a SELECT query to see if a row exists or not. If it is exists then I need to UPDATE the existing row, otherwise I need to INSERT a new row. I'm wondering if I could achieve a similar result (but not same) with a single REPLACE query.

Why it couldn't be the same result? Simply because REPLACE will DELETE the existing row and will INSERT a new row, which will lose the current primary key and will increase the auto incremented values. In contrast, in an UPDATE query, primary key and the AI fields will be untouched.

MySQL REPLACE.

I can't understand your question. What actually you wanna do? — Fathah Rehman P, Mar 05 '13 at 08:13
@Mahdi- Please provide table data before REPLACE query and your expected output after REPLACE query. I'll try to give you the solution — Fathah Rehman P, Mar 05 '13 at 08:58
1) share proper table schema. 2) That's not how you're supposed to use replace. 3) use replace only when you know `primary key` values. — जलजनक, Mar 05 '13 at 09:05
@Mahdi -i'm still not clear about your question. Whatever data is in the table it should be replaced by ('A','B') right? — Fathah Rehman P, Mar 05 '13 at 09:23
@FathahRehmanP The row which contains `1,A,A` should be replaced by `2,A,B`. I think it's not that confusing, is it? — Mahdi, Mar 05 '13 at 09:29
@Mahdi- Just delete all contents of the table then use insert query. — Fathah Rehman P, Mar 05 '13 at 09:39
@FathahRehmanP Oh, man, come on ... Why I should delete all the data in a table to insert a new row??? — Mahdi, Mar 05 '13 at 09:40
@Mahdi - suppose your table contain three rows before REPLACE (1, A, A) (2, A, B) (3, A, C) Then you used REPLACE INTO `table` (`a`,`b`) VALUES ('A','B') query . what is your expected result? — Fathah Rehman P, Mar 05 '13 at 09:43
See answer at http://stackoverflow.com/questions/4901769/reuse-rows-in-mysql-table-without-auto-incrementing?rq=1 — fatal_error, Feb 03 '15 at 21:37

score 10 · Accepted Answer · answered Mar 05 '13 at 09:07

10

That's not how you're supposed to use replace.
use replace only when you know primary key values.

Manual:

Note that unless the table has a PRIMARY KEY or UNIQUE index, using a REPLACE statement makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.

answered Mar 05 '13 at 09:07

जलजनक

3,072
2
24
30

What if we have a unique index, say column `A`, and I want to do `REPLACE INTO `table` (`a`, `b`) VALUES ('A', 'B')`? Is this a good use case? – kevin Oct 09 '18 at 03:31

score 3 · Answer 2 · edited May 23 '17 at 10:09

What if you have multiple rows that match the fields?

Consider adding a key that you can match on and use INSERT IGNORE.. ON DUPLICATE KEY UPDATE. The way INSERT IGNORE works is slightly different from REPLACE.

INSERT IGNORE is very fast but can have some invisible side effects.

INSERT... ON DUPLICATE KEY UPDATE

Which has fewer side effects but is probably much slower, especially for MyISAM, heavy write loads, or heavily indexed tables.

For more details on the side effects, see: https://stackoverflow.com/a/548570/1301627

Using INSERT IGNORE seems to work well for very fast lookup MyISAM tables with few columns (maybe just a VARCHAR field).

For example,

create table cities (
    city_id int not null auto_increment,
    city varchar(200) not null,
    primary key (city_id),
    unique key city (city))
    engine=myisam default charset=utf8;

insert ignore into cities (city) values ("Los Angeles");

In this case, repeatedly re-inserting "Los Angeles" will not result in any actual changes to the table at all and will prevent a new auto_increment ID from being generated, which can help prevent ID field exhaustion (using up all the available auto_increment range on heavily churned tables).

For even more speed, use a small hash like spooky hash before inserting and use that for a separate unique key column and then the varchar won't get indexed at all.

Also, note: if an insert is ignored, the value from `last_insert_id()` is undefined. Thus, instead of using that, you'll have to do `select city_id from cities where city="Los Angeles"` after your `insert ignore` to make sure you get a good ID value (if you need it). Even so, this will still be much faster versus to `ON DUPLICATE KEY UPDATE` since (almost always) writes are slower than reads. (On Innodb, use a transaction if desired.) — fatal_error, Feb 03 '15 at 22:23
If you're going to downvote, please add a comment explaining why you feel this is an invalid point. — fatal_error, Mar 09 '15 at 18:42

MySQL REPLACE in an auto incremented row

2 Answers2

Linked