MySQL查询存储xml的字段

问题背景：数据库中有一张日志表，主要存储和第三方交互的请求报文和返回报文。想找寻其中用户为A的返回报文，如果根据整个字段like查询，速度太慢。

期望结果：根据用户名查询出来数据，再不济把返回报文中的用户名字都输出出来，便于寻找当时的数据。

实现方法：
mysql根据版本不同提供了不同的方法。MySQL 5.7及更早版本中可用EXTRACTVALUE()函数；MySQL 8.0或更高版本，建议使用XMLQUERY()，因为EXTRACTVALUE()在未来的版本中可能会被弃用。

查询mysql版本

SELECT VERSION();

判断表中字段是否是正确的xml格式，可以用IS_VALID_XML()函数（这个函数在MySQL 8.0.17及更高版本中可用），如果版本不支持该函数可能要写个存储过程或利用外部工具来识别。我现在使用的MySQL版本过低不支持验证xml格式的IS_VALID_XML函数。查询数据只能用EXTRACTVALUE()函数。

SELECT
    id, -- 假设你有一个名为id的主键字段
    response,
    IS_VALID_XML(response) AS is_valid_xml
FROM
    t_int_log

EXTRACTVALUE()函数使用：

SELECT id, EXTRACTVALUE(response, '//InsuredName') AS target_node_value, response
FROM t_int_log
WHERE EXTRACTVALUE(response, '//InsuredName') = '李梓瑜'

EXTRACTVALUE函数使用示例.png

EXTRACTVALUE()函数使用：

SELECT XMLQUERY('//target_node/text()' PASSING xml_column AS "doc") AS target_node_value
FROM my_table
WHERE -- 这里可以添加你的筛选条件，例如某个ID
  id = 1;

在这个示例中，XMLQUERY()函数使用了XPath表达式//target_node/text()来提取目标节点的文本内容，PASSING xml_column AS "doc"指定了XML数据源。