代码之家 › 专栏 › 技术社区 › Conspicuous Compiler

检查值列表是否在范围表中

perl mysql

Conspicuous Compiler · 技术社区 · 14 年前

我正在检查整数列表中是否有任何一个属于范围列表。范围是在一个定义如下的表中定义的:

#   Extra   Type    Field       Default Null    Key 
0           int(11) rangeid     0       NO      PRI 
1           int(11) max         0       NO      MUL 
2           int(11) min         0       NO      MUL

使用MySQL5.1和Perl5.10。

我可以用如下语句检查单个值(比如7)是否在任何范围内

SELECT 1
  FROM range
  WHERE 7 BETWEEN min AND max

如果7在这些范围中的任何一个,我会得到一行返回。如果不是,则不返回任何行。

现在我有一个列表,比如说,50个这些值,目前没有存储在一个表中。我用 map :

my $value_list = '('
  . ( join ', ', map { int $_ } @values )
  . ')'
  ;

我想看看列表中的任何项目是否在任何范围内,但并不特别关心哪个数字,哪个范围。我想使用如下语法:

SELECT 1
  FROM range
  WHERE (1, 2, 3, 4, 5, 6, 7, 42, 309, 10000) BETWEEN min AND max

mysql很好地惩罚了我这样的语法:

Operand should contain 1 column(s)

我屏平 #mysql 很有帮助。然而,在他们回答的时候,我已经写了这篇文章,并且认为用一种更持久的媒介来解决这个问题会很有帮助,我想我无论如何都会把这个问题贴出来。或许可以提供不同的解决方案?

3 回复 | 直到 14 年前

Eric Strom 14 年前

您可以用Perl构造一个SQL查询,该查询将使用多个值,如下所示:

sub check_range {
    'SELECT 1 FROM range WHERE ' .
        join ' OR ' =>
        map "($_ BETWEEN min AND max)" => @_
}

print check_range( 1, 2, 3, 4, 5, 6, 7, 42, 309, 10000 ), "\n";

> SELECT 1 FROM range WHERE (1 BETWEEN min AND max) OR (2 BETWEEN min AND max)
> OR (3 BETWEEN min AND max) OR (4 BETWEEN min AND max) ...

Jamie McCarthy 14 年前

这听起来是个有趣的问题。我创建了一个这样的测试范围表:

CREATE TABLE `test_ranges` (
  `rangeid` int(11) NOT NULL,
  `max` int(11) NOT NULL,
  `min` int(11) NOT NULL,
  PRIMARY KEY  (`rangeid`),
  KEY `idx_minmax` (`min`,`max`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

我在该表中插入了50000行,每个行的最大值最小值为10,如下所示:

mysql> select * from test_ranges limit 2;
+---------+-----+-----+
| rangeid | max | min |
+---------+-----+-----+
|       1 |  15 |   5 | 
|       2 |  20 |  10 | 
+---------+-----+-----+
2 rows in set (0.00 sec)

获取与整数列表匹配的范围的Perl代码是创建一个临时表来保存整数,并让MySQL为我进行匹配:

$DB->do_sql("CREATE TEMPORARY TABLE test_vals ( val int NOT NULL ) ENGINE=InnoDB");
for (12, 345, 394, 1450, 999, 9999, 99999, 999999 ) {
  $DB->do_sql("INSERT INTO test_vals VALUES (?)", $_);
}
$answer = $DB->do_sql("SELECT DISTINCT * from test_vals, test_ranges WHERE val BETWEEN min AND max");

这会给我返回正确的列表。在mysql客户机中:

mysql> SELECT DISTINCT * from test_vals, test_ranges WHERE val BETWEEN min AND max;
+-------+---------+--------+-------+
| val   | rangeid | max    | min   |
+-------+---------+--------+-------+
|    12 |       1 |     15 |     5 | 
|    12 |       2 |     20 |    10 | 
|   345 |      67 |    345 |   335 | 
|   345 |      68 |    350 |   340 | 
|   345 |      69 |    355 |   345 | 
|   394 |      77 |    395 |   385 | 
|   394 |      78 |    400 |   390 | 
|  1450 |     288 |   1450 |  1440 | 
|  1450 |     289 |   1455 |  1445 | 
|  1450 |     290 |   1460 |  1450 | 
|   999 |     198 |   1000 |   990 | 
|   999 |     199 |   1005 |   995 | 
|  9999 |    1998 |  10000 |  9990 | 
|  9999 |    1999 |  10005 |  9995 | 
| 99999 |   19998 | 100000 | 99990 | 
| 99999 |   19999 | 100005 | 99995 | 
+-------+---------+--------+-------+
16 rows in set (0.00 sec)

或者,对于匹配值列表:

mysql> SELECT DISTINCT val from test_vals, test_ranges WHERE val BETWEEN min AND max;
+-------+
| val   |
+-------+
|    12 | 
|   345 | 
|   394 | 
|   999 | 
|  1450 | 
|  9999 | 
| 99999 | 
+-------+
7 rows in set (0.00 sec)

MySQL(至少5.0,我在上面)通过解释说它没有以正常的方式使用索引进行比较。但是,它报告说 Range checked for each record 哪一个 essentially 意味着它会像你想的那样:处理来自 test_vals 表作为常量,并在 test_ranges 使用索引的表 idx_minmax .

mysql> explain SELECT DISTINCT * from test_vals, test_ranges WHERE val BETWEEN min AND max \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_vals
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8
        Extra: Using temporary
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_ranges
         type: ALL
possible_keys: idx_minmax
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 48519
        Extra: Range checked for each record (index map: 0x2)
2 rows in set (0.00 sec)

它很快,但我不知道你会比我测试的8和50K多出多少行。我的猜测是,如果要查找的值超过一小部分,那么创建这样的临时表将是最佳解决方案。

DVK 14 年前

老实说,如果要检查的列表是单数字大小的,我可以在Perl中逐个检查(检查是您的查询),或者如果您担心连接/查询启动开销,请将它们填充到临时表中,然后在SQL循环中循环,一次将1个cValue提取到变量中,删除该va从临时表中退出,然后再次运行——在循环内部,您自己对该变量进行一次检查查询。

这里是Sybase代码-希望它能轻松地转换为MySQL

-- previously, CREATE TABLE #your_temp_table (num int)
CREATE TABLE #in_range (num int)
DECLARE @seven int -- This is a JOKE! NEVER use a variable name like that!!!
WHILE (exists (select 1 from #your_temp_table)) 
BEGIN
    SELECT @seven = min(num) from #your_temp_table
    DELETE #your_temp_table WHERE num = @seven
    INSERT #in_range
        SELECT @seven
        FROM range
        WHERE @seven BETWEEN min AND max
END
SELECT num from #in_range
DROP TABLE #in_range

我觉得这可以做得更优雅,但这至少在一个更好的解决方案的脓肿中起作用:)