Want to view column type tiny text not as blob

[gihrig]

Hello,

I have “inherited” a project where the previous dba seemed to be in love with MySQL type tinytext. It's used for such things as FirstName, LastName, Address, etc., etc. Because tinytext is technically a blob type (though limited to 255 characters) SqlYog shows the “BLOB …” button to open the blob viewer in place of the data, which is usually no more than 10 – 20 characters in this database.

I understand that this behavior is by design and makes a certain degree of sense, since tinytext could hold binary data. However I am reluctant to make radical changes to the database as there are some 1300 +- individual php files involved in the site and I don't know if there might be consequences to changing tinytext to varchar.

So, my question: Is there a way I can disable the blob viewer for tinytext only or even completely and thus view this data without opening a separate window?

Thanks for your help on this.

-Glen

[Ritesh]

You can switch to Result In Text Mode by pressing Ctrl+L. The option is also available from Edit menu.

[peterlaursen]

No way as of now, I'm afraid

and besides I disagree with this

Quote:

because tinytext is technically a blob type

It is technically a CLOB-type (Character Long OBject)

… but that's an old discussion at this forum 🙂

Your request has been proposed before. I don't know it it came be implemented wit the SQLyog code. The problem is that BLOB- and -text-type variables in MySQL are not stored in the table itself (only a pointer is stored). So reading the table does not read the values of BLOB's and text “in the first run”.

Basically I think it's an unwise use of text-variables. They should be chars (for performance) or varchars (for effective use of discspace). An with MySQL 5 varchars can be about 55000 characters … But that of course is no solution where you are now.

Sorry!

[peterlaursen]

@ Ritesh was here again .. it must be above bedtime for young people where he lives 😆

The propoasal of Ritesh works with the “RESULT”-pane but not the “TABLE DATA”-pane !

[gihrig]

Thanks very much for your replies.

I appreciate the distinction between BLOBs and CLOBs, I didn't get that from the MySQL docs (must have missed it).

I won't jump into the CLOB vs BLOB war 😉 I'll just add my “vote” for treating character objects as such regardless of size. Perhaps limiting the total number of characters displayed by default in the table data pane to a reasonable value, and then provide a viewer/editor pane to deal with larger numbers of characters than the default.

This works very well IMO in MS Access, where Shif-F2 opens an editor pane where you can edit character data too large to work with in the table data grid.

I guess I'll just put up with this until a) I have time to redesign the app. or b) I get so annoyed at not being able to see column contents without opening the viewer that I'm willing to change the table structure and see what breaks …

Thanks again for you input

-Glen

[Shadow]

For simplicity MySql does not use the term 'CLOB'. MySql has enough field types already.

[peterlaursen]

I'm not sure but but I think the term “CLOB” is used by diffent “Standard SQL” documents. The point is that CLOB/text will only accept character-data!

[gihrig]

Shadow is right, MySQL does not use the term CLOB, it's TEXT in MySQL, but the concept is the same, as peterlaursen points out.

For the benefit of clarity, here is the reference on this from the MySQL docs:

See: http://dev.mysql.com/doc/mysql/en/blob.html for details.

From: 11.4.3. The BLOB and TEXT Types:

Quote:

The four TEXT types, TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT, correspond to the four BLOB types and have the same maximum lengths and storage requirements.

BLOB columns are treated as binary strings (byte strings). TEXT columns are treated as non-binary strings (character strings). BLOB columns have no character set, and sorting and comparison is based on the numeric values of the bytes in column values. TEXT columns have a character set, and values are sorted and compared based on the collation of the character set assigned to the column …

When I said above:

Quote:

I understand that this behavior is by design and makes a certain degree of sense, since tinytext could hold binary data.

That was wrong, *TEXT does not hold binary data. But my suggestion to have a limited preview for text types, with a viewer/editor to handle longer column values seems even more appropriate, in light of the correct information. Just as a similar function to handle BLOB types in hex mode does (discussed in this thread)

In summary long data types *TEXT and *BLOB serve different purposes, it would be nice to have a viewer/editor scheme that fully supports the function of each.

-Glen

[peterlaursen]

If the read through the forms you'll find some more suggestions for enhancement/improvement of the BLOB-viever function. Some of them are on the TODO-list with sqlyog's development team.

[Author]

Posts