Как получить все отдельные слова указанной минимальной длины из нескольких столбцов в таблице MySQL?

В базе данных MySQL 5.6 у меня есть таблица tablename которая имеет (включая другие) три столбца TEXT: col_a, col_b, col_c.

Я хочу извлечь все уникальные слова (со словами, разделенными пробелами) из этих трех столбцов длиной не менее 5 символов. Под словом "слово" я подразумеваю любую строку непробельных символов, например, "foo-123" будет словом, как и "099423". Все столбцы являются столбцами InnoDB формата utf8.

Есть ли один запрос для этого?

РЕДАКТИРОВАТЬ: По запросу, вот пример: (в реальных данных 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"]

Меня не волнует порядок результатов. Спасибо!

РЕДАКТИРОВАТЬ: в моем примере выше, все в нижнем регистре, как то, как я могу хранить все в моей реальной таблице, к которой относится этот вопрос. Но, ради аргумента, если бы он содержал некоторую заглавную букву, я бы предпочел, чтобы запрос игнорировал заглавные буквы (это настройка моей конфигурации БД, как это происходит).

EDIT2: если это помогает, все текстовые столбцы имеют индекс FULLTEXT.

EDIT3: вот 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;

Ответы

Ответ 1

Сценарий оболочки может быть эффективным...

  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

Ответ 2

Лучшее решение - не использовать эту структуру для хранения данных и нормализации базы данных в соответствии с обычными формами. Но если вы хотите разбить строки на слова и получить их в виде таблицы, и вы не можете нормализовать базу данных и не можете использовать последнюю версию MYSQL с CTE, вы можете создать простую хранимую процедуру для разделения строк и сохранения их в временный стол. Например, хранимая процедура может выглядеть так:

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

Ответ 3

Используя 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     |
+-----------+

Ответ 4

Как насчет экспорта данных в файл, а затем импортировать его в новую таблицу?

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 INDEX, вы также можете просто прочитать слова из 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 INDEX, "слова", такие как "sarah-jane", будут разделены. Вы можете видеть это в результате:

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

дб-скрипка

Вы также пропустите стоп-слова, как "о".

См. Индексные таблицы InnoDB INFORMATION_SCHEMA FULLTEXT.

Ответ 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

Ответ 6

Из ваших требований к производительности и комментариев видно, что вам нужно регулярно выполнять этот запрос. К сожалению, ваши данные просто не в правильном разрешении, чтобы сделать это аккуратно или кратко

Я хотел бы рассмотреть возможность добавления сводной таблицы, чтобы помочь с окончательным запросом. Поддерживая сводную таблицу, как и при изменении данных в основной таблице, вы сможете упростить ситуацию

Предлагаемый формат для этой сводной таблицы будет

  • summary_table - id, main_table_id, column_name, word

Где main_table_id - это внешний ключ к столбцу идентификатора вашей основной таблицы

Вы также можете разместить составной уникальный индекс (main_table_id, column_name, word)

При редактировании соответствующего значения столбца в основной таблице, вы должны настроить сводную таблицу

  • Удалить существующие слова для main_table_id и column_name
  • Вставьте новый список уникальных слов длиной не менее 5 символов для main_table_id и column_name

Это можно сделать либо на уровне приложения, либо с помощью триггера.

Это сделало бы последний запрос намного проще.

SELECT DISTINCT word
  FROM summary_table

Ответ 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 на случай, если она пригодится кому-то в будущем.

Ответ 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 |
+------------+

Ответ 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 отдельности.

Посмотреть на БД 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 |

Ответ 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. Техника из этого ответа используется для преобразования строки с разделенными значениями в отдельные значения строки. В сочетании с умным приемом из этого ответа он сгенерировал таблицу, состоящую из последовательности возрастающих чисел: в данном случае 0 - 10000.
  4. Использование DISTINCT гарантирует, что ни одно слово не будет повторяться в выходных данных (вместо этого можно использовать GROUP BY если вы хотите считать вхождения). При желании можно использовать ORDER BY для упорядочения слов в алфавитном порядке - но, похоже, вы захотите удалить это, чтобы ускорить процесс.

Ответ 11

Если ваши данные всегда состоят из двух или трех частей, вы можете использовать этот простой и быстрый способ:

CREATE TEMPORARY TABLE temp1
SELECT SUBSTRING_INDEX(p.col_a, ' ', 1) col1 FROM table1 p
UNION
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(p.col_a,' ',2),' ',-1) col1 FROM table1 p
UNION
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(p.col_a,' ',3),' ',-1) col1 FROM table1 p
UNION
SELECT SUBSTRING_INDEX(p.col_b, ' ', 1) col1 FROM table1 p
UNION
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(p.col_b,' ',2),' ',-1) col1 FROM table1 p
UNION
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(p.col_b,' ',3),' ',-1) col1 FROM table1 p
UNION
SELECT SUBSTRING_INDEX(p.col_c, ' ', 1) col1 FROM table1 p
UNION
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(p.col_c,' ',2),' ',-1) col1 FROM table1 p
UNION
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(p.col_c,' ',3),' ',-1) col1 FROM table1 p;

SELECT DISTINCT col1 FROM temp1
WHERE CHAR_LENGTH(col1) >= 5