MySQL MATCH() AGAINST() Full-Text Search

MySQL full-text search uses MATCH() ... AGAINST() to search text columns that have a FULLTEXT index.

SELECT id, name, class
FROM student
WHERE MATCH(name, class) AGAINST(:search_text IN NATURAL LANGUAGE MODE);

Create a FULLTEXT index

FULLTEXT indexes can be defined on CHAR, VARCHAR and TEXT columns. Current MySQL supports them for both InnoDB and MyISAM tables.

ALTER TABLE student
ADD FULLTEXT INDEX idx_student_search (name, class);

Or define the index when the table is created:

CREATE TABLE student_search (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    class VARCHAR(20) NOT NULL,
    mark INT NOT NULL DEFAULT 0,
    sex VARCHAR(10) NOT NULL,
    FULLTEXT INDEX idx_student_search (name, class)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Natural-language search

SELECT id, name, class,
       MATCH(name, class) AGAINST('Alex Four' IN NATURAL LANGUAGE MODE) AS relevance
FROM student_search
WHERE MATCH(name, class) AGAINST('Alex Four' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;

The columns listed in MATCH() should correspond to a suitable full-text index.

Boolean-mode search

SELECT id, name, class
FROM student_search
WHERE MATCH(name, class) AGAINST('+Alex -Five' IN BOOLEAN MODE);

PDO prepared statement

<?php
require "config.php";

$searchText = trim((string)($_GET['q'] ?? ''));
$sql = "SELECT id, name, class,
               MATCH(name, class) AGAINST(:q IN NATURAL LANGUAGE MODE) AS relevance
        FROM student_search
        WHERE MATCH(name, class) AGAINST(:q IN NATURAL LANGUAGE MODE)
        ORDER BY relevance DESC";

$stmt = $dbo->prepare($sql);
$stmt->execute([':q' => $searchText]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

Download the original MATCH AGAINST example package

Related tutorials

Dynamic database search · Keyword search · LIKE · REGEXP · PDO connection

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: