我在MySQL数据库中有大约500万行与以下系统匹配
n1-n2-n3-n4-n5(例如8-23-43-12-3),其中每个数字对于该序列是唯一的。此外,每个数字的范围从1到99。
我需要一种方法来检索MySQL数据库中五个数字中有三个匹配的所有行。例如,用户输入4-23-65-82-3,我想返回所有共享输入的五个数字中任意三个的行,因此4-65-12-49-82匹配、34-23-76-3-65匹配等。
我的表格如下:
number_table
(id
、uuid
、five_numbers
、first_number
、second_number
、third_number
、fourth_number
、fifth_number
)
到目前为止我尝试过的:
-
循环遍历每一行,并匹配
first_number
为用户提交的五个数字中的任何一个的任何一行。这太不聪明了! -
与MySQL中的RegExp匹配,但这需要一个包含所有可能组合的SQL语句。这并不太聪明,但如果有人有一个更高效的MySQL
REGEXP
,我可以使用它。
我本质上是在寻找一个SQL解决方案,因为我不必担心内存耗尽,SQL执行会更快,我想把它放在一个视图中,这样我就可以在上面执行一些命令。
我为这篇冗长的帖子道歉,但我想提供同样多的信息。
谢谢!
假设用户的输入为n1、n2、n3、n4、n5,则可以编写如下sql:
select id, uuid, five_numbers from
(select *,
if(first_number in (n1,n2,n3,n4,n5), 1, 0) +
if(second_number in (n1,n2,n3,n4,n5), 1, 0) +
if(third_number in (n1,n2,n3,n4,n5), 1, 0) +
if(fourth_number in (n1,n2,n3,n4,n5), 1, 0) +
if(fifth_number in (n1,n2,n3,n4,n5), 1, 0) as total
from number_table) as t
where total >= 3
因此,如果这五个数字中有任何一个有这个值,它就会在总数上加1。然后,您可以用包含3个或更多用户输入的数字来过滤您的行
快速答案是:
SELECT * FROM
(SELECT *,
five_numbers REGEXP '^3-|-3-|-3$' as n1,
five_numbers REGEXP '65' as n2,
five_numbers REGEXP '82' as n3
FROM `number_table`) as t
WHERE t.n1+t.n2+t.n3=3
如果你需要,我可以用PDO设置php代码,只需询问
MySQL FIELD
函数在这里很有用:
SELECT id, uuid, five_numbers,
FIELD(A, first_number , second_number , third_number , fourth_number , fifth_number) > 0 as aFound,
FIELD(B, first_number , second_number , third_number , fourth_number , fifth_number) > 0 as bFound,
FIELD(C, first_number , second_number , third_number , fourth_number , fifth_number) > 0 as cFound,
FIELD(D, first_number , second_number , third_number , fourth_number , fifth_number) > 0 as dFound,
FIELD(E, first_number , second_number , third_number , fourth_number , fifth_number) > 0 as eFound
FROM number_table
WHERE
(
first_number in (A, B, C, D, E)
OR second_number in (A, B, C, D, E)
OR third_number in (A, B, C, D, E)
)
AND aFound + bFound + cFound + dFound + eFound >= 3;
FIELD
函数返回在其中查找第一个参数的参数的索引,或者返回0。如果选择(n > 0)
作为参数,它将解析为TRUE
,MySQL将其视为1
的整数值。因此,您可以将它们添加在一起以检查值,而无需执行子查询。通过对各个字段执行"in"检查,您可以获得一些额外的性能,这样就不必针对整个表行集计算函数值。不过,你只需要检查前三个字段,因为如果它至少有一个字段不匹配,它就不可能匹配五个数字中的三个。
也可以简单地选择
SELECT *
FROM number_table
WHERE
((LOCATE(A, five_numbers) > 0) +
(LOCATE(B, five_numbers) > 0) +
(LOCATE(C, five_numbers) > 0) +
(LOCATE(D, five_numbers) > 0) +
(LOCATE(E, five_numbers) > 0)) >= 3