CODEWITHSUNDEEP
mysql
NoviceMe
NoviceMe

Reputation: 3256

Using mysql variable in query to run a like query

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?

Upvotes: 1

Views: 76

Answers (2)

Krish
Krish

Reputation: 5917

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;

Upvotes: 0

Mihai
Mihai

Reputation: 26784

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

Upvotes: 3

Related Questions