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
Post a Comment