更新语句中的 MySQL 分号导致语法错误 [英] MySQL semicolon in update statement is causing syntax error

问题描述

我有一个带有文章表的 MySQL 数据库 (5.7.18).每篇文章在 abstract 列中都有一个作为 HTML 字符串的摘要.由于 HTML 编辑器中的错误，许多 HTML 标签被令人讨厌的样式污染，如下所示:

精彩摘要

为了解决这个问题，我正在尝试以下查询:

UPDATE `article` SET `abstract`=REPLACE(`abstract`,' style="color: rgb(0, 0, 0);"','')

不幸的是，显示了一个语法错误:

<块引用>

#1064 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册以了解要使用的正确语法靠近 '' style="color: rgb(0, 0, 0))' 在第 1 行

如果去掉分号，它就可以正常工作:

UPDATE `article` SET `abstract`=REPLACE(`abstract`,' style="color: rgb(0, 0, 0)"','');

但它也必须在字符串中替换...

我在

I have a MySQL DB (5.7.18) with articles table. Each article has an abstract as an HTML string in abstract column. Due to a bug in the HTML editor, many of HTML tags are polluted with annoying style, like this:

<h1 style="color: rgb(0, 0, 0);">Wonderful Abstract</h1>

In order to fix this I am trying the following query:

UPDATE `article` SET `abstract`=REPLACE(`abstract`,' style="color: rgb(0, 0, 0);"','')

Unfortunately, a syntax error is shown:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' style="color: rgb(0, 0, 0))' at line 1

If the semicolon is removed it works fine:

UPDATE `article` SET `abstract`=REPLACE(`abstract`,' style="color: rgb(0, 0, 0)"','');

But it must be replaced in the string too...

I found a reference here saying to escape it:

UPDATE `article` SET `abstract`=REPLACE(`abstract`,' style="color: rgb(0, 0, 0)\;"','')

But the error message is still shown.

Another option described in the same origin is to add a semicolon to the end of the statement:

UPDATE `article` SET `abstract`=REPLACE(`abstract`,' style="color: rgb(0, 0, 0);"','');

And the combination of those two:

UPDATE `article` SET `abstract`=REPLACE(`abstract`,' style="color: rgb(0, 0, 0)\;"','');

The error message is still there.

解决方案

Found it.

It was a field in phpMyAdmin which is set to semicolon(;) by default.

I have changed it to an unused char and the query ran normally.

这篇关于更新语句中的 MySQL 分号导致语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！