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?

@AmitGarg - i am trying to use this in phpMyAdmin. It is not working — NoviceMe, Nov 22 '14 at 12:43
[This Link](http://stackoverflow.com/questions/8537516/how-to-use-an-user-variables-in-mysql-like-clause) help you — Mohamed Jaleel Nazir, Nov 22 '14 at 12:48

score 3 · Accepted Answer · answered Nov 22 '14 at 12:51

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

score 0 · Answer 2 · answered Nov 22 '14 at 12:53

0

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;

answered Nov 22 '14 at 12:53

Krish

5,917
2
14
35

Using mysql variable in query to run a like query

2 Answers2