Shulou(Shulou.com)05/31 Report--

This article shows you how to use replace and replace into in Mysql database. The content is concise and easy to understand. It will definitely make you shine. I hope you can gain something from the detailed introduction of this article.

Mysql replace and replace into are frequently used functions;replace is actually an update operation, rather than delete and insert; replace into is actually similar to insert into, but for replace into, if an old record in the table has the same value as a new record for PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted.

Replace is a commonly used function for dealing with strings in mysql, which can replace the contents of strings. Similar to the processing of strings, there is also the trim interception operation, which is not mentioned here.

replace into primarily acts like an insert operation. The main difference is that replace checks whether the data exists based on the primary key or unique index, and if it exists, deletes the update first.

Examples:

#Table Structure: CREATE TABLE `t_test` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(1) NOT NULL DEFAULT '', PRIMARY KEY (`id`), UNIQUE KEY `idx_name` (`name`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Insert the first record

INSERT INTO t_test (`name`)VALUES('a')#OR REPLACE INTO t_test (`name`)VALUES('a')

Insert into: 1062 - Duplicate entry 'a' for key 'idx_name', Time: 0.001000s. Using replace into will execute normally, but the id is incremented by 1.

ps: replace into keyword can be omitted, looks the same, use a little different

replace(object,search,replace)

Replace all search entries in object with replace

select replace('www.jb51.net','w','n') from ... ---> nnn.jb51.net

Example: Replace detail in the name field of table with description

update table set name=replace(name,'detail','description')

Replace into

Equivalent to: if not exists (select 1 from t where id = 1) insert into t(id, update_time) values(1, getdate()) else update t set update_time = getdate() where id = 1;

REPLACE works much like INSERT. If an old record in the table has the same value as a new record for PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted.

Note that unless the table has a PRIMARY KEY or UNIQUE index, it makes no sense to use a REPLACE statement. This statement would be the same as INSERT because no index is used to determine whether a new row copies another row.

All column values are taken from the values specified in the REPLACE INTO statement. All missing columns are set to their default values, just like INSERT INTO. You cannot reference values from the current row, nor can you use values in new rows. If you use an assignment such as "SET col_name = col_name + 1," references to column names on the right are treated as DEFAULT(col_name). Therefore, this assignment is equivalent to SET col_name = DEFAULT(col_name) + 1.

To use REPLACE INTO, you must have INSERT and Delete permissions on the table.

The REPLACE statement returns a number indicating the number of affected rows. This number is the sum of the deleted and inserted rows. If the number is 1 for a single row REPLACE, one row is inserted and no rows are deleted. If the number is greater than 1, one or more old rows are deleted before new rows are inserted. If the table contains multiple unique indexes, and the new row replicates the values of different old rows in different unique indexes, it is possible that a single row replaces multiple old rows.

The number of rows affected makes it easy to determine if REPLACE adds only one row, or if REPLACE replaces other rows as well: check if the number is 1 (add) or greater (replace).

Currently, you cannot switch to a table and select from the same table in a subquery.

The following is a more detailed description of the algorithm used (this algorithm is also used for LOAD DATA... REPLACE）：

1. Try inserting a new row into the table

2. When an insert fails because of duplicate keyword errors for primary or unique keys:

a. Remove conflicting rows with duplicate key values from a 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:

There are three common statements for inserting data in mysql:

insert into means to insert data, the database will check the primary key, if there is duplication, an error will be reported;

replace into means to insert replacement data. There is a PrimaryKey or unique index in the requirement table. If there is already data in the database, replace it with new data. If there is no data, the effect is the same as insert into.

insert ignore means that if the same record already exists in, the current new data is ignored;

How do you use it in Mysql database? Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.