Regexp和MySQL：有没有“AND”操作者 [英] Regexp and mysql: is there an "AND" operator
问题描述
我有一个书名数据库，希望用户能够搜索标题，而不必按照标题的顺序使用他们的搜索词。搜索是通过一个查询数据库的表单来完成的。例如：我希望他们能够通过询问傲慢偏见或偏见来搜索傲慢与偏见（Pride and Prejudice）骄傲而不是输入自豪感和偏见。
我尝试使用REGEXP并使用搜索词爆炸，然后用OR运算符（|）[当我搜索到骄傲和偏见时用and如果您需要复杂的搜索功能，请使用全文搜索功能。如果您需要复杂的搜索功能，请使用全文搜索功能解决方案。
在MySQL中，内置全文搜索索引功能仅适用于MyISAM存储引擎。这很不幸，因为InnoDB是所有MySQL表的首选存储引擎。在大多数情况下，最近版本的MySQL中的InnoDB比MyISAM快，并且具有比MyISAM更好的崩溃恢复功能。
MySQL开发团队已经声明他们打算为InnoDB实现全文索引。但是我们距离这个可用时间还有好几个月甚至几年的时间，并且它肯定需要升级到一些更高版本的MySQL。
如果您必须使用MyISAM全文索引，我建议主要在InnoDB中存储数据，然后将可搜索文本的副本存储在MyISAM表中。
您也可以使用外部解决方案，如< Apache Solr 提供数据库外部的全文搜索功能。甚至有一种方法可以通过MySQL可插拔存储引擎界面使用 Sphinx搜索索引。
我不喜欢在通配符或 REGEXP LIKE >全文搜索。这些解决方案对每次搜索都会进行全表扫描，并且根据数据量的不同，运行速度可能比有索引时慢数百或数千倍。
更新：MySQL 5.6目前正在开发中2012年2月），并为InnoDB实现全文索引解决方案。
I have a database of book titles and would like the user to be able to search for the titles without having to use their search terms in the exact order of the title. The search is done using a form which queries the database
For example: I want them to be able to search Pride and Prejudice by asking for "Pride Prejudice" or "Prejudice Pride" instead of typing "pride and prejudice".
I tried using REGEXP and exploding the search terms and then imploding them with an OR operator (|) [gives me everything with the word "and" when searching pride and prejudice], (+) and (.*) to no avail.
If you need sophisticated search features, use a fulltext search solution.
In MySQL, the builtin fulltext search index feature works only in the MyISAM storage engine. This is unfortunate, because InnoDB is the preferable storage engine for all MySQL tables. InnoDB in recent versions of MySQL is faster than MyISAM in most cases, and has better crash recovery features than MyISAM.
There have been statements from the MySQL dev team that they intend to implement a fulltext index for InnoDB. But we're many months or even years away from this being available, and it will certainly require upgrading to some later version of MySQL.
If you must use MyISAM fulltext indexes, I'd suggest storing your data primarily in InnoDB, and then store a copy of the searchable text in a MyISAM table.
You could also use an external solution like Sphinx Search or Apache Solr to provide fulltext search capabilities externally to the database. There's even a way to use a Sphinx Search index via the MySQL pluggable storage engine interface.
I don't like using LIKE with wildcards or REGEXP for fulltext searches. These solutions incur full table scans for every search, and depending on the volume of your data, run hundreds or thousands of times slower than if you have an index.
I wrote a comparison of fulltext search solutions for MySQL in my presentation, "Practical Full-Text Search in MySQL," and also in a chapter of my book SQL Antipatterns: Avoiding the Pitfalls of Database Programming.
update: MySQL 5.6 is currently in development (as of Feb 2012), and implements a fulltext index solution for InnoDB.
这篇关于Regexp和MySQL：有没有“AND”操作者的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
