mysql的窗口函数，substring()函数，Locate()函数，Replace（）函数

记录贴，记录一下mysql窗口函数语法。

Locate（）

语法1:①.LOCATE(字符串1,字符串2)。

返回字符串1在字符串2中第一次出现的位置，只要字符串2中包含字符串1，那么返回值必然大于0。

语法2:②.LOCATE(字符串1,字符串2,pos)

返回字符串1在字符串2中第一次出现的位置，从位置pos开始算起；

如果返回0，表示从pos位置开始之后没有了

如果返回值大于0，表示有

出处：MySQL中LOCATE()函数的详解_locate函数-优快云博客

mysql substring()函数：截取字符串

SUBSTRING 是字符串处理函数，语法1为“SUBSTRING(字符串,n)。

substring()函数可以从字符串从特定位置开始截取一个给定长度的子字符串并返回。

语法2：SUBSTRING(s，n，len)。

从字符串 s 返回一个长度同 len 字符相同的子字符串，起始于位置 n。

n参数是一个整数，用于指定子串的起始字符，n可以是负值。

如果n为正值，则SUBSTRING函数从字符串的开始处提取子字符串。请参阅以下字符串。

如果n为负值，则子字符串的位置起始于字符串结尾的第 n 个字符，即倒数第 n 个字符，而不是字符串的开头位置。
len参数可省略，指定子串长度
请注意，如果n参数为零，则SUBSTRING函数返回一个空字符串：

substring()函数使用示例

1、len参数省略

从“MySQL SUBSTRING”字符串中获取子字符串：“SUBSTRING”，子串的位置必须从7开始

mysql> SELECT SUBSTRING('MYSQL SUBSTRING', 7);

请注意，如果n参数为零，则SUBSTRING函数返回一个空字符串：

mysql> SELECT SUBSTRING('MYSQL SUBSTRING', 0);

mysql> SELECT SUBSTRING('MySQL SUBSTRING', -10);

2、len参数不省略

mysql> SELECT SUBSTRING('MySQL SUBSTRING',1,5);

原文出处：mysql substring()函数如何用 • Worktile社区

mysql replace()函数

REPLACE语法

REPLACE(String,from_str,to_str)

即：将String中所有出现的from_str替换为to_str，这里的from_str不支持正则匹配。

REPLACE语法
REPLACE(String,from_str,to_str)

即：将String中所有出现的from_str替换为to_str，这里的from_str不支持正则匹配。

操作实例
测试表数据如下：

mysql> select * from `LOL`;
+----+----------------+--------------+-------+
| id | hero_title     | hero_name    | price |
+----+----------------+--------------+-------+
|  1 | 刀锋之影       | 泰隆         |  6300 |
|  2 | 迅捷斥候       | 提莫         |  6300 |
|  3 | 光辉女郎AA     | 拉克丝       |  1350 |
|  4 | 发条A魔灵A     | 奥莉安娜     |  6300 |
|  5 | 至高之拳       | 李青         |  6300 |
|  6 | 无极剑圣       | 易           |   450 |
|  7 | 疾风剑豪       | 亚索         |  6300 |
+----+----------------+--------------+-------+
7 rows in set (0.00 sec)

使用场景1 – (Query语句)：
需求：把"hero_title"字段中所有的"之"换成" - "来展示，SQL如下：

mysql> SELECT REPLACE(hero_title,'之',' - ') as repl_title,hero_name,price from `LOL`;
+----------------+--------------+-------+
| repl_title     | hero_name    | price |
+----------------+--------------+-------+
| 刀锋 - 影      | 泰隆         |  6300 |
| 迅捷斥候       | 提莫         |  6300 |
| 光辉女郎AA     | 拉克丝       |  1350 |
| 发条A魔灵A     | 奥莉安娜     |  6300 |
| 至高 - 拳      | 李青         |  6300 |
| 无极剑圣       | 易           |   450 |
| 疾风剑豪       | 亚索         |  6300 |
+----------------+--------------+-------+
7 rows in set (0.00 sec)

使用场景2 – (update语句)：

需求：把"hero_title"字段中所有的"A"去掉，SQL如下：

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

-- 去掉后在查询：
+----+--------------+--------------+-------+
| id | hero_title   | hero_name    | price |
+----+--------------+--------------+-------+
|  1 | 刀锋之影     | 泰隆         |  6300 |
|  2 | 迅捷斥候     | 提莫         |  6300 |
|  3 | 光辉女郎     | 拉克丝       |  1350 |
|  4 | 发条魔灵     | 奥莉安娜     |  6300 |
|  5 | 至高之拳     | 李青         |  6300 |
|  6 | 无极剑圣     | 易           |   450 |
|  7 | 疾风剑豪     | 亚索         |  6300 |
+----+--------------+--------------+-------+
7 rows in set (0.00 sec)

使用场景3 – (插入或替换：REPLACE INTO)
如果我们想插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录。

需求场景：
这张表存的每个客户最近一次交易订单信息，要求保证单个用户数据不重复录入，且执行效率最高，与数据库交互最少，支撑数据库的高可用。
此时，可以使用"REPLACE INTO"语句，这样就不必先查询，再决定是否先删除再插入。

"REPLACE INTO"语句是基于唯一索引或主键来判断唯一（是否存在）的。
"REPLACE INTO"语句是基于唯一索引或主键来判断唯一（是否存在）的。
"REPLACE INTO"语句是基于唯一索引或主键来判断唯一（是否存在）的。

注意事项：如下SQL所示，需要在username字段上建立唯一索引（Unique），transId设置自增即可。

-- 20点充值
REPLACE INTO last_transaction (transId,username,amount,trans_time,remark) 
   VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', '会员充值');
 
-- 21点买皮肤
REPLACE INTO last_transaction (transId,username,amount,trans_time,remark) 
   VALUES (null, 'chenhaha', 100, '2020-06-11 21:00:00', '购买盲僧至高之拳皮肤');

若username='chenhaha’的记录不存在，REPLACE语句将插入新记录（首次充值），否则，当前username='chenhaha’的记录将被删除，然后再插入新记录。

id不要给具体值，不然会影响SQL执行，业务有特殊需求除外。

原文路径：MySQL：replace函数的几种实用场景_mysql replace-优快云博客