我在mysql-Db中有字符串,如下所示
"Good Mayer Brown Corporate & Securities"
"Three Mayer Brown Good & Securities"
"Three Mayer Brown Corporate & Good"
"Three Mayer Brown Goodies & Securities"
我需要找到不喜欢的单词作为LIKE %%
关键字。这是一个例子,
我需要使用"Good"
关键字Word来选择行。这将是选择前3行。最后一个有额外的单词,如"ies"
这是我的预期结果
"Good Mayer Brown Corporate & Securities"
"Three Mayer Brown Good & Securities"
"Three Mayer Brown Corporate & Good"
我已经尝试了以下查询。
SELECT * FROM table WHERE myword LIKE '% Good %' OR myword LIKE 'Good%' OR myword LIKE '% Good'
但这对我不起作用。
SELECT * FROM table WHERE myword REGEXP '^.*GOOD.*$
您可以使用REGEXP
来执行此操作。或
SELECT * FROM table WHERE myword REGEXP '[[:<:]]GOOD[[:>:]]'
与word boundary
一起使用。请参阅演示。http://www.sqlfiddle.com/#!9/e1b9d/6/0
为什么不使用全文搜索,这意味着以一种优化的方式进行这种搜索,具有大量功能,而不是像通配符那样进行,因为通配符速度很慢,即使使用%%
的索引列也不会影响索引。如果您不知道如何操作,请参阅手册。这里有一个的示例
mysql> create table test (search varchar(200),fulltext key(search)) engine=myisam;
Query OK, 0 rows affected (0.08 sec)
mysql> insert into test values ('Good Mayer Brown Corporate & Securities'),('Three Mayer Brown Good & Securities'),('Three Mayer Brown Corporate & Good'),('Three Mayer Brown Goodies & Securities');
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from test where match(search) against ('good' in boolean mode);
+-----------------------------------------+
| search |
+-----------------------------------------+
| Good Mayer Brown Corporate & Securities |
| Three Mayer Brown Good & Securities |
| Three Mayer Brown Corporate & Good |
+-----------------------------------------+
3 rows in set (0.00 sec)