Let's analyze the difference between replace into and replace in MySQL

This article brings you relevant knowledge about mysql, which mainly introduces the detailed explanation of the difference between replace into and replace in MySQL. The article introduces it in detail through sample code, which will be a certain reference for everyone's study or work. Value, hope it helps everyone.

Recommended learning: mysql video tutorial

This article is just a starting point. I have never paid attention to the difference between replace into and replace before. After testing in multiple scenarios, I can't find any essential difference between the two when inserting data? If anyone knows the details, please leave a message to let me know, I would be very grateful! ! !

0. Background of the story

[Table structure]

CREATE TABLE `xtp_algo_white_list` (
  `strategy_type` int DEFAULT NULL,
  `user_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `status` int DEFAULT NULL,
  `destroy_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `xtp_algo_white_list_UN` (`strategy_type`,`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin


# `strategy_type`,`user_name` 这两个是联合唯一索引，多关注后续需要用到！！！

##[Requirements:]

According to the data of account 209133002266 in the table, re-insert a user 20220302001 so that the strategy_type & status & destroy_at fields in the newly generated data are consistent with those of user 209133002266.
• Using update to update one by one is also possible, but it is slower.
• The effect of using replace into will be much higher, but in-depth research found that there are also some pitfalls

1. How to use replace into

replace into xtp_algo_white_list (`strategy_type`, `user_name`, `status`, `destroy_at`)
select strategy_type ,20220302001, status, destroy_at from xtp_algo_white_list xawl where xawl.user_name = 209133002266;

# replace into 后面跟表格+需要插入的所有字段名（自动递增字段不用写）
# select 后面选择的字段，如果根据查询结果取值，则写字段名；如果是写死的，则直接写具体值即可
# 可以理解为，第一部分是插入表格的结构，第二部分是你查询的数据结果

2. There is a unique way When indexing—replace into & and replace effects

step1: First execution of sql

replace into xtp_algo_white_list (`strategy_type`, `user_name`, `status`, `destroy_at`)
select strategy_type ,20220302001, status, destroy_at from xtp_algo_white_list xawl where xawl.user_name = 209133002266;

[After execution, the query results are as follows:]

#step2: The second execution of sql

Why the second time During execution, update 12 rows of data are displayed and created at data is updated, but update 6 rows are displayed for the first time? ? ?

1. Because when executing sql, replace into is actually executed in two steps. The first step is to transform the query data into new data. In the second step, if the new data already has the same content in the table, it will be deleted. If there is no identical content, new data is inserted directly.

2. Because when the above is executed for the first time, new data has been generated. The second time it will be deleted first, and then the latest data will be inserted. Finally, the update 12 rows will be displayed.

step3 : The situation of the third execution of sql

# 此时执行的是replace 

replace xtp_algo_white_list (`strategy_type`, `user_name`, `status`, `destroy_at`)
select strategy_type ,20220302001, status, destroy_at from xtp_algo_white_list xawl where xawl.user_name = 209133002266;

##The final situation observed is the same as that of the second execution of sql.

• When new data already exists, replace into and replace are the same.
• Subsequently delete all 20220302001, execute SQL once and twice, and find that the effects of replace into and replace are the same
• [Summary:] When there is a unique index restriction, if the newly added data will be restricted by the unique index, the data will only be inserted once. If it already exists, it will be deleted first and then inserted. At this time, replace into has the same effect as replace.

3. When there is no unique index—replace into and replace

We will delete the joint unique index of strategy_type & user_name and delete all data of the 20220302001 user. The final table structure is as follows:

CREATE TABLE `xtp_algo_white_list` (
  `strategy_type` int DEFAULT NULL,
  `user_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `status` int DEFAULT NULL,
  `destroy_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

1) The specific situation of the replace function

step1: Execute the following replace corresponding sql:

replace xtp_algo_white_list (`strategy_type`, `user_name`, `status`, `destroy_at`)
select strategy_type ,20220302001, status, destroy_at from xtp_algo_white_list xawl where xawl.user_name = 209133002266;

#step2: Execute replace again corresponding to sql:

Execute replace for the second time corresponding to sql, because there is no unique index restriction, the original data did not change. Six new pieces of data were regenerated.

• If the above sql is executed later, the data will continue to increase
• 2) The specific situation of the .replace into function

Before executing, clean the data , delete all the data of 20220302001

step1: execute the following replace into corresponding sql:

replace into xtp_algo_white_list (`strategy_type`, `user_name`, `status`, `destroy_at`)
select strategy_type ,20220302001, status, destroy_at from xtp_algo_white_list xawl where xawl.user_name = 209133002266;

step2：再次执行replace into 对应sql：

最终发现，没有唯一索引的时候，replace into 与replace 居然一摸一样的效果，都是继续增加数据。

通过以上分析，没看出replace into 与replace 具体有啥区别？？？？有谁知道呢？

4.replace的用法

• 单独replace的作用是替换字段中某数值的显示效果。可以数值中的部分替换、也可以全部替换。
• 如下表格，将user_name的字段，20220302改为"A_20220303"显示，并且新字段叫做new_name显示

select *, replace(user_name,20220302,'A_20220303') as "new_name" from xtp_algo_white_list where user_name = 20220302001;

推荐学习：mysql视频教程

The above is the detailed content of Let's analyze the difference between replace into and replace in MySQL. For more information, please follow other related articles on the PHP Chinese website!