MySQL
WILDCARD

Wildcards in MySQL: A Comprehensive Guide on LIKE and FULLTEXT Search Wildcards

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.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
MySQL logo MySQL
THE MYSQL DATABASE

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:

Copy
        
1 SELECT * FROM [your_table] WHERE [your_column] LIKE ‘string%’;
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:

Copy
        
1 SELECT * FROM [your_table] WHERE MATCH([column]) AGAINST('"[string]*"' [MODE]);
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:

  • 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:

Copy
        
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:

Copy
        
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.

Dbvis download link img
About the author
LukasVileikisPhoto
Lukas Vileikis
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.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

Counter in MySQL: Counting Rows with Ease

author Lukas Vileikis tags MySQL SQL 8 min 2024-10-03
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30
title

The MySQL RENAME COLUMN Statement: How, What, and Why

author Lukas Vileikis tags MySQL SQL 6 min 2024-09-26
title

SQL FETCH: Retrieving Data In Database Cursors

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-09-24
title

MySQL Binary Logs – Walkthrough

author Lukas Vileikis tags Binary Log MySQL 6 min 2024-09-18
title

MySQL SHOW TABLES Statement: What it is, How It Works, What It Means for You

author Lukas Vileikis tags MySQL 5 min 2024-09-16
title

Distributing Data in a Database: A Guide to Database Sharding

author Lukas Vileikis tags MySQL OPTIMIZATION SQL 7 min 2024-09-09
title

MariaDB Docker: Server Setup Guide

author Lukas Vileikis tags MySQL SQL 5 min 2024-08-29
title

Everything You Need to Know About MySQL Full-Text Search

author Lukas Vileikis tags Full text search MySQL 6 min 2024-08-28
title

MySQL Error #1045 Explained: Everything You Need to Know

author Lukas Vileikis tags MySQL SQL 5 min 2024-08-26

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗