时间戳

首页 > 解决方案 > MySQL 列中多行数据的正则表达式

mysql - MySQL 列中多行数据的正则表达式

问题描述

我有一列包含 XML，我想使用正则表达式根据条件列出 id。

我复制到 outfile 的部分 XML 数据： 

  <definitions>^M\
    <definition id=

我想列出一个基于 xmldata 的列，<definitions>后面跟着<definition id="1"

到目前为止，我已经尝试过不同的正则表达式组合，但没有成功

where <col> REGEXP '.*<definitions>\r.\s+<definition id="1"'

标签： mysqlregex

解决方案

Note that REGEXP can match anywhere inside a string, not necessarily at the start (unlike LIKE), thus you need no .* at the start.

MySQL versions before 8.x did not support Perl-like shorthand classes, and \s is invalid there. In MySQL 8.x, the ICU regex library supports \s.

You may use in any MySQL:

where col REGEXP '<definitions>[[:space:]]+<definition id="1"'

Or, if the whitespace can be missing between the elements:

where col REGEXP '<definitions>[[:space:]]*<definition id="1"'

The [[:space:]] bracket expression containing [:space:] POSIX character class matches any whitespace chars, and + mathces 1 or more repetitions of the chars while * matches 0 or more of them.

推荐阅读