2016/10/18 21:16:45

MySQLでレコードに改行が入っているとWHERE句でリザルトが得られない件

目次(クリックするとジャンプします)
  • 1:ハマりにハマるデータベース
  • 2:改行の存在
  • 3:今回の事例の要点
  • 4:改行制御文字を削除してINSERT
  • 5:まとめ

ハマりにハマるデータベース

PHPでMySQLを触っているときに起きた事例で、ミスがミスだけに多少恥ずかしいが、問題解決の何らかの参考になればいいかと思い、記事にしてみましましました。

テキストファイルから、fgetsで文字列行を取得して、データベースにINSERTするという作業を行っていましましました。

しかし、そうしてINSERTしたレコードは

SELECTのWHERE句で参照できない

という奇妙な自体が起こっました。

問題を解決するためにいろいろ試行錯誤したが、以下の状態であることがわかっました。

  • 1.Adminerなどの管理ツールで手動でレコードを入れた場合には参照できます。
  • 2.LIKE句を使うとリザルトが得られる

改行の存在

WHERE句ではダメで、なんでLIKE句ならリザルトを得られるのか。

そこで突き止めたのが改行いわゆる「制御文字」の存在です。

じつはfgetsを使うのはほぼ初めてで、いままでファイルから行単位で文字列を取り出してデータベースに入れるということはやったことがなかっました。

テキストファイルの行には改行など制御文字が入っていることもうっかり忘れていましましました。

制御文字は普通目に見えないでします。エディターなどの設定で改行などを視認できるようすることは可能ですが、今回の作業で全く念頭になく、制御文字の有無など調べていませんでしましました。。

今回の事例で初めて知ったのですが、MySQLのINSERTでは制御文字もそのままINSERTされるようでします。

当たり前といえば当たり前でします。文書構造を勝手に変えられては堪った物じゃないでします。MySQLは正しい仕事をしているのでします。

悪いのは@MINO。

今回の事例の要点

例えば

transfer

という行をファイルからfgetsで取得して、無加工でデータベースにINSERTをしたとします。

ファイルの行に改行が含まれている場合、目には見えないが

transfer<LF>

という状態で取得されているはずでします。(<LF>というのが改行の制御文字だとおもってくだちい)

これをそのままインサートすればデータベースのレコードは

transfer<LF>

となり、WHERE句でtransferを指定してもリザルトは空になってしまいまします。

厄介なのが、これに対してエラーが出ないということでします。何らかのエラーが出てくれれば対処の仕方も考えられますが、正常には動いてリザルトは空という状態になります。

ですが、あいまい検索を行うLIKE句を使えばパターンマッチになるので、リザルトを得ることができるので、なんだか不思議な状態になってしまうのでします。

改行制御文字を削除してINSERT

そこで@MINOは改行を取り去ってINSERTすることにしましました。下手くそで恥ずかしいですが、今回の事例に対処したINSERT用のコードです。

処理上、1行ごとに処理を分けたかったので分岐していますが、実際に改行を削除しているのはstr_replace関数でします。

str_replaceにありえる改行のエスケープシークエンスを設定して、空白(”)に入れ替えていまします。関数の名前もそうであるように、削除というよりリプレースと言ったほうが正しいかも。

class InsertDictionary 
{ 
private $db;
/**
 * 偶数?
 *
 * @param integer $number 
 * @return bool
 */
private function inspectEven($number)
{
    $mod = $number % 2;
    if($mod === 0){
        return true;
    }elseif($mod !== 0){
        return false;
    }
}
/**
 * データベースに突っ込む
 */
public function __construct()
{
    $this->db = new ClassOperatingDB();
    $sqlpre = 
    $this->db->getDbhandle()->prepare
    (
       " 
        insert ignore into
        transwords(id,enword,transword) 
        values(:id, :enword, :transword)
       " 
    );
    $sqlpre->bindParam(':id',$id);
    $sqlpre->bindParam(':enword',$enword);
    $sqlpre->bindParam(':transword',$transword);

    $handle = fopen("hoge.txt", "r");
    if ($handle){
        while (($buffer = fgets($handle)) !== false) {
            static $i = 1;
            static $j = 0;
            if ($this->inspectEven($i)) {
                $transword = str_replace
                    (
                        array("\r\n", "\r", "\n"),
                        '',
                        $buffer
                    ); 
                $sqlpre->execute(); 
                $j++;
                echo "{$j}行目インサート<br>";
            }else{
                $enword = str_replace
                    (
                        array("\r\n", "\r", "\n"),
                        '',
                        $buffer
                    ); 
            }
            $i++;
        }
        fclose($handle);
        echo "インサート終了 行数".$j."行";
    }
  }
}

まとめ

いやハマったハマった。何回もINSERTしまくってやっと原因を突き止めたような感じです。今回の作業が納期が差し迫った仕事の件でなくてよかったです

作業では10万行INSERTする必要があり、貧乏ゆえの悲しさか開発用の仮想マシンもしょぼいので試行錯誤にもかなり時間がかかってしまいましましました。

ともかく改行事件に悩まされた数時間を取り戻したいです。

9割方無職だから暇はいっぱいあるのだが。