Difference between revisions of "MySQL full text Search equivalents to Google search"

From LemonWiki共筆
Jump to navigation Jump to search
 
Line 56: Line 56:
  
 
=== MySQL approach ===
 
=== MySQL approach ===
Input search keywords, and returned the matched paragraph. Using MySQL [http://www.w3resource.com/mysql/string-functions/mysql-substring-function.php SUBSTRING() function], [http://www.w3resource.com/mysql/string-functions/mysql-position-function.php POSITION() function] & [http://www.w3resource.com/mysql/string-functions/mysql-char_length-function.php CHAR_LENGTH() function].
+
Input search keywords, and returned the the first occurrence of matched paragraph. Using MySQL [http://www.w3resource.com/mysql/string-functions/mysql-substring-function.php SUBSTRING() function], [http://www.w3resource.com/mysql/string-functions/mysql-position-function.php POSITION() function] & [http://www.w3resource.com/mysql/string-functions/mysql-char_length-function.php CHAR_LENGTH() function].
  
 
<pre>
 
<pre>

Latest revision as of 10:19, 7 November 2019

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 the first occurrence of 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.
-- 行比武時,以此功對付吸星大法,使其全身凍僵、天池


Instructions: (1) MySQL POSITION() function - w3resource "MySQL POSITION() returns the position of a substring within a string."

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

原文網址:https://kknews.cc/zh-tw/culture/xzaxbq.html";
SELECT POSITION(@term IN @message)

-- > returns 46

(2) Avoid the the start position is 0 or negative. Minimum start position of each paragraph is 1.

SELECT IF(
            POSITION(@term IN @message) > 0 &&
            POSITION(@term IN @message) -10 < 0
            , 1
            , POSITION(@term IN @message) -10)

-- > returns 36 = 46 - 10

(3) Show 10 characters before or after the search keywords. MySQL SUBSTRING() function - w3resource"returns a specified number of characters from a particular position of a given string."

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";

-- > returns 行比武時,以此功對付吸星大法,使其全身凍僵、天池
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

Related news[edit]

MySQL OR nosql related news
Google關聯式資料庫服務Spanner現提供新的多地區選擇 - iThome Online
网站建设中常见的数据库SQL漏洞有哪些? - 站长网
网上商城系统排名介绍 - 站长网
動手優化openSUSE 向上提升儲存運作效能 - 網管人雜誌
智慧云扶贫云:中国移动云能力中心多措并举助力智慧扶贫_中国网客户端 - 中国网
数据中心22年基础架构演进史- 文章精选 - CTI论坛
Hostable 推出 0.99 美元認證的 3 年無限主機空間計劃! - TechOrz 囧科技
雲計算核心技術Docker教程: Docker 安裝mysql-科技新聞 - 臺灣新浪網
甲骨文著手變更MySQL術語,原Master與Slave改用Source、Replica - iThome Online
MySQL、Jenkins是漏洞最多的兩個開源碼專案 - iThome Online
MySQL資料庫叢集系統Vitess從CNCF孵化器畢業並釋出最新4.0版 - iThome Online
云计算核心技术Docker教程: Docker 安装mysql - 新浪网
AWS開源可跨關聯式與NoSQL資料庫的查詢語言PartiQL - iThome Online
Digitalocean擴充資料庫託管服務,新加入MySQL和Redis - iThome Online
調整組態、優化SQL指令動手找回MySQL效能 - 網管人雜誌
安全顧問揭露MySQL含有可竊取用戶檔案的設計漏洞 - iThome Online
吞吐量比Cassandra大10倍的NoSQL資料庫Scylla Open Source 3.0釋出 - iThome Online
Google為分散式NoSQL資料庫Bigtable增加全球複製功能 - iThome Online
安裝Grafana彙整資料繪製圖表快速判讀 - 網管人雜誌
實作MySQL備份還原詳實說明三種機制模式 - 網管人雜誌
MySQL 8.0正式版來了! 高負載讀寫效能是5.7版的2倍 - iThome Online
進階管理MySQL複製半同步模式不怕資料遺失 - 網管人雜誌
2018 全球資料庫7 月排行榜:雲端勢力崛起,老牌Top 3 有能力應付嗎? | TechOrange - TechOrange
Oracle MySQL 企业版高性价比优惠促销 - 新浪网
Google推出Cloud Spanner,兼具關聯式資料庫與NoSQL優點 - iThome Online
亞馬遜如何讓Aurora資料庫效能比MySQL快5倍 - iThome Online
幫MySQL加上安全傳輸走SSL連線資料庫才放心 - 網管人雜誌
DB-Engines 2019:MySQL 获得“年度数据库”称号- MySQL - cnBeta
瀏覽器中的資料庫 - iThome Online
腾讯云数据库MySQL 8.0 正式上线:“80后”渐感吃力,“20后”正茁长成长 - 新浪网
只談MySQL (第一天) - iT 邦幫忙::一起幫忙解決難題,拯救IT 人的一天 - iThome Online
Google推出NoSQL雲端資料庫服務Bigtable - iThome Online

Powered by Google News