MySQL supports various types of pattern matching operations to retrieve filtered result-sets from huge database tables. But, pattern matching with regular expressions is a powerful way to perform a complex search.

A regular expression is technically defined as a sequence of characters that represent a pattern in an input text. It is used to locate or replace text strings using some patterns; this pattern can either be a single character, multiple characters or words, etc.

These regular expressions in MySQL provide various functions and operators to easily perform the search operations. One such function is regexp_instr() function.

MySQL REGEXP_INSTR() Function

The MySQL regexp_instr() function is used to match specified patterns with either a string or the data in database tables. This function returns the starting index of the substring of a string that matches the specified pattern, returns 0 if there is no match, or NULL if the string or the pattern is NULL. Character indices of this string starts at 1.

Syntax

Following is the syntax of the MySQL regexp_instr() function −

REGEXP_INSTR(expr, pattern[, pos[, occurrence[, return_option[, match_type]]]])

Where expr is the string in which the search is to be performed and pat is the pattern/regular expression that is to be searched. In addition to the expression and string values this method accepts the following optional parameters.

Parameters

The regexp_instr() function takes following parameter values −

• expr: The string in which search is performed

• pattern: The pattern that is searched in the string

Following are the optional arguments that can be passed to this function −

• pos: The position in expr at which to start the search. If omitted, the default is 1.

• occurrence: Which occurrence of a match to search for. If omitted, the default is 1.

• return_option: Which type of position to return. If this value is 0, REGEXP_INSTR() returns the position of the matched substring”s first character. If this value is 1, REGEXP_INSTR() returns the position following the matched substring. If omitted, the default is 0.

• match_type:This is a string which consists of various characters representing the desired features of the match this may contain one or all of these characters. Following are various characters using which you can specify the match type.

• c This character indicates the case-sensitive matching.

• i This character indicates the case-insensitive matching.

• m This character indicates the i.e., multiple lines (separated by line terminators) with in a single string are recognized.

• n If this character is specified, the dot (.) character matches line terminators.

• u If this character is specified, only new line character is recognized as line ending by ., ^, and $.

Example

In this example, we are performing a search operation on a simple string using the MySQL REGEXP_INSTR() function −

SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''To'') AS RESULT;

The pattern ”To” is found at 9th index −

If there is no match found in the string, the return value will be ”0” −

SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''xx'') AS RESULT;

Following is the output −

Example

Let us also pass optional arguments to this function and observe the result. Here, the search search position starts at 5 to find the 2nd occurrence of ”T” after that position. As the return option is set to 1, the position following the match is returned. −

SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''T'', 5, 2, 1) AS RESULT;

Output

Following is the output −

Example

The following query searches for the position for any alphabetic character in the provided string ”9848032919”. If found, it returns 1. Else, 0.

SELECT REGEXP_INSTR(''9848032919'', ''[[:alpha:]]'');

Output

Executing the query above will produce the following output −

Example

The below query searches for the position of either ”town” or ”city” in the provided string −

SELECT REGEXP_INSTR(''Vishakapatnam is city of destiny '', ''town|city'')
As Result;

Output

The output for the above query is produced as given below −

Example

If either of the first two arguments passed to this function is NULL, this function returns NULL. Here, we are passing ”NULL” as search pattern.

SELECT REGEXP_INSTR(''Tutorialspoint'', NULL)
As Result;

If we compile and run the query, the result is produced as follows −

In the following query, we are passing ”NULL” to the string parameter.

SELECT REGEXP_INSTR(NULL, ''to'')
As Result;

When we execute the query above, the output is obtained as follows −

Example

In another example, let us perform a search operation on a database table named CUSTOMERS using the REGEXP_INSTR() function. Firstly, let us create the table using the following query −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Insert some records into the above created table using the following INSERT query −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the below query to display all the records present in CUSTOMERS table −

Select * from CUSTOMERS;

Following is the CUSTOMERS table −

The following query selects the position of the first occurrence of a string that starts with the letter ”K” from the NAME column in the CUSTOMERS table −

SELECT REGEXP_INSTR(NAME, ''^K'')
AS RESULT FROM CUSTOMERS;

As we can see in the output below, there are three string in NAME column that starts with letter K.

Client Program

We can also perform the MySQL REGEXP_INSTR() function using the client programs (such as PHP, Node.js, Java, Python) to match specified pattern with either a string or the data in database tables.

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql = "SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
$mysqli->query($sql);

sql = "SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
con.query(sql);

String sql = "SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
statement.executeQuery(sql);

sql = "SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''To'') AS RESULT"
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
if($result = $mysqli->query($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("Result: %d", $row[''RESULT'']);
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Result: 9

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
 console.log("Select query executed successfully..!");
 console.log("Table records: ");
 con.query(sql);
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Select query executed successfully..!
Table records:
[ { RESULT: 9 } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class regexp_instr {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
            rs = st.executeQuery(sql);
                  while(rs.next()) {
                String result = rs.getString("RESULT");
                System.out.println("Result: " + result);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Result: 9

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
regexp_instr_query = f"SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''To'') AS RESULT"
cursorObj.execute(regexp_instr_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Result of REGEXP_INSTR() Function:")
for row in results:
    position = row[0]
    if position > 0:
        print(f"The pattern ''To'' found at position {position}")
    else:
        print("The pattern ''To'' not found in the given string")
cursorObj.close()
connection.close()

Result of REGEXP_INSTR() Function:
The pattern ''To'' found at position 9

Regular expressions in MySQL are used in search operations to not only filter records but also replace the pattern occurrences in a string.

Consider a scenario where you noticed a spelling error among the huge sets of data present in a MySQL database. Now, you are supposed to correct all occurrences of these errors in this database without disturbing the other data. This is where regular expressions are extremely advantageous.

You can use regular expressions to find the accurate occurrences of the same error and replace it with the right characters. This is done using the regexp_replace() function.

MySQL REGEXP_REPLACE() Function

The MySQL regexp_replace() function is used to find and replace occurrences of a string that match specific patterns. If there”s a match, it replaces the string with another. If there”s no match, it returns the original string. If the string or pattern is NULL, it returns NULL. You can use a regular expression or a simple string as the pattern in this function.

Syntax

Following is the syntax of the MySQL regexp_replace() function −

REGEXP_REPLACE(expr, pattern, repl[, pos[, occurrence[, match_type]]])

Parameters

The regexp_replace() function takes following parameter values −

• expr: The string in which search is performed

• pattern: The pattern that is searched in the string

• repl: The replacement string

This method also accepts following optional arguments −

• pos − Starting position of the search

• occurrence − Which occurrence of a match to replace. If omitted, the default is 0 so it replaces all occurrences.

• match_type − A string that specifies how to perform matching.

Example

In the following query, we are performing a search operation on a simple string using the MySQL REGEXP_REPLACE() function −

SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welll'')
AS RESULT;

As we can observe the output below, the string ”Welcome” is found and replaced with ”Welll” −

But if the pattern is not found in the string, the original string is displayed by the function. Look at the following query −

SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''H'', ''Hi'') AS RESULT;

On executing the given query, the output is displayed as follows −

Example

Let us also try to pass optional arguments to this function as case-insensitive matching(i). Here, the search starts from the 10th position in the given string; and as we are passing the occurrence value as 1, only the first occurrence of the letter ”t” after 10th position will be replaced irrespective of its case −

SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''t'', ''x'', 10, 1, ''i'') AS RESULT;

Output

The output for the program above is produced as given below −

Example

The following query replaces all the occurrences of the string “is” in the given text −

SELECT REGEXP_REPLACE(''This is a sample string'', ''is'', ''@@@@'')
As Result;

Output

On executing the given query, the output is displayed as follows −

Example

The following query replaces only the first occurrence of the string “This” in the given text with “That” −

SELECT REGEXP_REPLACE(''This is a test and This is another test'', ''^This'', ''That'')
As Result;

Output

The output for the query above is produced as given below −

Example

Here, the below query replace the words ”wall” or ”floor” with the word ”bed” in the given string using the MySQL REGEXP_REPLACE() function −

SELECT REGEXP_REPLACE (''Humpty dumpty sat on a wall and slept on the floor'', ''wall|floor'', ''bed'') As Result;

Output

On executing the given program, the output is displayed as follows −

Example

The following query replaces the first occurrence of the string “eat” with the string “drink” in the provided input string.

In the query, the fourth parameter “1” specifies the position to start the search and the fifth parameter “1” is the number of replacements to be made. Therefore, only the first occurrence of “eat” is replaced with “drink”.

SELECT REGEXP_REPLACE(''eat sleep repeat and eat'', ''eat'', ''drink'', 1, 1)
As Result;

Output

Following is the output −

Example

If either of the first two arguments passed to this function is NULL, this function returns NULL. Here, we are passing NULL to the string parameter.

SELECT REGEXP_REPLACE(NULL, ''value'', ''test'') As Result;

Following is the output −

If we pass NULL to the pattern parameter, it returns NULL as output.

SELECT REGEXP_REPLACE(''Welcome to Tutorialspoint'', NULL, ''sample'')
As Result;

The output for the query above is produced as given below −

If you pass empty string as the replacement string, this function returns NULL.

SELECT REGEXP_REPLACE(''Welcome to Tutorialspoint'', NULL, '''')
As Result;

On executing the given query, the output is displayed as follows −

Example

In another example, let us try to perform a search operation on a database table named CUSTOMERS using the REGEXP_REPLACE() function. First of all, let us create the table using the following query −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The following query inserts 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the following SELECT statement to display all the records of CUSTOMERS table −

Select * from CUSTOMERS;

Following is the CUSTOMERS table −

The following query uses the REGEXP_REPLACE() function to update the NAME column in the person_tbl table. It looks for names that start with the letter ”A” and replaces that ”A” with ”An”.

SELECT REGEXP_REPLACE(NAME, ''^A'', ''An'') AS Result FROM CUSTOMERS;

On executing the given query, the output is displayed as follows −

But if the pattern is not found in any record of the table, the original values of the table are displayed by the function. Look at the following query −

SELECT REGEXP_REPLACE(ADDRESS, ''^Z'', ''P'') AS RESULT FROM CUSTOMERS;

There is no record in ADDRESS column that starts with letter ”Z”. So, it returned the original records as output −

The following query is using the REGEXP_REPLACE function to replace the second occurrence of the letter ”r” with ”R” in the ADDRESS column of the CUSTOMERS table −

SELECT REGEXP_REPLACE(ADDRESS, ''r'', ''R'', 2, 0, ''c'')
AS RESULT FROM CUSTOMERS;

As we can see in the output, the records ”Hyderabad” and ”Indore” has letter ”r” in it. And they are replaced by ”R” −

REGEXP_REPLACE() Funcion Using a Client Program

We can also perform the MySQL REGEXP_REPLACE function using the client programs to find and replace occurrences of a string that match specific patterns.

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql = "SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'')";
$mysqli->query($sql);

sql = "SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'')";
con.query(sql);

String sql = "SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'')";
statement.executeQuery(sql);

sql = "SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'')"
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'') AS RESULT";
if($result = $mysqli->query($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("Result: %s", $row[''RESULT'']);
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Result: Welcom To Tutorialspoint!

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'') AS RESULT";
 console.log("Select query executed successfully..!");
 console.log("Table records: ");
 con.query(sql);
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Select query executed successfully..!
Table records:
[ { RESULT: ''Welcome To Tutorialspoint!'' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class regexp_replace {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'') AS RESULT";
            rs = st.executeQuery(sql);
            while(rs.next()) {
                String result = rs.getString("RESULT");
                System.out.println("Result: " + result);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Result: Welcom To Tutorialspoint!

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
regexp_replace_query = f"SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'') AS RESULT;"
cursorObj.execute(regexp_replace_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Result of REGEXP_REPLACE() Function:")
for row in results:
    result = row[0]
    print(f"The modified string is: ''{result}''")
cursorObj.close()
connection.close()

Result of REGEXP_REPLACE() Function:
The modified string is: ''Welcom To Tutorialspoint!''

The MySQL Full-Text Search allows us to search for a text-based data, stored in the database. Before performing the full-text search in a column(s) of table, we must create a full-text index on those columns.

The FTS (full-text search) provides the capability to match the searched string value through large text content such as blogs, articles, etc.

MySQL Full-Text Search

To perform a Full-Text Search on a MySQL table, we use MATCH() and AGAINST() functions in a WHERE clause of an SQL SELECT statement.

Stop words are words that are commonly used (such as ”on”, ”the”, or, ”it”) in sentences and will be ignored during the searching process.

The basic syntax to perform a full-text search on a MySQL is as follows −

SELECT column_name(s) FROM table_name
WHERE MATCH(col1, col2, ...)
AGAINST(expression [search_modifier])

Here,

• MATCH() function contains one or more columns separated by commas to be searched.
• AGAINST() function contains a search string to use for the full-text search.

Key Points of MySQL Full-Text Search

Following are some key points about the full-text search in MySQL −

• Either InnoDB or MyISAM tables use the full-text indexes. The minimum length of the word for full-text searches is three characters for InnoDB tables and four characters for MyISAM tables.
• Full-Text indexes can be created on text-based columns (CHAR, VARCHAR or TEXT columns).
• A FULLTEXT index can be defined while creating the table using CREATE TABLE statement or can be defined later using the ALTER TABLE or CREATE INDEX statements.
• Without FULLTEXT index, it is faster to load large data sets into a table than to load data into a table which has an existing FULLTEXT index. Therefore it is recommended to create the index after loading data.

Types of Full-Text Searches

There are three types of full-text searches. The same is described below:

• Natural Language Full-Text Searches: This allows the user to enter the search query in a natural human language without any special characters or operators. The search engine will examine the query entered by the user and returns the relevant results based on the user”s intent.
• Boolean Full-Text Searches: This allows us to perform a full-text search based on very complex queries in the Boolean mode along with Boolean operators such as +, -, >,
• Query Expansion Searches: This expands the user”s query to widen the search result of the full-text searches based on automatic relevance feedback or blind query expansion.

Creating MySQL FULLTEXT Index

In MySQL, we can define a full-text index on particular column while creating a new table or on an existing table. This can be done in three ways:

• Using the FULLTEXT Keyword

• Using the ALTER TABLE Statement

• Using the CREATE INDEX Statement

Using the FULLTEXT Keyword

To define full-text index on a column while creating a new table, we use the FULLTEXT keyword on that column within the CREATE TABLE query. Following is the syntax −

CREATE TABLE table_name(
   column1 data_type,
   column2 data_type,
   ...,
   FULLTEXT (column1, column2, ...)
);

Let us create first a table named FILMS and define the full-text index on NAME and DIRECTOR columns, using the following query −

CREATE TABLE FILMS (
   ID int auto_increment not null primary key,
   NAME varchar(50),
   DIRECTOR TEXT,
   FULLTEXT (NAME, DIRECTOR)
);

Now, let us insert values into this table using the following query −

INSERT INTO FILMS (NAME, DIRECTOR) VALUES
(''RRR'', ''Directed by Rajamouli''),
(''Bahubali'', ''Directed by Rajamouli''),
(''Avatar'', ''Directed by James cameron''),
(''Robot'', ''Directed by Shankar'');

The table will be created as −

Here, we are fetching all the rows from the FILMS table where the NAME or DIRECTOR column matches the string ‘Rajamouli’ using the MATCH and AGAINST functions as shown below −

SELECT * FROM FILMS
WHERE MATCH (NAME, DIRECTOR)
AGAINST (''Rajamouli'');

As we can see in the output below, the full-text search has been performed against a string ‘Rajamouli’ and it returned the rows which contains this string.

Using the ALTER TABLE Statement

In MySQL, we can create full-text index on particular columns of an existing table using the ALTER TABLE statement. Following is the syntax −

ALTER TABLE table_name
ADD FULLTEXT (column1, column2,...)

In this example, we are defining a full-text index named FULLTEXT on NAME and DIRECTOR columns of the previously created FILMS table −

ALTER TABLE FILMS ADD FULLTEXT (NAME, DIRECTOR);

Now, let us retrieve all the rows from the FILMS table where the NAME or DIRECTOR column matches the string ”Shankar”.

SELECT * FROM FILMS
WHERE MATCH (NAME, DIRECTOR)
AGAINST (''Shankar'');

Following is the output −

Using the CREATE INDEX Statement

In MySQL, we can also create a full-text index for an existing table using the CREATE INDEX statement. Following is the syntax −

CREATE FULLTEXT INDEX index_name
ON table_name (index_column1, index_column2,...)

We are creating a full-text index with the name INDEX_FULLTEXT on the NAME and DIRECTOR column of the FILMS table −

CREATE FULLTEXT INDEX INDEX_FULLTEXT ON FILMS (NAME, DIRECTOR);

Now, let us retrieve all the rows from the FILMS table where the NAME or DIRECTOR column matches the string value as shown in the below query −

SELECT * FROM FILMS
WHERE MATCH(NAME, DIRECTOR)
AGAINST (''James Cameron'');

Following is the output −

Dropping MySQL FULLTEXT index

In MySQL, we can remove or drop a full-text index from a table using the ALTER TABLE DROP INDEX statement.

Following is the syntax −

ALTER TABLE table_name DROP INDEX index_name;

In the following query, we will delete the previously created full-text index −

ALTER TABLE FILMS DROP INDEX INDEX_FULLTEXT;

Let us verify whether the index is dropped or not by executing the below query −

SELECT * FROM FILMS
WHERE MATCH(NAME, DIRECTOR)
AGAINST (''James Cameron'');

As we can see in the output, the full-text index is removed on the NAME and DIRECTOR columns.

ERROR 1191 (HY000): Can''t find FULLTEXT index matching the column list

Full-Text Search Using Client Program

In addition to performing the full-text search using MySQL Query, we can also do so using the client program.

Syntax

$sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
$mysqli->query($sql);

sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
con.query(sql);

String sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
statement.execute(sql);

fulltext_search_query = "SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST (''Rajamouli'')"
cursorObj.execute(fulltext_search_query)

Example

Following are the programs −

$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 films that stores fulltext.
$sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
$result = $mysqli->query($sql);
if ($result) {
    printf("Table created successfully...!n");
}
//insert data
$q = "INSERT INTO FILMS (NAME, DIRECTOR) VALUES (''RRR'', ''The film RRR is directed by Rajamouli''), (''Bahubali'', ''The film Bahubali is directed by Rajamouli''),
      (''Avatar'', ''The film Avatar is directed by James cameron''), (''Robot'', ''The film Robot is directed by Shankar'')";
if ($res = $mysqli->query($q)) {
    printf("Data inserted successfully...!n");
}
//now display the table records
$s = "SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST (''Rajamouli'')";
if ($r = $mysqli->query($s)) {
    printf("Table Records: n");
    while ($row = $r->fetch_assoc()) {
        printf(" ID: %d, Name: %s, Director: %s", $row["ID"], $row["NAME"], $row["DIRECTOR"]);
        printf("n");
    }
} else {
    printf(''Failed'');
}
$mysqli->close();

Table created successfully...!
Data inserted successfully...!
Table Records:
 ID: 1, Name: RRR, Director: The film RRR is directed by Rajamouli
 ID: 2, Name: Bahubali, Director: The film Bahubali is directed by Rajamouli

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
  con.query(sql);

  //insert data into created table
  sql = `INSERT INTO FILMS (NAME, DIRECTOR)
  VALUES (''RRR'', ''The film RRR is directed by Rajamouli''),
  (''Bahubali'', ''The film Bahubali is directed by Rajamouli''),
  (''Avatar'', ''The film Avatar is directed by James cameron''),
  (''Robot'', ''The film Robot is directed by Shankar'')`;
  con.query(sql);

  //display the table details!...
  sql = `SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST (''Rajamouli'')`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

[
  {
    ID: 1,
    NAME: ''RRR'',
    DIRECTOR: ''The film RRR is directed by Rajamouli''
  },
  {
    ID: 2,
    NAME: ''Bahubali'',
    DIRECTOR: ''The film Bahubali is directed by Rajamouli''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class FulltextSearch {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //creating a table that takes fulltext column...!
         String sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
         statement.execute(sql);
         System.out.println("Table created successfully...!");

         //inserting data to the tables
         String insert = "INSERT INTO FILMS (NAME, DIRECTOR) VALUES (''RRR'', ''The film RRR is directed by Rajamouli''), (''Bahubali'', ''The film Bahubali is directed by Rajamouli'')," +
                 "(''Avatar'', ''The film Avatar is directed by James cameron''), (''Robot'', ''The film Robot is directed by Shankar'')";
         statement.execute(insert);
         System.out.println("Data inserted successfully...!");

         //displaying the table records...!
         ResultSet resultSet = statement.executeQuery("SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST (''Rajamouli'')");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2)+ " "+resultSet.getString(3));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Connected successfully...!
Table created successfully...!
Data inserted successfully...!
1 RRR The film RRR is directed by Rajamouli
2 Bahubali The film Bahubali is directed by Rajamouli

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
fulltext_search_query = f"SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST (''Rajamouli'');"
cursorObj.execute(fulltext_search_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Full-text search results:")
for row in results:
    print(row)
cursorObj.close()
connection.close()

Full-text search results:
(1, ''RRR'', ''The film RRR is directed by Rajamouli'')
(2, ''Bahubali'', ''The film Bahubali is directed by Rajamouli'')

Regular expressions in MySQL are used in search operations to filter and retrieve records from a database table that match specified patterns.

This process of detecting patterns in a set of data is known as pattern matching. It is helpful whenever the data is considered to have similar characteristics; in such cases, you might locate a pattern in the data and all its occurrences. Pattern matching is usually performed on raw data to make sure it is syntactically correct.

Similarly, in MySQL, pattern matching is performed to check whether the data is correctly stored or not. If not, the incorrect pattern is detected (and sometimes replaced) using functions of regular expressions. The regexp_substr() function is used to detect specified patterns from a set of data.

MySQL REGEXP_SUBSTR() Function

The MySQL regexp_substr() function is used for pattern matching in a database. This function returns the substring of a string that matches the pattern specified, NULL if either there is no match or, the string or the pattern is NULL. Here, a pattern is defined as an extended regular expression or just an ordinary string.

Syntax

Following is the syntax of the MySQL regexp_substr() function −

REGEXP_SUBSTR(expr, pattern[, pos[, occurrence[, match_type]]])

Parameters

The regexp_substr() function takes following parameter values −

• expr: The string in which search is performed

• pattern: The pattern that is searched in the string

This method also accepts following optional arguments −

• pos: Starting position of the search

• occurrence: Which occurrence of a match to replace. If omitted, the default is 1 so it retrieves the first occurrence only.

• match_type: A string that specifies how to perform matching.

Example

Following example shows the usage of MySQL regexp_substr() function on a simple string ”Welcome To Tutorialspoint!” using the SELECT statement as follows −

SELECT REGEXP_SUBSTR(''Welcome To Tutorialspoint!'', ''We'') AS RESULT;

On executing the given query, the output is displayed as follows −

If the pattern is not present in the string, the result is returned as NULL. Let us try to search for the pattern ”Hi” in the same string ”Welcome To Tutorialspoint!” using the following query −

SELECT REGEXP_SUBSTR(''Welcome To Tutorialspoint!'', ''Hi'') AS RESULT;

Following is the output −

Example

Let us pass 5 as value to the ”pos” parameter so the search starts from the 5th position in the given string; and as we are passing the occurrence value as 2, the second occurrence of the pattern ”to” after 5th position will be retrieved, irrespective of its case −

SELECT REGEXP_SUBSTR(''Welcome To Tutorialspoint!'', ''To'', 5, 2, ''i'')
AS RESULT;

Output

When we execute the above query, the output is obtained as follows −

Example

If either of the first two arguments passed to this function is NULL, this function returns NULL. In the below query, we are passing NULL to the expression parameter.

SELECT REGEXP_SUBSTR(NULL, ''value'');

On executing the given program, the output is displayed as follows −

Here, we are passing NULL as a pattern to search −

SELECT REGEXP_SUBSTR(''Welcome to Tutorialspoint'', NULL)
AS Result;

Following is the output −

Example

In the following query, we are performing a search operation on a database table named CUSTOMERS using the REGEXP_SUBSTR() function. Firstly, let us create the table using the query below −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Following query adds 7 records into the above-created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the following query to display all the records of CUSTOMERS table −

Select * from CUSTOMERS;

Following is the CUSTOMERS table −

Now, we are using the REGEXP_SUBSTR() function to retrieve the substring from the the NAME column values that begin with ”Ra”.

SELECT REGEXP_SUBSTR(NAME, ''^Ra'') AS RESULT FROM CUSTOMERS;

Output

As we can see the output below, only the first record in NAME column has a substring ”Ra” in it −

REGEXP_SUBSTR() Funcion Using a Client Program

We can also perform the MySQL REGEXP_SUBSTR() function using the client programs to detect specified patterns from a set of data.

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql = "SELECT REGEXP_SUBSTR(''Welcome To Tutorialspoint!'', ''We'') AS RESULT";
$mysqli->query($sql);

sql = "SELECT REGEXP_SUBSTR(''Welcome To Tutorialspoint!'', ''We'') AS RESULT";
con.query(sql);

String sql = "SELECT REGEXP_SUBSTR(''Welcome To Tutorialspoint!'', ''We'') AS RESULT";
statement.executeQuery(sql);

sql = "SELECT REGEXP_SUBSTR(''Welcome To Tutorialspoint!'', ''We'') AS RESULT"
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT REGEXP_SUBSTR(''Welcome To Tutorialspoint!'', ''We'') AS RESULT";
if($result = $mysqli->query($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("Result: %s", $row[''RESULT'']);
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Result: We

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SELECT REGEXP_SUBSTR(''Welcome To Tutorialspoint!'', ''We'') AS RESULT";
 console.log("Select query executed successfully..!");
 console.log("Table records: ");
 con.query(sql);
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Select query executed successfully..!
Table records:
[ { RESULT: ''We'' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class regexp_substr {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT REGEXP_SUBSTR(''Welcome To Tutorialspoint!'', ''We'') AS RESULT";
            rs = st.executeQuery(sql);
            while(rs.next()) {
                String result = rs.getString("RESULT");
                System.out.println("Result: " + result);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Result: We

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
regexp_substr_query = f"SELECT REGEXP_SUBSTR(''Welcome To Tutorialspoint!'', ''We'') AS RESULT"
cursorObj.execute(regexp_substr_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Result of REGEXP_SUBSTR() Function:")
for row in results:
    result = row[0]
    print(f"The extracted substring is: ''{result}''")
cursorObj.close()
connection.close()

Result of REGEXP_SUBSTR() Function:
The extracted substring is: ''We''

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 −

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 −

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’ −

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 −

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 −

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 −

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 −

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 −

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

$sql = "SELECT * FROM articles WHERE MATCH(ARTICLE_TITLE, DESCRIPTION) AGAINST(''+data -sets'' IN BOOLEAN MODE)";
$mysqli->query($sql);

sql = `SELECT * FROM articles WHERE MATCH(ARTICLE_TITLE, DESCRIPTION) AGAINST(''+data -sets'' IN BOOLEAN MODE)`;
con.query(sql);

String sql = "SELECT * FROM articles WHERE MATCH(ARTICLE_TITLE, DESCRIPTION) AGAINST(''+data -sets'' IN BOOLEAN MODE)";
statement.executeQuery(sql);

boolean_fulltext_search_query = ''select * from articles where MATCH (ARTICLE_TITLE, DESCRIPTION) AGAINST(''data'' IN BOOLEAN MODE)''
cursorObj.execute(boolean_fulltext_search_query)

Example

Following are the programs −

$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();

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

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  sql = "CREATE TABLE Articles (ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, ARTICLE_TITLE VARCHAR(100), DESCRIPTION TEXT, FULLTEXT (ARTICLE_TITLE, DESCRIPTION) )";
  con.query(sql);

  //insert data into created table
  sql = `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'')`;
  con.query(sql);

  //display the table details!...
  sql = `SELECT * FROM articles WHERE MATCH(ARTICLE_TITLE, DESCRIPTION) AGAINST(''+data -sets'' IN BOOLEAN MODE)`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

[
  {
    id: 1,
    ARTICLE_TITLE: ''MySQL Tutorial'',
    DESCRIPTION: ''MySQL is a relational database system that uses SQL to structure data stored''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class BooleanFulltextSearch {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //creating a table that takes fulltext column...!
         String sql = "CREATE TABLE Articles (ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, ARTICLE_TITLE VARCHAR(100), DESCRIPTION TEXT, FULLTEXT (ARTICLE_TITLE, DESCRIPTION) )";
         statement.execute(sql);
         System.out.println("Table created successfully...!");

         //inserting data to the table
         String insert = "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'')";
         statement.execute(insert);
         System.out.println("Data inserted successfully...!");

         //displaying the fulltext records in the boolean mode:
         ResultSet resultSet = statement.executeQuery("SELECT * FROM articles WHERE MATCH(ARTICLE_TITLE, DESCRIPTION) AGAINST(''+data -sets'' IN BOOLEAN MODE)");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2)+ " "+resultSet.getString(3));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Connected successfully...!
Table created successfully...!
Data inserted successfully...!
1 MySQL Tutorial MySQL is a relational database system that uses SQL to structure data stored

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
boolean_fulltext_search_query = f"select * from articles where MATCH (ARTICLE_TITLE, DESCRIPTION) AGAINST(''data'' IN BOOLEAN MODE)"
cursorObj.execute(boolean_fulltext_search_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Boolean Fulltext search results:")
for row in results:
    print(row)
cursorObj.close()
connection.close()

Boolean Fulltext search results:
(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'')

Before we fully get into the concept of Natural Language Full-text Search, let us try to understand the context of it. Nowadays, the keywords used for searches might not always match the results that users expect. So search engines are designed to focus on increasing search relevance to reduce the accuracy gap between search queries and search results. Thus, results are displayed in order of most relevance to the search keyword.

Similarly, in relational databases like MySQL, full-text search is a technique used to retrieve result-sets that might not perfectly match the search keyword. There are three types of search modes used with full-text search −

• Natural Language Mode

• Query Expansion Mode

• Boolean Mode

Natural Language Full-text Search

The Natural Language Full-text search performs the usual Full-text search in the IN NATURAL LANGUAGE mode. When a Full-text search is performed in this mode, the search results are displayed in the order of their relevance to the keyword (against which this search is performed). This is the default mode for the Full-text search.

Since this is a Full-text search, the FULLTEXT indexes must be applied on text-based columns (like CHAR, VARCHAR, TEXT datatype columns). The FULLTEXT index is a special type of index that is used to search for the keywords in the text values instead of trying to compare the keyword with these column values.

Syntax

Following is the basic syntax to perform the Natural Language Full-text Search −

SELECT * FROM table_name
WHERE MATCH(column_name(s))
AGAINST (''keyword_name'' IN NATURAL LANGUAGE MODE);

Example

Let us understand how to perform Natural Language Full-text Search on a database table in the following example.

For that, we will first create a table named ARTICLES containing the title and description of an article. The FULLTEXT index is applied on text columns article_title and descriptions as shown below −

CREATE TABLE ARTICLES (
   ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
   ARTICLE_TITLE VARCHAR(100),
   DESCRIPTION TEXT,
   FULLTEXT (ARTICLE_TITLE, DESCRIPTION)
) ENGINE = InnoDB;

Now, let us insert details about articles, like their titles and DESCRIPTION, into this table using the following queries −

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 language''),
(''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 table is created is as follows −

Using the Natural Language Mode in Full-text search, search for records of articles relevant to data, with the keyword ”data set”.

SELECT * FROM ARTICLES
WHERE MATCH(ARTICLE_TITLE, DESCRIPTION)
AGAINST (''data set'' IN NATURAL LANGUAGE MODE);

Output

Following is the output −

As we see above, among all the articles present in the table, three search results are obtained which are relevant to the term ”data set” and are arranged in the order of their relevance. But note how keyword ”data set” is not a perfect match in the ”MySQL Tutorial” article record and its still retrieved because MySQL deals with data sets as well.

Stop Words in a Search

The Natural Language Full-text Search uses tf-idf algorithm, where ”tf” refers to term frequency and ”idf” is inverse document frequency. The search refers to the frequency of a word in a single document, and the number of documents the word is present in. However, there are some words that the search usually ignores, like words having less than certain characters. InnoDB ignores words with less than 3 characters while MyISAM ignores words less than 4 characters. Such words are known as Stopwords (the, a, an, are etc.).

Example

In the following example, we are performing a simple Natural Language Full-text Search on the ARTICLES Table created above. Let us see how stop words impact the Full-text search by performing it against two keywords: ”Big Tutorial” and ”is Tutorial”.

Searching ”Big Tutorial”:

Following query performs the full-text search in Natural Language Mode against ”Big Tutorial” keyword −

SELECT ARTICLE_TITLE, DESCRIPTION FROM ARTICLES
WHERE MATCH(ARTICLE_TITLE, DESCRIPTION)
AGAINST (''Big Tutorial'' IN NATURAL LANGUAGE MODE);

Output:

The output is obtained as −

Searching ”is Tutorial”:

Following query performs the full-text search in Natural Language Mode against ”is Tutorial” keyword −

SELECT ARTICLE_TITLE, DESCRIPTION FROM Articles
WHERE MATCH(ARTICLE_TITLE, DESCRIPTION)
AGAINST (''is Tutorial'' IN NATURAL LANGUAGE MODE);

Output:

The output is obtained as −

As we see in the example above, since the word ”Tutorial” is present in all the records of the table, all of them are retrieved in both cases. However, the order of relevance is determined by the second word of the keyword specified.

In the first case, as the word ”Big” is present in ”Big Data Tutorial”, that record is retrieved first. In the second case, the order of records in the result-set are the same as that of original table since the word ”is” is a stop word, so it is ignored.

Natural-language-Fulltext-search Using a Client Program

We can also Perform Natural-language-fulltext-search operation on a MySQL database using the client program.

Syntax

$sql = "SELECT * FROM Articles WHERE MATCH(ARTICLE_TITLE, DESCRIPTION)  AGAINST (''data set'' IN NATURAL LANGUAGE MODE)";
$mysqli->query($sql);

sql = `SELECT * FROM Articles  WHERE MATCH(ARTICLE_TITLE, DESCRIPTION) AGAINST (''data set'' IN NATURAL LANGUAGE MODE)`;
con.query(sql);

String sql = "SELECT * FROM Articles WHERE MATCH(ARTICLE_TITLE, DESCRIPTION)  AGAINST (''data set'' IN NATURAL LANGUAGE MODE)";
statement.executeQuery(sql);

natural_language_search_query = ''SELECT * FROM Articles WHERE MATCH(ARTICLE_TITLE, DESCRIPTION) AGAINST (''data set'' IN NATURAL LANGUAGE MODE)''
cursorObj.execute(natural_language_search_query)

Example

Following are the programs −

$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.
'');

$s = "SELECT * FROM Articles WHERE MATCH(ARTICLE_TITLE, DESCRIPTION)  AGAINST (''data set'' IN NATURAL LANGUAGE 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();

Table Records:
ID: 4, Title: Big Data Tutorial, Descriptions: Big Data refers to data that has wider variety of data sets in larger numbers
ID: 1, Title: MySQL Tutorial, Descriptions: MySQL is a relational database system that uses SQL to structure data stored
ID: 3, Title: Hadoop Tutorial, Descriptions: Hadoop is framework that is used to process large sets of data

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //display the table details!...
  sql = `SELECT * FROM Articles  WHERE MATCH(ARTICLE_TITLE, DESCRIPTION)  AGAINST (''data set'' IN NATURAL LANGUAGE MODE)`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

We get the following output, after executing the above NodeJs Program.
[
  {
    id: 4,
    ARTICLE_TITLE: ''Big Data Tutorial'',
    DESCRIPTION: ''Big Data refers to data that has wider variety of data sets in larger numbers''
  },
  {
    id: 1,
    ARTICLE_TITLE: ''MySQL Tutorial'',
    DESCRIPTION: ''MySQL is a relational database system that uses SQL to structure data stored''
  },
  {
    id: 3,
    ARTICLE_TITLE: ''Hadoop Tutorial'',
    DESCRIPTION: ''Hadoop is framework that is used to process large sets of data''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class NaturalLanguageSearch {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //displaying the fulltext records in the Natural language mode:
         ResultSet resultSet = statement.executeQuery("SELECT * FROM Articles WHERE MATCH(ARTICLE_TITLE, descriptions)  AGAINST (''data set'' IN NATURAL LANGUAGE MODE)");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2)+ " "+resultSet.getString(3));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Connected successfully...!
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

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
   host=''localhost'',
   user=''root'',
   password=''password'',
   database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
natural_language_search_query = ''''''
SELECT * FROM Articles
WHERE MATCH(ARTICLE_TITLE, DESCRIPTION)
AGAINST (''data set'' IN NATURAL LANGUAGE MODE)
''''''
cursorObj.execute(natural_language_search_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("NATURAL LANGUAGE search results:")
for row in results:
   print(row)
cursorObj.close()
connection.close()

NATURAL LANGUAGE search results:
(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'')

In relational databases like MySQL, Full-text search is a technique used to retrieve result-sets that might not perfectly match the search keyword. This type of search is useful in cases where the keywords used for searching do not match the results a user expects. So, this searching technique is designed to focus on increasing search relevance in order to reduce the accuracy gap between search queries and search results. Thus, search results are displayed in the order of highest to the lowest relevancy to the search keyword.

There are three types of search modes used with Full-text search −

• Natural Language Mode

• Query Expansion Mode

• Boolean Mode

Query Expansion Full-Text Search

Search is always done by the user with the limited knowledge they possess. Thus, there are cases when the search keywords are way too short to conduct a proper search. This is where Blind Expansion Search technique comes into picture.

Blind Expansion Search, also known as Automatic Relevance Feedback, is used to widen the search results based on additional keywords that are closely related to the original keywords. It is enabled using the ”WITH QUERY EXPANSION” search phrase.

The search is performed twice in this cases by following the steps given below −

Step 1 − All the rows that match the given search keyword are searched first.

Step 2 − These obtained rows are then checked for relevant words to the original keyword in them.

Step 3 − Finally, the rows are searched again based on these relevant words instead of the original keywords specified by the users.

To perform the query expansion full-text search on a database table, the WITH QUERY EXPANSION or IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION search modifiers must be specified in the AGAINST() function.

Example

Let us understand how to perform Query Expansion Full-text Search on a database table in the following example.

For that, we will first create a table named DBMS_TUTORIALS containing the title and description of an article. The FULLTEXT index is applied on text columns TUTORIAL_TITLE and DESCRIPTIONS as shown below −

CREATE TABLE DBMS_TUTORIALS(
   TUTORIAL_TITLE VARCHAR(200),
   DESCRIPTIONS TEXT,
   FULLTEXT(TUTORIAL_TITLE, DESCRIPTIONS)
);

Now, let us insert details about tutorials, like their titles and descriptions, into this table using the following queries −

INSERT INTO DBMS_TUTORIALS VALUES
(''MySQL Tutorial'', ''MySQL is an RDBMS that uses SQL to structure the data stored''),
(''ORACLE Tutorial'', ''ORACLE is an RDBMS that uses SQL to structure the data stored''),
(''MySQL Security'', ''MySQL Database can store sensitive data, so security is required''),
(''MySQL vs MariaDB'', ''Comparing two databases...''),
(''JDBC Tutorial'', ''In this Java-based database connectivity...'');

The table is created as −

Using the Query Expansion Mode in full-text search, we search for records of articles relevant to data, with the keyword ‘RDBMS’

SELECT * FROM DBMS_TUTORIALS
WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS)
AGAINST (''RDBMS'' WITH QUERY EXPANSION);

Output

The output is obtained as −

IN NATURAL LANGUAGE MODE

In the result-set obtained above, all tutorial records are about databases, which is why the query retrieved all the records ordered based on relevance.

SELECT * FROM DBMS_TUTORIALS
WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS)
AGAINST (''Security'' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);

Output

The output is obtained as −

In this result-set, even if the search keyword is ”Security”, the actual security related tutorials are just ”MySQL Security” and ”JDBC Tutorial”, so they are retrieved first. These records are then followed by database related records as an expanded query.

Query Expansion Full-Text Search Using Client Program

We can also Perform Query expansion full-text search operation using the client program.

Syntax

$sql = "SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) AGAINST (''RDBMS'' WITH QUERY EXPANSION)";
$mysqli->query($sql);

sql = "SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) AGAINST (''RDBMS'' WITH QUERY EXPANSION)";
con.query(sql);

String sql = "SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) AGAINST (''RDBMS'' WITH QUERY EXPANSION)";
statement.executeQuery(sql);

queryexpansionfulltext_search = ''SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS)  AGAINST (''RDBMS'' WITH QUERY EXPANSION)''
cursorObj.execute(queryexpansionfulltext_search)

Example

Following are the programs −

$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.
'');
/*CREATE Table*/
$sql = "CREATE TABLE DBMS_TUTORIALS(TUTORIAL_TITLE VARCHAR(200), DESCRIPTIONS TEXT, FULLTEXT(TUTORIAL_TITLE, DESCRIPTIONS))";
$result = $mysqli->query($sql);
if ($result) {
    printf("Table created successfully...!n");
}
//insert data
$q = "INSERT INTO DBMS_TUTORIALS (TUTORIAL_TITLE , DESCRIPTIONS) VALUES
(''MySQL Tutorial'', ''MySQL is an RDBMS that uses SQL to structure the data stored''),
(''ORACLE Tutorial'', ''ORACLE is an RDBMS that uses SQL to structure the data stored''),
(''MySQL Security'', ''MySQL Database can store sensitive data, so security is required''),
(''MySQL vs MariaDB'', ''Comparing two databases...''),
(''JDBC Tutorial'', ''In this Java-based database connectivity...'')";
if ($res = $mysqli->query($q)) {
    printf("Data inserted successfully...!n");
}
//Using the Query Expansion Mode in Full-text search, try to search for records of DBMS_TUTORIALS relevant to data, with the keyword ''RDBMS''
$s = "SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) AGAINST (''RDBMS'' WITH QUERY EXPANSION)";
if ($r = $mysqli->query($s)) {
    printf("Table Records: n");
    while ($row = $r->fetch_assoc()) {
        printf("Tutorial_title: %s, Descriptions: %s", $row["TUTORIAL_TITLE"], $row["DESCRIPTIONS"]);
        printf("n");
    }
} else {
    printf(''Failed'');
}
$mysqli->close();

Table created successfully...!
Data inserted successfully...!
Table Records:
Tutorial_title: ORACLE Tutorial, Descriptions: ORACLE is an RDBMS that uses SQL to structure the data stored
Tutorial_title: MySQL Tutorial, Descriptions: MySQL is an RDBMS that uses SQL to structure the data stored
Tutorial_title: MySQL Security, Descriptions: MySQL Database can store sensitive data, so security is required
Tutorial_title: MySQL vs MariaDB, Descriptions: Comparing two databases...
Tutorial_title: JDBC Tutorial, Descriptions: In this Java-based database connectivity...

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //create a table...
  sql =
    "CREATE TABLE DBMS_TUTORIALS(TUTORIAL_TITLE VARCHAR(200), DESCRIPTIONS TEXT, FULLTEXT(TUTORIAL_TITLE, DESCRIPTIONS))";
  con.query(sql);

  //insert data
  sql = `INSERT INTO DBMS_TUTORIALS (TUTORIAL_TITLE , DESCRIPTIONS) VALUES
  (''MySQL Tutorial'', ''MySQL is an RDBMS that uses SQL to structure the data stored''),
  (''ORACLE Tutorial'', ''ORACLE is an RDBMS that uses SQL to structure the data stored''),
  (''MySQL Security'', ''MySQL Database can store sensitive data, so security is required''),
  (''MySQL vs MariaDB'', ''Comparing two databases...''),
  (''JDBC Tutorial'', ''In this Java-based database connectivity...'')`;

  con.query(sql);

  //Using the Query Expansion Mode in Full-text search, try to search for records of DBMS_TUTORIALS relevant to data, with the keyword ''RDBMS''
  sql = "SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) AGAINST (''RDBMS'' WITH QUERY EXPANSION)";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

[
    {
      TUTORIAL_TITLE: ''ORACLE Tutorial'',
      DESCRIPTIONS: ''ORACLE is an RDBMS that uses SQL to structure the data stored''
    },
    {
      TUTORIAL_TITLE: ''MySQL Tutorial'',
      DESCRIPTIONS: ''MySQL is an RDBMS that uses SQL to structure the data stored''
    },
    {
      TUTORIAL_TITLE: ''MySQL Security'',
      DESCRIPTIONS: ''MySQL Database can store sensitive data, so security is required''
    },
    {
      TUTORIAL_TITLE: ''MySQL vs MariaDB'',
      DESCRIPTIONS: ''Comparing two databases...''
    },
    {
      TUTORIAL_TITLE: ''JDBC Tutorial'',
      DESCRIPTIONS: ''In this Java-based database connectivity...''
    }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class QeFSearch {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //creating a table that takes fulltext column...!
         String sql = "CREATE TABLE DBMS_TUTORIALS(TUTORIAL_TITLE VARCHAR(200), DESCRIPTIONS TEXT, FULLTEXT(TUTORIAL_TITLE, DESCRIPTIONS))";
         statement.execute(sql);
         System.out.println("Table created successfully...!");

         //inserting data to the table
         String insert = "INSERT INTO DBMS_TUTORIALS (TUTORIAL_TITLE , DESCRIPTIONS) VALUES" +
         "(''MySQL Tutorial'', ''MySQL is an RDBMS that uses SQL to structure the data stored'')," +
         "(''ORACLE Tutorial'', ''ORACLE is an RDBMS that uses SQL to structure the data stored'')," +
         "(''MySQL Security'', ''MySQL Database can store sensitive data, so security is required'')," +
         "(''MySQL vs MariaDB'', ''Comparing two databases...'')," +
         "(''JDBC Tutorial'', ''In this Java-based database connectivity...'')";
         statement.execute(insert);
         System.out.println("Data inserted successfully...!");

         //Using the Query Expansion Mode in Full-text search, try to search for records of DBMS_TUTORIALS relevant to data, with the keyword ''RDBMS''...!
         ResultSet resultSet = statement.executeQuery("SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) AGAINST (''RDBMS'' WITH QUERY EXPANSION)");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Connected successfully...!
Table created successfully...!
Data inserted successfully...!
ORACLE Tutorial ORACLE is an RDBMS that uses SQL to structure the data stored
MySQL Tutorial MySQL is an RDBMS that uses SQL to structure the data stored
MySQL Security MySQL Database can store sensitive data, so security is required
MySQL vs MariaDB Comparing two databases...
JDBC Tutorial in this Java-based database connectivity...

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
queryexpansionfulltext_search = ''''''
SELECT * FROM DBMS_TUTORIALS
WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS)
AGAINST (''RDBMS'' WITH QUERY EXPANSION)
''''''
cursorObj.execute(queryexpansionfulltext_search)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Query expansion Fulltext search results:")
for row in results:
    print(row)
cursorObj.close()
connection.close()

Query expansion Fulltext search results:
(''ORACLE Tutorial'', ''ORACLE is an RDBMS that uses SQL to structure the data stored'')
(''MySQL Tutorial'', ''MySQL is an RDBMS that uses SQL to structure the data stored'')
(''MySQL Security'', ''MySQL Database can store sensitive data, so security is required'')
(''MySQL vs MariaDB'', ''Comparing two databases...'')
(''JDBC Tutorial'', ''In this Java-based database connectivity...'')

Many user applications require the storage of different types of data, including text, images, files, and more. Using BLOBs in a MySQL database allows you to store all these types of data within the same database, eliminating the need for a separate file system.

The MySQL BLOB Data Type

The MySQL BLOB (Binary Large Object) data type is used to store binary data, such as images, audio, video, or any other type of binary file. BLOB columns can store variable-length binary data, making it suitable for handling files of various sizes.

Consider an application that collects user information through forms. This information may include personal details, such as name and address, along with image proofs like PAN cards or AADHAR cards. Instead of managing these files separately in a file system, you can store them as BLOBs in a MySQL database.

Syntax

Following is the basic syntax to assign BLOB data type on a table field −

CREATE TABLE table_name (column_name BLOB,...)

Example

Let us consider a basic example to show how to assign BLOB datatype to a table field. Here, we are creating a table named ”demo_table” with two fields “ID” and “DEMO_FILE” −

CREATE TABLE demo_table (
   ID INT NOT NULL,
   DEMO_FILE BLOB
);

Following is the output obtained −

Query OK, 0 rows affected (0.01 sec)

You can see the table structure with the following command −

DESC demo_table;

The table obtained is as follows −

Inserting Data into BLOB Fields

You can insert some values into a database table, by loading a file to the BLOB field using the LOAD_FILE() function. However, before doing so, ensure that the following conditions are met −

• File Existence −The file you want to insert must exist on the MySQL server host location. To determine the required location, you can use the secure_file_priv variable with the following command. If the result of this command is not empty, the file to be loaded must be located in that specific directory.

SHOW VARIABLES LIKE secure_file_priv;