MySQL replace into behavior with unique constraint

226
July 03, 2017, at 8:20 PM

I have a quick question about MySQL behavior.

Imagine a table with 3(relevant) columns:

id (PK + AI),somedate,someuser,etc...

I have put a unique constraint on (date,user). So when I start with a clean test table and run the following query twice:

REPLACE INTO `testtable` (somedate,someuser) VALUES('2017-01-01','admin');

I expected a row with the 'id' column on 1. but instead everytime I run this query the id goes up because of the auto increment and I can't have that happen (this would corrupt my data relations). Why is this? Can I make it so that I can keep the original primary key when a replace into occurs?

Answer 1

Not with the REPLACE. That's like an INSERT preceded by a DELETE. The behavior you observe with REPLACE is the same as the behavior you would see if you executed these two statements:

 DELETE FROM `testtable` WHERE somedate = '2017-01-01' and someuser = 'admin';
 INSERT INTO `testtable` (somedate,someuser) VALUES ('2017-01-01','admin');

And that means the auto_increment column on the newly inserted row will have a new value.

Perhaps consider using INSERT ... ON DUPLICATE KEY UPDATE.

Reference: https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

(Note that the attempt to insert a row that gets updated will use an auto_increment value.)

Answer 2

To me looks like you actually wanted an UPDATE statement rather like

update `testtable` 
set somedate = '2017-01-01',
    someuser = 'admin'
where id = <id of the record> ;
READ ALSO
Set default value to number column in mysql

Set default value to number column in mysql

Trying to set default value of 10 digits to number column, type is INTGives error saying #1067 Invalid default value for number

256
All POST data stored under form data in Chrome Dev Tools?

All POST data stored under form data in Chrome Dev Tools?

When we record the network tab in Chrome Developer Tools, and logging in to our site we found that the POST data are stored in plain-text under form dataWhen we use enctype="multipart/form-data" in the form tag, we won't get the Form Data list, but we can still see the data inside...

275
Cakephp 3 - cache query with limit

Cakephp 3 - cache query with limit

I have a list of topics, 15 by default, and a load more button which load 15 more topics with an ajax requestIt takes too much time for initial query to get first 15 topics so I cached the query, which looks like this:

192
Wordpress caching SQL queries

Wordpress caching SQL queries

There are a lot of sql queries in my wordpress theme, for example, 20 sql query in last 6 added postsSo, normally there are minimum 100-150 query in my website and my website is loading late because of this

236