mysql - MySQL Regexp 解决方案的 Multi-Like 查询

Question

我希望以下内容有意义。我的查询应该返回列中的字符串值（+ 或 - 5）size不存在于其对应title列中的所有行。

桌子：

 --------------
| size  | title|
 --------------
| 50    | 50ml |
| 75    | 75ml |
| 75    | 50ml |
| 100   | 97ml |
 --------------

控制器：

    $data = [
        'find_all_sizes' => $model
            ->where('CONCAT(brand, " ", title) NOT LIKE CONCAT("%", size - 5, "%")')
            ->where('CONCAT(brand, " ", title) NOT LIKE CONCAT("%", size - 4, "%")')
            ->where('CONCAT(brand, " ", title) NOT LIKE CONCAT("%", size - 3, "%")')
            ->where('CONCAT(brand, " ", title) NOT LIKE CONCAT("%", size - 2, "%")')
            ->where('CONCAT(brand, " ", title) NOT LIKE CONCAT("%", size - 1, "%")')
            ->where('CONCAT(brand, " ", title) NOT LIKE CONCAT("%", size, "%")')
            ->where('CONCAT(brand, " ", title) NOT LIKE CONCAT("%", size + 1, "%")')
            ->where('CONCAT(brand, " ", title) NOT LIKE CONCAT("%", size + 2, "%")')
            ->where('CONCAT(brand, " ", title) NOT LIKE CONCAT("%", size + 3, "%")')
            ->where('CONCAT(brand, " ", title) NOT LIKE CONCAT("%", size + 4, "%")')
            ->where('CONCAT(brand, " ", title) NOT LIKE CONCAT("%", size + 5, "%")')
            ->groupBy('id')
            ->findAll()
    ];

这很好用 - 但我猜可能有更好的方法通过使用正则表达式来做到这一点，这是我真正需要帮助的地方。

标签： mysqlregexquery-builderregexp-like

Answer 1

你可以试试：

$data = [
        'find_all_sizes' => $model
            ->where("abs(size - cast(replace(title, 'ml', '') as signed)) < 5")
            ->groupBy('id')
            ->findAll()
    ];

在 SQL 中，一个 select * 查询看起来像这样，假设ml标题中唯一的文本不是数字：

select * from t
where abs(size - cast(replace(title, 'ml', '') as signed)) < 5;

+------+-------+
| size | title |
+------+-------+
|   50 | 50ml  |
|   75 | 75ml  |
|  100 | 97ml  |
+------+-------+

如果您的标题也可以包含其他字符串，您可以创建一个类似此处指定的函数：如何从 MySQL 查询中的字符串中提取数值？然后运行

select * from t
where abs(size - cast(digits(title) as signed)) < 5;

你可能会侥幸逃脱

select * from t
where abs(size - cast(title as signed)) < 5;

或者

$data = [
        'find_all_sizes' => $model
            ->where("abs(size - cast(title as signed)) < 5")
            ->groupBy('id')
            ->findAll()
    ];