Using mysql variable in query to run a like query

Question

I am trying this:

SET @query1 =  'star';
   SELECT *
   FROM businesses
   WHERE business_name LIKE @query1

But i get following error:

Error Code: 1267 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation 'like'

What am I doing wrong?

Answer 1

Just specify the collation

SELECT * FROM businesses WHERE business_name LIKE @query1 COLLATE utf8_unicode_ci

For a more permanent fix you need to change the collation of the server to be compatible with the db or viceversa.I dont have much experience in collations so you have do to a bit of reading.

Docs

Answer 2

weird.. but you can convert your table to that charset like:

ALTER TABLE mydb.businesses  DEFAULT COLLATE utf8_unicode_ci;
ALTER TABLE mydb.businesses CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;