背景
英和辞典のデータベースをローカルで管理して勉強に役立てたいと考えたのがきっかけです。
一から単語帳を作るのは大変なので、ファイルとして提供されているものを探したところ、以下に公開されていました。
http://kujirahand.com/web-tools/EJDictFreeDL.php
タグ区切りでファイル(英単語 和訳) と提供されているので、これを mysql にそのままインポートを試みました。
テーブルの準備
mysql のインストールや、DBは予め作成済みとします。
テーブル名: english_words
カラムは、ユニークIDと英語、和訳の３つのみです。
CREATE TABLE english_words(
id int NOT NULL AUTO_INCREMENT,
english varchar(10000) NOT NULL,
japanese varchar(10000) NOT NULL,
PRIMARY KEY(id)
);
varchar のサイズについて
（必要であるかどうかは別として）65535 が上限のはずなので、以下で指定してみると
english varchar(65535) NOT NULL,
japanese varchar(65535) NOT NULL,
エラーを頂戴しました。
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
https://dev.mysql.com/doc/refman/5.6/ja/innodb-restrictions.html
ドキュメントを確認すると、1カラムではなくテーブル全体での上限が 65535 らしく、また、UTF-8 が最大3バイト消費するため、最大でも「65535 ÷ 文字のバイト数」まで指定できると考える必要がありました。
65535 - 4(byte) = 65531 # id 列分のサイズを引いて
65531 / 2(column) / 3(byte) = 10921 # english と japanesse 列の分
-> varchar(10921) までであればテーブルを生成することが可能
mysqlimport でインポート
ローカルにダウンロード済みのファイルを簡単にインポートできるのですが、注意点としては
- --columns=でインポート先のカラムを意図的に指定する
何故ならインポートするファイルには id 列は書かれていないためです。
指定せずにインポートした場合 id の列に英単語がインサートされるような動きになってしまいます。
2.インポートするファイル名とテーブル名を一致させる
ファイル名（english_words）からインポート先のテーブル名を判断しているようです。
mysqlimport -u {user} --password={password} --local --columns=english,japanese {db_name} english_words.tsv
しかしながら実行したところ、Warnings の表示あり。
... Warnings: 3
レコードはすべて登録されており、どこに問題があったかが分からなかったため、別のアプローチを採ります。
LOAD DATA LOCAL でインポート
mysql にログインした状態でも出来ますが、mysql コマンド経由で以下を実行します。
mysql -u {user} -p {db_name} --execute="LOAD DATA LOCAL INFILE 'english_words.tsv' INTO TABLE english_words FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '\"' IGNORE 1 LINES (english,japanese); SHOW WARNINGS"
実行したところ、Warnings の詳細を得ることができました。
+---------+------+-------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------+
| Warning | 1262 | Row 627 was truncated; it contained more data than there were input columns |
| Warning | 1262 | Row 37206 was truncated; it contained more data than there were input columns |
| Warning | 1262 | Row 40473 was truncated; it contained more data than there were input columns |
+---------+------+-------------------------------------------------------------------------------+
このファイルは TAB 区切りで提供されており、基本的には 英単語<TAB>和訳 という構成になっているのですが、これらの3つの行にだけ余分な<TAB>が含まれていることが分かりました。
これら(余分なTAB)を手動で削除した後、改めてインポートしたところ、Warnings がゼロになりました。
テーブルのサイズをチェック
以下のコマンドを実行します。
SELECT
table_name, engine, table_rows AS tbl_rows,
avg_row_length AS rlen,
floor((data_length+index_length)/1024/1024) AS allmb,
floor((data_length)/1024/1024) AS datamb,
floor((index_length)/1024/1024) AS indexmb
FROM
information_schema.tables
WHERE
table_schema=database()
ORDER BY
(data_length+index_length) DESC;
次のような結果が得られます。
全体で 6MB 程度のデータサイズです。
+----------------+--------+----------+------+-------+--------+---------+
| table_name | engine | tbl_rows | rlen | allmb | datamb | indexmb |
+----------------+--------+----------+------+-------+--------+---------+
| english_words | InnoDB | 45006 | 151 | 6 | 6 | 0 |
+----------------+--------+----------+------+-------+--------+---------+
Index 作成
実用時には english 列を元に search するケースが多いため、Index を作成しておきます。
※index_name は任意です
ALTER TABLE {db_name}.english_words ADD INDEX {index_name}(english);
Index 作成後にテーブルサイズを見てみると、1 - 2 MB 程度の使用量増加。
大したサイズではないですが、25% の増加と見ると少なくはないです。
+----------------+--------+----------+------+-------+--------+---------+
| table_name | engine | tbl_rows | rlen | allmb | datamb | indexmb |
+----------------+--------+----------+------+-------+--------+---------+
| english_words | InnoDB | 45838 | 149 | 8 | 6 | 1 |
+----------------+--------+----------+------+-------+--------+---------+