Wildcards in MySQL: A Comprehensive Guide on LIKE and FULLTEXT Search Wildcards
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:
A LIKE query in DbVisualizer.
↓
↑ A LIKE query in DbVisualizer.
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:
A wildcard in a fulltext-based search query.
↓
↑ A wildcard in a fulltext-based search query.
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:
A wildcard symbol with an underscore.
↓
↑ A wildcard symbol with an underscore.
- 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.
To use wildcards with FULLTEXT indexes, first add a FULLTEXT index onto your column:
Adding a fulltext search index onto a MySQL table.
↓
↑ 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:
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:
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!
