Why does changing the collation affects how binary data is stored?
Posted by: Claude Quézel
Date: October 05, 2009 12:09PM

I'm not sure this question is well suited for this forum. Please point out which forum would be better if you have an idea.

Here is my situation. I've developed a small php application that inserts binary data in a mediumblob (binary attribute) field. When I tested the insert with a binary data made up of the first 256 bytes (00 to FF), I noticed that all bytes above 7F were replaced by 3F. I changed the DB collation from the default value (utf8_swedish I think) to "utf8_unicode_ci" and things started working as expected.

1) I used PHP mysql_query to insert the binary data "INSERT INTO table (blobField) VALUES ('$blobField')".
2) Before inserting I used mysql_real_escape_string.
3) PhpMyAdmin return: MySQL
a) client version: 4.1.22
b) Server version: 5.0.81-community
c) Protocol version: 10
d) MySQL charset: UTF-8 Unicode (utf8)
e) MySQL connection collation: UTF-8 Unicode (utf8)

Note that when I used phpMyAdmin to insert the data (using upload interface), everything worked fine before changing the collation.

My question is very simple: Why does changing the collation affect how binary data is stored?

Other questions: Why does it work with phpMyAdmin?

Options: ReplyQuote

Written By
Why does changing the collation affects how binary data is stored?
October 05, 2009 12:09PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.