mysql - 如何从mysql文本列中提取所需的数据

Question

您好，我有一个 mysql 表，其中包含如下数据

mysql> select * from query limit 5\G
*************************** 1. row ***************************
text:   <AUDIT_RECORD TIMESTAMP="2013-08-20T14:07:41" NAME="Query" CONNECTION_ID="12427041" STATUS="0" SQLTEXT="/* mysql-connector-java-5.1.18 ( Revision: tonci.grgin@oracle.com-20110930151701-jfj14ddfq48ifkfq ) */SELECT @@session.auto_increment_increment"/> 
*************************** 2. row ***************************
text:   <AUDIT_RECORD TIMESTAMP="2013-08-20T14:07:41" NAME="Query" CONNECTION_ID="12427032" STATUS="0" SQLTEXT="/* mysql-connector-java-5.1.18 ( Revision: tonci.grgin@oracle.com-20110930151701-jfj14ddfq48ifkfq ) */SELECT @@session.auto_increment_increment"/> 
*************************** 3. row ***************************
text:   <AUDIT_RECORD TIMESTAMP="2013-08-20T14:07:41" NAME="Query" CONNECTION_ID="12427046" STATUS="0" SQLTEXT="select * from MM_CUSTOMER where upper(custcode) ='DECT'"/> 
*************************** 4. row ***************************
text:   <AUDIT_RECORD TIMESTAMP="2013-08-20T14:07:41" NAME="Query" CONNECTION_ID="12427042" STATUS="0" SQLTEXT="select * from MM_CUSTOMER where upper(custcode) ='MKTF'"/> 
*************************** 5. row ***************************
text:   <AUDIT_RECORD TIMESTAMP="2013-08-20T14:07:41" NAME="Query" CONNECTION_ID="12427040" STATUS="0" SQLTEXT="select * from MM_CUSTOMER where upper(custcode) ='FLYC'"/> 
5 rows in set (0.00 sec)

现在我想提取以下格式的结果

+--------------------------+----------------------------------+--------------------------------------+
| ID                       | timestamp                        | text                                 |
+--------------------------+----------------------------------+--------------------------------------+
| CONNECTION_ID="12427042" | TIMESTAMP="2013-08-20T14:07:41"  | "SET sql_mode='STRICT_TRANS_TABLES'" |
+--------------------------+----------------------------------+--------------------------------------+

最后的结果是像all set一样按ID排序，按text分组，然后all select，然后update....

备选方案:

如果可能，请告诉我如何加载如下文件

<AUDIT>
      <AUDIT_RECORD TIMESTAMP="2013-08-20T14:07:41" NAME="Query" CONNECTION_ID="12427046" STATUS="0" SQLTEXT="select * from MM_CUSTOMER where upper(custcode) ='DECT'"/>
      <AUDIT_RECORD TIMESTAMP="2013-08-20T14:07:41" NAME="Query" CONNECTION_ID="12427042" STATUS="0" SQLTEXT="select * from MM_CUSTOMER where upper(custcode) ='MKTF'"/>
      <AUDIT_RECORD TIMESTAMP="2013-08-20T14:07:41" NAME="Query" CONNECTION_ID="12427040" STATUS="0" SQLTEXT="select * from MM_CUSTOMER where upper(custcode) ='FLYC'"/>
</AUDIT>

按照上面提到的标准，像.....一样进入表格。

mysql> desc query;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| ID        | varchar(50) | YES  |     | NULL    |       |
| timestamp | varchar(50) | YES  |     | NULL    |       |
| text      | text        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

谁能帮我实现这个目标？

Answer 1

理想情况下，您已经解析了这些记录，并为每个属性插入值，而不必事后解析它们。

但是，如果您受困于 XML，并且需要从中创建一个真正的表格，您可以尝试使用 ExtractValue() 来获取您想要的部分。

SELECT
    ExtractValue(`text`, '//@TIMESTAMP')     audit_time,
    ExtractValue(`text`, '//@CONNECTION_ID') connection_id,
    ExtractValue(`text`, '//@SQLTEXT')       sql
FROM query;

注意:不要定期执行此操作。理想情况下，您会执行一次，以获取这些值并将它们放入适当的表中。每次都从 XML 中提取会降低 MySQL 服务器的性能；您最终不仅要为每一行解析 XML，而且没有 XML 之外的东西来过滤或排序，基本上每个操作都会强制进行全表扫描。

(哦，说真的。你如何选择一半的列名作为关键字？TIMESTAMP 和 TEXT 是 SQL 值类型的名称。)