MySQL and MariaDB- Wildcard Filtering

<<Back to MySQL & MariaDB Main Page
I am struggling with maxscale basic authentication and wanted to find out some workaround. There are several limitation while using maxscale (eg. MaxScale can not manage authentication that uses wildcard matching in hostnames in the mysql.user table of the backend database. The only wildcards that can be used are in IP address entries.) mariadb-maxscale-limitations-and-known-issues
So I was trying to find out all the user@host combination having % (wildcard) in its hostname. This took me a while, to find a way to negate the effect of an special character in MySQL and MariaDB and this motivated me to write this blog today.

What is wildcard filtering?

Wildcard filtering is a mechanism for comparing text in a column against a search pattern. eg.
Example 1
SELECT user, host
FROM mysql.user
WHERE user LIKE '%tiwari_anand%';
This query will retrieve all users and its hostname from mysql.user table if the user contains the string 'tiwari_anand'. How this works will be explained in the article.

Requirement of wildcard filtering?

we need wildcard filtering if we don't know the precise value of a filed. Without wildcard filtering, we would only be able to filter data against precisely known values. eg.
you want to find all the user starts with certain character
or
You want to search the descriptions of a product catalog in case you don't know the exact product name
The LIKE operator allows wildcards to be used in search clauses  as shown in above example Example1. Let us see the various possible scenarios to use wildcard with like operator.
Example2
Below query selects the columns prod_id and prod_name from the tab5 table. Return the rows whose prod_name begins with the string 'BINY' and is followed by 0 or more occurrences of any character
SELECT prod_id, prod_name
FROM tab5
WHERE prod_name LIKE 'BINY%';
+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| PNCG01  | BINY TOYS    |
| PNCG02  | BINY TOYS    |
| PNCG01  | BINY FASHION |
| PNCG02  | BINY FASHION |
+---------+--------------+
4 rows in set (0.00 sec)
Example3
Let us examine the result of the following query .
SELECT prod_id, prod_name
FROM tab5
WHERE prod_name LIKE '%BINY%';
+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| PNCG01  | BINY TOYS    |
| PNCG02  | BINY TOYS    |
| PNCG01  | BINY FASHION |
| PNCG02  | BINY FASHION |
| PNCG01  | FASHION BINY |
| PNCG01  | TOMBINYSTYLE |
+---------+--------------+
6 rows in set (0.00 sec)
this time we get 6 results. this query returns all prod_name having BINY somewhere in its name.
Example4
If you see the output of below query it returns all the prod_name from tab5 table having "T (and then any one character) and then YS" in its name
SELECT prod_id, prod_name
FROM tab5
WHERE prod_name LIKE '%T_YS%';
+---------+-----------+
| prod_id | prod_name |
+---------+-----------+
| PNCG01  | BINY TOYS |
| PNCG02  | BINY TOYS |
+---------+-----------+
2 rows in set (0.01 sec)
Example5
Now I want to find all the user name from mysql.user table where hostname contains % wildcard character.
Let us see the below query
select user,host from mysql.user where host like '%';
263 rows in set (0.08 sec).
Yes because as we can see in the above examples % is used to find any patter and since I am filtering everything containing (nothing) mens I am selecting everything.
Anyways let us see to find the desired result.
select user,host from mysql.user where host like '%\%%';
34 rows in set (0.00 sec)
select user,host from mysql.user where host like '%\%%' and user like '%anand%';
+--------------+---------------------------+
| user         | host                      |
+--------------+---------------------------+
| anand_tiwari | d-test-plt%.test.com |
+--------------+---------------------------+
1 row in set (0.00 sec)
Note : you can negate the special effect of the character by just inserting \ before it.