Mysql replace And replace into Are often used functions ;replace Actually, it was done once update operation , Not first delete Again insert; and replace into In fact, with insert into Very much alike , But for the replace into, Suppose an old record in the table is used for PRIMARY KEY Or a UNIQUE New records of the index have the same value , Before a new record is inserted , Old records are deleted .
replace yes mysql Inside processing string more commonly used function , You can replace the contents of a string . Similar to the string processing trim Intercept operation , I don't want to talk about it here .
replace into The main function is similar to insert The insert . The main difference is replace It checks whether the data exists according to the primary key or unique index , If it exists, delete it first and update it .
Insert two records in the above way ,insert into It will give you an error ：1062 - Duplicate entry ‘a' for key ‘idx_name', Time: 0.001000s. Use replace into It will execute normally , It's just id Self growth 1.
ps: replace into in into Keywords can be omitted , It looks the same , It's a little different in use .
mysql replace usage
replace into table (id,name) values('1','aa'),('2','bb')
The function of this statement is to table table Insert two records . If the primary key id by 1 or 2 non-existent
insert into table (id,name) values('1','aa'),('2','bb')
If the same value exists, no data will be inserted
hold object It appears that search Replace all of them with replace
example ： Keep watch table Medium name In the field aa Replace with bb
update table set name=replace(name,'aa','bb')
REPLACE The operation of and INSERT Very much alike . If an old record in the table is used with a PRIMARY KEY Or a UNIQUE New records of the index have the same value , Before a new record is inserted , Old records are deleted .
Be careful , Unless the watch has a PRIMARY KEY or UNIQUE Indexes , otherwise , Use one REPLACE The sentence has no meaning . The sentence will be associated with INSERT identical , Because no index is used to determine whether new rows replicate other rows .
The values of all columns are taken from REPLACE INTO The value specified in the statement . All missing columns are set to their respective default values , This sum INSERT INTO equally . You cannot reference a value from the current row , You can't use values in new lines either . If you use one such as “SET col_name = col_name + 1” Assignment , Then the reference to the column name on the right is taken as DEFAULT(col_name) Handle . therefore , This assignment is equivalent to SET col_name = DEFAULT(col_name) + 1.
Use REPLACE INTO, Must have a watch INSERT and DELETE jurisdiction .
REPLACE Statement will return a number , To indicate the number of rows affected . This number is the sum of the number of rows deleted and inserted . If for a single line REPLACE The number is 1, Then a line is inserted , At the same time, no lines have been deleted . If the number is greater than 1, Before the new line is inserted , One or more old lines have been deleted . If the table contains multiple unique indexes , And the new row copies the values of different old rows in different unique indexes , It is possible that a single line replaces multiple old lines .
The number of rows affected can easily determine whether REPLACE Only one line has been added , Or Yes No REPLACE It also replaced other lines ： Check if the number is 1（ add to ） Or bigger （ Replace ）.
at present , You cannot be in a subquery , Change... To a table , Select... From the same table at the same time .
Here is a more detailed description of the algorithm used （ The algorithm is also used for LOAD DATA...REPLACE）：
1. Try inserting new rows into the table
2. When the insert fails due to a duplicate keyword error for the primary key or unique key ：
a. Key contains duplicate values from the table
b. Try inserting the new row into the table again
Three forms ：
1. replace into tbl_name(col_name, ...) values(...)
2. replace into tbl_name(col_name, ...) select ...
3. replace into tbl_name set col_name=value, ...
mysql There are three kinds of statements to insert data :
insert into Means insert data , The database checks the primary key , If there is a repetition, an error will be reported ;
replace into Indicates insert replacement data , What is in the demand table PrimaryKey, perhaps unique Indexes , If the database already has data , Replace with new data , If there's no data effect, it's the same as insert into equally ;
insert ignore Express , If the same record already exists in , Ignore the current new data ;
This is the end of the article today , If you have any questions, please leave a message in the comments section .
original text author ：CODETC
Last , If you want to be a programmer too , Want to master programming quickly , Join us now Learn from Penguin circle ！
There are senior professional software development engineers , Answer all your doubts online ~ Introduction to programming language “so easy”
Programming learning books ：
Programming learning video ：