intro
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.
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:
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:
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:
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.
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.
