ID #1039

Why are my BIT datatypes displayed as squares?

You experience that BIT datatypes that previously displayed as "0" or "1" (and maybe even any numerical value between "-128" and "127") in SQLyog DATA and RESULT pane are displayed as squares when connecting to a MySQL version 5 server.

The explanation is that the meaning of the BIT type has changed with MySQL version 5. Changes were implemented for MyISAM tables with MYSQL version 5.0.3 and other table types with 5.0.5.

The MySQL docs at now say about the BIT(M) type:

  • A bit-field type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.

that means that the BIT type as of MySQL 5.0.3/5.0.5 can't be displayed in text since is is purely binary data. It is not a string type with an associated character set. And it is not a "decimal-numerical" either (actually MySQL list the BIT type among numerical types, but it is only to be considered as a 'binary-numerical'. To display a BIT(3) as for instance "101" or the decimal representation of binary "101" (that is "9") would be very confusing since it is "one-zero-one" and not AT ALL neither "one-hundred-and-one" nor "nine" !).

The square is how non-printable data is shown in SQLyog...

In near future we will let the BLOB-viewer open BIT types from where they can be edited in binary or hexadecimal mode.

Please note too that the meaning of the BOOL data type (that is still converted to a TINYINT(1) by the MySQL server like BIT was before 5.0.3) will change with a future MySQL version as well. So for backward compatibility use a TINYINT(1) as a boolean type. For the future and for compatibility with STANDARD SQL you may consider using the BOOL type. But don't assign no other value than "0" or "1" to it, if you do that! It will work now, but no guarantees for the future!

Tags: -

Related entries:

You can comment this FAQ