2

My application uses UTF-8 as the default encoding for all fields, and the connection itself; every connection starts with:

SET NAMES utf8;

I need to store a few SHA-1 hashes though, and for efficiency I'd like to store them in their compact, binary form (20 bytes) instead of their hexadecimal form (40 chars).

These fields are declared as BINARY(20).

I've tested it and everything seems to be working fine, but just to be on the safe side, I thought I'd ask if there is anything I need to know, any pitfalls to avoid? Or is it perfectly safe to transmit binary data over an UTF-8 connection?

1

From my experience storing such data, is it perfectly safe to transmit binary data when the character encoding of the connection is configured as UTF-8. The character encoding is just that: a way of representing the characters in binary form (this is the crux of the issue in Python bytes/str problems). MySQL rightly does not try to 'encode' binary data.

From the fine manual (emphasis added by me):

The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they contain binary strings rather than nonbinary strings. That is, they contain byte strings rather than character strings. This means that they have no character set, and sorting and comparison are based on the numeric values of the bytes in the values.

You should be aware that "the connection" is not UTF-8, but rather UTF-8 is the encoding that the application and MySQL have agreed upon in order to represent text characters.

  • I'm still confused by this, but you reassure me a little :) – Benjamin Mar 25 '14 at 16:45
  • Don't worry about being confused, it is a very confusing topic! This is a good starting place if you want to know more. – dotancohen Mar 26 '14 at 5:57
  • I'm quite comfortable with Unicode and UTF-8 actually, and the effects of charsets on table fields. But I'm very confused by how MySQL handles charsets while the data is being transmitted over the wire. What I precisely fail to understand is, why the hell the client & connection have a charset, and how this affects the data being transmitted to and from my app! I feel like there's some magic charset conversion happening on-the-fly, and this idea scares me. If the field contains UTF-8, I want my app to receive a UTF-8 string; if it contains binary, I want to get binary back, full stop! – Benjamin Mar 26 '14 at 10:15
  • There is a difference between how MySQL stores the data and how it transmits the data. Everybody hates that, and everybody wishes that the problem would just go away! Alas, there are reasons that the problem remains. Just ensure that UTF-8 is used wherever text is concerned, and you can pretty much pretend that the problem went away (it hasn't). Don't forget to ensure that the website is sending UTF-8 to the client, and that it has set an HTTP header stating such. Also, if you are using PHP, know that PHP regular string functions assume single-byte encodings! Use mb_* functions when possible. – dotancohen Mar 26 '14 at 11:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.