我想创建查询以搜索table中的给定信息users
。表包括:中id
,username
,firstname
,lastname
,phone
和email
。
示例搜索文字: mat h 50 @l d
唯一应返回的记录: 1 | admin | mat | hladeo | 123450789 | admin@localhost
我的查询:
SELECT * FROM `users` WHERE (
(`firstname` LIKE '%mat%') || (`firstname` LIKE '%h%') ||
(`firstname` LIKE '%50%') || (`firstname` LIKE '%@l%') ||
(`firstname` LIKE '%d%')
) && (
(`lastname` LIKE '%mat%') || (`lastname` LIKE '%h%') ||
(`lastname` LIKE '%50%') || (`lastname` LIKE '%@l%') ||
(`lastname` LIKE '%d%')
) && (
(`phone` LIKE '%mat%') || (`phone` LIKE '%h%' ) ||
(`phone` LIKE '%50%') || (`phone` LIKE '%@l%') ||
(`phone` LIKE '%d%')
) && (
(`email` LIKE '%mat%') || (`email` LIKE '%h%') ||
(`email` LIKE '%50%' ) || (`email` LIKE '%@l%') ||
(`email` LIKE '%d%')
) && (
(`username` LIKE '%mat%') || (`username` LIKE '%h%') ||
(`username` LIKE '%50%') || (`username` LIKE '%@l%') ||
(`username` LIKE '%d%')
)
但是此查询返回的用户名包含d
,电话号码包含50
。
编辑:
此查询返回3行:
1 | admin | mat | hladeo | 123450789 | admin@localhost
8 | dillese | Adriana | Zolch | 44450232 | [email protected]
12 | dcolhut | Denise | Colhut | 502222222 | [email protected]
并且应该仅返回第一行(因为匹配所有要求)。
==========
主要问题-如何优化此查询?有可能使其更简单吗?
问候
我认为应该这样做:
SELECT * FROM `users` WHERE (
(firstname LIKE '%mat%' OR lastname LIKE '%mat%' OR
phone LIKE '%mat%' OR email LIKE '%mat%' OR username LIKE '%mat%')
AND
(firstname LIKE '%h%' OR lastname LIKE '%h%' OR
phone LIKE '%h%' OR email LIKE '%h%' OR username LIKE '%h%')
AND
(firstname LIKE '%50%' OR lastname LIKE '%50%' OR
phone LIKE '%50%' OR email LIKE '%50%' OR username LIKE '%50%')
AND
(firstname LIKE '%@l%' OR lastname LIKE '%@l%' OR
phone LIKE '%@l%' OR email LIKE '%@l%' OR username LIKE '%@l%')
AND
(firstname LIKE '%d%' OR lastname LIKE '%d%' OR
phone LIKE '%d%' OR email LIKE '%d%' OR username LIKE '%d%')
)
您需要分别测试每个条件,而不是每个字段。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句