Replace and replace1__mysql of mysql

Replace and replaceinto bitsCN.com of mysql

1. batch replace of mysql

Update candidate set education = replace (education, 'core', 'learn') where education like '% section % ';

Replace all "subjects" in the data with "learning ".

2. replace into of mysql

SQL = "replace into score (quest_id, jdg_id, cd_id, score) values ('" + questionId + "', '" + judgesId + "', '"+ candidateId +"', '"+ score + "')";

Reprinted http://www.cnblogs.com/wangtao_20/archive/2011/02/03/1948959.html for the following

Id is the primary key
Test Method 1: insert the same index value:
Replace into fanwe_order (id, sn) VALUES ('33', 'Test replace into use') result: the number of affected rows:

2 SELECT * FROM fanwe_order WHERE sn = 'Test replace into use'
Result: a row of records is found.


Test Method 2: the primary key value is inserted repeatedly:

Specify the insert id as 34. In this way, it does not overlap with the id in the data table and then runs the query

Replace into fanwe_order (id, sn) VALUES ('34', 'Test replace into use ')

Result: no new data is inserted. The original line is replaced. Id changed from 33 to 34


Cause analysis:
As mentioned in the manual, if an old record in the table has the same value as a new record used for the primary key or a UNIQUE index.

Meaning, unless the table has a primary key or UNIQUE index, it makes no sense to use a REPLACE statement.
In the test example just now. Id is the primary key and sn is the unique index. The test method is the same as the primary key value, and the test method is the unique cable.

The value is the same. Replace occurs in both cases.



Understanding: when inserting data, if the primary key value or unique index key value is the same. Replace

And delete the original one. Replace with the currently inserted row (so you must have both the insert and delete permissions ).

)

Delete and insert a new one. It exactly shows what I see: it shows that the number of affected rows is 2

In either case, replace is used only when the replace statement is used. 1. the primary key value is the same. 2. the index key value is the same.

BitsCN.com