Exporting Binary Value B'1' B'0' To Csv, Xml, Jason

[exbungee]

Is it possible to control presentation of the binary values (such as ‘b’0′, b’1’) when exporting table to  XML,CSV, Jason formats?

 

By default, exporting a table to any of these formats results in the following:

 

Binary 1 (shown as b’1′) results in “SOH character”.

I then use NP++ to replace x01 with 1, so it works for “1”, but b’0′ disappears.

 

Help needed.

 

 

 

 

 

[peterlaursen]

The BIT type in MySQL is a huge problem. 🙁

 

In particular people coming from an SQL Server backgorund experience a lot of problems (because they use a MySQL BIT as they would use a SQL Server BIT, not realizing that they are actually very different things. They only have the name in common). 

 

It is the very implementation in the server that results in “Binary 1 (shown as b’1′) results in “SOH character” (because that is what ‘binary 1’  is when it is not stored as a MySQL BIT) as well as this “but b’0′ disappears” (because this is the NULL-character/NULL byte when not stored in a MySQL BIT).

 

Do you know MySQL documentation pages:

https://dev.mysql.com/doc/refman/5.7/en/bit-type.html

https://dev.mysql.com/doc/refman/5.7/en/bit-field-literals.html

 

This almost 10 years old blog summarizes the problems very well: http://www.xaprb.com/blog/2006/04/11/bit-values-in-mysql/ (though some of the regular bugs listed here are fixed. But the complaints about the implementation are still valid). I also blogged myself http://blog.webyog.com/2011/12/04/a-little-bit-about-bit-again/ 4 years ago about another oddity with MySQL BIT.

 

It is much easier to use a TINYINT(1) rather than a BIT(1) to represent a boolean value in MySQL. But since MySQL supports it, we should also handle it. We will check this case.

 

The last MySQL docs page lists a workaround: “To display them in printable form, add 0 ..”. You may use this (ie execute “SELECT non_bit_type, bit_type+0 FROM table;”) and export from RESULT tab.

[Author]

Posts