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
– NoviceMe, Commented Nov 22, 2014 at 12:43

Mihai · Accepted Answer · 2014-11-22 12:51:20Z

3

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

answered Nov 22, 2014 at 12:51

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Krish · Accepted Answer · 2014-11-22 12:53:12Z

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, 2014 at 12:53

Krish

5,9272 gold badges19 silver badges37 bronze badges

Collectives™ on Stack Overflow

Using mysql variable in query to run a like query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related