如何在MYSQL中使用REGEXP匹配数组中的日期？

Rohan 发表于 Dev

罗汉

我试图匹配很多年以来从表的publication_date开始的年份。我正在使用laravel（PHP）。

我写过类似的东西：

if($this->request->has('publication_years')){
    $years = implode('-[0-9]{2}-[0-9]{2}|', explode(',', $this->request->get('publication_years'))) . '-[0-9]{2}-[0-9]{2}';

    $model = $model->whereExists(function ($query) use ($years)
    {
        $query->select(DB::raw(1))
            ->from('patent')
            ->whereRaw('patent.id = document.documentable_id')
            ->whereRaw('document.documentable_type = "patent"')
            ->whereRaw('(patent.publication_date REGEXP (' . $years . '))');
    });
}

岁月如来?publication_years=2015,2016。我使用,定界符将它们爆炸，然后将其内爆以使其与日期匹配的方式制作REGEXP模式。此后多年的倾倒就像2015-[0-9]{2}-[0-9]{2}|2016-[0-9]{2}-[0-9]{2}。

这在http://regexr.com/上有效，但是我仍然遇到错误，因此我猜还有MYSQL的其他语法。我看到的错误是：

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[0-9]{2}-[0-9]{2}|2016-[0-9]{2}-[0-9]{2})))' at line 1 (SQL: select count(*) as aggregate from `document` where exists (select * from `folder` where `document`.`folder_id` = `folder`.`id` and (exists (select * from `user` inner join `accessible_by_user` on `user`.`id` = `accessible_by_user`.`user_id` where `accessible_by_user`.`accessible_id` = `folder`.`id` and `accessible_by_user`.`accessible_type` = folder and `user_id` = 1) or exists (select * from `role` inner join `accessible_by_role` on `role`.`id` = `accessible_by_role`.`role_id` where `accessible_by_role`.`accessible_id` = `folder`.`id` and `accessible_by_role`.`accessible_type` = folder and `role_id` in (1)))) and exists (select 1 from `patent` where patent.id = document.documentable_id and document.documentable_type = "patent" and (patent.publication_date REGEXP (2015-[0-9]{2}-[0-9]{2}|2016-[0-9]{2}-[0-9]{2}))))

我究竟做错了什么？我该如何纠正？

威克多·斯特里比尤

这是一种准备在MySQL中使用的正则表达式的方法：

$this_request_get_publication_years = "2015,2016,2017";
$years = '^(' . implode('|', explode(',',      
$this_request_get_publication_years)) . ')-[0-9]{2}-[0-9]{2}';
echo "(patent.publication_date REGEXP '" . $years . "')";
// => (patent.publication_date REGEXP '^(2015|2016|2017)-[0-9]{2}-[0-9]{2}')

该REGEXP '^(2015|2016|2017)-[0-9]{2}-[0-9]{2}'只取记录如下：