In MySQL How do I specify Collation for WHERE-IN clause?

Question

In MySQL, I can specify the collation if I have a standard WHERE clause like this:

SELECT * FROM myTable WHERE email_address = 'foo@bar.com' COLLATE utf8_general_ci;

However the following fails if I have multiple email addresses in a WHERE-IN clause

SELECT * FROM myTable WHERE email_address IN ('foo@bar.com', 'test@test.com') COLLATE utf8_general_ci;

What am I missing to get this to work? Mysql 5.5.

Thanks!

Why do you use collation for `email` column which by definition should always have only ascii-safe characters? And what collation is the column itself? — zerkms, Jan 09 '13 at 01:24
I wish that were the case. Emails can have all sorts of interesting characters: http://stackoverflow.com/questions/3844431/are-email-addresses-allowed-to-contain-non-alphanumeric-characters — Newtang, Jan 09 '13 at 01:37
oh that's interesting. Why didn't you create the column with utf8 collation then? — zerkms, Jan 09 '13 at 01:40
It should be, and I plan on changing it. The collate is a temporary stopgap solution. — Newtang, Jan 09 '13 at 01:43

score 3 · Accepted Answer · answered Jan 09 '13 at 01:23

3

What if you specify it after every string:

WHERE email_address IN ('foo@bar.com' COLLATE utf8_general_ci, 'test@test.com' COLLATE utf8_general_ci)

?

answered Jan 09 '13 at 01:23

zerkms

1

It appears this works when having only a single COLLATE clause within IN. – Newtang Jan 16 '13 at 01:03
@Newtang: how exactly? And what happens if you specify it several times? – zerkms Jan 16 '13 at 01:06
I tried putting it after the first entry, and at the very end. I didn't test it extremely thoroughly, but it seems like the results are the same as putting it in several times. I did notice a significant slowdown for large queries on occasion though. – Newtang Jan 16 '13 at 01:13
@Newtang: it's an expected slowdown - fullscan isn't a cheap operation – zerkms Jan 16 '13 at 01:14

score 1 · Answer 2 · answered Jan 09 '13 at 01:26

1

try this

SELECT * 
FROM myTable 
WHERE FIND_IN_SET( email_address, 'foo@bar.com,test@test.com' COLLATE utf8_general_ci );

answered Jan 09 '13 at 01:26

Sir Rufo

Any reason to prefer `FIND_IN_SET` (which cannot be optimized well) over `IN` in this case? – zerkms Jan 09 '13 at 01:27
@zerkms no prefer but an alternative to your answer – Sir Rufo Jan 09 '13 at 01:28

2 Answers2