Hatena::ブログ(Diary)

おっさんプログラマの戯れ言

2013-06-19

データベースの作成、文字列エンコーディング、DDLロードなどの初期作業

前回はデータベースユーザーを作成するところまで進めました。

今回は、

・データベースインスタンス作成
・テーブル作成(DDLファイルから)
・マスタデータロード(CSVファイルをロード)

を目標にやっていきたいと思います!

データベースインスタンス作成

データベース所有者となるユーザーでmysqlに接続して、

CREATE DATABASE database_name CHARACTER SET utf8;

を実行します。

$ mysql -u pizza -p
Enter password: 
MariaDB [(none)]> CREATE DATABASE pizzadb CHARACTER SET utf8;

※私は上記のようにDBを作成して以降の作業を進めましたが、サーバーのデフォルトエンコーディングをUTF-8にするには、COLLATE utf8_general_ciも同時に指定した方が良さそうです。(後述)


テーブル作成

DDLファイルからテーブルを作成するには、mysqlコマンドを実行するときに、データベース名を指定した上で、標準入力にDDLファイル名を指定すれば良いようです。

mysql database_name -u user_name -p < ddl_file_path

$ mysql pizzadb -u pizza -p < ddl.sql



その都度調べながらやりましたが、ここまでは特に問題なく進められました。


CSVデータをロードする

さて、これまで、DB作成時と、テーブル作成時にUTF-8を指定してきました。*1
しかし、MariaDBには(MySQLにも)多くの、細かなエンコード指定があるみたいです。

具体的に調べてみると、ざっとこんだけありました。

MariaDB [(none)]> show variables like 'character%';
+--------------------------+-------------------------------------+
| Variable_name            | Value                               |
+--------------------------+-------------------------------------+
| character_set_client     | utf8                                |
| character_set_connection | utf8                                |
| character_set_database   | utf8                                |
| character_set_filesystem | binary                              |
| character_set_results    | utf8                                |
| character_set_server     | utf8                                |
| character_set_system     | utf8                                |
| character_sets_dir       | /opt/mariadb-5.5.31/share/charsets/ |
+--------------------------+-------------------------------------+

上記は設定を終えた後に表示したものなので、必要なエンコードはすべてUTF-8になっていますが、ここまでの作業をしただけでは、いくつかlatin1のものがありました。どうやら、ざっくり言って、サーバー側とクライアント側のエンコードをそれぞれ設定してやる必要がありそうです。

ちょっと、いったんCSVファイルのロードは保留にして、文字列エンコードの設定を先に済ませることにします。


サーバーのエンコードを設定する

サーバー側のデフォルトの文字列エンコードはデータディレクトリ内の、データベース名ディレクトリ内の、db.optファイルに設定されているそうです。例えば、私の環境では、

・データディレクトリ:/var/mariadb/data
・データベース名:pizzadb

なので、

/var/mariadb/data/pizzadb/db.opt

というファイルになります。
このファイル内に、以下の設定をしてやれば良さそうです。

default-character-set=utf8
default-collation=utf8_general_ci

そして、これは後から気づいたことですが、CREATE DATABASEをするときに、

CHARACTER SET utf8

だけでなく、

COLLATE utf8_general_ci

も一緒に設定してやれば、db.optに自動的に上記設定がされていたのではないか、という気がします。実際には試していないのですが、マニュアルにはそう書いてますので、恐らく間違いないかと。
http://dev.mysql.com/doc/refman/5.1/ja/create-database.html
(5.1ですが。)


クライアント(localhost)のエンコードを設定する

さて、クライアントの文字列エンコードも指定してみます。
少し面倒な事情がありまして、私の場合、サーバー用のmy.cnfを、/etc/init.dではなく、/var/mariadb/dataに格納しています。即ち、これは、mysqlコマンド実行時に自動的に読み込まれる場所ではありません。従って、ローカルからmysqlコマンドで接続する際に、自動的に読み込まれるパスに、別途クライアント用のmy.cnfを作成しなければならないということです。

今回は、~/.my.cnfを作成することにしました。

[client]
#password	= your_password
port		= 3306
socket		= /tmp/mysql.sock
default-character-set=utf8

最後の1行でUTF-8を設定しています。
まあ、この設定をしなくても、mysqlコンソールに接続する度に、

SET NAMES utf8;

とすれば良いみたいですけど、面倒なので、上記のように設定ファイルを使うことにしました。

これで、サーバー、クライアント共に文字列エンコードの設定ができたので、安心してCSVファイルをロードする作業に戻れます。


CSVデータをロードする

CSVファイルをテーブルにロードするには、mysqlコンソールから、以下のコマンドを実行すれば良さそうです。

LOAD DATA LOCAL INFILE "path/to/import.csv" INTO TABLE table_name FIELDS terminated by ',';

しかし、ここで少しはまってしまいました。

MariaDB [(none)]> use pizzadb;
MariaDB [pizzadb]> LOAD DATA LOCAL INFILE "~/data.csv" INTO TABLE test_tbl FIELDS terminated by ',';
ERROR 1148 (42000): The used command is not allowed with this MariaDB version

少し調べてみたところ、どうやらローカルファイルのロードはデフォルトでは無効になっているみたいです。*2

mysqlコンソールを起動するときに、

--enable-local-infile

を指定することによって、有効化できました。

まあ、これをやるのはマスタデータのロード時くらいで、インポートはそんなに頻繁にすることもないだろう、ということで、必要なときだけ、上記オプションを使用するということにしておきます。


ふう。

今日はここまでにしておきます。
これまでの作業で、データベースの入れ物となる部分と初期データの登録方法までわかりました。しかし、DDLのロードオペレーションが分かったというだけで、実際にはまだテーブルの物理設計の方法、その詳細が不明です。次回はその辺りを追ってみたいと思います。


--

*1:ここでは「DDLをロード」としか書いておらず、具体的なテーブルの作成方法は書いていませんが、日本語が格納される可能性のあるテーブルにはUTF-8を指定しているものとします。

*2:コンパイル時か、あるいはインストール時に、ローカルファイルのインポートをデフォルトで有効にすることができるかもしれません。ちょっと時間がなくて、今のところは追えていません。

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

リンク元