MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

Boolean Full-Text Search
MySQL Boolean Full-Text Search
The MySQL provides a full-text search functionality that supports three types of searches, one of which is the Boolean full-text search.
This Boolean full-text search enables complex search operations on large amounts of text data, by allowing the use of Boolean operators such as (+, -, >, <, *, etc.) and search strings.
Unlike the natural language full-text search, which searches for concepts, the Boolean full-text search in MySQL looks for specific words. To perform this type of search, it is necessary to include the IN BOOLEAN MODE modifier in the AGAINST expression.
Syntax
Following is the syntax to perform a Boolean full-text search using the IN BOOLEAN MODE modifier with the AGAINST expression in MySQL −
SELECT column_name(s) FROM table_name
WHERE MATCH(target_column_names) 
AGAINST(expression IN BOOLEAN MODE);
﻿
Where,
The target_column_names are the names of the columns that we want to search the keyword in.
The expression is the list of keywords with the Boolean operators.
MySQL Boolean Full-Text Search Operators
The following table specifies the full-text search Boolean operators −
Operator
Description
+
Include, the word must be present.
-
Exclude, the word must not be present.
>
Include, the word must be present, and have a higher priority.
<
Include, the word must be present, and have a lower priority.
()
Groups words into subexpressions
Example
First of all, let us create a table with the name ARTICLES using the following query −
CREATE TABLE ARTICLES (
   ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
   ARTICLE_TITLE VARCHAR(100),
   DESCRIPTION TEXT,
   FULLTEXT (ARTICLE_TITLE, DESCRIPTION)
);
﻿
In the above query, we have defined full-text index on the columns ARTICLE_TITLE and DESCRIPTION. Now, let us insert values into the above-created table −
INSERT INTO ARTICLES (ARTICLE_TITLE, DESCRIPTION) VALUES 
('MySQL Tutorial', 'MySQL is a relational database system that uses SQL to structure data stored'),
('Java Tutorial', 'Java is an object-oriented and platform-independent programming languag'),
('Hadoop Tutorial', 'Hadoop is framework that is used to process large sets of data'),
('Big Data Tutorial', 'Big Data refers to data that has wider variety of data sets in larger numbers'),
('JDBC Tutorial', 'JDBC is a Java based technology used for database connectivity');
﻿
The ARTICLES table is created as follows −
ID
ARTICLE_TITLE
DESCRIPTION
1
MySQL Tutorial
MySQL is a relational database system that uses SQL to structure data stored
2
Java Tutorial
Java is an object-oriented and platform-independent programming language
3
Hadoop Tutorial
Hadoop is framework that is used to process large sets of data
4
Big Data Tutorial
Big Data refers to data that has wider variety of data sets in larger numbers
5
JDBC Tutorial
JDBC is a Java based technology used for database connectivity
Now, let us perform the full-text search in Boolean mode, where we are searching for a row that contains the word ‘data’ −
SELECT * FROM ARTICLES 
WHERE MATCH (ARTICLE_TITLE, DESCRIPTION) 
AGAINST('data' IN BOOLEAN MODE);
﻿
Output
As we can see in the output below, the above query returned three rows that contains the word ‘data’ −
ID
ARTICLE_TITLE
DESCRIPTION
4
Big Data Tutorial
Big Data refers to data that has wider variety of data sets in larger numbers
1
MySQL Tutorial
MySQL is a relational database system that uses SQL to structure data stored
3
Hadoop Tutorial
Hadoop is framework that is used to process large sets of data
Example
In the following query, we are searching for the rows that contains the word ‘data’ but not ‘sets’ −
SELECT * FROM ARTICLES 
WHERE MATCH(ARTICLE_TITLE, DESCRIPTION) 
AGAINST('+data -sets' IN BOOLEAN MODE);
﻿
Output
The output for the query above is produced as given below −
ARTICLE_TITLE
DESCRIPTION
MySQL Tutorial
MySQL is a relational database system that uses SQL to structure data stored
Example
Here, we are searching for the rows that contain both the words ‘data’ and ‘set’ −
SELECT * FROM ARTICLES 
WHERE MATCH(ARTICLE_TITLE, DESCRIPTION) 
AGAINST('+data +sets' IN BOOLEAN MODE);
﻿
Output
On executing the given query, the output is displayed as follows −
ID
ARTICLE_TITLE
DESCRIPTION
4
Big Data Tutorial
Big Data refers to data that has wider variety of data sets in larger numbers
3
Hadoop Tutorial
Hadoop is framework that is used to process large sets of data
Example
In the following query, we are searching for the rows that contains the word ‘set’ but not the higher rank for the rows that contain ‘set’ −
SELECT * FROM ARTICLES
WHERE MATCH(ARTICLE_TITLE, DESCRIPTION) 
AGAINST('+data sets' IN BOOLEAN MODE);
﻿
Output
When we execute the query above, the output is obtained as follows −
ID
ARTICLE_TITLE
DESCRIPTION
4
Big Data Tutorial
Big Data refers to data that has wider variety of data sets in larger numbers
3
Hadoop Tutorial
Hadoop is framework that is used to process large sets of data
1
MySQL Tutorial
MySQL is a relational database system that uses SQL to structure data stored
Example
Using the following query, we are searching for rows that contain the word ‘data’ and rank the particular record lower in the search, if it contains the word ‘tutorial’ −
SELECT * FROM ARTICLES 
WHERE MATCH(ARTICLE_TITLE, DESCRIPTION) 
AGAINST('+data ~sets' IN BOOLEAN MODE);
﻿
Output
On executing the given query, the output is displayed as follows −
ID
ARTICLE_TITLE
DESCRIPTION
4
Big Data Tutorial
Big Data refers to data that has wider variety of data sets in larger numbers
1
MySQL Tutorial
MySQL is a relational database system that uses SQL to structure data stored
3
Hadoop Tutorial
Hadoop is framework that is used to process large sets of data
Example
Here, we are finding all the rows that contains words starting with ‘set’ −
SELECT * FROM ARTICLES
WHERE MATCH(ARTICLE_TITLE, DESCRIPTION) 
AGAINST('set*' IN BOOLEAN MODE);
﻿
Output
On executing the given query, the output is displayed as follows −
ID
ARTICLE_TITLE
DESCRIPTION
3
Hadoop Tutorial
Hadoop is framework that is used to process large sets of data
4
Big Data Tutorial
Big Data refers to data that has wider variety of data sets in larger numbers
MySQL Boolean Full-Text Search Features
Following are some important features of MySQL Boolean full-text search −
In Boolean full-text search, MySQL does not sort the rows automatically by the relevance in descending order.
The InnoDB table requires all columns of the MATCH expression has a FULLTEXT index to perform Boolean queries.
If we provide multiple Boolean operators on a search query on InnoDB tables e.g. '++hello', MySQL does not support them and it generates an error. However, if we do the same thing in MyISAM, it ignores the extra operator and uses the operator that is closest to the search word.
Trailing (+) or (-) signs are not supported in InnoDB full-text search. It only supports leading + or − sign.
MySQL will generate an error if the search word is 'hello+' or 'hello-'. In addition to that, the following will also generate an error '+*', '+-'.
MySQL will ignore the word in the search result, if it appears in more than 50% of the rows. This is called 50% threshold.
Boolean Full-Text Search Using Client Program
We can also perform Boolean Full-Text Search operation on a MySQL database using the client program.
Syntax
PHPNodeJSJavaPython
To perform the Boolean Full-Text Search through a PHP program, we need to execute the following SELECT statement using the mysqli function query() as follows −
$sql = "SELECT * FROM articles WHERE MATCH(ARTICLE_TITLE, DESCRIPTION) AGAINST('+data -sets' IN BOOLEAN MODE)";
$mysqli->query($sql);
﻿
Example
Following are the programs −
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s", $mysqli->connect_error);
    exit();
}
// printf('Connected successfully.');
﻿
//creating a table Articles that stores fulltext.
$sql = "CREATE TABLE Articles (ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, ARTICLE_TITLE VARCHAR(100), DESCRIPTION TEXT, FULLTEXT (ARTICLE_TITLE, DESCRIPTION))";
$result = $mysqli->query($sql);
if ($result) {
    printf("Table created successfully...!\n");
}
//insert data
$q = "INSERT INTO Articles (ARTICLE_TITLE, DESCRIPTION) VALUES
('MySQL Tutorial', 'MySQL is a relational database system that uses SQL to structure data stored'),
('Java Tutorial', 'Java is an object-oriented and platform-independent programming languag'),
('Hadoop Tutorial', 'Hadoop is framework that is used to process large sets of data'),
('Big Data Tutorial', 'Big Data refers to data that has wider variety of data sets in larger numbers'),
('JDBC Tutorial', 'JDBC is a Java based technology used for database connectivity')";
if ($res = $mysqli->query($q)) {
    printf("Data inserted successfully...!\n");
}
$s = "SELECT * FROM articles WHERE MATCH(ARTICLE_TITLE, DESCRIPTION) AGAINST('+data -sets' IN BOOLEAN MODE)";
if ($r = $mysqli->query($s)) {
    printf("Table Records: \n");
    while ($row = $r->fetch_assoc()) {
        printf(" ID: %d, Title: %s, Descriptions: %s", $row["id"], $row["ARTICLE_TITLE"], $row["DESCRIPTION"]);
        printf("\n");
    }
} else {
    printf('Failed');
}
$mysqli->close();
﻿
Output
The output obtained is as shown below −
Table created successfully...!
Data inserted successfully...!
Table Records:
ID: 1, Title: MySQL Tutorial, Descriptions: MySQL is a relational database system that uses SQL to structure data stored  
﻿
﻿
﻿
﻿
﻿
﻿
﻿
﻿

Operator	Description
+	Include, the word must be present.
-	Exclude, the word must not be present.
>	Include, the word must be present, and have a higher priority.
<	Include, the word must be present, and have a lower priority.
()	Groups words into subexpressions

ID	ARTICLE_TITLE	DESCRIPTION
1	MySQL Tutorial	MySQL is a relational database system that uses SQL to structure data stored
2	Java Tutorial	Java is an object-oriented and platform-independent programming language
3	Hadoop Tutorial	Hadoop is framework that is used to process large sets of data
4	Big Data Tutorial	Big Data refers to data that has wider variety of data sets in larger numbers
5	JDBC Tutorial	JDBC is a Java based technology used for database connectivity

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Boolean Full-Text Search

MySQL Boolean Full-Text Search

Syntax

MySQL Boolean Full-Text Search Operators

Example

Output

Example

Output

Example

Output

Example

Output

Example

Output

Example

Output

MySQL Boolean Full-Text Search Features

Boolean Full-Text Search Using Client Program

Syntax

Example

Output