Clever Geek Handbook

Replace the id setting in mysql with

I am using mysql REPLACE INTO syntax for my site. below is my table of test tables,

id  name  address
1   Tom   US
2   Dick  UK
3   Harry US
4   Jony  Spain

Query:

REPLACE INTO table_name VALUES (3, 'Roni', 'India')

now this mysql query is executed saying

2 lines are affected. Entered row id: 4 (request took 0.0003 sec)

Can anyone explain to me what the above text means? Whay 2 lines affected ?

Hello

+3


source to share


4 answers


Two lines are affected because REPLACE INTO will DELETE and then INSERT on key collision, so you end up with two operations, first DELETE (1 row affected) and then INSERT (second row affected).



You may want to consider other solutions as DELETE is expensive as the indexes need to be updated. In many cases, an INSERT ... query on a DUPLICATE KEY UPDATE will be faster.

+2


source


MySQL REPLACE as UPDATE statement like this:



REPLACE INTO table_name SET column_name1 = value1 AND column2 = value2
+1


source


Use the query like this:

EXPLAIN REPLACE INTO table_name VALUES (3, 'Roni', 'India')

Check out the addition of the "EXPLAIN" keyword! This is how MySQL explains what's going on.

However, note that REPLACE INTO is the same as INSERT INTO, but it will replace the string if it exists and insert it if not. So when exists it will delete the row and then insert a new one and why you have two affected rows.

0


source


In your case, it looks like id, not 3, and then reinserted again. This is why he says 2 entries are affected. Your table may have row 3, Roni and India inserted and deleted row 3, Harry, US

0


source






More articles:


All Articles