MySQL: Querying for unicode entities

by

I have absolutely no idea why, but triple escaping helps!

Well, that’s only double-escaping, but yes it works and here’s why: in MySQL, there is a second layer of escaping involved when you use the LIKE operator.

services LIKE '%L\\\\u00e4mm\\\\u00f6n%'

parsing that MySQL string literal gives you a comparison with the LIKE-query %L\\u00e4mm\\u00f6n%. Because MySQL treats \ in a LIKE query as an escape, that will actually match the literal string containing L\u00e4mm\u00f6n.

The reason for this is so that you can match strings against a query expression that contains a literal % or _ character. For example if I want to search a column for the literal string 100%, I can match it against 100\% (written in a query as '100\\%') and make sure I’m really getting one hundred percent and just not any string starting with a hundred.

It’s unfortunate that MySQL uses backslash for both its LIKE query escaping and its string literal escaping, especially given that you’re probably writing in an enclosing programming language that also uses them, ending up with actual triple-encoding, which looks like "services LIKE '%L\\\\\\\\u00e4mm\\\\\\\\u00f6n%'" – argh!

It’s doubly unfortunate given that this behaviour is not ANSI SQL conformant, and won’t work in any other database. ANSI SQL says that there is no escape character in LIKE queries by default, so if you want to match a literal % or _ you have to opt in by nominating an escape character of your own, eg.:

something LIKE '100=%' ESCAPE '='

For cross-database compatibility, it is best always to use the LIKE…ESCAPE form, and pick something other than the horrible backslash! (Aside – MySQL’s backslashes for SQL string literal escaping aren’t ANSI conformant either! But you can turn that misbehaviour off with the NO_BACKSLASH_ESCAPES sql_mode setting.)

Probably a better idea would be to break services out into a second table rather than squashing them into a single string column – ie. put your schema in First Normal Form. Then you could get a simple lookup of individual values rather than having to do a slow full-table-scan substring-match.

More Related Contents:

  1. Trouble with UTF-8 characters; what I see is not what I stored
  2. What’s the difference between utf8_general_ci and utf8_unicode_ci?
  3. How to display UTF-8 characters in phpMyAdmin?
  4. How to store unicode in MySQL?
  5. What are the differences between utf8_general_ci and utf8_unicode_ci? [duplicate]
  6. MySQL VARCHAR Lengths and UTF-8
  7. Does MySQL Regexp support Unicode matching
  8. MySQL treats ÅÄÖ as AAO?
  9. MySQL matching unicode characters with ascii version
  10. If two columns are 1 then make new column
  11. Can we use CONCAT and JOIN together in MySQL? [closed]
  12. Truncate all tables in a MySQL database in one command?
  13. Retrieving the last record in each group – MySQL
  14. Find distance between two points using latitude and longitude in mysql
  15. CROSS/OUTER APPLY in MySQL
  16. Solution to “subquery returns more than 1 row” error
  17. Storing SHA1 hash values in MySQL
  18. Downloading MySQL dump from command line
  19. DOUBLE vs DECIMAL in MySQL
  20. Terminating idle mysql connections
  21. int(11) vs. int(anything else)
  22. ALTER TABLE to add a composite primary key
  23. MySQL – How to select data by string length
  24. SQL Order By Count
  25. ColdFusion Parameterizing a Query
  26. How to select the first row for each group in MySQL?
  27. What to do when you need integers larger than 20 digits on mysql?
  28. How to update JSON data type column in MySQL 5.7.10?
  29. How can a query multiply 2 cell for each row MySQL?
  30. Rails select random record

Leave a Comment