mysql 全列搜索_mysql – 使用sql使用一个或多个关键字搜索多个列

有几种方法可以做到 –

最基本的策略是通过查询将输入与我们的表列匹配,如您所提到的那样 –

1. SELECT * FROM table WHERE (name='%name%' or zip='%name%' or city='%name%');

2. SELECT * FROM table WHERE LOCATE(name,GROUP_CONCAT(name,city,zip)) > 0;

3.

SELECT * FROM table WHERE name like '%name%'

UNION

SELECT * FROM table WHERE name like '%name%'

UNION

SELECT * FROM table WHERE name like '%name%';

但是假设输入框有字符串 – “varun bharti”但数据库中的实际名称是“varun bal bharti”的情况所以当你搜索时你会错过记录.对于这种情况,您应该通过空格将字符串分解为数组元素,并将这些查询用于元素,或者您可以替换名称列中的空格并匹配.

set @var=REPLACE ('varun bharti',' ','%');

SELECT * FROM table WHERE name like concat('%',@var,'%') or

zip like concat('%','%') or

city like concat('%','%');

你也可以使用regualar表达式.

例如,当天老板得到的输入字符串

Hitesh> select * from test;

+--------------------+

| name |

+--------------------+

| i am the boss |

| You will get soon |

| Happy birthday bro |

| the beautiful girl |

| oyee its sunday |

+--------------------+

5 rows in set (0.00 sec)

Hitesh> set @var=CONCAT('.*',REPLACE('the day boss get','.*|.*'),'.*');

Query OK,0 rows affected (0.00 sec)

Hitesh> select @var;

+----------------------------------+

| @var |

+----------------------------------+

| .*the.*|.*day.*|.*boss.*|.*get.* |

+----------------------------------+

1 row in set (0.00 sec)

Hitesh> select * from test where name REGEXP @var;

+--------------------+

| name |

+--------------------+

| i am the boss |

| You will get soon |

| Happy birthday bro |

| the beautiful girl |

| oyee its sunday |

+--------------------+

5 rows in set (0.00 sec)