MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
1PLs Company - #1Payday.Loans Agency - Loans online and near me $100-$2500 (Same Day)
Powered by MySQL
 
Home / Indexes / Full Text Indexes / Question No: 182

How to use natural language full-text search in MySQL?

I want to use MySQL fulltext feature. Can you explain, how to use natural language full-text search in MySQL?

Answer No: 182

MySQL provides MATCH() and AGAINST() functions to perform full-text searches. By default, MATCH() function performs a natural language search for a string against a text collection. A collection is a set of one or more columns included in a FULLTEXT index. The search string is given as the argument to AGAINST() function. For natural-language full-text searches, it is required that the columns named in the MATCH() function must be same columns included in FULLTEXT index as you created in your table. So, if you want to search the title or body columns separately, you would need to create separate FULLTEXT indexes for each column.

By default, the search is performed in case-insensitive fashion. In MySQL 4.1 and up, you can make a full-text search by using a binary collation for the indexed columns. For example, a column that has a character set of latin1 character set of can be assigned a collation of latin1_bin to make it case sensitive for full-text searches.

Let's take an example easy to understand copied from mysql.com:

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
);

INSERT INTO articles (title,body)
VALUES('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');

SELECT * FROM articles WHERE MATCH (title, body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title | body |
+----+-------------------+------------------------------------------+
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
+----+-------------------+------------------------------------------+

Related MySQL FAQs to the Above FAQ

Main-features-and-basic-information-about-full-text-search Main features and basic information about full-text search

What-are-full-text-restrictions-in-MySQL What are full-text restrictions in MySQL?

On-what-data-types-fulltext-index-can-be-created On what data types fulltext index can be created?

How-to-create-full-text-index-in-MySQL How to create full-text index in MySQL?

How-to-create-multi-columns-full-text-index-in-MySQL How to create multi columns full-text index in MySQL?

What-are-natural-language-and-boolean-and-query-expansion-full-text-searches What are natural language and boolean and query expansion full-text searches?

How-to-use-boolean-full-text-search-in-MySQL How to use boolean full-text search in MySQL?

How-to-use-full-text-searches-with-query-expansion-in-MySQL How to use full-text searches with query expansion in MySQL?

What-are-stopwords-in-MySQL What are stopwords in MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2023  www.mysqlfaqs.net
All rights reserved.