php - 剖析数字范围

Question

我正在尝试找出生成 WHERE 查询的最有效方法。我之前问过另一个类似的问题，但我会在这个问题上开门见山。

给定一组数字范围，即 1-1000、1500-1600，创建一个 mysql where 条件来选择这些值之间的记录非常简单.

即，你只需要做:

WHERE (lft BETWEEN 1 and 1000) OR (lft BETWEEN 1500-1600)。但是，如果您还想合并一个 NOT BETWEEN 怎么办。

例如，如果您定义了多个规则，例如...

• 允许 1 - 1000
• 允许在 1500 - 1600 之间
• 允许在 1250 - 1300 之间
• 拒绝 25 - 50

如何合并这些规则以有效地生成 WHERE 条件。我希望 WHERE 剖析 ALLOW BETWEEN 1 - 1000 以便在其中创建一个间隙。这样它将变为 1-24 和 51-1000。因为 DENY 规则是在第一条规则之后定义的，所以它“覆盖”了前面的规则。

再举个例子，说你有

• 允许 5 - 15 人
• 拒绝 10 - 50
• 允许 45 - 60 岁

然后我想生成一个允许我做的 WHERE 条件:

WHERE (lft BETWEEN 5 and 9) OR (lft BETWEEN 45 and 60)。

注释(编辑)

• 此外，允许的最大范围是 1 - 5600000。(即“地球”)。允许地球上的一切。
• 数字范围实际上是 NESTED SET MODEL 中的 LEFT 值。这些不是唯一的键。您可以在我之前提出的这个问题中了解我为什么要这样做。 https://stackoverflow.com/questions/6020553/generating-a-mysql-between-where-condition-based-on-an-access-ruleset
• 关于我的数字范围的可能重要说明 我可能不应该使用我所做的示例，但关于数字范围性质的一个重要说明是，范围实际上应该总是完全消耗或被先前的规则消耗。比如我用上面的例子，10-50允许，拒绝45-60。这实际上不会在我的数据集中发生。它实际上是，allow 10-50，然后 DENY 将必须完全被该范围消耗，即 34-38。或者，完全使用以前的规则。 9-51。这是因为范围实际上表示嵌套集合模型中的 lft 和 rgt 值，并且您不能像我介绍的那样有重叠。

我在问这个问题的时候没想到要提到这一点，但是在看到下面的工作示例代码之后，我可以看出这个注释实际上很重要。

(根据下面的评论编辑示例 mysql 以包含 OR 而不是 AND)

Answer 1

老实说，何必呢？只要您要查询的键已编入索引，只需将多个查询放在那里:

WHERE (foo BETWEEN 1 AND 1000 
        OR foo BETWEEN 1500 AND 1600
        OR foo BETWEEN 1250 AND 1300
    ) AND (
        foo NOT BETWEEN 25 AND 50
    )

您可以通过构建解析器来稍微提高效率，但我会质疑这样做是否值得。所有 WHERE 子句项目都将脱离索引，因此您不会阻止任何硬操作的发生(这意味着您不会通过这样做来停止全表扫描)。

因此，与其花时间构建一个系统来为您做这件事，不如实现一个简单的解决方案(OR将 Allows 组合在一起，AND将 Denys 组合在一起)并继续做更重要的事情。然后，如果它以后成为问题，请重新访问它。但我真的不认为这会成为一个太大的问题......

编辑 好的，这是一个非常简单的算法。它使用字符串作为数据存储，因此它对于较小的数字(低于 100 万)相当有效:

class Dissector {
    protected $range = '';
    public function allow($low, $high) {
        $this->replaceWith($low, $high, '1');
    }
    public function deny($low, $high) {
        $this->replaceWith($low, $high, '0');
    }
    public function findRanges() {
        $matches = array();
        preg_match_all(
            '/(?<!1)1+(?!1)/', 
            $this->range, 
            $matches, 
            PREG_OFFSET_CAPTURE
        );
        return $this->decodeRanges($matches[0]);
    }
    public function generateSql($field) {
        $ranges = $this->findRanges();
        $where = array();
        foreach ($ranges as $range) {
            $where[] = sprintf(
                '%s BETWEEN %d AND %d', 
                $field, 
                $range['from'], 
                $range['to']
            );
        }
        return implode(' OR ', $where);
    }
    protected function decodeRanges(array $matches) {
        $range = array();
        foreach ($matches as $match) {
            $range[] = array(
                'from' => $match[1] + 1, 
                'to' => ($match[1] + strlen($match[0]))
            );
        }
        return $range;
    }
    protected function normalizeLengthTo($size) {
        if (strlen($this->range) < $size) {
            $this->range = str_pad($this->range, $size, '0');
        }
    }
    protected function replaceWith($low, $high, $character) {
        $this->normalizeLengthTo($high);
        $length = $high - $low + 1;
        $stub = str_repeat($character, $length);
        $this->range = substr_replace($this->range, $stub, $low - 1, $length);
    }
}

用法:

$d = new Dissector();
$d->allow(1, 10);
$d->deny(5, 15);
$d->allow(10, 20);
var_dump($d->findRanges());
var_dump($d->generateSql('foo'));

生成:

array(2) {
  [0]=>
  array(2) {
    ["from"]=>
    int(1)
    ["to"]=>
    int(4)
  }
  [1]=>
  array(2) {
    ["from"]=>
    int(10)
    ["to"]=>
    int(20)
  }
}
string(44) "foo BETWEEN 1 AND 4 OR foo BETWEEN 10 AND 20"