如何从MySQL表的多个列中获取长度大于指定最小值的所有不同单词？

Question

在MySQL 5.6数据库中，我有一个名为tablename的表格，其中包括三个TEXT列：col_a, col_b, col_c等。我想从这三列中提取所有长度至少为5个字符的唯一单词（以空格分隔）。"Word"指任何非空格字符的字符串，例如 "foo-123" 将被视为一个单词，"099423" 也是。这些列都是utf8格式InnoDB列。是否有单个查询可以完成此操作？编辑：根据请求，这里有一个示例：（在实际数据中，col_a、col_b和col_c是TEXT字段，并且可能有大量单词。）

select id, col_a, col_b, col_c from tablename;

id  | col_a              | col_b          | col_c
----|--------------------|----------------|----------------------
1   | apple orange plum  | red green blue | bill dave sue
2   | orange plum banana | yellow red     | frank james
3   | kiwi fruit apple   | green pink     | bill sarah-jane frank

expected_result: ["apple", "orange", "banana", "fruit", 
                  "green", "yellow", "frank", "james", "sarah-jane"]

我不在意结果的顺序。谢谢！

编辑：在上面的示例中，所有内容都是小写的，因为这是我在实际表格中存储所有内容的方式。但是，为了论证起见，如果它包含一些大写字母，我希望查询忽略大小写（这正是我的数据库配置设置所发生的情况）。

编辑2：如有帮助，所有文本列均具有FULLTEXT索引。

编辑3：以下是创建示例数据的SQL：

DROP TABLE IF EXISTS `tablename`;
CREATE TABLE `tablename` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col_a` text,
  `col_b` text,
  `col_c` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
LOCK TABLES `tablename` WRITE;
INSERT INTO `tablename` VALUES (1,'apple orange plum','red green blue','bill dave sue'),(2,'orange plum banana','yellow red','frank james'),(3,'kiwi fruit apple','green pink','bill sarah-jane frank');
UNLOCK TABLES;

- Max Williams

---

请展示一些表格数据的样例和预期结果 - 都以格式化的文本形式呈现（不要使用图片）。并且请查看 https://stackoverflow.com/help/reprex。 - jarlh

MySQL 的哪个版本？ - Nick

@MaxWilliams 特定值中的最大单词数是3个吗？还是可以是任何数量？ - Madhur Bhaiya

最简单和最快的方法是使用CTE - 公共表达式，它使您能够递归地“扫描”列和行。我没有MySQL。您是否对MS SQL Server示例感兴趣？ - Maciej Los

你最好使用真正的编程语言来完成任务，而不是受限的SQL语言。 - Rick James

Answer 1

最好的解决方案是不使用该结构来存储数据，并遵守范式来规范化您的数据库。但是，如果您想将字符串拆分为单词并将其作为表获取，而且无法规范化数据库并且无法使用带有CTE的最新版本MYSQL，则可以创建一个简单的存储过程来拆分字符串并将它们存储到临时表中。例如，存储过程可能如下所示：

DELIMITER //
CREATE PROCEDURE split_string_to_table (str longtext)
BEGIN
  DECLARE val TEXT DEFAULT NULL;
  DROP TEMPORARY TABLE IF EXISTS temp_values;
  CREATE TEMPORARY TABLE temp_values (
     `value` varchar(200)  
  );

  iterator:
  LOOP  
    IF LENGTH(TRIM(str)) = 0 OR str IS NULL THEN
      LEAVE iterator;
    END IF;
    SET val = SUBSTRING_INDEX(str, ' ', 1);
    INSERT INTO temp_values (`value`) VALUES (TRIM(val));
    SET str = INSERT(str, 1, LENGTH(val) + 1, '');
  END LOOP;
  SELECT DISTINCT(`value`) FROM temp_values WHERE CHAR_LENGTH(`value`) >= 5;
END //
DELIMITER ;

之后，您可以将所有字符串连接成一个字符串并将其存储在临时变量中，并将其值传递给存储过程：

SELECT CONCAT_WS(' ', 
                 GROUP_CONCAT(col_a SEPARATOR ' '), 
                 GROUP_CONCAT(col_b SEPARATOR ' '), 
                 GROUP_CONCAT(col_c SEPARATOR ' ')
       ) INTO @text
FROM mytable;

CALL split_string_to_table(@text);

结果：

--------------
| value      |
--------------
| apple      |
--------------
| orange     |
--------------
| banana     |
--------------
| fruit      |
--------------
| green      |
--------------
| yellow     |
--------------
| frank      |
--------------
| james      |
--------------
| sarah-jane |
--------------

您可以在DBFiddle中查看该实现的演示。

- Maksym Fedorov

Answer 2

使用一个SELECT语句嵌套在另一个SELECT语句中，再加上UNION SELECT和SUBSTRING_INDEX函数，得出了以下结果。

SELECT DISTINCT results.col_a as "values"

FROM(

    SELECT DISTINCT      
    SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.col_a, ' ', numbers.n), ' ', -1) col_a
    FROM (SELECT 1 n UNION ALL SELECT 2
    UNION ALL SELECT 3 UNION ALL SELECT 4) numbers INNER JOIN tablename
    ON CHAR_LENGTH(tablename.col_a)-CHAR_LENGTH(REPLACE(tablename.col_a, ' ', ''))>=numbers.n-1

    UNION DISTINCT
    SELECT DISTINCT
    SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.col_b, ' ', numbers.n), ' ', -1) col_b
    FROM (SELECT 1 n UNION ALL SELECT 2
    UNION ALL SELECT 3 UNION ALL SELECT 4) numbers INNER JOIN tablename
    ON CHAR_LENGTH(tablename.col_b)-CHAR_LENGTH(REPLACE(tablename.col_b, ' ', ''))>=numbers.n-1

    UNION DISTINCT
    SELECT DISTINCT
    SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.col_c, ' ', numbers.n), ' ', -1) col_c
    FROM (SELECT 1 n UNION ALL SELECT 2
    UNION ALL SELECT 3 UNION ALL SELECT 4) numbers INNER JOIN tablename
    ON CHAR_LENGTH(tablename.col_c)-CHAR_LENGTH(REPLACE(tablename.col_c, ' ', ''))>=numbers.n-1

) AS results

WHERE length(results.col_a) > 4

结果

+-----------+
|values     |
+-----------+
|apple      |
+-----------+
|banana     |
+-----------+
|frank      |
+-----------+
|fruit      |
+-----------+
|green      |
+-----------+
|james      |
+-----------+
|orange     |
+-----------+
|sarah-jane |
+-----------+
|yellow     |
+-----------+

- Binara Medawatta

---

这似乎可以工作，但是它非常慢，在真实数据下就像“30分钟后杀掉它，我甚至没有让它完成”那么慢。我没有指定它需要不慢，所以如果没有人提出更快的解决方案，我将标记它为正确，谢谢。 - Max Williams

谢谢您的评论，我会尝试想出更快的解决方案。 - Binara Medawatta

你必须从FROM子句中删除tablename，，否则会得到一个CROSS JOIN。 - dnoeth

Answer 3

将数据导出到文件中，然后导入到新表中，如何？

select col_a, col_b, col_c
    into outfile '/tmp/words.csv'
    fields terminated by ' ' escaped by '' 
    lines  terminated by ' '
    from tablename;

create table tmp_words(word varchar(50));

load data infile '/tmp/words.csv'
    into table tmp_words
    lines terminated by ' ';

select distinct word from tmp_words where char_length(word) >= 5;

drop table tmp_words;

结果：

word
----------
apple
orange
green
banana
yellow
frank
james
fruit
sarah-jane

由于您拥有FULLTEXT索引，因此您也可以直接从information_schema中读取单词:

set global innodb_ft_aux_table = 'test/tablename';

select WORD
from information_schema.INNODB_FT_INDEX_TABLE
where char_length(WORD) >= 5
union 
select WORD
from information_schema.INNODB_FT_INDEX_CACHE
where char_length(WORD) >= 5

然而，由于FULLTEXT索引的工作方式，“sarah-jane”这样的“单词”将被分割。您可以在结果中看到这一点：

WORD
------
apple
banana
frank
fruit
green
james
orange
sarah   <-- !
yellow

db-fiddle

您还会错过类似于“about”的停用词。

请参阅：InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables

- Paul Spiegel

Answer 4

Shell脚本可能非常高效...

1. SELECT CONCAT_WS(' ', col_a, col_b, col_c) INTO OUTFILE 'x' ... 可以将列输出到文件中
2. tr ' ' "\n" <x -- 拆分为每行一个单词
3. awk 'length($1) >= 5' -- 每个单词至少有5个字符
4. sort -u -- 去重

没有停用词，但sed或awk可以处理。

 mysql -e "SELECT ... INTO OUTFILE 'x' ..." ...
 tr ' ' "\n" <x  |  awk 'length($1) >= 5'  |  sort -u

- Rick James

---

谢谢 - 这是一个有前途的方法，但 uniq 似乎没有去重行。这是我的做法：dbname='my_database'; fname='/tmp/tablename_strings.txt'; sudo chown max:max $fname; rm $fname; mysql -u root $dbname -e "SELECT CONCAT_WS(' ', col_a, col_b, col_c) FROM tablename INTO OUTFILE '$fname'"; tr ' ' '\n' <"$fname" | awk 'length($1) >= 5' | uniq，我得到了这个（用换行符分隔）：apple orange green orange banana yellow frank james fruit apple green sarah-jane frank - Max Williams

@MaxWilliams - 哎呀，我忘记了 sort。 （而且 sort 可能有去重选项，这样就不需要用到 uniq 了。） - Rick James

你可以使用 sort -u 命令来合并排序和去重步骤。 - Bill Karwin

我已经选择了这个答案（并奖励了悬赏），因为它是众多建议中最快的。谢谢！ - Max Williams

有时候“跳出框架”会有所帮助。（在这种情况下，框架是MySQL。） - Rick James

Answer 5

这是我的解决方案。不是为每一列计算并使用UNION，而是首先使用CONCAT_WS将所有列连接起来。然后获取distinct值并应用所需的条件。这样可以忽略union并提高性能。

SELECT MYWORD FROM (
SELECT
  DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT_WS(' ', COL_A, COL_B, COL_C), ' ', N.DIGIT+1), ' ', -1)  MYWORD 
FROM
  MYTABLE 
  INNER JOIN
  (SELECT 0 DIGIT UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) N
  ON (LENGTH(REPLACE(CONCAT_WS(' ', COL_A, COL_B, COL_C), ' ' , '')) <= LENGTH(CONCAT_WS(' ', COL_A, COL_B, COL_C))-N.DIGIT) ) MYRESULT
  WHERE LENGTH(MYRESULT.MYWORD)>=5

输出: DBFIDDLE

- Arun Palanisamy

Answer 6

根据您的性能要求和评论，似乎您需要定期运行此查询。不幸的是，您的数据并不具备足够的分辨率，无法整洁而简洁地完成此操作。

---

我会考虑添加一个汇总表格来帮助最终查询。通过维护汇总表，当主表中的数据发生变化时，您应该能够保持简单。

建议的格式为：

• summary_table - id, main_table_id, column_name, word

其中main_table_id是指向主表id列的外键。

您还可以在(main_table_id, column_name, word)上放置复合唯一索引。

---

在主表中编辑相关列的值时，您应该调整摘要表

• 删除main_table_id和column_name的现有单词
• 插入一个新的唯一单词列表，至少包含5个字符，用于main_table_id和column_name

这可以在应用程序级别或使用触发器完成

---

这将使最终查询变得更简单。

SELECT DISTINCT word
  FROM summary_table

- Arth

---

有趣的是，所涉及的表格已经是某种程度上的“摘要”表格，即我想在相应的其他表格上进行全文搜索的数据索引。我想要唯一单词的原因是用于建议算法，例如：“我们找不到任何与'applx'匹配的结果，您是否想说的是'apple'？”我认为最好的方法是离线生成单词列表（比如在计划任务中），并将其存储在一个单独的表格中，就像你建议的那样。 - Max Williams

如果它们已经是摘要表，我会建议你它们还没有足够地进行摘要！你把单词都整齐地用空格分开有点可疑，你能否在创建这些摘要表时将单词拆分成单独的行呢？ - Arth

我真的不想改变我已经有的模式，这个单词匹配方面就像是一个额外的层面。 - Max Williams

我可能会将其作为额外的层添加，所以感谢您在这个方向上的推动。 - Max Williams

Answer 7

编辑: 发现您正在使用MySQL 5.6后，在此发布一个新的解决方案。

使用一些变量将允许您不需要函数/过程或奇怪的UNION连接。这使用当前表上的交叉连接生成序列，从而允许我们通过分隔符拆分字符串。

在您的创建语句之后尝试这个：

SET @row = 0;
SET @list = (SELECT GROUP_CONCAT(CONCAT_WS(' ', col_a, col_b, col_c) SEPARATOR ' ') FROM tablename);
SET @limiter = (SELECT LENGTH(@list) - LENGTH(REPLACE(@list, ' ', '')) + 1);

SELECT DISTINCT word 
FROM (
    SELECT RTRIM(
               SUBSTRING_INDEX(
                   SUBSTRING_INDEX(
                       @list,
                       ' ',
                       r
                   ),
                   ' ',
                   -1
               )
           ) AS word
    FROM (SELECT @row := @row + 1 AS r
          FROM tablename t1, tablename t2, tablename t3) gen_seq
    WHERE r <= @limiter
) words WHERE LENGTH(word) >= 5;

根据您的表的大小，您可能可以从交叉连接中删除表名 t3 以加速查询。由于该表很小，因此需要进行3次交叉连接迭代。

---

如果您使用的是至少MySQL 8，递归是一种选择。

我使用了您创建的表，并在其上运行了以下操作：

SET @list = (SELECT GROUP_CONCAT(CONCAT_WS(' ', col_a, col_b, col_c) SEPARATOR ' ') 
             FROM tablename);

WITH RECURSIVE words AS (
    (SELECT 1 AS n, @list AS words)
    UNION 
    (SELECT n+1 AS m, @list
     FROM words 
     WHERE n < (LENGTH(words) - LENGTH(REPLACE(words,' ', ''))) + 1
    )
)
SELECT DISTINCT LTRIM(
           SUBSTRING(
               SUBSTRING_INDEX(words, ' ', n),
               CHAR_LENGTH(
                   SUBSTRING_INDEX(words, ' ', n-1)
               ) + 1
           )
       ) word 
FROM words
WHERE n <= (LENGTH(words) - LENGTH(REPLACE(words,' ', ''))) + 1
HAVING LENGTH(word) >= 5;

---

这两个选项都会得到以下结果：

1. 苹果
2. 橙子
3. 绿色的
4. 香蕉
5. 黄色的
6. 弗兰克
7. 詹姆斯
8. 水果
9. 萨拉-简

编辑：保留MySQL 8选项，以防对未来的某些人有用。

- derek.wolfe

Answer 8

尽管我非常喜欢 SQL 解决方案，但这种情况并不适合。SQL 希望将每个列作为一个原子值处理，而您在单个 TEXT 列中存储单词列表的设计违反了这一原则。这相当于 使用逗号分隔的列表。

在纯 SQL 中解决此任务的解决方案都很复杂，这应该是错误方法的红旗。如果您使用的代码解决方案已经达到了您的理解上限，那么调试或维护将会太困难。

您在评论中提到您愿意接受 Ruby 解决方案。我测试了这个。优点是代码更加清晰。

require 'mysql2'

client = Mysql2::Client.new(:host => "localhost", :database => "test", :username => "root")

words = {}
client.query("SELECT LOWER(CONCAT_WS(' ', col_a, col_b, col_c)) AS words FROM tablename").each do |row|
  row["words"].split(' ').each do |word|
    if word.length >= 5
      words[word] = true
    end
  end
end

print words.keys

输出：

["apple", "orange", "green", "banana", "yellow", "frank", "james", "fruit", "sarah-jane"]

我实际上会将单词单独存储，而不是在列表中。我知道你说你不想改变你的架构，但如果你想要这个运行更高效和更简单的解决方案，那就是必要的。

CREATE TABLE words (
  id SERIAL PRIMARY KEY,
  word_length SMALLINT NOT NULL,
  word VARCHAR(191) NOT NULL,
  KEY (word_length)
);

mysql> SELECT DISTINCT word FROM words WHERE word_length >= 5;
+------------+
| word       |
+------------+
| apple      |
| orange     |
| green      |
| banana     |
| yellow     |
| frank      |
| james      |
| fruit      |
| sarah-jane |
+------------+

- Bill Karwin

---

谢谢。实际上我最终自己完成了这个任务，使用了@RickJames在上面回答中提供的SQL代码，它是我找到的最快的解决方案。纯SQL解决方案似乎非常慢。 - Max Williams

Answer 9

假设：单词之间只用一个空格分隔（不是多个）。如果有多个空格，将会变得更加复杂。

1. 我们需要使用数字生成器序列。根据您的情况，我只考虑了从1到6的序列。您可以考虑更大的序列。在这里查找生成它们的方法：https://dba.stackexchange.com/questions/75785/how-to-generate-a-sequence-in-mysql
2. 使用多个字符串操作，您可以将以空格分隔的字符串转换为行。在WHERE中，我们将使用CHAR_LENGTH函数指定字符长度约束。然后，我们可以使用UNION将col_a、col_b和col_c的结果分别组合起来。

在DB Fiddle上查看

查询＃1

(
SELECT 
  SUBSTRING_INDEX(SUBSTRING_INDEX(t.col_a, ' ', ngen.num), ' ', -1) AS word 
FROM
  tablename AS t 
  INNER JOIN
  (SELECT 1 num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4  UNION ALL SELECT 5 UNION ALL SELECT 6) AS ngen
    ON LENGTH(REPLACE(t.col_a, ' ' , '')) <= LENGTH(t.col_a)-ngen.num+1
WHERE CHAR_LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(t.col_a, ' ', ngen.num), ' ', -1)) >= 5
)

UNION 

(
SELECT 
  SUBSTRING_INDEX(SUBSTRING_INDEX(t.col_b, ' ', ngen.num), ' ', -1) AS word 
FROM
  tablename AS t 
  INNER JOIN
  (SELECT 1 num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4  UNION ALL SELECT 5 UNION ALL SELECT 6) AS ngen
    ON LENGTH(REPLACE(t.col_b, ' ' , '')) <= LENGTH(t.col_b)-ngen.num+1
WHERE CHAR_LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(t.col_b, ' ', ngen.num), ' ', -1)) >= 5
)

UNION 

(
SELECT 
  SUBSTRING_INDEX(SUBSTRING_INDEX(t.col_c, ' ', ngen.num), ' ', -1) AS word 
FROM
  tablename AS t 
  INNER JOIN
  (SELECT 1 num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4  UNION ALL SELECT 5 UNION ALL SELECT 6) AS ngen
    ON LENGTH(REPLACE(t.col_c, ' ' , '')) <= LENGTH(t.col_c)-ngen.num+1
WHERE CHAR_LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(t.col_c, ' ', ngen.num), ' ', -1)) >= 5
);

结果：

| word       |
| ---------- |
| apple      |
| orange     |
| fruit      |
| banana     |
| yellow     |
| green      |
| frank      |
| james      |
| sarah-jane |

- Madhur Bhaiya

Answer 10

方法

我建议使用正则表达式替换函数来完成这个任务。如果要求发生变化，例如单词可能由多个空格或其他类型的空白符号（如制表符）分隔，甚至可能处理标点符号（如逗号和句号），这将提供更大的灵活性。鉴于您已经说明了MySQL v5.6，新的REGEXP_REPLACE函数不可用 - 但几年前我编写了一个自定义正则表达式函数来填补这个空缺。以下是它的演示...

演示

Rextester在线演示：https://rextester.com/DCJE11797

SQL

SELECT DISTINCT word
FROM 
(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(words, '¬', digits.idx + 1), '¬', -1) word
 FROM
 (SELECT reg_replace(LOWER(TRIM(CONCAT(col_a, ' ', col_b, ' ', col_c))),
                     '[[:space:]]+',
                     '¬',
                     TRUE,
                     1,
                     0) AS words
  FROM table_name) delimited
 INNER JOIN
 (SELECT @row := @row + 1 as idx FROM 
  (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
            UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
            UNION ALL SELECT 9) t1,
  (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
            UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
            UNION ALL SELECT 9) t2, 
  (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
            UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
            UNION ALL SELECT 9) t3, 
  (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
            UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
            UNION ALL SELECT 9) t4, 
  (SELECT @row := -1) t5) digits
 ON LENGTH(REPLACE(words, '¬' , '')) <= LENGTH(words) - digits.idx) subq
WHERE CHAR_LENGTH(word) >= 5

输出

word
1   apple
2   banana
3   frank
4   fruit
5   green
6   james
7   orange
8   sarah-jane
9   yellow

解释

上述SQL中使用了一些技巧，需要一些认证：

1. 将列用空格连接起来，每个空格之间加上一个空格，然后去除前导/尾随空格并转换为小写：LOWER(TRIM(CONCAT(col_a, ' ', col_b, ' ', col_c))
2. 然后使用正则表达式替换器替换所有连续的空白块 - 每个块都被单个¬字符替换：reg_replace(str, '[[:space:]]+', '¬', TRUE, 1, 0)。注意：如果这个字符可能出现在单词中，则可以选择其他不同的字符。
3. 使用this answer中的技术将带有分隔值的字符串转换为单独的行值。结合this answer中的巧妙技巧生成由一系列递增数字组成的表：在本例中为0-10,000。
4. 使用DISTINCT确保输出中没有重复的单词（如果要计算出现次数，可以使用GROUP BY）。可选地，可以使用ORDER BY按字母顺序排序单词 - 但听起来您可能希望删除它以加快速度。

- Steve Chambers