在MySQL 5.7中提取HTML标记中的数据

Question

我试图从html标签中增加额外的数据。我创建了一个像t中那样充当split_string的函数。虽然它的工作不一致，并且想知道其他人发现了什么。我将尝试包括一个例子，但它可能不起作用，因为它上到处都是html标记。

<div class="sub-sub-head"><b>Overview:</b></div>Establish and maintain an accurate, detailed, and up-to-date inventory of all enterprise assets with the potential to store or process data, to include: end-user devices (including portable and mobile), network devices, non-computing/IoT devices, and servers. Ensure the inventory records the network address (if static), hardware address, machine name, data asset owner, department for each asset, and whether the asset has been approved to connect to the network. For mobile end-user devices, MDM type tools can support this process, where appropriate. This inventory includes assets connected to the infrastructure physically, virtually, remotely, and those within cloud environments. Additionally, it includes assets that are regularly connected to the enterprise’s network infrastructure, even if they are not under control of the enterprise. Review and update the inventory of all enterprise assets bi-annually, or more frequently.<br/><br/>
 
 <div class="sub-sub-head"><b>Action Items:</b></div>1) Maintain a detailed Hardware Asset Inventory.<br/>
 
 <div class="sub-sub-head"><b>Additional Guidance:</b> </div>Asset Type: Devices <br> <br>
 Security Function: Identify

Answer 1

使用substring_index来拆分；对于每个字段，您要调用它两次，一次是为了获得第一个N字段，另一次是从该字段获得最后一个字段：

select
    substring_index(substring_index(bar, '<div class="sub-sub-head">', 2), '<div class="sub-sub-head">', -1) overview,
    substring_index(substring_index(bar, '<div class="sub-sub-head">', 3), '<div class="sub-sub-head">', -1) action_items,
    substring_index(substring_index(bar, '<div class="sub-sub-head">', 4), '<div class="sub-sub-head">', -1) guidance
from foo;

小提琴