php - MySQL WHERE - "ignore"选项的通配符？

Question

由于遗留设计问题，我正在尝试找出解决此问题的最有效方法。使用具有两个字段的 TABLE:pk int(10), year timestamp 其中“pk”为主键，也用于被查找的文档编号。

我从用户那里收到的数据可能是那个整数，也可能是整数前面的项目的组合。例如:

用户来自的文档编号:"FM"+ 年份的最后两位数 + "-A"+ pk.因此，主键为 1025 的 2015 年示例表单将是 FM15-A1025。

使用 AJAX，用户可以搜索以下任何内容来查找此(和其他)表单:

FFMFM1FM15FM15-FM15-AFM15-A1FM15-A10FM15-A102FM15-A1025

My question is, how, with a combination of MySQL and PHP, can I return all of the PK values that apply to this search? Obviously, "F" and "FM" will return all values, but the year component will 2015 will give a different answer than 2020.

I am not aware of a MySQL wildcard character or method that allows you to ignore parts of the column in a search, and there is a lot of PHP (using a lot of CPU/memory in the process) for this simple look-up. Currently, my PHP gets the job done, but it is incredibly complex for such a simple task:

$cleansearch = strtoupper(filter_var($_POST['search']['value'],FILTER_SANITIZE_STRING));

$searchid = $searchyear ="";
if ($cleansearch==="p" || $cleansearch==="pk"){
   $searchid = " pk LIKE '%' ";
} else if (strlen($cleansearch) === 3) {
   for($i = 0; $i<10;  $i++   ) {
        $test = "IR".$i;
        if($test===$cleansearch){
            $searchid= "pk LIKE '%' ";
            $searchyear= " AND LEFT(DATE_FORMAT(year,'%y'), 1) =$i ";
        }       
    }
} elseif ((strlen($cleansearch) === 4) || (
    ((strlen($cleansearch)=== 5 && "-"===substr($cleansearch,4,1)) ||
    (strlen($cleansearch)=== 6 && "-D"===substr($cleansearch,4,2))))){
    $cleansearch=substr($cleansearch,0,4);
    for($i = 10; $i<100;  $i++   ) {
        $test = "IR".$i;
        if($test===$cleansearch){
            $searchid= "pk LIKE '%' ";
            $searchyear= " AND DATE_FORMAT(year,'%y') =$i ";
        }
    }
} elseif (strlen($cleansearch) > 6 && "-D"===substr($cleansearch,4,2) ) {
    for($i = 10; $i<100;  $i++ ) {
        $test = "IR".$i;
        if($test===substr($cleansearch,0,4) ){   
            $searchyear= " AND DATE_FORMAT(year,'%y') =$i ";
        }
    }
    $searchid= " LPAD(pk,4,'0') LIKE '".substr($cleansearch,6)."%' ";
}
if(!empty($searchid)){
   $wheresearchterm = " OR (".$searchid . $searchyear.") ";
} else {
   $wheresearchterm = "";
}
$query = "SELECT pk, year FROM myTable WHERE 1 $wheresearchterm;";//The search query

有没有更好的办法？

Answer 1

我建议制作一个 SQL View ，让您的生活更轻松。

CREATE VIEW DocumentIdView AS
SELECT CONCAT('FM', DATE_FORMAT(year,'%y'), '-A', pk) AS docId, pk FROM table

然后在你的 PHP 代码中，你可以做一个

SELECT b.* FROM DocumentIdView a LEFT JOIN table b ON a.pk = b.pk WHERE a.docId LIKE 'FM15-A1%'

显然，如果您不想使用 SQL View ，可以将这两个选择结合起来。

SELECT * FROM (SELECT CONCAT('FM', DATE_FORMAT(year,'%y'), '-A', pk) AS docId, pk FROM table) a LEFT JOIN table b ON a.pk = b.pk WHERE a.docId LIKE 'FM15-A1%'

如果效果不佳，我建议将文档 ID 作为一个列存储在您的表中以便快速查找。或者，如果更新表不是一个选项，请创建一个 materialized view对于@rlanvin 建议的查询。