MySQL

MySQL IntroductionComment MySQLInstall MySQL container with Docker-ComposeMySQL AdminMySQL ALTER TABLEMySQL ArithmeticMySQL BackticksMySQL Backup using mysqldumpMySQL Change PasswordMySQL Character Sets and CollationsMySQL clientMySQL ClusteringMySQL Configuration and tuningMySQL Connecting with UTF-8 Using Various Programming language.MySQL Converting from MyISAM to InnoDBMySQL Create New UserMySQL Creating databasesMySQL Customize PS1MySQL Data TypesMySQL Date and Time OperationsMySQL Dealing with sparse or missing dataMySQL DELETEMySQL Drop TableMySQL Dynamic Un-Pivot Table using Prepared StatementMySQL ENUMMySQL Error 1055: ONLY FULL GROUP BY: something is not in GROUP by clause ...MySQL Error codesMySQL EventsMySQL Extract values from JSON typeMySQL Full-Text searchMySQL Group byMySQL Handling Time ZonesMySQL Indexes and KeysMySQL INSERTMySQL JoinsMySQL JOINS: Join 3 table with the same name of id.MySQL JSONMySQL Limit and OffsetMySQL LOAD DATA INFILEMySQL LOCK TABLEMySQL Log filesMySQL Many-to-many Mapping tableMySQL MyISAM EngineMySQL mysqlimportMySQL NULLMySQL One to ManyMySQL ORDER byMySQL PartitioningMySQL Performance TipsMySQL Performance TuningMySQL Pivot queriesMySQL PREPARE StatementsMySQL Recover from lost root passwordMySQL Regular ExpressionsMySQL ReplicationMySQL Reserved WordsMySQL Security via GRANTsMySQL SELECTMySQL Server InformationMySQL SSL Connection SetupMySQL Stored routines (procedures and functions)MySQL String operationsMySQL Table CreationMySQL Temporary TablesMySQL Time with subsecond precisionMySQL TransactionMySQL TRIGGERSMySQL UNIONMySQL UnionsMySQL UPDATEMySQL Using VariablesMySQL VIEWRecover and reset the default root password for MySQL 5.7+



MySQL Full-Text search

From WikiOD

MySQL offers FULLTEXT searching. It searches tables with columns containing text for the best matches for words and phrases.

Remarks[edit | edit source]

FULLTEXT searching works strangely on tables containing small numbers of rows. So, when you're experimenting with it, you may find it helpful to obtain a medium-sized table online. Here's a table of book items, with titles and authors. You can download it, unzip it, and load it into MySQL.

FULLTEXT search is intended for use with human assistance. It's designed to yield more matches than an ordinary WHERE column LIKE 'text%' filtering operation.

FULLTEXT search is available for MyISAM tables. It is also available for InnoDB tables in MySQL version 5.6.4 or later.

Simple FULLTEXT search[edit | edit source]

    SET @searchTerm= 'Database Programming';
 SELECT MATCH (Title) AGAINST (@searchTerm IN NATURAL LANGUAGE MODE) Score,
        ISBN, Author, Title 
   FROM book
  WHERE MATCH (Title) AGAINST (@searchTerm IN NATURAL LANGUAGE MODE)
  ORDER BY MATCH (Title) AGAINST (@searchTerm IN NATURAL LANGUAGE MODE) DESC;

Given a table named book with columns named ISBN, 'Title', and 'Author', this finds books matching the terms 'Database Programming'. It shows the best matches first.

For this to work, a fulltext index on the Title column must be available:

ALTER TABLE book ADD FULLTEXT INDEX Fulltext_title_index (Title);

Simple BOOLEAN search[edit | edit source]

    SET @searchTerm= 'Database Programming -Java';
 SELECT MATCH (Title) AGAINST (@searchTerm IN BOOLEAN MODE) Score,
        ISBN, Author, Title 
   FROM book
  WHERE MATCH (Title) AGAINST (@searchTerm IN BOOLEAN MODE)
  ORDER BY MATCH (Title) AGAINST (@searchTerm IN BOOLEAN MODE) DESC;

Given a table named book with columns named ISBN, Title, and Author, this searches for books with the words 'Database' and 'Programming' in the title, but not the word 'Java'.

For this to work, a fulltext index on the Title column must be available:

ALTER TABLE book ADD FULLTEXT INDEX Fulltext_title_index (Title);

Multi-column FULLTEXT search[edit | edit source]

    SET @searchTerm= 'Date Database Programming';
 SELECT MATCH (Title, Author) AGAINST (@searchTerm IN NATURAL LANGUAGE MODE) Score,
        ISBN, Author, Title 
   FROM book
  WHERE MATCH (Title, Author) AGAINST (@searchTerm IN NATURAL LANGUAGE MODE)
  ORDER BY MATCH (Title, Author) AGAINST (@searchTerm IN NATURAL LANGUAGE MODE) DESC;

Given a table named book with columns named ISBN, Title, and Author, this finds books matching the terms 'Date Database Programming'. It shows the best matches first. The best matches include books written by Prof. C. J. Date.

(But, one of the best matches is also The Date Doctor's Guide to Dating : How to Get from First Date to Perfect Mate. This shows up a limitation of FULLTEXT search: it doesn't pretend to understand such things as parts of speech or the meaning of the indexed words.)

For this to work, a fulltext index on the Title and Author columns must be available:

ALTER TABLE book ADD FULLTEXT INDEX Fulltext_title_author_index (Title, Author);

Credit:Stack_Overflow_Documentation