Comprehensive Guide on LIKE and FULLTEXT Search Wildcards

Wildcards in MySQL are a frequent source of confusion even for the most experienced DBAs in this area. Read this blog and find out everything you need to know about them and their inner demons.

Tools used in this tutorial

DbVisualizer, top rated database management tool and SQL client
The MySQL database version 8 or later

Those who had a database-backed project will have surely heard about wildcards at some point in their career. Wildcards in database management systems serve a very unique and interesting purpose – they let us search for data in very exotic and interesting ways. For example, running a query like so:

1 SELECT * FROM [your_table] WHERE [your_column] LIKE ‘string%’;

Is ought to provide some interesting results and part of that is related to the query structure in and of itself. LIKE queries allow us to build on an already interesting functionality available within MySQL – it’s a wildcard character! MySQL also offers another approach to using MySQL wildcards with FULLTEXT-based queries: if you use a FULLTEXT index, you can also run queries like so:

1 SELECT * FROM [your_table] WHERE MATCH([column]) AGAINST('"[string]*"' [MODE]);

Interesting, right?

What Are Wildcards in MySQL? Why Should I Be Concerned?

MySQL wildcards allow us to perform fuzzy matching searches in a database. There are two types of wildcards in MySQL:

• Wildcards that can be used with a LIKE query.
• Wildcards that can be used as part of a fulltext-powered SQL query (in such a case, all columns must have a FULLTEXT index on them.)

You already see examples of both of such wildcard-based queries in action above – but there’s much more to them than meets the eye.

MySQL LIKE Wildcard

If you find yourself using LIKE queries and want to use wildcard symbols to extract data without knowing its full structure, keep the following things in mind:

• A LIKE SQL query will be way more effective with a wildcard only at the end of the search statement. Avoid using a wildcard at the beginning of your search statement because in that case you would tell your database that anything can precede your search query, and that can make the query slower.

• A wildcard in a LIKE query is a percentage (“%”) sign. LIKE queries, as such, have other types of signs available to be used in conjunction though and one of those is the underscore (“_”) sign. An underscore in a LIKE query means “match any character in this string” and can be used as follows:
  
  
  

• LIKE queries won’t use an index if you use a percentage sign before the search query.

• Columns that you run LIKE queries on can have all types of indexes, including FULLTEXT indexes, on them.

• It’s useful to use backslashes (“\”) within your LIKE queries when searching for exact matches of data to escape certain characters (that would be the case if your column has a “_” sign in it and you’re searching for that exact symbol.)

LIKE queries are not too complex to understand and by understanding these points you will have a clearer view of what you can do with your data.

MySQL Wildcard with FULLTEXT Indexes

Utilizing wildcards on FULLTEXT-based columns is a different topic altogether. FULLTEXT indexes are known to offer a wide variety of additional things to choose from including search modes and wildcards as well. We won’t get into the search modes of fulltext-based columns in this blog, but feel free to have a read here if you’re interested in how they work.

Adding a fulltext search index onto a MySQL table.
Consider the following tips for using MySQL full text search wildcards.

Firstly, keep in mind that wildcards on FULLTEXT-based columns work a little differently: they’re the “*” signs and not the “%” sign (see example above) - wildcards can also be used only with the Boolean search mode together with the fulltext search. That means that your SQL query making use of fulltext indexes and a wildcard would look like this:

1 SELECT * FROM [your_table] WHERE MATCH([column]) AGAINST (‘Demo*’ IN BOOLEAN MODE);

Secondly, don’t forget the IN BOOLEAN MODE search modifier – other search modes don’t have this feature and this is the only available fulltext wildcard search method in MySQL.

Also keep in mind that if you have very big data sets and are running queries in boolean mode that search for “@” signs on an older version of MySQL, that would be no longer feasible – running queries like so:

1 SELECT * FROM [your_table] WHERE MATCH([column]) AGAINST (‘demo@demo.com’ IN BOOLEAN MODE);

Would mean death for your MySQL database. Why? That’s a bug within the RDBMS! Our friends over at Database Dive like to dive into similar things concerning databases and their performance, so make sure to have a look over there if you’re interested as well.

DbVisualizer and Search Queries

Now that you know your way around wildcards in MySQL, remember to monitor the performance of your MySQL database. That’s frequently easier said than done, but with SQL clients like DbVisualizer at the helm, doing so is a piece of cake. DbVisualizer is a top-rated SQL client used by notable companies like Tesla, Honda, Citi, Netflix, NASA, and the rest and it can help manage any kind of database management system, be it MySQL, PostgreSQL, SQL Server, Cassandra, MongoDB, SQLite, ClickHouse, or any other DBMS.

Blogs like these will help you solve specific issues, and SQL clients like DbVisualizer will help you ensure that your database always stays on top of its game.

Make sure to evaluate DbVisualizer in your company environment today – we’re confident that you’ll like what it has to offer!

Summary

In this blog, we’ve walked you through two types of wildcards in MySQL: an ordinary LIKE wildcard search and a fulltext-based wildcard search.

We hope that this blog has been informative and useful for you, and until next time!

About the author

Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.