带有存储过程逗号分隔值输入参数的MySQL类IN

Question

我正在创建一个存储过程来搜索用户的城镇。用户将从用户界面中选择将发送到存储过程的多个城镇。存储过程的城镇参数的示例输入类似于orangi,faisal,nazimabad。

存储在表中的城镇是

• 奥兰吉
• 可兰吉
• 沙·费萨尔
• 沙拉-费萨尔
• 纳兹马巴德
• 北纳兹马巴德

现在，我想从输入中选择所有上述记录。也就是说，我想从存储过程的参数中使用LIKE运算符和IN。

我不知道投入中会有多少个城镇。可能只是上面提到的一、二、三或所有这些。

我想针对那个查询搜索所有的城镇

示例存储过程是

CREATE PROCEDURE `GetUsersByTown`(
IN `SearchTowns` VARCHAR(200) -- `ORANGI,FAISAL,NAZIMABAD`
)
BEGIN
      SELECT
      name,
      Town,
      FROM userLocation where Town in (SearchTowns)
END

预期结果

• 名字-城镇
• 哈姆扎-奥兰吉
• 贾马尔-科兰吉
• 哈沙姆--沙阿·费萨尔
• Aliyan --Shahrah-
• 丹麦人-纳兹马巴德
• 法鲁克--北纳兹马巴德

我尝试过使用find_in_set()，但是FIND_IN_SET不接受像%这样的通配符，也不使用索引？

我也考虑过使用REGEXP ValueA|ValueB，但是我不知道如何在REGEXP中放置输入参数，即逗号分隔的值。

Answer 1

这是一个存储过程，它将输入的逗号分隔字符串(SearchTowns)拆分为单独的行，将这些行插入临时表(城镇)，然后在所需的条件下使用userLocation和This表之间的联接(其中userLocation.town就像城镇表中的%userLocation.town%)。与此条件不相对应的所有其他行都将从最终的结果集中删除。

此外，我还使用了DISTINCT来避免行重复，以防userLocation.town对应于城镇表中的多行。例如，如果SearchTowns包含'Orangi‘和' Korangi’，那么位于korangi的用户将与两个城镇连接(因为'korangi‘类似于'%orangi%')。

CREATE PROCEDURE GetUsersByTown(SearchTowns VARCHAR(200))

    BEGIN
      DECLARE pos INT Default 0 ;
      DECLARE str VARCHAR(200);
      CREATE TEMPORARY TABLE Towns(town VARCHAR(200));
      #Loop through comma-separated values
      #pos - is the number of current word within search string
      simple_loop: LOOP
         SET pos=pos+1;
         SET str=REPLACE(SUBSTRING(SUBSTRING_INDEX(SearchTowns, ',', pos),
                    LENGTH(SUBSTRING_INDEX(SearchTowns, ',', pos -1)) + 1),
                    ',', '');
         IF str='' THEN
            LEAVE simple_loop;
         END IF;
         #Do Inserts into temp table here with str going into the row
         insert into Towns values (str);
    END LOOP simple_loop;
    SELECT DISTINCT
      u.name,
      u.Town
    FROM userLocation AS u
    JOIN Towns AS t ON u.Town LIKE CONCAT('%', t.town, '%');
   DROP TEMPORARY TABLE Towns;
END