MySQL full text search equivalents to Google search: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
mNo edit summary |
||
| Line 1: | Line 1: | ||
== AND == | == AND == | ||
Google search: {{kbd | key = keyword1 keyword2}} same as {{kbd | key = keyword1 AND keyword2}} or {{kbd | key = keyword1 +keyword2}}. {{exclaim}} (1) The following is '''exact words''' search. (2) Replace ''column_name'' with your column name | |||
* Google: {{kbd | key = <nowiki>易筋經 AND 吸星大法</nowiki>}} | * Google: {{kbd | key = <nowiki>易筋經 AND 吸星大法</nowiki>}} | ||
* MySQL: {{kbd | key = <nowiki>column_name REGEXP '易筋經' AND column_name REGEXP '吸星大法'</nowiki>}} | * MySQL: {{kbd | key = <nowiki>column_name REGEXP '易筋經' AND column_name REGEXP '吸星大法'</nowiki>}} | ||
| Line 9: | Line 9: | ||
== OR == | == OR == | ||
Google search: {{kbd | key = keyword1 OR keyword2}} | |||
* Google: {{kbd | key = <nowiki>易筋經 OR 吸星大法</nowiki>}} | * Google: {{kbd | key = <nowiki>易筋經 OR 吸星大法</nowiki>}} | ||
* MySQL: {{kbd | key = <nowiki>column_name REGEXP '易筋經' OR column_name REGEXP '吸星大法'</nowiki>}} | * MySQL: {{kbd | key = <nowiki>column_name REGEXP '易筋經' OR column_name REGEXP '吸星大法'</nowiki>}} | ||
| Line 16: | Line 16: | ||
== NOT == | == NOT == | ||
Google search: {{kbd | key = keyword1 NOT keyword2}} same as {{kbd | key = keyword1 -keyword2}} | |||
* Google: {{kbd | key = <nowiki>易筋經 NOT 吸星大法</nowiki>}} | * Google: {{kbd | key = <nowiki>易筋經 NOT 吸星大法</nowiki>}} | ||
* MySQL: {{kbd | key = <nowiki>column_name REGEXP '易筋經' AND column_name NOT REGEXP '吸星大法'</nowiki>}} ([http://sqlfiddle.com/#!2/6fe6a6/9/0 online demo]) | * MySQL: {{kbd | key = <nowiki>column_name REGEXP '易筋經' AND column_name NOT REGEXP '吸星大法'</nowiki>}} ([http://sqlfiddle.com/#!2/6fe6a6/9/0 online demo]) | ||
| Line 23: | Line 23: | ||
== * wildcard operator == | == * wildcard operator == | ||
Google * wildcard operator. "Use *, an asterisk character, known as a wildcard, to match one or more words in a phrase" <ref>[http://www.googleguide.com/wildcard_operator.html Google's * Wildcard Operator - Google Guide]</ref> ([http://sqlfiddle.com/#!2/e148e/1/0 online demo]) | |||
* Google: {{kbd | key = <nowiki>狐狸*叫</nowiki>}} | * Google: {{kbd | key = <nowiki>狐狸*叫</nowiki>}} | ||
* MySQL: {{kbd | key = <nowiki>column_name LIKE '狐狸%叫'</nowiki>}}<ref>[http://www.mysqltutorial.org/mysql-like/ Using MySQL LIKE Operator to Select Data Based on Pattern Matching]</ref> | * MySQL: {{kbd | key = <nowiki>column_name LIKE '狐狸%叫'</nowiki>}}<ref>[http://www.mysqltutorial.org/mysql-like/ Using MySQL LIKE Operator to Select Data Based on Pattern Matching]</ref> | ||
Revision as of 17:47, 22 March 2017
AND
Google search: keyword1 keyword2 same as keyword1 AND keyword2 or keyword1 +keyword2. 
(1) The following is exact words search. (2) Replace column_name with your column name
- Google: 易筋經 AND 吸星大法
- MySQL: column_name REGEXP '易筋經' AND column_name REGEXP '吸星大法'
- MySQL: column_name LIKE '%易筋經%' AND column_name LIKE '%吸星大法%' (online demo[1])
- MySQL: IF(LOCATE('易筋經', column_name) > 0) AND IF(LOCATE('吸星大法, column_name) > 0)
- MySQL: column_name LIKE '%易筋經%吸星大法%' AND column_name LIKE '%吸星大法%易筋經%' Trivial for multiple keywords
OR
Google search: keyword1 OR keyword2
- Google: 易筋經 OR 吸星大法
- MySQL: column_name REGEXP '易筋經' OR column_name REGEXP '吸星大法'
- MySQL: IF(LOCATE('易筋經', column_name) > 0) OR IF(LOCATE('吸星大法, column_name) > 0)
- MySQL: column_name LIKE '%易筋經%' OR column_name LIKE '%吸星大法%' (online demo[2])
NOT
Google search: keyword1 NOT keyword2 same as keyword1 -keyword2
- Google: 易筋經 NOT 吸星大法
- MySQL: column_name REGEXP '易筋經' AND column_name NOT REGEXP '吸星大法' (online demo)
- MySQL: IF(LOCATE('易筋經', column_name) > 0) AND IF(LOCATE('吸星大法, column_name) = 0)
- MySQL: column_name LIKE '%易筋經%' AND column_name NOT LIKE '%吸星大法%'
* wildcard operator
Google * wildcard operator. "Use *, an asterisk character, known as a wildcard, to match one or more words in a phrase" [1] (online demo)
- Google: 狐狸*叫
- MySQL: column_name LIKE '狐狸%叫'[2]
Ignore special characters
Ignore return symbol and span tag
- Searched the keywords e.g. "意法" site:ptt.cc on Google and found the search result contains 意 & 法 located in the nearest but different rows. 意 is at the end of the n-th row. 法 is at the beginning of n+1-th row [3].
Ignore space, Halfwidth and fullwidth symbol (半形字元和全形字元)
- Searched the keywords e.g. "嗎有" on Google and found the search result contains 嗎？ 有 & 嗎- 有.
- Searched the keywords e.g. "人物誌Persona" on Google and found the search result contains 人物誌（Persona）, 人物誌(Persona) & 「人物誌」（persona）.
Ranking factors
Possibile factors
- Google PageRank - Wikipedia
- MySQL POSITION() function - w3resource / MySQL LENGTH() function - w3resource where the keywords located.
to explore strange new worlds / related articles:
- MySQL :: MySQL 5.1 Reference Manual :: 12.5.2 Regular Expressions
- regex - MySQL REGEXP word boundaries and double quotes - Stack Overflow keyin column REGEXP "[[:<:]]word[[:>:]]"
- Google 進階搜尋, Punctuation, symbols & operators in search - Search Help
- Yahoo Advanced Web Search
- Advanced search options of bing
- 在 Excel 或 Google 試算表中，布林搜尋多個關鍵字
other search cases: if the column ... (inspired by OutWit)
- contains ____
- does not contain ____
- begins with ____
- does not begins with ____
- ends with ____
- does not ends with ____
- equals to ____
- does not equal ____
references
related news:
| MySQL related news |
|---|
|
Failed to load RSS feed from https://news.google.com.tw/news/feeds?pz=1&cf=all&ned=us&hl=en-US&q=mysql&cf=all&scoring=n&output=rss: Error parsing XML for RSS
|