Character set 'binary' cannot be used in conjunction with 'utf8mb4_unicode_ci' in call to regexp_like

user_11047183 0
Viewed 2987

I've been using a case sensitive regular expression in a MySQL query for awhile and it has worked perfectly. It suddenly stopped working this weekend, I'm guessing after an update.

Per other people's questions, I learned to use CAST as BINARY for getting the query to be case sensitive.

Below is a simplified version of the query I am using.

SELECT * FROM Table
WHERE CAST(Name AS BINARY) REGEXP 'anything in here really'

I am now getting the error:

Character set 'binary' cannot be used in conjunction with 'utf8mb4_unicode_ci' in call to regexp_like.

I currently use utf8_general_ci on everything, but tried changing the column, table, and even database to utf8_bin without any change in the error message.

I'm running mysqlnd 7.4.3 on Ubuntu and doing most everything through PHP, but debugging in phpMyAdmin.

I should also point out that I am an extreme novice who doesn't used MySQL much and and never really has a need beyond the most basic things, so online guides are the only way I've even gotten this far.

Thank you for any help you can provide!

mysql
1 Answers

I just encountered this error with queries like:

SELECT * FROM Table
WHERE Name REGEXP BINARY 'anything in here really'

...which previously worked without error.

I'm finding that changing it to the following kind of thing is working:

SELECT * FROM Table
WHERE CAST(Name AS BINARY) REGEXP BINARY 'anything in here really'
user_69512030
Related