Does the mysql CLI tool provide a way to display binary data in a console-friendly manner?

Question

I have a MySQL database containing a table with a binary-typed column. I'd like to be able to project that column without having to run it through, e.g., HEX(). Does the mysql CLI tool have a configuration option or other means to display a representation of binary data in a manner that won't output arbitrary bytes for my console to interpret in hilarious/annoying ways?

Prole

What is wrong with HEX()? It seems the perfect solution. – Naashom

Because having to do that when all I really want is a quick SELECT * ... is fairly inconvenient. – Compendium

Answer 1

Start MySQL CLI with param --binary-as-hex

Example:

mysql --binary-as-hex

Pantisocracy

Answer 2

Set mysql client options in /etc/my.cnf works for me:

[client]
binary-as-hex = true

[mysql]
binary-as-hex = true

Huygens

This config breaks mysqldump (version 5.7): mysqldump: [ERROR] unknown variable 'binary-as-hex=true' – Cohla

It's enough to use [mysql] binary-as-hex = true and this will not break mysqldump – Providing

This does not works with mysql 5.5, could you specify from which version this has been introduced? Where can the doc with the possible option could be found? – Armageddon

Added on 5.7.19 as per manpagez.com/man/1/mysql – Providing

Answer 3

Since you want to look at the table mostly for convenience, create a view:

CREATE OR REPLACE VIEW myview AS
 SELECT col1, HEX(col2) AS col2, col3, etc....
 FROM table;

Then, all you have to do is reference myview instead of table:

SELECT * FROM myview;

Naashom

Answer 4

The behavior of the MySQL command line client when viewing result sets with binary data has always been an annoyance to me, in fact I found this page because I was once again annoyed by the MySQL command line client (dumping binary data into my terminal when looking at a result set with binary UUID columns) and I wanted to solve the issue once and for all :-)

Creating views really isn't an option for me (I'm looking at dozens of tables with binary UUID columns) and I also found that it's really annoying to switch from SELECT * to typing out all of the column names instead (just so HEX() can be applied to the value of one column).

Eventually I came up with a creative hack that provides inspiration for alternative solutions to this annoyance: Using a custom pager command to sanitize output for terminal rendering. Here's how it works:

1. Create an executable (chmod +x) Python script with the following contents:

   #!/usr/bin/python
   
   import binascii, string, sys
   
   for line in sys.stdin:
       line = line.rstrip()
       column, _, value = line.partition(': ')
       if any(c not in string.printable for c in value):
           sys.stdout.write("%s: %s\n" % (column, binascii.hexlify(value)))
       else:
           sys.stdout.write("%s\n" % line)
   

2. Start the MySQL command line client as follows:

   $ mysql --pager=/home/peter/binary-filter.py --vertical ...
   

   Change the pathname of the Python script as applicable. You can also put the script in your $PATH, in that case you can just pass the name to the --pager option (similar to how you would use less as a pager for the MySQL client).

3. Now when you SELECT ..., any line that shows a column whose value contains non-printable characters is rewritten so that the complete value is rendered as hexadecimal characters, similar to the results of MySQL's HEX() function.

Disclaimer: This is far from a complete solution, for example the Python snippet I showed expects SELECT ... \G format output (hence the --vertical option) and I tested it for all of five minutes so it's bound to contain bugs.

My point was to show that the problem can be solved on the side of the MySQL command line client, because that's where the problem is! (this is why it feels backwards for me to define server side views - only to make a command line client more user friendly :-P)

Seem

Answer 5

For me there is no problem with database size, so I will use two different column in every table, one as binary(16), and the second as char(32) without indexing. both of them will have the same value. when I need to search I will use binary column, and when I need to read I will use char(32). is there any problem with this scenario?

Phosphoresce