ID #1131

Can I store 'long strings' in BLOBs with SQLyog?

Yes, you can. But basically you should consider a better option!

BLOB types are 'BINARY LONG' data. It is intended for storing binary data and not character data. For that reason SET NAMES has no effect on BLOBs. Data from binary types like BLOBs will always be retrieved as an exact 'binary copy' of how it is stored on the server.

The similar l-o-n-g datatype for character data is named TEXT in MySQL. We recommend that our users use TEXT for characters (if a char or varchar type is not appropriate) and BLOB for BINARY (like images, media clips etc) only.

However we also realize that users are not always in control. A lot of standard web applications (mostly PHP based guestbook's, Forum's, Wiki's etc) use BLOBs for long character data and the user has no option to change this.

Since SQLyog version 6 we have therefore built in a 'encoding detector' module in the BLOB viewer (visible for BLOBs only (and only for character data) and not TEXTs) that will detect what encoding is used for that storage. In the upper right corner of the BLOB viewer a listbox will display telling the encoding that SQLyog has detected for the data. If user edits data from the BLOB viewer and saves data, they will be encoded and saved as they were retrieved.

There is no way to have the Server handle this. We have needed to implement our own solution on the client side.

We think that this solution is rather elegant as a 'workaround' for bad practices among web developers! - but still TEXTs are preferred for character data. Use TEXTs and not BLOBs whenever you can!

Also note that when entering data into an empty BLOB field you should select the encoding to use before typing!

Categories for this entry

Tags: -

Related entries:

You can comment this FAQ