MYSQLでバイナリデータの取り扱い

MYSQLには慣れているつもりでしたので、対象がバイナリとなっても大したことはないだろうと高を括っていました。

 


 

 #!/bin/bash
dir_SECURE_FILE_PRIV=/var/lib/mysql-files

mysql DATABASE << _SQL_
create table if not exists t_sound(
seq int primary key auto_increment,
DLdate datetime,
estimate datetime,
number int,
fullname varchar(100),
sound mediumblob
);
_SQL_

for ( ( i=701; i<710; i++ ) )
do
cp raw2/$i.raw ${dir_SECURE_FILE_PRIV}
mysql DATABASE << _SQL_
insert into t_sound(number,sound) value($i,load_file('${dir_SECURE_FILE_PRIV}/$i.raw'));
_SQL_
rm ${dir_SECURE_FILE_PRIV}/$i.raw
done

 1)データ型

これは想定内。データ型はBLOB型を使います。特にmediumblob型というのが適当に大きなデータまで取り扱えて使い勝手が良いそうです。

2)insertコマンド

さてバイナリファイルをinsertしようとしてハタと困りました。シェル変数にどうやって読み込もう?

binary=`cat binary_file`

みたいなので読み込めてさえしまえば

insert into table( col1,col2 ) value( $ascii, $binary );

 てな具合にinsertできるのですが、バイナリではそうもいきません。

調べてみるとMYSQLにはload_file関数というのが用意されているそうです。しかし、このload_file関数は次のように制約が多いようです。

3) secure_file_priv値

load_file関数で読み込めるファイルは必ずsecure_file_priv値に示してあるディレクトリ内に存在しないとならないそうです。

secure_file_priv値を調べている画像を下に示します。

f:id:S_E_Hyphen:20171105155945p:plain

 

 使用中の環境では/var/lib/mysql-filesでした。なので必ずこのディレクトリにファイルをコピーしてからload_file関数を使用する必要があります。

my.cnfでsecure_file_priv値は変更できるそうなのですが、ちょっとうまくゆきませんでした。

 

4) データの取り出し

mysql DATABASE << _SQL_ > 701.raw
select sound from t_sound where number=701;
_SQL_

でOKかと思っていたら全然ダメでした。

 


 

#!/bin/bash
dir_SECURE_FILE_PRIV=/var/lib/mysql-files
temp=`mktemp ${dir_SECURE_FILE_PRIV}/XXXX`

rm playback.raw
for ( ( i=701; i<710; i++ ) )
do
rm $temp
mysql nhkradio << _SQL_
select sound into dumpfile "$temp" from t_sound where number=$i;
_SQL_
cat $temp >> playback.raw
done
rm $temp

sox -r 48000 -c 2 -b 16 -e signed-integer playback.raw playback.wav

 select文でinto dumpfileを使用する必要があります。ところが、このdumpfileの書き出し先もまた、先ほどのload_file関数と同じディレクトリでなくてはなりません。

なんだか思っていたよりも、ずっと野暮ったいスクリプトになってしまいました。