MySQL full text search equivalents to Google search: Difference between revisions

Revision as of 10:40, 13 February 2017

AND

case1: 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

case2: 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

case3: 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

case4: 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.

References or related articles

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

@@ Line 35: / Line 35: @@
 * Searched the keywords e.g. {{kbd | key = <nowiki>"人物誌Persona"</nowiki>}} on Google and found the search result contains {{kbd | key = <nowiki>人物誌（Persona）</nowiki>}}, {{kbd | key = <nowiki>人物誌(Persona) </nowiki>}} &  {{kbd | key =<nowiki>「人物誌」（persona）</nowiki>}}.
-== references or related articles ==
+== Ranking factors ==
+Possibile factors
+* Google [https://en.wikipedia.org/wiki/PageRank PageRank - Wikipedia]
+* [http://www.w3resource.com/mysql/string-functions/mysql-position-function.php MySQL POSITION() function - w3resource] / [http://www.w3resource.com/mysql/string-functions/mysql-length-function.php MySQL LENGTH() function - w3resource] where the keywords located.
+== References or related articles ==
 to explore strange new worlds / related articles:
 * [http://dev.mysql.com/doc/refman/5.1/en/regexp.html MySQL :: MySQL 5.1 Reference Manual :: 12.5.2 Regular Expressions]