检查SQL数组中的多个值

I need to check if one (or more) of several values is present in a SQL array. In its simplest form it would look something like this:

我需要检查SQL数组中是否存在多个值中的一个(或多个)。在最简单的形式,它看起来像这样:

SELECT
  (1, 7, 8) IN (1, 2, 3, 4, 5)
FROM DUAL

Obviously this statement won't work. It only works if I check for just one value in the array. But I need to know if one (not all!) of the values I supply is present in the SQL array. The statement should return TRUE if one or more values are present in the array and FALSE if none is.

显然这句话不起作用。它只有在我检查数组中的一个值时才有效。但我需要知道我提供的值中是否有一个(不是全部!)存在于SQL数组中。如果数组中存在一个或多个值,则该语句应返回TRUE,否则返回FALSE。

I realize I could just add several checks, like this:

我意识到我可以添加几个检查,如下所示:

SELECT
   1 IN (1, 2, 3, 4, 5)
OR 7 IN (1, 2, 3, 4, 5)
OR 8 IN (1, 2, 3, 4, 5)
FROM DUAL

My programming logic however supplies the values in array format "(1, 7, 8)" and the amount of values in this array differs every time i need to run the SQL statement. It would be very handy if I could somehow just paste this array into my existing SQL statement instead of rebuilding the SQL statement on every run, and create the "OR" statements for every value in the array.

然而,我的编程逻辑以数组格式“(1,7,8)”提供值,并且每次需要运行SQL语句时,此数组中的值的数量都不同。如果我能以某种方式将此数组粘贴到我现有的SQL语句中而不是在每次运行时重建SQL语句,并为数组中的每个值创建“OR”语句,那将非常方便。

Is there a way to do this? I'm using MySQL btw.

有没有办法做到这一点?我正在使用MySQL btw。

Thanx in advance, keep up the good programming!

Thanx提前,保持良好的编程!

2 个解决方案

#1

You can use REGEXP with comma separated strings. You'll have to convert the values first to strings.

您可以将REGEXP与逗号分隔的字符串一起使用。您必须先将值转换为字符串。

SELECT REPLACE(CONCAT(",", "1, 7, 8", ",")," ", "")
       REGEXP CONCAT(",", REPLACE(REPLACE("1 ,2 , 3 , 4 ,5 " ," ", ""),",", ",|,"), ",")
FROM DUAL

Explanation:

This works only with comma-separated strings, not lists as in your example.

这仅适用于以逗号分隔的字符串,而不适用于示例中的列表。

The statement removes any spaces from the strings.

该语句从字符串中删除任何空格。

Commas are added at both ends of the strings to avoid partial matches.

在字符串的两端添加逗号以避免部分匹配。

In the regex pattern all commas are replaced by comma + pipe-sign + comma, so it works as an OR , while avoiding partial matches.

在正则表达式模式中,所有逗号都用逗号+管道符号+逗号替换,因此它用作OR,同时避免部分匹配。

#2

Can you use a simple join creating a dynamic/temporary table and making a simple join with the other values?

您是否可以使用简单连接创建动态/临时表并与其他值进行简单连接?

create table a (
id int);

insert into a values (1),(7),(8);

create table b like a;

insert into b values (1),(2),(3),(4),(5);


select a.* from a
inner join b
on a.id = b.id

#1