从mysql中的字符串数组中删除重复的单词

hwamh0ep 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(336)

我有两个varchar列的表-col\u name1和col\u name2

(1, 'hello world', 'hello test'),
(2, 'the stack over', 'over the flow'),
(3, 'hello from my sql fiddle', 'hello my sql');

请参见sqlfiddlehttp://sqlfiddle.com/#!9/cf90c1/1型
我正在寻找一种方法，在两列中找到重复的单词，并从col\u name1中删除这些单词。
这意味着在mysql操作+update+set之后-col\u name1应该包含如下单词

(1, 'world', 'hello test'),
(2, 'stack', 'over the flow'),
(3, 'from fiddle', 'hello my sql');

sql mysql

来源：https://stackoverflow.com/questions/50486095/delete-the-duplicate-words-from-array-of-strings-in-mysql

2条答案

按热度按时间

lp0sw83n1#

如果您使用的是mysql 8.0，那么可以使用递归公共表表达式（recursive common table expressions，cte）对字符串进行迭代以查找所有单词。下面的update语句应该可以完成这项工作：

WITH RECURSIVE
word_boundaries (id, i, start, stop) AS (
  SELECT id, 1, 1, LOCATE(' ', col_name1, 1) FROM table_name
  UNION ALL
  SELECT id, i+1, stop+1, LOCATE(' ', col_name1, stop+1)
  FROM word_boundaries JOIN table_name USING(id) WHERE stop != 0
),
words(id, i, word) AS (
  SELECT id, i, IF(stop != 0,
                   SUBSTRING(col_name1, start, stop-start),
                   SUBSTRING(col_name1, start))
  FROM table_name JOIN word_boundaries USING(id)
),
unique_words(id, i, word) AS (
  SELECT id, i, word FROM words JOIN table_name USING(id)
  WHERE LOCATE(word, col_name2) = 0
),
new_text(id, new_colname1) AS (
  SELECT id, GROUP_CONCAT(word ORDER BY i SEPARATOR ' ')
  FROM unique_words
  GROUP BY id
)
UPDATE table_name
SET col_name1 = 
    (SELECT new_colname1 FROM new_text WHERE id = table_name.id);

此update语句使用多个CTE：
单词边界：标识每个单词的开始和停止位置
单词：col\u name1中的所有单词
唯一单词：对应列名称2中不存在的所有单词
new\u text：通过连接每个行id的唯一\u单词中的所有单词来生成新的列值
如果字符串可能包含超过1000个空格字符，则必须增加cte\u max\u recursion\u depth的值。将table_name.id定义为主键可以在表较大时加快速度。
我认为这个例子展示了CTE在将复杂任务分解为简单步骤方面是多么有用。

赞(0）回复(0）举报 2021-06-20

vddsk6oq2#

以下是解决问题的方法：
解决问题的sql语句：

update table_name x3
join (
select id,replace(group_concat(w),',',' ') w from (SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) w
FROM (SELECT id,concat(col_name1) c FROM table_name) t
INNER JOIN
(
    SELECT 1 + a.i + b.i * 10 x
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
) x
ON (LENGTH(t.c) +1 - LENGTH(REPLACE(t.c, ' ', ''))) >= x.x
group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1)) x2
where not exists (select 1 from (SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) w
FROM (SELECT id,concat(col_name2,' ',col_name1) c FROM table_name) t
INNER JOIN
(
    SELECT 1 + a.i + b.i * 10 x
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
) x
ON (LENGTH(t.c) +1 - LENGTH(REPLACE(t.c, ' ', ''))) >= x.x
group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) 
having count(1) > 1) x1 where x2.id = x1.id and x2.w = x1.w)
group by id
) x
on x3.id = x.id
set x3.col_name1 = x.w;

下面是从示例数据到预期输出的示例：

mysql> create table table_name(id int, col_name1 varchar(200),col_name2 varchar(200));
Query OK, 0 rows affected (0.36 sec)

mysql> insert into table_name values
    -> (1, 'hello world', 'hello test'),
    -> (2, 'the stack over', 'over the flow'),
    -> (3, 'hello from my sql fiddle', 'hello my sql');
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> update table_name x3
    -> join (
    -> select id,replace(group_concat(w),',',' ') w from (SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) w
    -> FROM (SELECT id,concat(col_name1) c FROM table_name) t
    -> INNER JOIN
    -> (
    ->     SELECT 1 + a.i + b.i * 10 x
    ->     FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    ->     CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
    -> ) x
    -> ON (LENGTH(t.c) +1 - LENGTH(REPLACE(t.c, ' ', ''))) >= x.x
    -> group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1)) x2
    -> where not exists (select 1 from (SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) w
    -> FROM (SELECT id,concat(col_name2,' ',col_name1) c FROM table_name) t
    -> INNER JOIN
    -> (
    ->     SELECT 1 + a.i + b.i * 10 x
    ->     FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    ->     CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
    -> ) x
    -> ON (LENGTH(t.c) +1 - LENGTH(REPLACE(t.c, ' ', ''))) >= x.x
    -> group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) 
    -> having count(1) > 1) x1 where x2.id = x1.id and x2.w = x1.w)
    -> group by id
    -> ) x
    -> on x3.id = x.id
    -> set x3.col_name1 = x.w;
Query OK, 3 rows affected (0.13 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from table_name;
+------+-------------+---------------+
| id   | col_name1   | col_name2     |
+------+-------------+---------------+
|    1 | world       | hello test    |
|    2 | stack       | over the flow |
|    3 | from fiddle | hello my sql  |
+------+-------------+---------------+
3 rows in set (0.00 sec)

希望它能解决你的问题。祝你一切顺利！！！

编辑-根据问题所有者的要求：处理大量单词。现在它可以处理多达10000个单词

update table_name x3
join (
select id,replace(group_concat(w),',',' ') w from (SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) w
FROM (SELECT id,concat(col_name1) c FROM table_name) t
INNER JOIN
(
    SELECT 1 + a.i + b.i * 10 x
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d 
) x
ON (LENGTH(t.c) +1 - LENGTH(REPLACE(t.c, ' ', ''))) >= x.x
group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1)) x2
where not exists (select 1 from (SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) w
FROM (SELECT id,concat(col_name2,' ',col_name1) c FROM table_name) t
INNER JOIN
(
    SELECT 1 + a.i + b.i * 10 x
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d 
) x
ON (LENGTH(t.c) +1 - LENGTH(REPLACE(t.c, ' ', ''))) >= x.x
group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) 
having count(1) > 1) x1 where x2.id = x1.id and x2.w = x1.w)
group by id
) x
on x3.id = x.id
set x3.col_name1 = x.w;

赞(0）回复(0）举报 2021-06-20

我来回答

从mysql中的字符串数组中删除重复的单词

2条答案

下面是从示例数据到预期输出的示例：

编辑-根据问题所有者的要求：处理大量单词。现在它可以处理多达10000个单词

相关问题

热门标签

最新问答