CODEWITHSUNDEEP
mysqllaravel-5collation
Adam
Adam

Reputation: 29119

COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'binary'

I want to find a user in my Laravel app with name "Yılmaz" by searching for "Yilmaz"

by default in Laravel (with utf8mb4_unicode_ci as collation for my connection) this

\App\User::whereRaw('name LIKE ?', '%Yilmaz%')->get();

returns nothing.

As I found out at MySQL does not treat ı as i?, it works with collation utf8mb4_general_ci. However, it is not recommended to use utf8mb4_general_ci as default collation because it is not accuratly sorting.

My first try was 

\App\User::whereRaw('name LIKE ? collate utf8mb4_general_ci', '%Yilmaz%')->get();

but this throws

SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'binary' (SQL: select * from users where name LIKE %Yilmaz% collate utf8mb4_general_ci)

Next, I tried https://stackoverflow.com/a/58493518/2311074

\App\User::whereRaw('name LIKE convert(? using utf8mb4)', '%Yilmaz%')->get();

but this returns

SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation 'like' (SQL: select * from users where name LIKE convert(%Yilmaz% using utf8mb4))

How can I change collation to utf8mb4_general_ci in Laravel only for one query?

Upvotes: 1

Views: 4238

Answers (1)

Remul
Remul

Reputation: 8252

A combination of both works for me:

\App\User::whereRaw(
    'name LIKE convert(? using utf8mb4) collate utf8mb4_general_ci',
    '%Yilmaz%'
)->get();

But I am not sure if this is the best approach.

Upvotes: 4

Related Questions