如何运行mySQL函数来更新所有行？

I am trying to strip HTML tags from all of my records directly through MySQL. Thanks to *'s this question, I found the following function, that kind of does strip html tags -

我试图直接通过MySQL从我的所有记录中删除HTML标记。感谢*的这个问题，我发现了以下函数，这种函数可以去掉html标签 -

SET GLOBAL log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS fnStripTags;
DELIMITER |
CREATE FUNCTION fnStripTags( Dirty varchar(4000) )
RETURNS varchar(4000)
DETERMINISTIC 
BEGIN
  DECLARE iStart, iEnd, iLength int;
    WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO
      BEGIN
        SET iStart = Locate( '<', Dirty ), iEnd = Locate( '>', Dirty, Locate('<', Dirty ));
        SET iLength = ( iEnd - iStart) + 1;
        IF iLength > 0 THEN
          BEGIN
            SET Dirty = Insert( Dirty, iStart, iLength, '');
          END;
        END IF;
      END;
    END WHILE;
    RETURN Dirty;
END;
|
DELIMITER ;
SELECT fnStripTags('this <html>is <b>a test</b>, nothing more</html>');

But I am unable to find out, How to use this function to update all records. For e.g. I have records in Address column of myTable, from which I want to strip HTML tags using above mentioned function. How can I directly update all records of the Address column with the help of mentioned function, or if direct update is not possible, then any way to insert all updated records in a 2nd column of the table Address_Stripped?

但我无法找到，如何使用此功能更新所有记录。对于例如我在myTable的Address列中有记录，我想使用上面提到的函数从中删除HTML标记。如何在上述函数的帮助下直接更新Address列的所有记录，或者如果无法直接更新，那么可以在表Address_Stripped的第二列中插入所有更新的记录吗？

P.S., I know, my question lacks any research done to get the answer by myself, but it's simply because I don't know how to search for it.

P.S.，我知道，我的问题没有做任何研究来自己得到答案，但这只是因为我不知道如何搜索它。

2 个解决方案

#1

You just need to call this function in an update statement:

您只需要在更新语句中调用此函数：

UPDATE mytable
SET    address = fnStripTags(address)

#2

It depends on how many rows you should update and can or can not you lock the entire table for the update time.

这取决于您应该更新多少行，并且可以或不可以锁定整个表以获取更新时间。

If the table is rather small or can be locked for the update time:

如果表格相当小或可以在更新时间内锁定：

UPDATE table_name
SET    address = fnStripTags(address)

And if the table is big and/or you can not lock the table for the entire update time - you should perform those updates in loop, by chunks with ORDER BY primary_key

如果表很大并且/或者您无法在整个更新时间内锁定表 - 您应该通过ORDER BY primary_key的块来循环执行这些更新

UPDATE table_name
SET    address = fnStripTags(address)
WHERE primary_key > <previous_value>
ORDER BY primary_key
LIMIT 1000

(you can use any suitable limit)

（你可以使用任何合适的限制）

#1