使用来自另一个表的数据在MySQL中查找和替换字符串

Question

我有两个MySQL表，我想使用另一个表中的数据在一个表中查找和替换文本字符串。

表texts

+---------------------+
|      messages       |
+---------------------+
| 'thx guys'          |
| 'i think u r great' |
| 'thx again'         |
| ' u rock'           |
+---------------------+

表dictionary

+--------------+---------------+
| bad_spelling | good_spelling |
+--------------+---------------+
|    'thx'     |    'thanks'   |
|    ' u '     |    ' you '    |
|    ' r '     |    ' are '    |
+--------------+---------------+

我希望SQL遍历并查看消息中的每一行，并将bad_spelling的每个实例替换为good_spelling，并对所有对bad_spelling和good_spelling执行此操作。

我得到的最接近的是：

update texts, dictionary
set texts.message = replace(texts.message,
                            dictionary.bad_spelling,
                            dictionary.good_spelling)

但这只会将"thx“改为”致谢“(在两行中)，而不会继续将”u“替换为”you“或”r“，将其替换为”are“。

有什么想法可以让它使用字典中的所有行替换语句吗？

PS忘记提到这是一个很小的例子，在实际情况中，我将有很多查找/替换对，随着时间的推移，它们可能会被添加到其中。

Answer 1

我从未使用过MySql，所以这只是基于我的其他数据库工作的理论。在阅读其他答案时，我尝试使用替换()，我想我可以发布这篇文章，并让具有MySql语法经验的人有一些想法来制定一个基本的解决方案。

下面是为您完成大部分工作的一些Server代码：

DECLARE @Source table (Texts varchar(50))
INSERT @Source VALUES ('thx guys')
INSERT @Source VALUES ('i think u r great')
INSERT @Source VALUES ('thx again')
INSERT @Source VALUES ('u rock')

DECLARE @Dictionary table (bad_spelling varchar(50), good_spelling varchar(50))
INSERT @Dictionary VALUES ('thx', 'thanks')
INSERT @Dictionary VALUES ('u', 'you')
INSERT @Dictionary VALUES ('r', 'are')

SELECT
    t.Texts,COALESCE(d.good_spelling,c.ListValue) AS WordToUse
    FROM @Source                                     t
        CROSS APPLY dbo.FN_ListToTable(' ',t.Texts)  c
        LEFT OUTER JOIN @Dictionary                  d ON c.ListValue=d.bad_spelling

产出：

Texts              WordToUse
------------------ ---------
thx guys           thanks
thx guys           guys
i think u r great  i
i think u r great  think
i think u r great  you
i think u r great  are
i think u r great  great
thx again          thanks
thx again          again
u rock             you
u rock             rock

(11 row(s) affected)

与查询中的实际“文本”相比，使用"real“PK更好，但OP没有列出该表中的许多列，所以我使用”Text“。

使用Server时，您需要使用一些时髦的XML语法将这些行重新连接起来(因此我不会显示代码，因为这并不重要)，但是使用MySql的GROUP_CONCAT()，您应该能够将单词行连接到短语行中。

在这里可以找到( Server)拆分函数的代码及其工作方式：SQL Server: Split operation

Answer 2

您必须在文本上多次调用“替换”：

Update ...
Set texts.message = Replace(
                        Replace(
                            Replace( texts.message, 'thx ', 'thanks ' )
                            , ' u ', ' you ')
                        , ' r ', ' are ')

编辑，考虑到您已经有了大量的替换，您需要在具有多个UPDATE语句调用的游标中这样做。类似的事情(我根本没有测试过这个，所以要小心)：

Create Temporary Table ReplaceValues 
    (
    BeforeText varchar(100) not null
    , AfterText varchar(100) not null
    )

Insert ReplaceValues(BeforeText, AfterText) Values('thx ', 'thanks ')
Insert ReplaceValues(BeforeText, AfterText) Values(' u ', ' you ')
Insert ReplaceValues(BeforeText, AfterText) Values(' r ', ' are ')

DECLARE done int DEFAULT(0)
DECLARE BeforeValue varchar(100);
DECLARE AfterValue varchar(100);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

DECLARE ReplaceList CURSOR FOR Select BeforeText, AfterText From ReplaceValues;

OPEN ReplaceList;   

REPEAT
    If NOT done THEN
        FETCH ReplaceList INTO BeforeValue, AfterValue;

        Update texts
        Set texts.message = REPLACE(texts.message, BeforeValue, AfterValue);
    END IF  
UNTIL done END REPEAT;
CLOSE ReplaceList;

您可以将所有这些打包到一个过程中，以便以后再调用它。

Answer 3

它并不是一帆风顺的，因为即使替换运行了x次(其中x是字典中的行数)，也只保留了一个更新(最后一个更新)。

事务不会记录中间结果，因此不能将它们视为下一批替换的输入值。

由于(AFAIK) MySQL不支持递归查询，您将不得不求助于过程方法。