Several practical scenarios for implementing the replace function in MySQL

REPLACE Syntax

REPLACE(String,from_str,to_str)

That is, replace all occurrences of from_str in String with to_str. Here, from_str does not support regular expression matching.

Operation Example

The test table data is as follows:

mysql> select * from `LOL`;
+----+----------------+--------------+-------+
| id | hero_title | hero_name | price |
+----+----------------+--------------+-------+
| 1 | Blade Shadow | Talon | 6300 |
| 2 | Swift Scout | Teemo | 6300 |
| 3 | Radiant Lady AA | Lux | 1350 |
| 4 | Clockwork A, Summon A | Orianna | 6300 |
| 5 | Supreme Fist | Lee Sin | 6300 |
| 6 | Wuji Sword Master | Easy | 450 |
| 7 | Swift Blade Master | Yasuo | 6300 |
+----+----------------+--------------+-------+
7 rows in set (0.00 sec)

Usage scenario 1 – (Query statement):

Requirement: Replace all "之" in the "hero_title" field with " - " to display. The SQL is as follows:

SELECT REPLACE(hero_title,'之',' - ')as repl_title,hero_name,price from LOL;
mysql> SELECT REPLACE(hero_title,'之',' - ') as repl_title,hero_name,price from `LOL`;
+----------------+--------------+-------+
| repl_title | hero_name | price |
+----------------+--------------+-------+
| Blade - Shadow | Talon | 6300 |
| Swift Scout | Teemo | 6300 |
| Radiant Lady AA | Lux | 1350 |
| Clockwork A, Genie A | Orianna | 6300 |
| Supreme - Fist | Lee Sin | 6300 |
| Wuji Sword Master | Easy | 450 |
| Swift Blade Master | Yasuo | 6300 |
+----------------+--------------+-------+
7 rows in set (0.00 sec)

Usage scenario 2 – (update statement):

Requirement: Remove all "A" in the "hero_title" field. The SQL is as follows:

UPDATE LOL SET hero_title=REPLACE(hero_title,'A','');

mysql> UPDATE `LOL` SET hero_title=REPLACE(hero_title,'A','');
Query OK, 2 rows affected (0.05 sec)
Rows matched: 7 Changed: 2 Warnings: 0

-- Remove and query:
+----+--------------+--------------+-------+
| id | hero_title | hero_name | price |
+----+--------------+--------------+-------+
| 1 | Blade Shadow | Talon | 6300 |
| 2 | Swift Scout | Teemo | 6300 |
| 3 | Radiant Lady | Lux | 1350 |
| 4 | Clockwork Genie | Orianna | 6300 |
| 5 | Supreme Fist | Lee Sin | 6300 |
| 6 | Wuji Sword Master | Easy | 450 |
| 7 | Swift Blade Master | Yasuo | 6300 |
+----+--------------+--------------+-------+
7 rows in set (0.00 sec)

Use scenario 3 – (Replace or insert: REPLACE INTO)

If we want to insert a new record (INSERT), but if the record already exists, delete the original record first and then insert the new record.

Demand scenario:

This table stores the most recent transaction order information for each customer. It is required to ensure that the data of a single user is not entered repeatedly, and to have the highest execution efficiency, the least interaction with the database, and support high availability of the database.
At this time, you can use the "REPLACE INTO" statement, so you don't have to query first and then decide whether to delete and then insert.

• The "REPLACE INTO" statement determines whether a uniqueness (existence) is based on a unique index or primary key.
• The "REPLACE INTO" statement determines whether a uniqueness (existence) is based on a unique index or primary key.
• The "REPLACE INTO" statement determines whether a uniqueness (existence) is based on a unique index or primary key.

Note: As shown in the following SQL, you need to create a unique index (Unique) on the username field and set the transId to auto-increment.

-- 20 points recharge REPLACE INTO last_transaction (transId,username,amount,trans_time,remark) 
  VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', 'Member Recharge');
 
-- Buy skin at 21 o'clock REPLACE INTO last_transaction (transId, username, amount, trans_time, remark) 
  VALUES (null, 'chenhaha', 100, '2020-06-11 21:00:00', 'Purchase Lee Sin Supreme Fist skin');

If the record with username='chenhaha' does not exist, the REPLACE statement will insert a new record (first recharge), otherwise, the current record with username='chenhaha' will be deleted and then the new record will be inserted.

Do not give a specific value for id, otherwise it will affect SQL execution, unless there are special business requirements.

This concludes this article about several practical scenarios for implementing the replace function in MySQL. For more relevant content on practical scenarios of MySQL replace, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!