[05.21]MYSQL中正则表达式匹配这种格式的日期
发布网友
发布时间：2022-05-08 00:11
共4个回答
懂视网
时间：2022-05-08 04:33
对于一般不涉及到集合的字串拆分合并运算，各数据库的SQL都有较丰富的支持，甚至包括正则表达式等复杂分析功能。当然，这些功能集算器也都有，作为动态语言，集算器还提供将字串作为表达式计算的功能。
在SQL中比较麻烦的字串处理主要和分组逆运算相关。字串由分隔符分成若干段，需要拆开形成多条记录或构成集合进一步运算，SQL没有显式集合，这类操作就很困难。
相对来讲，分组汇总时将字段值合并成一个串要容易一些，MySQL有group_concat函数完成这一功能，其它数据库一般也有形式略复杂的相关函数。
先看简单的拼串任务。设有学生表结构为：班级、姓名、性别；现要按班级分组后将男生和女生分别列成逗号分隔的串，串成员按姓名排序。
| A |
1 | =db.query("select * from 学生表") |
2 | =A1.group(班级; ~.select(性别==’男’).(姓名).sort().string():男生,~.select(性别==’女’).(姓名).sort().string():女生) |
集算器有集合数据，无需专门针对分组的拼串函数，可以随意组合出各种运算。
常规的拆串将伴随生成多记录，如上例的逆运算，要将结构为：班级、男生、女生的班级表再计算成结构为班级、姓名、性别的学生表：
| A |
1 | =db.query("select * from 班级表") |
2 | =create(班级,姓名,性别) |
3 | >A1.run(A2.insert(0:男生.array(),A1.班级,~,”男”),A2.insert(0:女生.array(),A1.班级,~,”女”)) |
将串拆成集合后再据此生成记录就可以，不需要象SQL那样要用递归查询或与基准表JOIN的复杂方案。
有时拆串的目标是为了做集合运算。设有图书表结构为：书名、作者；其中作者是由逗号分隔的多人。现要找出同一组作者写过两本或以上书的数据，作者串中人名次序不确定。
| A |
1 | =db.query("select * from 图书表") |
2 | =A1.group(作者.array().sort()).select(~.len()>1).conj() |
拆串成集合后排序，就可以用来作为分组键值了，剩下都是常规动作了。
日期
与字串类似，数据库对常规的单值日期处理能力也没问题，但同样在碰到日期拆分或日期序列生成时就会比较麻烦。其实根本上的原因还是SQL的集合化不够彻底。
设有旅行记录表结构为：人员、开始日、结束日、…；现在要统计这批记录中哪5天正在旅行的人数最多。
这个问题需要将开始日和结束日之间的日期拆成单个的日期集合再分组统计即事。
| A |
1 | =db.query("select 开始日,结束日 from 旅行表") |
2 | =A1.conj(periods(开始日,结束日)).groups(~:日期,count(1):人次) |
3 | =A2.sort(人次:-1).to(5) |
集算器可以基于集合数据类型提供日期拆分方法，完成这个问题就很轻松。
日期序列生成的复杂度则主要由日期本身特点造成，在伴随逆分组运算时就更麻烦。
设有事件发生表T结构为：I（事件）、S（开始日期）、E（结束日期）；现在要将两个日期之间的时段按月拆开多条，头月、尾月份分别以S、E为开始日和结束日，中间的月份是完整月。
| A |
1 | =db.query("select I,S,E from T") |
2 | =A1.news(interval@m(a=S,b=E)+1;I, max(a,pdate@m(after@m(a,~-1))):S,min(b,pdate@me(after@m(a,~-1))):E) |
pdate@m和@me可分别计算出每月的头尾日期。after@m将生成某日期几个月后的日期，这个函数会自动针对月末，在生成月区间时非常有用。
热心网友
时间：2022-05-08 01:41
热心网友
时间：2022-05-08 02:59
MySQL 一直以来都支持正则匹配，不过对于正则替换则一直到MySQL 8.0 才支持。对于这类场景，以前要么在MySQL端处理，要么把数据拿出来在应用端处理。
比如我想把表y1的列str1的出现第3个action的子 串替换成dble，怎么实现？
1. 自己写SQL层的存储函数。代码如下写死了3个，没有优化，仅仅作为演示，MySQL 里非常不建议写这样的函数。
mysql
DELIMITER $$
USE `ytt`$$
DROP FUNCTION IF EXISTS `func_instr_simple_ytt`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `func_instr_simple_ytt`(
f_str VARCHAR(1000), -- Parameter 1
f_substr VARCHAR(100), -- Parameter 2
f_replace_str varchar(100),
f_times int -- times counter.only support 3.
) RETURNS varchar(1000)
BEGIN
declare v_result varchar(1000) default 'ytt'; -- result.
declare v_substr_len int default 0; -- search string length.
set f_times = 3; -- only support 3.
set v_substr_len = length(f_substr);
select instr(f_str,f_substr) into @p1; -- First real position .
select instr(substr(f_str,@p1+v_substr_len),f_substr) into @p2; Secondary virtual position.
select instr(substr(f_str,@p2+ @p1 +2*v_substr_len - 1),f_substr) into @p3; -- Third virtual position.
if @p1 > 0 && @p2 > 0 && @p3 > 0 then -- Fine.
select
concat(substr(f_str,1,@p1 + @p2 + @p3 + (f_times - 1) * v_substr_len - f_times)
,f_replace_str,
substr(f_str,@p1 + @p2 + @p3 + f_times * v_substr_len-2)) into v_result;
else
set v_result = f_str; -- Never changed.
end if;
-- Purge all session variables.
set @p1 = null;
set @p2 = null;
set @p3 = null;
return v_result;
end;
$$
DELIMITER ;
-- 调用函数来更新：
mysql> update y1 set str1 = func_instr_simple_ytt(str1,'action','dble',3);
Query OK, 20 rows affected (0.12 sec)
Rows matched: 20 Changed: 20 Warnings: 0
2. 导出来用sed之类的工具替换掉在导入，步骤如下：（推荐使用）
1）导出表y1的记录。
mysqlmysql> select * from y1 into outfile '/var/lib/mysql-files/y1.csv';Query OK, 20 rows affected (0.00 sec)
2）用sed替换导出来的数据。
shellroot@ytt-Aspire-V5-471G:/var/lib/mysql-files# sed -i 's/action/dble/3' y1.csv
3）再次导入处理好的数据，完成。
mysql
mysql> truncate y1;
Query OK, 0 rows affected (0.99 sec)
mysql> load data infile '/var/lib/mysql-files/y1.csv' into table y1;
Query OK, 20 rows affected (0.14 sec)
Records: 20 Deleted: 0 Skipped: 0 Warnings: 0
以上两种还是推荐导出来处理好了再重新导入，性能来的高些，而且还不用自己费劲写函数代码。
那MySQL 8.0 对于以上的场景实现就非常简单了，一个函数就搞定了。
mysqlmysql> update y1 set str1 = regexp_replace(str1,'action','dble',1,3) ;Query OK, 20 rows affected (0.13 sec)Rows matched: 20 Changed: 20 Warnings: 0
还有一个regexp_instr 也非常有用，特别是这种特指出现第几次的场景。比如定义 SESSION 变量@a。
mysqlmysql> set @a = 'aa bb cc ee fi lucy 1 1 1 b s 2 3 4 5 2 3 5 561 19 10 10 20 30 10 40';Query OK, 0 rows affected (0.04 sec)
拿到至少两次的数字出现的第二次子串的位置。
mysqlmysql> select regexp_instr(@a,'[:digit:]{2,}',1,2);+--------------------------------------+| regexp_instr(@a,'[:digit:]{2,}',1,2) |+--------------------------------------+| 50 |+--------------------------------------+1 row in set (0.00 sec)
那我们在看看对多字节字符支持如何。
mysql
mysql> set @a = '中国 美国 俄罗斯 日本 中国 北京 上海 深圳 广州 北京 上海 武汉 东莞 北京 青岛 北京';
Query OK, 0 rows affected (0.00 sec)
mysql> select regexp_instr(@a,'北京',1,1);
+-------------------------------+
| regexp_instr(@a,'北京',1,1) |
+-------------------------------+
| 17 |
+-------------------------------+
1 row in set (0.00 sec)
mysql> select regexp_instr(@a,'北京',1,2);
+-------------------------------+
| regexp_instr(@a,'北京',1,2) |
+-------------------------------+
| 29 |
+-------------------------------+
1 row in set (0.00 sec)
mysql> select regexp_instr(@a,'北京',1,3);
+-------------------------------+
| regexp_instr(@a,'北京',1,3) |
+-------------------------------+
| 41 |
+-------------------------------+
1 row in set (0.00 sec)
那总结下，这里我提到了 MySQL 8.0 的两个最有用的正则匹配函数 regexp_replace 和 regexp_instr。针对以前类似的场景算是有一个完美的解决方案。
热心网友
时间：2022-05-08 04:33
MYSQL中貌似是不支持正则替换的
