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


exploring tables have blob fields. how select * command line client , have surpress printing (or truncate standard field width) blob fields rather scrolling bunch of binary junk on screen? mysql 5.1 client. want select * , not list of non-blob fields individually, development.

this can performed natively in mysql, it's quite unwieldy:

set @sql=concat('select ', (select group_concat(column_name)      information_schema.columns table_schema='test' , table_name='test'      , data_type!='blob'), ' test.test'); prepare preparedsql @sql; execute preparedsql; deallocate prepare preparedsql; 

i prefer bash aliases/function mysql procedures they're more transportable between systems:

function blobless() {   cols=''   _ifs=$ifs   ifs=$(echo -en "\n\b")   col in $(mysql --skip-column-names -e "select column_name        information_schema.columns table_schema='$1' , table_name='$2'        , data_type not '%blob'");      cols="$cols,$col"   done   ifs=$_ifs   mysql -e "select $(echo $cols | cut -c2-) $1.$2 $3" } 

invoke so:

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

if in mysql client console use ctrl-z suspend program , drop shell. use blobless db table check blobless data. fg restore suspended job (i.e. mysql client) foreground.

you can set default mysql connection details in ~/.my.cnf (howto) save having supply host/user/pass on command line - used bash functions.


Comments

Popular posts from this blog

c++ - Convert big endian to little endian when reading from a binary file -

linq to sql - Linq2EF: Return DateTime with a TimeZone Offset -

gdi+ - WxWidgets draw a bitmap with opacity -