替换mysql中包含方括号的确切字符串

I am very new to sql querys and I am looking for a way to remove the following string from anywhere in my database: [lesson-navigation] including the square brackets. (Some instances have an unnecessary extra space before and/or after the string that would be nice to delete as well if possible too.

我是sql querys的新手，我正在寻找一种方法从我的数据库中的任何地方删除以下字符串：[lesson-navigation]包括方括号。 （有些实例在字符串之前和/或之后有一个不必要的额外空间，如果可能的话也可以删除。

This was a shortcode that I was inserting into my site, but then decided to implement it into the template instead. Now I have over 2000 instances of this in various places in my database and want to remove it.

这是我插入我的网站的短代码，但后来决定将其实现到模板中。现在我在我的数据库的不同位置有超过2000个这样的实例，并希望将其删除。

Thanks in advance for your help. ~Cam

在此先感谢您的帮助。 〜凸轮

1 个解决方案

#1

Use REPLACE() to perform a string replacement that removes the string from each column. If this string appears in multiple columns, you'll need to perform the query below for each column, or specify each column in the query (second example):

使用REPLACE（）执行字符串替换，从每列中删除字符串。如果此字符串出现在多个列中，则需要对每个列执行以下查询，或者在查询中指定每一列（第二个示例）：

UPDATE tablename SET columnname = REPLACE(columnname, '[lesson-navigation]', '');

/* Replace in multiple columns */
UPDATE tablename SET
  columnname = REPLACE(columnname, '[lesson-navigation]', ''),
  columnname2 = REPLACE(columnname2, '[lesson-navigation]', ''),
  columnname3 = REPLACE(columnname3, '[lesson-navigation]', '')
;