mysql - 如何匹配两个表之间varchar字段中的字符串

Question

我有两个表，

table1 有两列:

id(PK)     |   sentence(varchar 255)
1          |   I am a boy; a good boy.

和 table2 有两列:

word    |   meaning
I       |     مين
am      |     هون
a       |     أيك 
boy     |     لرقة 
good    |     اشا 

现在我想查询(仅使用 MySQL 的单个查询)这两个表，这样我将获得每个单词及其翻译。

对于示例数据，查询结果必须是:

sentence                  |    word_meaning (virtual column)
I am a boy; a good boy.   |  {"I":"مين", "am":"هون", "a":"أيك", "boy":"لرقة", "good":"اشا"}

正如您在word_meaning 列中看到的那样，每个单词在json 中都有其含义。

Answer 1

这是一个简化的答案。您需要准备好句子 - 用空格更改所有非单词符号。此查询将在比较时使用 UPPER 忽略字符大小写。

创建和填充表格:

CREATE TABLE table1(id int auto_increment primary key, sentence varchar(250));
CREATE TABLE table2(word varchar(120), meaning varchar(120));
INSERT INTO table1(sentence) VALUES ("This is a test sentence"), ("This is another sentence");
INSERT INTO table2 VALUES ("This", "used to identify a specific person or thing"), ("is", "auxiliary verb"), ("test", "a procedure intended to establish the quality, performance, or reliability of something"), ("sentence", "a set of words that is complete in itself");

查询:

SELECT prepared.id, prepared.idx, prepared.word, IFNULL(table2.meaning, "!!! NO TRANSLATION FOUND")
FROM
    (SELECT
        id,
        idx,
        SUBSTRING_INDEX(SUBSTRING_INDEX(sentence, ' ', idx), ' ', -1) word
    FROM
        table1
    JOIN 
        (SELECT t1.idx * 10 + t2.idx + 1 idx FROM (
            (SELECT 0 idx UNION ALL SELECT 1 idx UNION ALL SELECT 2 idx UNION ALL SELECT 3 idx UNION ALL SELECT 4 idx UNION ALL SELECT 5 idx UNION ALL SELECT 6 idx UNION ALL SELECT 7 idx UNION ALL SELECT 8 idx UNION ALL SELECT 9 idx) t1
            CROSS JOIN
            (SELECT 0 idx UNION ALL SELECT 1 idx UNION ALL SELECT 2 idx UNION ALL SELECT 3 idx UNION ALL SELECT 4 idx UNION ALL SELECT 5 idx UNION ALL SELECT 6 idx UNION ALL SELECT 7 idx UNION ALL SELECT 8 idx UNION ALL SELECT 9 idx) t2
        )) idx on idx.idx - 2 < LENGTH(sentence) - LENGTH(REPLACE(sentence, " ", ""))) prepared
LEFT JOIN
    table2 on UPPER(table2.word) = UPPER(prepared.word)
ORDER BY prepared.id, prepared.idx;

这将产生:

1   1   This        used to identify a specific person or thing
1   2   is          auxiliary verb
1   3   a           !!! NO TRANSLATION FOUND
1   4   test        a procedure intended to establish the quality, performance, or reliability of something
1   5   sentence    a set of words that is complete in itself
2   1   This        used to identify a specific person or thing
2   2   is          auxiliary verb
2   3   another     !!! NO TRANSLATION FOUND
2   4   sentence    a set of words that is complete in itself

请注意，这只是一个示例。使用索引有助于提高性能。

获取一些 JSON 的数组:

SELECT 
    JSON_ARRAY(
        group_concat(JSON_OBJECT("sentecne_id", id, "word_idx", idx, "word", word, "meaning", meaning)),
        "]"
    ) result
FROM (
    SELECT prepared.id, prepared.idx, prepared.word, IFNULL(table2.meaning, "!!! NO TRANSLATION FOUND") meaning
    FROM
        (SELECT
            id,
            idx,
            SUBSTRING_INDEX(SUBSTRING_INDEX(sentence, ' ', idx), ' ', -1) word
        FROM
            table1
        JOIN 
            (SELECT t1.idx * 10 + t2.idx + 1 idx FROM (
                (SELECT 0 idx UNION ALL SELECT 1 idx UNION ALL SELECT 2 idx UNION ALL SELECT 3 idx UNION ALL SELECT 4 idx UNION ALL SELECT 5 idx UNION ALL SELECT 6 idx UNION ALL SELECT 7 idx UNION ALL SELECT 8 idx UNION ALL SELECT 9 idx) t1
                CROSS JOIN
                (SELECT 0 idx UNION ALL SELECT 1 idx UNION ALL SELECT 2 idx UNION ALL SELECT 3 idx UNION ALL SELECT 4 idx UNION ALL SELECT 5 idx UNION ALL SELECT 6 idx UNION ALL SELECT 7 idx UNION ALL SELECT 8 idx UNION ALL SELECT 9 idx) t2
            )) idx on idx.idx - 2 < LENGTH(sentence) - LENGTH(REPLACE(sentence, " ", ""))) prepared
    LEFT JOIN
        table2 on UPPER(table2.word) = UPPER(prepared.word)
    ORDER BY prepared.id, prepared.idx
) translated;