Issues to be aware of when using wildcard characters in Mysql _mysql

Phenomenon:

There is a table action_conf, the data is as follows:
If you want to get a en_name record that starts with exp_site_10_, how do you write the SQL statement?

So easy!

Select En_name from action_conf where en_name like ' exp_site_10_% '

Very confident that the SQL was executed in the IDB and that the results were not expected.

You'll find that executing the SQL above lists all the records that start with exp_site_10.

Reason:

In fact, this is all in the SQL wildcard characters in the mischief. In SQL, an underscore _ is a wildcard that matches any single character.

Now that you know why, it's easy to modify SQL. The correct SQL should be:

Select En_name from action_conf where en_name like ' exp\_site\_10\_% '

After adding the transfer character to the wildcard, MySQL treats the wildcard as a normal character.

Advanced Step:

Wildcard collation:

% replaces one or more characters

_ Replaces only one character

Any single character of the [charlist] Word columns

[^charlist] or any single character [!charlist] that is not a word columns