mysql使用正则表达式替换报错
[Err] 1305 - FUNCTION xxxxxxx.REGEXP_REPLACE does not exist
mysql 5.7.34 REGEXP_REPLACE does not exist,
You're correct that MySQL 5.7 does not support the REGEXP_REPLACE function, as it was introduced in MySQL 8.0. In MySQL 5.7, you can achieve similar functionality using a combination of string functions, but it will be less straightforward since it doesn't support regular expressions for replacement. To remove everything from the specific <div> tag to the end of the string in the content field, you can use the LOCATE and SUBSTRING functions. Here’s how you can do it:
UPDATE your_table_name SET content = SUBSTRING(content, 1, LOCATE('<div id="product-document" class="s-product-content__item">', content) - 1) WHERE LOCATE('<div id="product-document" class="s-product-content__item">', content) > 0;
### Explanation: - LOCATE('<div id="product-document" class="s-product-content__item">', content) finds the position of the specified <div> tag in the content field. - SUBSTRING(content, 1, position - 1) extracts the part of the string before the <div> tag. - The WHERE clause ensures that only rows containing the <div> tag are updated. Make sure to replace your_table_name with the actual name of your table. As always, it's a good practice to back up your data before performing bulk updates.
前面的故事
mysql 使用正则表达式,把content字段内<div id="product-document" class="s-product-content__item">开始直到结尾的内容都替换掉
Answer :
在 MySQL 中,可以使用 REGEXP 和 REPLACE 函数来处理正则表达式。要将 content 字段中从 <div id="product-document" class="s-product-content__item"> 开始到结尾的内容替换掉,可以使用如下 SQL 语句:
sql
UPDATE your_table_name SET content = REGEXP_REPLACE(content, '<div id="product-document" class="s-product-content__item">.*', '') WHERE content REGEXP '<div id="product-document" class="s-product-content__item">';
在这个 SQL 语句中: - your_table_name 是你要更新的表的名称。 - REGEXP_REPLACE 函数用于根据正则表达式替换内容。 - .* 表示匹配从 <div> 标签开始到字符串末尾的所有内容。 - WHERE 子句确保只有包含该 <div> 的记录会被更新。 请根据你的实际情况替换 your_table_name ,并在执行之前备份数据以防止意外丢失。