MySQL search: Searching for data in tables

This article will introduce a couple of ways for MySQL search data in tables. First, how to search for data in tables will be shown using the classic Where clause and Like operator. Then, MySQL full-text search feature will be introduced and, in the end, how to perform data search will be shown using a third-party extension for VS Code, called ApexSQL Database Power Tools for VS Code.

For MySQL search data, as an example, data from the sakila database will be used.

Where clause search

Where clause is used for filtering records, data in databases. Where clause is used to extract data from tables that fulfill a specified condition. It is often used in conjunction with Select statement, but it can be used with Delete, Update statement, etc.

In our examples, the Where clause will be used with Select statement, in order to find (list) corresponding results from the tables that match the given condition.

The following example selects all actors whose last name is AKROYD, in the actor table:

SELECT actor. actor_id, 
      actor. first_name, 
      actor. last_name, 
      actor. last_update
FROM sakila. actor
WHERE actor. last_name = 'AKROYD';

Using INNER JOIN to select matching values from multiple tables. In the example below, there is a statement which will find all actors that are played in the ACE GOLDFINGER movie:

SELECT first_name, 
        last_name
FROM film 
    INNER JOIN film_actor USING(film_id)
    INNER JOIN actor USING(actor_id)
WHERE title = 'ACE GOLDFINGER';

Like operator search

This operator is often used in combination with the Where clause to search for a specified pattern in a column.

The % (percent) and _ (underscore ) are often used in conjunction with the Like operator.

The percent replaces zero, one, or multiple characters while underscore replaces a single character.

So let’s find all actors that are played in the movie which title contains the Academy word:

SELECT first_name, 
      last_name,
      film.title
FROM film
      INNER JOIN film_actor USING(film_id)
      INNER JOIN actor USING(actor_id)
WHERE title LIKE '%Academy%';

MySQL full-text search

Full-text search is a MySQL search technique to search for data in a database. Please note that not all engines support the full-text search feature. Starting from MySQL version 5.6 or higher, the MyISAM and InnoDB storage engines support a full-text search.

To enable full-text search, first, the FULLTEXT index needs to be created on the table columns on which the user wants to search data. Note that the FULLTEXT index can be created only in columns that have CHAR, VARCHAR, or TEXT data type.

Enabling the FULLTEXT index can be done by using the CREATE TABLE statement, then creating a new table using the ALTER TABLE or CREATE INDEX statement for the existing tables.

In our case, the Alter statement will be used to modify the film table and add the FULLTEXT index over the title column:

ALTER TABLE film
ADD FULLTEXT(title);

The FULLTEXT index can be added in one or more columns of the table.

To perform the full-text search, the MATCH() and AGAINST() functions need to be used.

The MATCH() function specifies which set of columns are indexed using full-text search. The columns list provided in the MATCH () function needs to be the same as the list of columns used for creating the FULLTEXT index.

So, if the below statement is executed:

SELECT first_name, 
        last_name,
        title
FROM film
    INNER JOIN film_actor USING(film_id)
    INNER JOIN actor USING(actor_id)
WHERE MATCH (title, last_name) AGAINST ('Academy');

The following message will appear:

Incorrect arguments to MATCH

The error message will also occur when using the MATCH() and AGAINST() functions over a table where full text search is not enabled:

SELECT `city`.`city_id`, 
  `city`.`city`, 
  `city`.`country_id`, 
  `city`.`last_update`
FROM `sakila`.`city`
WHERE MATCH (`city`) AGAINST ('Abha');

The message will be:

Can’t find FULLTEXT index matching the column list

In the AGAINST() function, it is specified for which word we’re performing a full-text search. In our example, that will be the Academy word. When executing the statement below:

SELECT first_name, 
       last_name,
       title
FROM film
    INNER JOIN film_actor USING(film_id)
    INNER JOIN actor USING(actor_id)
WHERE MATCH (title) AGAINST ('Academy');

The following result will be shown:

By default, the full-text search uses the natural language MySQL search mode, which means that the provided word is in the AGAINST() function search directly from user input without any pre-processing. So the same result will be shown in the results grid if in the AGAINST() function is specified the IN NATURAL LANGUAGE MODE modifier:

SELECT first_name, 
       last_name,
       title
FROM film
     INNER JOIN film_actor USING(film_id)
     INNER JOIN actor USING(actor_id)
WHERE MATCH (title) AGAINST ('Academy' IN NATURAL LANGUAGE MODE);

Besides the IN NATURAL LANGUAGE MODE search modifiers, there are other modifiers e.g. IN BOOLEAN MODE for Boolean text searches.

In Boolean mode, you can specify the searched keyword along with Boolean operates. For example, the + and – operators indicate that a keyword must be present or absent, respectively, for a match to occur.

Execute the below statement with the IN BOOLEAN MODE search modifier:

SELECT first_name, 
        last_name,
        title
FROM film
      INNER JOIN film_actor USING(film_id)
      INNER JOIN actor USING(actor_id)
WHERE MATCH (title) AGAINST ('+ Academy - DINOSAUR' IN BOOLEAN MODE);

MySQL search using a third-party software

The Text search feature is a part of ApexSQL Database Power Tools for VS Code extension that searches text within database objects, data stored in tables, and views.

To search for text (data) stored in the tables and views, in the ApexSQL server explorer pane, select a server or a database, right-click, and, from the context menu, choose the Text search command:

The Text search pane will be opened. In the Search phrase box, enter the search string and if it is not chosen, from the Database drop-down box, select a database to search in. In the search grid, choose tables and views of interest or leave them all checked. To narrow down the MySQL search data scope, select the table, views, numeric, text type, and date columns checkboxes. To start the search, click the Find button or hit the Enter key from the keyboard.

All found values will be shown in the text search results grid: