15,032
edits
MySQL full text search equivalents to Google search: Difference between revisions
Jump to navigation
Jump to search
MySQL full text search equivalents to Google search (edit)
Revision as of 11:28, 28 June 2017
, 28 June 2017→Highlight search query words on resulting pages
| 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>}}. | * 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>}}. | ||
== Highlight search query | == Highlight search query keywords on resulting pages == | ||
Total 130 ~ 240 characters. ( | Total 130 ~ 240 characters on Google resulting pages. | ||
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]. | |||
<pre> | |||
SET @message := "笑傲江湖中嵩山派掌門左冷禪所創掌法，可發出至陰至寒的真氣。左冷禪與任我行比武時，以此功對付吸星大法，使其全身凍僵、天池穴被封;與岳不群比劍奪帥時，左又使出寒冰神掌，與紫霞神功旗鼓相當、不分勝敗。 | |||
原文網址：https://kknews.cc/zh-tw/culture/xzaxbq.html"; | |||
SELECT | |||
@message | |||
, CASE | |||
WHEN POSITION("吸星大法" IN @message) > 0 THEN SUBSTRING(@message | |||
, IF( | |||
POSITION("吸星大法" IN @message) > 0 && | |||
POSITION("吸星大法" IN @message) -10 < 0 | |||
, 1 | |||
, POSITION("吸星大法" IN @message) -10) | |||
, CHAR_LENGTH("吸星大法") + 20 | |||
) | |||
ELSE '' | |||
END AS "scrapbook" | |||
-- Returned result of scrapbook column: Show 10 characters before or after the search keywords. | |||
-- 行比武時，以此功對付吸星大法，使其全身凍僵、天池 | |||
</pre> | |||
<pre> | |||
SET @message := "原文網址：https://kknews.cc/zh-tw/culture/xzaxbq.html"; | |||
SELECT | |||
@message | |||
, CASE | |||
WHEN POSITION("吸星大法" IN @message) > 0 THEN SUBSTRING(@message | |||
, IF( | |||
POSITION("吸星大法" IN @message) > 0 && | |||
POSITION("吸星大法" IN @message) -10 < 0 | |||
, 1 | |||
, POSITION("吸星大法" IN @message) -10) | |||
, CHAR_LENGTH("吸星大法") + 20 | |||
) | |||
ELSE '' | |||
END AS "scrapbook" | |||
-- Returned result of scrapbook column: Show 10 characters before or after the search keywords. | |||
-- [EMPTY] | |||
</pre> | |||
== Ranking factors == | == Ranking factors == | ||