how to get mysql command line client not to print blob fields in select *

Question

Exploring some tables which have blob fields. How could I do a select * with the command line client and have it surpress the printing (or truncate to a standard field width) the blob fields rather than scrolling a bunch of binary junk on the screen? This is with mysql 5.1 client. Just want to do a select * and not list all of the non-blob fields individually, for development.

Answer 1

This can be performed natively in MySQL, but it's quite unwieldy:

SET @sql=CONCAT('SELECT ', (SELECT GROUP_CONCAT(COLUMN_NAME) FROM 
    INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='test' 
    AND DATA_TYPE!='blob'), ' FROM test.test');
PREPARE preparedsql FROM @sql;
EXECUTE preparedsql;
DEALLOCATE PREPARE preparedsql;

I generally prefer BASH aliases/function to MySQL procedures as they're more transportable between systems:

function blobless()
{
  cols=''
  _ifs=$IFS
  IFS=$(echo -en "\n\b")
  for col in $(mysql --skip-column-names -e "SELECT COLUMN_NAME FROM 
      INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='$1' AND TABLE_NAME='$2' 
      AND DATA_TYPE NOT LIKE '%blob'"); do 
    cols="$cols,$col"
  done
  IFS=$_ifs
  mysql -e "SELECT $(echo $cols | cut -c2-) FROM $1.$2 $3"
}

Invoke like so:

[andy ~]# blobless test test "where id>0"
+----+--------+
| id | t      |
+----+--------+
|  1 | 123    |
|  2 | 124213 |
+----+--------+

If you are in the MySQL client console use Ctrl-Z to suspend the program and drop to the shell. Then use blobless db table to check that blobless data. fg will restore the suspended job (i.e. MySQL client) to the foreground.

You can set default MySQL connection details in ~/.my.cnf (howto) to save you having to supply host/user/pass on the command line - this will also be used by BASH functions.