如何匹配逗号分隔的字符串，而不管它们在 Mysql 中的位置顺序 [英] How can match a string of comma separated , irrespective of their position order in Mysql

问题描述

问题:

我想测试一组值是否等于另一组值，但不必它们的位置顺序相同.例如:'a,b,c,d' 必须等于 'b,a,c,d'

I want to test a set of values is equal to another set but not necessary their order of position will be same. For example: 'a,b,c,d' must be equal to 'b,a,c,d'

我尝试过的:

我已经尝试了 IN 子句并且我已经检查了 FIND_IN_SET.

I have tried IN Clause and I have checked with FIND_IN_SET.

SELECT 'a,b,c,d' IN 'b,c,a,d';

他们俩都做不了这个工作.

Both of them can not do this work.

如果有人能提供帮助，将不胜感激.

Will be thankful if anyone can help.

谢谢桑迪普

推荐答案

这演示了将值拆分为多行的使用，GolezTrol 提到与 FIND_IN_SET 结合使用，修改为要使用的函数形式如下:

This demonstrates the use the splitting of values to multiple rows, mentioned by GolezTrol in combination with FIND_IN_SET, modified to function to be used in forms like:

SELECT are_sets_equal(col_with_set, 'a,b,d,c') FROM example;

或

SELECT * FROM example
WHERE are_sets_equal(col_with_set, 'a,b,d,c')

想法是这样的:

• 将第一个集合拆分为一个临时表
• 检查在第二组中找到了多少这些值.
• 如果这个计数等于两个集合中元素的个数，那么这两个集合是相等的
• 如果两个集合相等，函数将返回 1，如果集合不同，则返回 0.

两个集合的限制都是 1000 个值，但可以轻松扩展:

The limit for both sets is 1000 values, but could be expanded easily:

DELIMITER //
CREATE FUNCTION are_sets_equal(set_a VARCHAR(2000), set_b VARCHAR(2000)) RETURNS BOOLEAN
BEGIN
  DECLARE is_equal BOOLEAN;
  DECLARE count_a INT;
  DECLARE count_b INT;

  -- calculate the count of elements in both sets 
  SET count_a = 1 + LENGTH(set_a) - LENGTH(REPLACE(set_a, ',', ''));
  SET count_b = 1 + LENGTH(set_b) - LENGTH(REPLACE(set_b, ',', ''));

  SELECT
    -- if all elements of the first set are contained in the second
    -- set and both sets have the same number of elements then both
    -- sets are considered equal
    COUNT(t.value) = count_a AND count_a = count_b INTO is_equal
    FROM (
      SELECT
        SUBSTRING_INDEX(SUBSTRING_INDEX(e.col, ',', n.n), ',', -1) value
      FROM ( SELECT set_a AS col ) e
      CROSS JOIN(
        -- build for up to 1000 separated values
        SELECT 
            a.N + b.N * 10 + c.N * 100 + 1 AS n
        FROM
            (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
           ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
           ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
        ORDER BY n
    ) n
      WHERE n.n <= count_a
    ) t
    WHERE FIND_IN_SET(t.value, set_b);

    return is_equal;
END //
DELIMITER ;

说明

建立数字表格

SELECT 
    a.N + b.N * 10 + c.N * 100 + 1 AS n
FROM
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
ORDER BY n

使用从 1 到 1000 的值动态构建一个数字表.如何将其扩展到更大的范围应该是显而易见的.

builds a number table with the values from 1 to 1000 on the fly. How to expand this to a greater range should be obvious.

注意这样的数字表可以包含在您的数据库中，因此无需即时创建.

Note Such a numbers table could be contained in your database, so there would be no need to create one on the fly.

将集合拆分为表格

借助这个数字表，我们可以将值列表拆分为一个表，使用嵌套的 SUBSTRING_INDEX 调用从列表中一个接一个地剪切一个值，如 SQL 将值拆分为多行:

With the help of this number table we can split the value list to a table, using nested SUBSTRING_INDEX calls to cut just one value after the other from the list as mentioned in SQL split values to multiple rows:

SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(t.col, ',', n.n), ',', -1) value
FROM (SELECT @set_a as col ) t CROSS JOIN (
    -- build for up to 100 separated values
    SELECT 
        a.N + b.N * 10 + c.N * 100 + 1 AS n
    FROM
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
       ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
       ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
    ORDER BY n
) n
WHERE 
    n <= 1 + LENGTH(@set_a) - LENGTH(REPLACE(@set_a, ',', ''))

计算集合的元素

我们通过WHERE 子句中的表达式获得列表中元素的计数:我们的值比分隔符的出现次数多一个.

We get the count of elements in the list by the expression in the WHERE clause: we have one more values than occurences of the separator.

然后我们通过使用 FIND_IN_SET 在第二组中搜索这些值来限制结果.

Then we restrict the result by searching those values in the second set with FIND_IN_SET.

作为最后一步，我们根据两个集合中的值计数检查结果中的值计数并返回该值.

As a last step we check count of values in the result against the count of values in both sets and return this value.

演示

使用这个演示进行实验.

这篇关于如何匹配逗号分隔的字符串，而不管它们在 Mysql 中的位置顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！