mysql - 如何删除 mysql 记录中的部分字符串？

Question

我在mysql中有以下代码db 和我需要更改 json代码。我正在尝试找到一个可以删除 <script> 的解决方案标签以及它们之间的所有内容。然后我可以使用 CONCAT添加更新的<script>内容。

如何删除部分记录？ RLIKE 和 REGEXP 似乎没有满足我的需要。

有什么建议吗？

<script type="application/ld+json">
{
"@context": "http://schema.org/",
"@type": "Product",
"name": "Birlea Sophia Single Cream Metal Bed Frame",
 "image": [
"http://www.myshop.co.uk/cache/images/products/50265_thumb.jpg",
"http://www.myshop.co.uk/cache/images/products/50265.jpg",
"http://www.myshop.co.uk/cache/images/products/50265_large.jpg"
],
"description": "The Birlea Sophia cream metal bed frame has classic styling with delightful heart details on the head and foot ends and a smooth cream finish.
This bed frame is sturdy in construction and has a sprung slatted base to give any style of mattress the ideal support it needs. The Sophia is also available in a pretty pink finish.
The Sophia from Birlea is a pretty girl&#039;s bed frame and perfect for brightening up any bedroom, so BUY NOW.
Please note: Bed frames are self assembly and priced without a mattress.",
"color": "Cream/Ivory",
"mpn": "SOPB3CRM",
"gtin14": "05060307681031",
"offers": {
"@type": "Offer",
"priceCurrency": "GBP",
"price": "84.97"
},
"brand": {
  "@type": "Thing",
"name": "Birlea"}
}</script> <p style="text-align: justify">The Birlea Sophia cream metal bed frame has classic styling with delightful heart details on the head and foot ends and a smooth cream finish.</p>
<p style="text-align: justify">This&nbsp;bed frame is&nbsp;sturdy in construction and has a sprung slatted base to give any style of mattress the ideal support it needs. The Sophia is also available in a <a href="http://www.myshop.co.uk/Birlea-Sophia-Single-Pink-Metal-Bed-Frame-p1402.html">pretty pink finish</a>.</p>
<p style="text-align: justify">The Sophia from Birlea is a pretty girl's bed frame and perfect for brightening up any bedroom, so <strong><span style="color: #df0000">BUY NOW.</span></strong></p>
<p style="text-align: justify"><b style="text-align: start;">Please note: Bed frames are self assembly and priced without a mattress.</b></p>

Answer 1

我建议您看一下 UpdateXML MySQL 函数 ( https://dev.mysql.com/doc/refman/5.7/en/xml-functions.html#function_updatexml )。

这是一个基于您提供的内容的示例。

我将您的 xml 内容放入 XML 表的 CONTENT 列中:

mysql> select CONTENT from XML\G
*************************** 1. row ***************************
CONTENT: <script type="application/ld+json">
{
"@context": "http://schema.org/",
"@type": "Product",
"name": "Birlea Sophia Single Cream Metal Bed Frame",
 "image": [
"http://www.myshop.co.uk/cache/images/products/50265_thumb.jpg",
"http://www.myshop.co.uk/cache/images/products/50265.jpg",
"http://www.myshop.co.uk/cache/images/products/50265_large.jpg"
],
"description": "The Birlea Sophia cream metal bed frame has classic styling with delightful heart details on the head and foot ends and a smooth cream finish.
This bed frame is sturdy in construction and has a sprung slatted base to give any style of mattress the ideal support it needs. The Sophia is also available in a pretty pink finish.
The Sophia from Birlea is a pretty girl&#039;s bed frame and perfect for brightening up any bedroom, so BUY NOW.
Please note: Bed frames are self assembly and priced without a mattress.",
"color": "Cream/Ivory",
"mpn": "SOPB3CRM",
"gtin14": "05060307681031",
"offers": {
"@type": "Offer",
"priceCurrency": "GBP",
"price": "84.97"
},
"brand": {
  "@type": "Thing",
"name": "Birlea"}
}</script> <p style="text-align: justify">The Birlea Sophia cream metal bed frame has classic styling with delightful heart details on the head and foot ends and a smooth cream finish.</p>
<p style="text-align: justify">This&nbsp;bed frame is&nbsp;sturdy in construction and has a sprung slatted base to give any style of mattress the ideal support it needs. The Sophia is also available in a <a href="http://www.myshop.co.uk/Birlea-Sophia-Single-Pink-Metal-Bed-Frame-p1402.html">pretty pink finish</a>.</p>
<p style="text-align: justify">The Sophia from Birlea is a pretty girl's bed frame and perfect for brightening up any bedroom, so <strong><span style="color: #df0000">BUY NOW.</span></strong></p>
<p style="text-align: justify"><b style="text-align: start;">Please note: Bed frames are self assembly and priced without a mattress.</b></p>
1 row in set (0.00 sec)

然后我使用 UpdateXML 将每个与“//script”Xpath 匹配的标签替换为空字符串:

mysql> select UpdateXML(CONTENT, '//script', '') as WITHOUT_SCRIPT from XML\G
*************************** 1. row ***************************
WITHOUT_SCRIPT:  <p style="text-align: justify">The Birlea Sophia cream metal bed frame has classic styling with delightful heart details on the head and foot ends and a smooth cream finish.</p>
<p style="text-align: justify">This&nbsp;bed frame is&nbsp;sturdy in construction and has a sprung slatted base to give any style of mattress the ideal support it needs. The Sophia is also available in a <a href="http://www.myshop.co.uk/Birlea-Sophia-Single-Pink-Metal-Bed-Frame-p1402.html">pretty pink finish</a>.</p>
<p style="text-align: justify">The Sophia from Birlea is a pretty girl's bed frame and perfect for brightening up any bedroom, so <strong><span style="color: #df0000">BUY NOW.</span></strong></p>
<p style="text-align: justify"><b style="text-align: start;">Please note: Bed frames are self assembly and priced without a mattress.</b></p>
1 row in set (0.00 sec)