MySQL IF giving me BLOB

Question

I have the following two lines in an SQL query:

if( tb2.show_count = 0, 'hidden', count( tb1.user_id ) ) as 'count'
if( tb2.show_name = 0, 'hidden', tb1.name ) as 'name'

The first line gives me blob and the second line returns either the name or hidden based on the value of show_name

How do I do the same for the first line? i.e. stop it from giving me a blob and get it to return either the count or hidden based on the value of show_count?

Answer 1

Try:

if( tb2.show_count=0, 'hidden', cast(count(tb1.user_id) as char(10)) ) as 'count'

- if the results of an if evaluate to different types, they are returned as BLOBs, so the answer is to convert the count to a character type (the same as 'hidden').

Answer 2

In MySQL Workbench, go to: "Edit -> Preferences... -> SQL Queries" OR "Edit -> Preferences... -> SQL Editor" (depending upon what version of Workbench you have).

Check the option 'Treat BINARY/VARBINARY as nonbinary character string' to show the actual value.