MySQL full text Search equivalents to Google search

From LemonWiki共筆
Jump to: navigation, search

AND[edit]

Google search: keyword1 keyword2 same as keyword1 AND keyword2 or keyword1 +keyword2. Icon exclaim.gif (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 '%吸星大法%易筋經%' Icon exclaim.gif Trivial for multiple keywords

OR[edit]

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[edit]

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[edit]

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]

English issue[edit]

When the keyword is short and written in English e.g. AI, the query result using column_name LIKE '%AI%' may NOT what you want e.g. Tainan, main, hair and so on.

  • (1) Remove all non-alpha-numeric-characters[3] (2) REGEXP word boundaries[4] e.g. (REPLACE(CONVERT(column_name USING ascii), '?', ' ') REGEXP '([[:<:]])AI([[:>:]])')


Cited from MySQL :: MySQL 5.7 Reference Manual :: 12.5.2 Regular Expressions

[[:<:]], [[:>:]]

These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_).

Ignore special characters[edit]

Ignore return symbol and span tag

  • Example:
    • 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].
  • Approach: (1) remove the html tag (2) remove the return symbol (Carriage return).

Ignore space, Halfwidth and fullwidth symbol (半形字元和全形字元)

  • Examples:
    • 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).
  • Approach: (1) remove the space symbol (2) remove the Halfwidth and fullwidth symbol.
  • References: PHP remove symbols from string - Stack Overflow

Highlight search query keywords on resulting pages[edit]

Returned result: Show 10 characters before or after the search keywords. (cf: Total 130 ~ 240 characters on Google resulting pages.)

MySQL approach[edit]

Input search keywords, and returned the matched paragraph. Using MySQL SUBSTRING() function, POSITION() function & CHAR_LENGTH() function.

SET @term := "吸星大法";
SET @message := "笑傲江湖中嵩山派掌門左冷禪所創掌法,可發出至陰至寒的真氣。左冷禪與任我行比武時,以此功對付吸星大法,使其全身凍僵、天池穴被封;與岳不群比劍奪帥時,左又使出寒冰神掌,與紫霞神功旗鼓相當、不分勝敗。

原文網址:https://kknews.cc/zh-tw/culture/xzaxbq.html";


SELECT 
@message

, CASE
  WHEN POSITION(@term IN @message) > 0 THEN SUBSTRING(@message
        , IF(
            POSITION(@term IN @message) > 0 &&
            POSITION(@term IN @message) -10 < 0
            , 1
            , POSITION(@term IN @message) -10)
        , CHAR_LENGTH(@term) + 20
      )
  ELSE ''
END AS "scrapbook"

-- Returned result of scrapbook column: Show 10 characters before or after the search keywords.
-- 行比武時,以此功對付吸星大法,使其全身凍僵、天池


SET @term := "吸星大法";
SET @message := "原文網址:https://kknews.cc/zh-tw/culture/xzaxbq.html";


SELECT 
@message

, CASE
  WHEN POSITION(@term IN @message) > 0 THEN SUBSTRING(@message
        , IF(
            POSITION(@term IN @message) > 0 &&
            POSITION(@term IN @message) -10 < 0
            , 1
            , POSITION(@term IN @message) -10)
        , CHAR_LENGTH(@term) + 20
      )
  ELSE ''
END AS "scrapbook"

-- Returned result of scrapbook column: Show 10 characters before or after the search keywords.
-- [EMPTY]

Google sheet approach[edit]

Using REGEXEXTRACT function Icon exclaim.gif case-sensitive!:

A B
1 文章 笑傲江湖中嵩山派掌門左冷禪所創掌法,可發出至陰至寒的真氣。左冷禪與任我行比武時,以此功對付吸星大法,使其全身凍僵、天池穴被封;與岳不群比劍奪帥時,左又使出寒冰神掌,與紫霞神功旗鼓相當、不分勝敗。 原文網址:https://kknews.cc/zh-tw/culture/xzaxbq.html
2 關鍵字 吸星大法
3 搜尋結果摘要 =IF(ISERROR(REGEXEXTRACT(LOWER(B1), "(.{10}"&B2&".{10})")), "", REGEXEXTRACT(LOWER(B1), "(.{10}"&B2&".{10})"))

Microsoft Spreadsheet approach[edit]

Using FIND, MID & CONCATENATE functions. Icon exclaim.gif FIND function is case-sensitive!

A B
1 文章 笑傲江湖中嵩山派掌門左冷禪所創掌法,可發出至陰至寒的真氣。左冷禪與任我行比武時,以此功對付吸星大法,使其全身凍僵、天池穴被封;與岳不群比劍奪帥時,左又使出寒冰神掌,與紫霞神功旗鼓相當、不分勝敗。 原文網址:https://kknews.cc/zh-tw/culture/xzaxbq.html
2 關鍵字 吸星大法
3 搜尋結果摘要 =IF(ISERROR(FIND(B2, B1)), "", CONCATENATE(MID(B1, IF(FIND(B2, B1)-10 >= 1, FIND(B2, B1)-10, 1), 10), MID(B1, FIND(B2, B1), 10+LEN(B2))))

PHP approach[edit]

PHP solution: php - highlight multiple keywords in search - Stack Overflow Unverified

Ranking factors[edit]

Possibile factors

References or related articles[edit]

to explore strange new worlds / related articles:

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

  1. Google's * Wildcard Operator - Google Guide
  2. Using MySQL LIKE Operator to Select Data Based on Pattern Matching
  3. mysql - How to remove all non-alpha numeric characters from a string? - Stack Overflow
  4. regex - MySQL REGEXP word boundaries [[:<:]] [[:>:]] and double quotes - Stack Overflow https://stackoverflow.com/questions/18901704/mysql-regexp-word-boundaries-and-double-quotes


Related news[edit]

MySQL OR nosql related news
MariaDB coming to Azure as Microsoft joins the MariaDB Foundation

Microsoft 'Connects' With Open Source Databricks, Cassandra, MariaDB
Australian Broadcasting Corporation leaks passwords, video from AWS S3 bucket
Microsoft insiste dans l'open source avec MariaDB
Google Cloud Spanner update includes SLA that promises less than five minutes of downtime per year
Microsoft launches Azure Databricks, a new cloud data platform based on Apache Spark
Q新闻丨阿里重启维护Dubbo;Go语言成2017增长最快语言;WebAssembly 已被所有主流浏览器支持
Google Gives SAP Cloud Customers New Access Transparency Feature
Cloud computing technology for 2018: Transform or die
WordPress 4.9“Tipton”正式版发布
Microsoft updates Cosmos DB with Cassandra support, better availability guarantees
Bash in Azure Cloud Shell Now Commercially Available
Oracle pushes emergency patch for critical Tuxedo server vulnerabilities
ABC leaks sensitive data through misconfigured S3 bucket
SnapLogic Fall 2017 Release Extends Integration Capabilities to Wider Range of Big Data Sources, Additional Microsoft EnvironmentsEnhancements to Iris AI ...
EnterpriseDB Accelerates Digital Transformation with Release of the EDB Postgres Platform 10
Codemotion Milano 2017: coding, network e collaborazione
Datos IO Targets Enterprise Customers for Cloud Data Management Use Cases with Latest Product Release of RecoverX 2.5RecoverX v2.5 release comes ...
Software Engineer with Ops Experience

Amazon Aurora expands reach, but maybe not far enough

Powered by Google News

Retrieved from "https://wiki.planetoid.info/index.php?title=MySQL_full_text_Search_equivalents_to_Google_search&oldid=18742"