Considerations when using wildcards in MySQL _ MySQL

This article mainly introduces the issues that should be paid attention to when using wildcards in MySQL, mainly the errors that are easily caused by the use of underlines. For more information, see Symptom:

There is a table action_conf with the following data:
If you want to obtain an en_name record starting with exp_site_10 _, how can I write an SQL statement?

So easy!

Select en_name from action_conf where en_name like 'Exp _ site_10 _ %'

If you execute this SQL statement in idb, you will find that the result is not as expected.

You will find that all the records starting with exp_site_10 will be listed when you execute the preceding SQL statement.

Cause:

In fact, these are all wildcards in SQL. In SQL, underscore _ is a wildcard that can match any single character.

Since you know the reason, it is easy to modify the SQL statement. The correct SQL statement should be:

Select en_name from action_conf where en_name like 'Exp \ _ site \ _ 10 \ _ %'

After the transfer character is added before the wildcard, mysql regards the wildcard as a common character.

Advanced:

Wildcard collation:

% Replace one or more characters

_ Replace only one character

[Charlist] any single character in the character column

[^ Charlist] or [! Charlist] any single character not in the character column