SQL 用户搜索查询 [英] SQL users searching query

问题描述

我想创建查询来搜索表 users 中的给定信息.表包括:id、username、firstname、lastname、phone 和 <代码>电子邮件.

I would like to create query to search given information in table users. Table consists: of id, username, firstname, lastname, phone and email.

示例搜索文本:mat h 50 @l d

应该返回的唯一记录:1 |管理员 |垫 |赫拉迪奥 |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.

But this query is returning people who have got username containing d and their phone number contains 50.

此查询返回 3 行:

this query returns 3 rows:

1 | admin | mat | hladeo | 123450789 | admin@localhost
8 | dillese | Adriana | Zolch | 44450232 | [e-mail]
12 | dcolhut | Denise | Colhut | 502222222 | [e-mail]

并且应该只返回第一行(因为匹配所有要求).

and should return only first row (because matches all the requirements).

==========

主要问题 - 如何优化此查询?有没有可能让它更简单?

And the main question - how to optimize this query? Is it possible to make it simplier?

问候

推荐答案

我认为应该这样做:

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

您需要单独测试每个条件，而不是每个字段.

You need to test each criteria separately, not each field.

这篇关于SQL 用户搜索查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！