0

I have a MySQL table where the username field is a latin1 charset. If I search this table with a UTF8 string, I get this error:

Error: ER_CANT_AGGREGATE_2COLLATIONS: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation 'like'

Side question: is this because I'm using the LIKE statement, specifically?

I feel like the only solution to my problem is to check if the username parameter from the HTTP query is a latin1 compatible string or not. If it is not, then omit it from the SQL query.

How could I check if a particular string is compatible with a specific character set like latin1 in JavaScript/Node.js?

Sam
  • 6,414
  • 11
  • 46
  • 61
  • Even swedes should be using UTF8 – adeneo Apr 21 '16 at 20:38
  • Also, you can use [iconv middleware](https://github.com/bnoordhuis/node-iconv) for Node to convert strings to different character encodings etc. – adeneo Apr 21 '16 at 20:40
  • adeneo: I don't think UTF8 is a good idea for usernames https://labs.spotify.com/2013/06/18/creative-usernames/ – Sam Apr 22 '16 at 21:23
  • And why would you think Latin1 is better, which only supports very few characters, when UFT-8 supports just about everyting -> [read this](http://stackoverflow.com/questions/2708958/differences-between-utf8-and-latin1#answer-2709023) – adeneo Apr 22 '16 at 23:44
  • There are issues I don't want to run into. Read the article about this from Spotify. I could try to solve the problems, but that's extra overhead I don't need. The simpler solution is to just restrict usernames to a subset of characters in latin1. – Sam Apr 24 '16 at 19:41

1 Answers1

0

I realized a good solution to my problem is to just convert the encodings within SQL using the CONVERT function.

Sam
  • 6,414
  • 11
  • 46
  • 61