gpt4 book ai didi

mysql - SQL 用户搜索查询

转载 作者:行者123 更新时间:2023-11-29 00:11:41 25 4
gpt4 key购买 nike

我想创建查询来搜索表 users 中的给定信息。表格包括:idusernamefirstnamelastnamephone电子邮件

示例搜索文本: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 的人。

编辑:
此查询返回 3 行:

1 | admin | mat | hladeo | 123450789 | admin@localhost
8 | dillese | Adriana | Zolch | 44450232 | dilesse@msn.com
12 | dcolhut | Denise | Colhut | 502222222 | dcolhut@msn.com

并且应该只返回第一行(因为符合所有要求)。

==========

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

问候

最佳答案

我认为应该这样做:

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

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

关于mysql - SQL 用户搜索查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/24785418/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com