MYSQL 大量文本查询 解决方案-fulltext索引

zhouyi

 为了更好查看 请到http://www.aiice.com/295.html



查看代码 复制到剪贴板

• SELECT * from tbl_name   
  
• 
  WHERE col1 LIKE 'pat' OR col2 LIKE 'pat' OR col3 LIKE 'pat' ...  
  

SELECT * from tbl_name
WHERE col1 LIKE 'pat' OR col2 LIKE 'pat' OR col3 LIKE 'pat' ...

　　当然可以查出结果来 但是基于大数据就熄火了吧 所以的使用FULLTEXT索引

查看代码 复制到剪贴板

• ALTER TABLE kjv ADD FULLTEXT (vtext);  
  

ALTER TABLE kjv ADD FULLTEXT (vtext);

　　再用 To perform a search using the index, use MATCH( ) to name the indexed column and
AGAINST( ) to specify what text to look for. For example, to answer the question “How often
does the name Mizraim occur?” (you’ve often wondered about that, right?), search the vtext
column using this query:
　　“Mizraim”在vtext里面出现了多少次？

查看代码 复制到剪贴板




 

mysql> SELECT COUNT(*) from kjv WHERE MATCH(vtext) AGAINST('Mizraim');
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
　　想查询到具体的列

查看代码 复制到剪贴板
    -> FROM kjv WHERE MATCH(vtext) AGAINST('Mizraim')\G
*************************** 1. row ***************************
bname: Genesis
cnum: 10
vnum: 6
vtext: And the sons of Ham; Cush, and Mizraim, and Phut, and Canaan.
*************************** 2. row ***************************
bname: Genesis
cnum: 10
vnum: 13
vtext: And Mizraim begat Ludim, and Anamim, and Lehabim, and Naphtuhim,
*************************** 3. row ***************************
bname: 1 Chronicles
cnum: 1
vnum: 8
vtext: The sons of Ham; Cush, and Mizraim, Put, and Canaan.
*************************** 4. row ***************************
bname: 1 Chronicles
cnum: 1
vnum: 11
vtext: And Mizraim begat Ludim, and Anamim, and Lehabim, and Naphtuhim,





　　这里给个sql语句

查看代码 复制到剪贴板




 

SELECT bname, cnum, vnum, vtext
FROM kjv WHERE MATCH(vtext) AGAINST('search string')
ORDER BY bnum, cnum, vnum;
　　如果你要同时索引多列 那就这么干

查看代码 复制到剪贴板

• ALTER TABLE tbl_name ADD FULLTEXT (col1, col2, col3); //给列加入索引   
  
•   
  
• 
  SELECT ... FROM tbl_name   
  
• 
  WHERE MATCH(col1, col2, col3) AGAINST('search string'); //对多列索引