mysqlimport編TweetMySQLへCSVなどのテキスト形式のデータファイルを一括でインポートします。今回はLinuxなどのコマンドとして提供されているmysqlimportを利用します。通常MySQLをインストールした際に自動的に付いてきますのですぐに実行することが可能です。tablename.tsvシェルスクリプト内で実行する場合はこちらが手軽ですね。場合、INSERT文を大量に発行するよりも高速にデータを入れることができます。参考ページ環境準備テスト用テーブルここではサンプルとして書籍の情報を扱うDBとテーブルを作成し、データをインポートします。KEY(id));サンプルデータインポートするためのテキストファイルをタブ区切り形式で用意します。文字コードはUTF-8、改行コードはLFにしてあります(Windows上で動作しているMySQLへインポートする場合はCRLF)。my.cnfなどで文字コードの設定を変更している場合はそれに合わせます。1行目に各カラムのタイトルは付けません。今回は1月28日時点のAmazonのコミック売上げランキングから抜き出してきました。792サンプルデータをダウンロード注意点として、mysqlimportコマンドを利用する場合はテーブル名とインポートするファイル名を同じ名前にする必要があります。拡張子は自由に設定することができます。インポートする実行mysqlimportコマンドでインポートする場合は以下のように入力します。0各オプションは以下の通り。-uの後にユーザー名を指定-pを指定するとEnterを押した直後にパスワードを聞かれます。パスワードをオプションに続けて直接書くと.bash_historyなどのログに記録されてしまうので止めた方が良いです。--localは所定のディレクトリ以外の場所のファイルをインポートする場合に必要です。最後にインポートするファイルのパスインポートの実行にはFILE権限が必要です。GRANThttps://blog.katsubemakito.net/mysql/grant作成済みのユーザーに権限を付与するにはGRANT文を利用します。実行にはGRANTを利用できる権限が必要です。katsube@localhost;上記はdb1という名前のデータベース内にあるすべてのテーブルに全権限を付与しています。基本的な利用方法権限の指...ねこの足跡R確認MySQLへログインして正常にデータが入っているか確認しておきます。sec)大丈夫そうですね。CSVをインポートカラムをカンマ(,)で区切ったファイル形式の方のCSVをインポートします。サンプルデータ先ほどのファイルをカンマ区切りに変換しました。文字列のカラムはダブルコーテーションで囲うことにします。GAME!10",792サンプルデータをダウンロード実行このCSVファイルをインポートするにはmysqlimportにオプションを2つ追加するだけです。bookshelf.csv追加されたオプションは以下の通り。–ignore-lines1行目にカラム名がある場合、このままだとカラム名までもデータとして取り込まれるか、テーブルのデータ型と合わない場合は弾かれてしまいます。0--ignore-linesオプションでカラム名が書かれたヘッダ行をスキップすることができます。bookshelf.tsv正確には--ignore-lines=はファイルの先頭から指定した行数をスキップするオプションですので、ファイルの途中からインポートするといったことにも応用できます。エスケープカラムの中に区切り文字など処理場まずい文字が登場する場合にはバックスラッシュ(円記号)でエスケープしてやります。bookshelf.csvこの場合はダブルコーテーションなどで囲い--fields-enclosed-byオプションを指定するやり方でも解決できます。–portデフォルトだとlocalhostの3306番のポートへ接続しますが、-hまたは--hostオプションでホストを、--portオプションでポートを指定することができます。bookshelf.csvサービスの規模が大きくなってくるとDBサーバは別に建てることが多いですし、手元にあるデータを遠隔地にあるサーバへ直接インポートしたい時にも重宝します。注意点としては、事前にMySQLへログイン元のホストでユーザーを新規作成しておく必要があります。MySQLはログイン元のホストが異なると、ユーザー名が同じでも全く別のユーザーとして扱われます。ユーザー作成後にGRANTもお忘れなく。USER文を実行します。rootなどユーザーを作成できる権限でログインしておく必要があります。–columns特定のカラムを自動採番したい場合、データファイル上はその列を空にしておきます。KEY(id));idがあった箇所を消しておくだけです。0(中略)もしカラムを自体が無かったことにしてしまうと、以下のように何だかよくわからないことになってしまいます。|+----+-------+-------+もしどうしてもデータファイル上からカラムを取り払ってしまいたい場合は、--columnsで指定することで回避できます。–delete--deleteオプションを指定すると、テーブルのデータを削除してからインポートしてくれます。INCREMENTの値はリセットされません。全テーブルをロックしてからインポートインポートする際に--lock-tablesトラブルシューティング–localオプションを付けていないMySQL5.7.16以降から特定のディレクトリ以外にあるファイルをインポートしようとすると、以下のようなエラーとなります。bookshelfMySQLのシステム変数secure_file_privで指定されているディレクトリ以外にあるファイルを気軽にインポート出来なくする制約のようです。Linux環境の多くは以下の通り/var/lib/mysql-files/がデフォルトで割り当てられています。sec)解決策としては--localオプションを付けて実行するか、secure_file_privで指定されているディレクトリにデータファイルを置く必要があります。Warningsの具体的な内容を確認したいどうもmysqlimportを利用した際のWarningsの内容はどこにも出力されないようです。INFILE構文を利用する必要があります。設定も確認したんですけどね。どなたかご存知でしたら教えてくださいませ。自宅警備がてらゲームのお仕事や講師などしてご飯を食べてます。ご質問やリクエストなどお気軽に。メールアドレスの入力は任意です。書き込みが反映されるまで時間がかかります。コメントデータの処理方法の詳細はこちらをご覧ください。ブログをメールで購読メールアドレスを記入して購読すれば、更新をメールで受信できます。