编程知识 cdmana.com

Difference and usage of replace and replace into in MySQL (dry goods sharing)

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 .


 

Example :


 

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

1.replace into 

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  

Equivalent to  

insert into table (id,name) values('1','aa'),('2','bb') 

If the same value exists, no data will be inserted

2.replace(object,search,replace) 

hold object It appears that search Replace all of them with replace 

select replace('www.jb51.net','w','Ww')--->WwWwWw.jb51.net 

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

PS:

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 ;

summary

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 :


 

版权声明
本文为[Mu Ge AI programming]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201224170110306m.html

Scroll to Top