有个Attendee表,表中有emailAddress列,为此列加了一个fullText索引,
假如有个列数据为"[email protected]"的数据,
我想使用此表全文索引进行查找结果要求与 like '%[email protected]%'结果类似 ,
使用
SELECT * FROM AttendeeTest as at where match(emailAddress) against('[email protected]' IN BOOLEAN MODE),如果是这样的话, 查询结果有很多,比如类似"[email protected]"都能查出来,
如果使用
SELECT * FROM AttendeeTest as at where match(emailAddress) against('"[email protected]"' IN BOOLEAN MODE),只能查出"[email protected]"这个数据,查不出"[email protected]"数据,
求解.
假如有个列数据为"[email protected]"的数据,
我想使用此表全文索引进行查找结果要求与 like '%[email protected]%'结果类似 ,
使用
SELECT * FROM AttendeeTest as at where match(emailAddress) against('[email protected]' IN BOOLEAN MODE),如果是这样的话, 查询结果有很多,比如类似"[email protected]"都能查出来,
如果使用
SELECT * FROM AttendeeTest as at where match(emailAddress) against('"[email protected]"' IN BOOLEAN MODE),只能查出"[email protected]"这个数据,查不出"[email protected]"数据,
求解.
-> FROM articles;
+----+-----------------------------------------+
| id | MATCH (title,body) AGAINST ('Tutorial') |
+----+-----------------------------------------+
| 1 | 0.65545833110809 |
| 2 | 0 |
| 3 | 0.66266459226608 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
+----+-----------------------------------------+
select * from (SELECT * FROM AttendeeTest as at where match(firstName,lastName,emailAddress) against('[email protected]' IN BOOLEAN MODE)) as a where CONCAT(a.lastName,a.firstName,a.lastName,a.emailAddress) like '%[email protected]%'
115万的数据,使用了155秒,效率不行
SELECT count(*) FROM AttendeeTest as at where match(firstName,lastName,emailAddress) against('[email protected]' IN BOOLEAN MODE)