替换随机单词

Question

我想替换WordPress帖子中的随机单词。我有一个MySQL的代码。 这段代码运行良好：

update wp_posts set post_content =
replace(post_content,'oldword','newword'); 

但是当我想替换数据库中的随机时，例如

replace(post_content,'oldword',rand('newword','google','stackoverflow','stackover')); 

此代码不起作用。

我不知道如何将此代码用于随机替换单词。我希望每次出现的旧词都被不同的词替换

Error
SQL query:
UPDATE wp_posts SET post_content =
REPLACE_RANDOM(post_content, 'oldword')
MySQL said: Documentation 
#1366 - Incorrect string value: 'xD8xA7xDBx8CxD9x86...' for column 'String' at row 1

问题出在哪里？

DELIMITER $$
DROP FUNCTION IF EXISTS Replace_Random$$
CREATE FUNCTION Replace_Random(String TEXT, Word TEXT)
RETURNS TEXT
BEGIN
DECLARE New TEXT DEFAULT '';
DECLARE Loc INT DEFAULT INSTR(String, Word);
DECLARE WLen INT DEFAULT Length(Word);
WHILE Loc DO
SET New = CONCAT(New, SUBSTR(String, 1, Loc-1), ELT(FLOOR(RAND()*5)+1, 'biterrr', 'stackoverflow', 'serress', 'ms-ms-ms', 'Nick'));
SET String = SUBSTR(String, Loc + WLen);
SET Loc = INSTR(String, Word);
END WHILE;
SET NEW = CONCAT(New, String);
RETURN New;
END$$
DELIMITER ;
UPDATE wp_posts SET post_content =
REPLACE_RANDOM(post_content, 'oldword');`

Answer 1

您可以使用ELT从字符串列表中选择一个随机值：

UPDATE wp_posts SET post_content =
REPLACE(post_content, 'oldword', ELT(FLOOR(RAND()*5)+1, 'hello', 'world', 'friend', 'tuesday', 'sunny'))

如果列表中有不同数量的字符串，只需将RAND()的乘数从 5 更改为字符串数即可。

更新

如果需要用不同的单词替换短语中多次出现的字符串，则需要一个存储函数。这个将完成这项工作：

DELIMITER \
DROP FUNCTION IF EXISTS Replace_Random\
CREATE FUNCTION Replace_Random(String TEXT CHARACTER SET utf8, Word TEXT CHARACTER SET utf8)
RETURNS TEXT CHARACTER SET utf8
BEGIN
DECLARE New TEXT CHARACTER SET utf8 DEFAULT '';
DECLARE Loc INT DEFAULT INSTR(String, Word);
DECLARE WLen INT DEFAULT Length(Word);
WHILE Loc DO
SET New = CONCAT(New, SUBSTR(String, 1, Loc-1), ELT(FLOOR(RAND()*5)+1, 'hello', 'world', 'friend', 'tuesday', 'sunny'));
SET String = SUBSTR(String, Loc + WLen);
SET Loc = INSTR(String, Word);
END WHILE;
SET NEW = CONCAT(New, String);
RETURN New;
END\
DELIMITER ;
SELECT REPLACE_RANDOM('a string with oldword in it twice - oldword!', 'oldword');

输出(可变(：

a string with sunny in it twice - tuesday!
a string with hello in it twice - friend!