MySQL WHERE — подстановочный знак для «игнорирования» вариант?

Question

MySQL WHERE — подстановочный знак для «игнорирования» вариант?

Из-за устаревшей проблемы проектирования я пытаюсь найти наиболее эффективный способ решения этой проблемы. Используя СТОЛ с двумя полями:
pk int(10), year timestamp Где «pk» является первичным ключом, но также используется в поиске номера документа.

Данные, которые я получаю от пользователя, могут входить как целое число, или как комбинация элементов, предшествующих целому числу. Например:

Номер документа, с которого приходит пользователь: "FM" + Last two digits of the year + "-A" + pk.
Таким образом, пример формы с 2015 года с первичным ключом 1025 будет FM15-A1025.

Используя AJAX, пользователь может искать любое из следующего, чтобы искать эту (и другие) формы:

F
FM
FM1
FM15
FM15-
FM15-A
FM15-A1
FM15-A10
FM15-A102
FM15-A1025

Мой вопрос заключается в том, как, используя комбинацию MySQL и PHP, я могу вернуть все значения PK, применимые к этому поиску? Очевидно, что «F» и «FM» вернут все значения, но компонент года 2015 даст другой ответ, нежели 2020.

Мне неизвестен подстановочный знак или метод MySQL, который позволяет игнорировать части столбца в поиске, и для этого простого поиска требуется много PHP (в процессе используется много ресурсов ЦП / памяти). В настоящее время мой PHP выполняет свою работу, но он невероятно сложен для такой простой задачи:

$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

Есть ли способ лучше?

2

mysql php wildcard

Решение

Другие решения

Использование MySQL String-Operator LIKE, что позволяет % подстановочный знак, вы можете разделить строку и уточнить критерии поиска.

$sql_where = "";
$search = "FM15-A1025";

if (strlen($search) >= 3) {
$y = int(substr($search, 2, 2));
$sql_where .= "DATE_FORMAT(year,'%y') LIKE '$y%'";
}
if (strlen($search) >= 7) {
$pk = int(substr($search, 6));
$sql_where .= " AND CAST(pk as char) LIKE '$pk%'";
}

Обратите внимание, что LIKE имеет плохую производительность, и склеивание строк SQL может быть опасным, если вы не уделяете дополнительного внимания избеганию SQL-инъекций.

1

Источник

Accepted Answer

Я бы предложил создать представление SQL, чтобы сделать вашу жизнь намного проще.

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.

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%'

Если это не работает, я рекомендую сохранить идентификатор документа в виде столбца в таблице для быстрого поиска. Или, если обновление таблицы не вариант, создайте материализованный взгляд для запроса, как предлагает @rlanvin.

2