MySQL select查询的解析与查询

Question

我有一个表，其中我们有超过20亿行，我的搜索总是基于索引字段。

需要尝试的不是什么新鲜事，就是将xml字符串的字段存储在一个长文本和varchar(4000)列中，这两个列都存储xml文件，一个是大的xml文件，另一个是小的xmls文件。

我需要查询xml文件中的节点或文件。

例如，我需要获取出现在第一个xml文件(DS第6列)中的statusCode和出现在第二个xml文件(SY列最后一列)中的providerT。

此搜索将始终基于CT_ID，这是索引列，然后是DS SY列。

因此，我正在搜索类似如下的查询

select * from AD_TABLE where CT_ID ='6ththeurtyiru‘and DS.statusCode='COMPLETED’AND SY.providerT='noOfNRe'；

我知道我的查询是错误的，但是如果我有json，这是可以工作的，但是我们在MySql中有用于xml的东西吗？

我已经做了一些搜索，但查询看起来非常非常难以理解，特别是解析查询。

有人能帮我这个忙吗？

ID,VERSION,AN_TYPE,EN_TYPE,CT_ID,DS,OT_TYPE,DATE_TIME,GP_ID,OT_NAME,OT_ID,UR_NAME,UR_ID,PT_ID,NS,SY

6uyuitygjh82mcrzz,0,SD_CASE,MYCHECK,6ththeurtyiru
,<?xml version="1.0" encoding="UTF-8" standalone="yes"?><sCPayload><cId>suerywe7r-21cf-4c7e-8071-suerywe7r</cId><statusCode>COMPLETED</statusCode><sngState>INPROGRESS</sngState><noOfNRe>1</noOfNRe><noOfRRR>0</noOfRRR><noOfER>0</noOfER><noOfARR>1</noOfARR><providerT>WATCHLIST</providerT></sCPayload>,CASE,9/16/2020 9:45,bd7c9519-d726-4672-8599-83d21927bec5,,5f53b903-21cf-4c7e-8071-suerywe7r,System User,USER_SYSTEM,,,<?xml version="1.0" encoding="UTF-8" standalone="yes"?><screenCES><providerT>MYWATCH</providerT><noOfNRe>1</noOfNRe></screenCES>,

Answer 1

在MySQL8.0中，您可以使用ExtractValue

对于MariaDB (10.5)，文档为：ExtractValue

mysql> set @xml='<sCPayload><cId>suerywe7r-21cf-4c7e-8071-suerywe7r</cId><statusCode>COMPLETED</statusCode><sngState>INPROGRESS</sngState><noOfNRe>1</noOfNRe><noOfRRR>0</noOfRRR><noOfER>0</noOfER><noOfARR>1</noOfARR><providerT>WATCHLIST</providerT></sCPayload>';
Query OK, 0 rows affected (0.00 sec)

mysql> select ExtractValue(@xml,'//statusCode');
+-----------------------------------+
| ExtractValue(@xml,'//statusCode') |
+-----------------------------------+
| COMPLETED                         |
+-----------------------------------+
1 row in set (0.00 sec)

mysql>

Answer 2

您还可以将extract的结果放在一个永久的虚拟字段中。因此，您可以构建索引或组合索引来获得快速结果，而不是全表扫描。如果更改XML，此字段将自动更新

将此字段添加到表中

ALTER TABLE yourTable

    ADD COLUMN
    `statusCode` varchar(16) AS (ExtractValue(DS,'//statusCode')) PERSISTENT;