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.

Answer 1

• 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.

Answer 2

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.