背景

　かつて誰もが為し得なかった神への道を目指し、僕はPythonで動画サイトのサムネ画像をスクレイピングで色々収集してまして、それをPillowモジュールで加工したものをMySQLで作ったテーブルのBlobカラムに保管しておきたいという必要に迫られました。画像の数は大量にあるので、ストレージの節約のため、いちいちファイルに書き出したりせずにオンメモリでBlobカラムに挿入する方法が欲しかった。その過程で調べて得られた手順をここに残しておきます。

BLOBフィールド、展開！

以下のようなBLOB型のカラムimgfileを含んだimg_materialテーブルをMySQLのDBで定義しておきます。

CREATE TABLE `img_material` (
  `video_id` char(11) NOT NULL DEFAULT '' COMMENT '動画ID',
  `imgfile` blob DEFAULT NULL COMMENT '画像ファイル'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

P.I.L.緊急排出！！

次のように指定したURLからrequestsで取得した画像があり、それをpillowのImageオブジェクトに格納し、トリミング加工をしています。

from PIL import Image
import urllib.request as req
import io

thumbnail_url = "https://xxx.xxx.xxx/xxxxxx.jpg" # 取り込みたいサムネ画像のURL
f = io.BytesIO(req.urlopen(thumbnail_url).read())#URLからサムネ画像を展開
thumbnail = Image.open(f).convert('RGB') #Imageオブジェクトに格納
thumbnail = thumbnail.crop((0, 11, 120, 79))#黒帯部分をトリミング

一旦Imageオブジェクトに変換して加工しましたが、これを上記のテーブルのBlobカラムであるimgfileへ格納したい！なのでもう一度ByteIOによりバイナリデータに戻します。

imgdata = io.BytesIO()
thumbnail.save(imgdata, "JPEG") 
imgdata.seek(0) #ファイルの先頭へ
imgfile = imgdata.read() #バイナリデータを格納

こうしてできたimgfileを先ほど定義したBlobカラムのあるテーブルへINSERTします。

挿入、カラム並べて

MySQLdbにより、DBへの接続を行います。db_configの中身は、ご自分の環境に合わせて適宜変えてください。

import MySQLdb

# DB設定
db_config = {
    'host': 'localhost',
    'db': 'database_name',
    'user': 'root',
    'passwd': 'your_password',
    'charset': 'utf8',
}

# 接続する
conn = MySQLdb.connect(
  host=db_config['host'], 
  db=db_config['db'], 
  user=db_config['user'],
  passwd=db_config['passwd'], 
  charset=db_config['charset']
  )

上記ののコネクションから得られたカーソルにより、先述のimgfileを挿入するINSERT文を実行します。

# SQLクエリ
SQL_INSERT_IM = '''
  insert into img_material
  values(%s,%s)
'''

# DB操作用にカーソルを作成
cur = conn.cursor()

try:
    # 挿入データをタプルに格納
    insert_blob_tuple = ("VIDEO000001", imgfile)
    # INSERT文を実行
    result = cur.execute(SQL_INSERT_IM , insert_blob_tuple)
    conn.commit()
    print("Image data inserted successfully as a BLOB into table", result)

except MySQLdb.Error as error:
    print("Failed inserting BLOB data into MySQL table {}".format(error))

finally:
    if conn.open:
        cursor.close()
        conn.close()
        print("MySQL connection is closed")

参考資料

Image Module — Pillow (PIL Fork) 7.2.0 documentation
https://pillow.readthedocs.io/en/stable/reference/Image.html
io --- ストリームを扱うコアツール — Python 3.8.6 ドキュメント
https://docs.python.org/ja/3/library/io.html
BLOB 型と TEXT 型
https://dev.mysql.com/doc/refman/5.6/ja/blob.html
裏死海文書
https://w.atwiki.jp/evacommu/pages/92.html

現在業務委託でRailsアプリを使ってスクレイピングでデータ収集のお手伝いをさせていただいてるのですが、Docker環境を使っているため何かの拍子にうっかりvolume等を削除してしまったら大変なので、処理が終わるたびにデータのバックアップを取ってます。（レコード数33万件ほどある）

その方法をご紹介します！

バックアップ手順

バックアップデータを突っ込みたいファイルをRailsのrootディレクトリにつくり以下のコマンドを実行する。

$ docker exec -it CONTAINER_NAME（例:myapp_db_1） mysqldump DATABASE_NAME(例:myapp_developmentなど) > backup.sql

リストア(インポート）手順

取り込みたいバックアップファイル（dump.sql)をRailsのrootディレクトリにつくり以下のコマンドを実行する。

$ docker cp dump.sql mydocker_db_1:/tmp/dump.sql
$ docker exec -it myapp_db_1 bash
$ mysql -u USER_NAME -p -h HOST_NAME(database.ymlのhost名,dbとか) DB_NAME(myapp_developmentなど) < /tmp/dump.sql

docker exec -it myapp_db_1 bashでコンテナ内に入り、mysqlコマンドでインポートできます！

今後はデータのバックアップも自動化できればなおいいと思うので挑戦していきたいです！

以上です！

読んでいただきありがとうございます！

ご指摘などあればコメントいただけると嬉しいです！

はじめに

今回はMySQL 8.0について以下の内容を紹介します。
- なぜ、バージョン5から8に一気に上がったのか
- Charsetとは何か
- Collationとは何か

Youtubeでも解説しているので、ぜひ確認してみてください。
【YouTube動画】MySQL8.0

MySQLの名前の由来

MySQLは共同創設者のMichael Widenius (通称 Monty) さんの長女 Myにちなんで名付けられました。
ちなみに、MySQLをベースに、完全なGPLライセンスにしたMariaDBは、次女のMariaにちなんでいます。

[参考]
Why is the Software Called MariaDB?

MySQLがバージョンアップで5から8に上がった理由

MySQL 6.0はストレージエンジンにFalconを搭載したものを作っていたそうです。
しかし、Falconではなく、InnoDBを搭載したものの開発を進めたため、6.0は廃止になりました。

MySQL 7.0はMySQL Clusterと被るのを防ぎ、他のMySQL関連プロジェクトと数字を合わせるために、スキップしたという話です。
開発者のコメントとか特に見つからなかったので、ここら辺は眉唾程度に思ってください。

[参考]
What happened to MySQL 6 & 7?
MySQL 8.0.0 Development Milestone Release登場！！

MySQLの歴史に興味があれば、以下のQiita記事が詳しくてオススメです！
MySQLの歴史が面白い

Charsetについて

MySQLではDBに文字を保存するとき、Charset (文字コード) を指定します。
Charsetのデフォルトは、5.7まではlatin1、8.0からはutf8mb4になっています。
昨今の絵文字ブームの影響を受けてか、MySQL 8.0からは絵文字もDBに保存できます！

Charsetの確認方法

Charsetを確認するのに便利なコマンドを紹介します。

使用できるCharsetの一覧を表示する。

$ SHOW CHARSET;

データベースのデフォルトのCharsetを調べる。

$ SHOW CREATE DATABASE <db名>;

テーブルのデフォルトのCharsetを調べる。

$ SHOW CREATE TABLE <table名>;

[参考]
めちゃくちゃオススメします。浅すぎず深すぎず、MySQL 8.0について説明されています。
MySQL 徹底入門 第4版

Collationについて

DBに保存した文字をソートするときの規則としてCollationが使われます。
ただ、これだけでも説明が長くなるので、Collationの読み方だけ紹介します (Collationは別記事で紹介します！)。

Collationの例として以下があるとします。

utf8mb4_ja_0900_as_cs_ks

utf8mb4はCharset (文字コード) の種類を示します。

jaの部分は言語名を示します。中国語ならzh、ロシア語ならruです。

0900はUnicodeのバージョンを示します。

asの部分はアクセントを区別するかどうかを示します。
日本語の場合、濁音、半濁音の区別で、例えば「ぱぱ」と「はは」を区別するかどうかです。
また、アクセントの違いを区別しないときは、aiを付けます (accent insensitive)。

csの部分は大文字小文字を区別するかどうかを示します。
日本語の場合、「びよういん」と「びょういん」を区別するかどうかです。

ksの部分は平仮名、カタカナを区別するかどうかを示します。
言語名をjaにした場合のみ使用できます。

以上のことを踏まえると次のように解釈できます。
意外と読むのは簡単ですね。

utf8mb4_ja_0900_as_cs_ks
=> ルールを適用する文字は、文字コード utf8mb4、日本語かつUnicode バージョン9.00であり、濁音・半濁音・大文字・小文字・平仮名・カタカナを区別する。

まとめ

今回MySQLのメジャーナンバーの謎、Charset/Collationの意味について紹介しました。
別動画・別記事でもっと詳細にCollationを扱うので、ぜひ楽しみにしてください！

twitterやyoutubeでのコメントもお待ちしています！

はじめに

はじめての投稿です。
20代は不動産付け、30代未経験、就活一社でそのままその自社開発企業に内定頂きました駆け出しエンジニアです。
今日はMysqlについて書きます。

土地のデータから一定の条件を満たすものを取り出すというプログラムです。
坪単価１００万円以下の土地をピックアップします。

create TABLEから。

-- テーブルをcreate (カラム：id :m2 :price)
CREATE TABLE lands(
 id INT NOT NULL AUTO_INCREMENT,
 m2 INT,
 price INT,
 PRIMARY KEY (id)
);

-- 次にデータをテーブルに入れ込みます
INSERT INTO lands (m2, price) VALUES
 (200, 100000000),
 (300, 30000000),
 (500, 40000000),
 (100, 50000000);

-- ＊（データぜんぶ）をSELECTしてlandsテーブルからWHEREで条件にあう形でデータを取り出します。
-- 「坪単価」なので定数3.30578を入れます。

SELECT * FROM lands WHERE price / m2 * 3.30578 < 1000000 ;

今回はテーブルを作り、データを入れ込むところからやりましたが、
もし、データが蓄積されているような場合であれば、

SELECT * FROM　テーブル名　WHERE　条件;

で取ってくることができますね。
以上です。

※　INT：整数ですよ、という宣言です。
※　NOT NULL：NULLを許さないよという記述です。主キーなのにNULLではこまりますので。
※　AUTO_INCREMENT：自動でidを連番で入れ込んでくれる便利なメソッドです。
※　PRIMARY KEY (id):idカラムのデータが主キーですよ〜という宣言です。これがないとAUTO_INCREMENTは使えません。
※　INSERT INTO：これから先に宣言するデータを入れ込みますよ〜という記述です。
※　VALUES：値のことです。上で言うと、（m2,price）となっているので、(m2 => 200, price => 1000000)と言う意味です。

はじめに

はじめての投稿です。
20代は不動産付け、30代未経験、就活一社でそのままその自社開発企業に内定頂きました駆け出しエンジニアです。
今日はMysqlについて書きます。

土地のデータから一定の条件を満たすものを取り出すというプログラムです。
坪単価１００万円以下の土地をピックアップします。

create TABLEから。

-- テーブルをcreate (カラム：id :m2 :price)
CREATE TABLE rands(
 id INT NOT NULL AUTO_INCREMENT,
 m2 INT,
 price INT,
 PRIMARY KEY (id)
);

-- 次にデータをテーブルに入れ込みます
INSERT INTO rands (m2, price) VALUES
 (200, 100000000),
 (300, 30000000),
 (500, 40000000),
 (100, 50000000);

-- ＊（データぜんぶ）をSELECTしてrandsテーブルからWHEREで条件にあう形でデータを取り出します。
-- 「坪単価」なので定数3.30578を入れます。

SELECT * FROM rands WHERE price / m2 * 3.30578 < 1000000 ;

今回はテーブルを作り、データを入れ込むところからやりましたが、
もし、データが蓄積されているような場合であれば、

SELECT * FROM　テーブル名　WHERE　条件;

で取ってくることができますね。
以上です。

※　INT：整数ですよ、という宣言です。
※　NOT NULL：NULLを許さないよという記述です。主キーなのにNULLではこまりますので。
※　AUTO_INCREMENT：自動でidを連番で入れ込んでくれる便利なメソッドです。
※　PRIMARY KEY (id):idカラムのデータが主キーですよ〜という宣言です。これがないとAUTO_INCREMENTは使えません。
※　INSERT INTO：これから先に宣言するデータを入れ込みますよ〜という記述です。
※　VALUES：値のことです。上で言うと、（m2,price）となっているので、(m2 => 200, price => 1000000)と言う意味です。

背景

MySQL 8.0にしてしばらく、おかしいデータが有ることに気付いた。
INSERT文を見てみたら成功しているがwarningが出ている。失敗ではないのでエラーを上げておらず気づかなかった。

'Data truncated for column x at row 1'

よくあるのは、varchar(255) のカラムに 256文字以上のデータを突っ込んだときだ。この場合最初に255文字だけがinsertされて、後ろは切り捨てられる。
そう思って見ると、そのカラムは datetime 型だった。
datetimeでtruncatedってなんやろな。

Insertしようとしてるデータは 2020-10-02T12:34:56.000+0900 だった。ああ・・・。
MySQL 5.7の頃はそれでもtruncatedされて　2020-10-02 12:34:56 がinsertされていたが、目の前のMySQL 8.0では truncate された結果 0000-00-00 00:00:00 が入っていた。なるほど・・・

@@sql_mode

0000-00-00 00:00:00 はそのdatetime columnのデフォルト値だ。warningのためにそのfieldに当てたデータはすべて切り捨てられデフォルト値が入った。この動きはあれだ。sql_modeだ。移行前後の環境を比較した。

mysql 5.7

select @@sql_mode;
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

mysql 8.0

select @@sql_mode;
'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

STRICT_TRANS_TABLES が原因でいいと思うのだが、Manualを読んでも自分が経験した事象と合わなくてよくわからなかった。が、真面目に読み進めたらあっさり原因が書いていた。

If strict mode is enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict

strict mode というのは either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled のときだ。今回は STRICT_TRANS_TABLES が有効だったので strict mode is enabled だ。

その状態では本来 '0000-00-00' はエラーになるが、 INSERT IGNORE するとwarningは出るが成功する（値が入る）。これが原因だった。

解決法

STRICT_TRANS_TABLES を外すというのは最高最短の解決法だ。しかし一歩上を目指すならやっちゃいけない。いいエンジニアになるには遠回りする勇気が必要らしい。

1. INSERT文から IGNORE を外す。

とりあえず、で作ったコードにはIGNOREが入りまくっている。しかしいつの間にかそのサービスの運用は義務になり止まると怒られが発生される。いいエンジニアは最初からIGNOREを使わないでスパパっと完璧なコードが書けるものだ。そうなりたいものだ。ひとつずつでいいから IGNORE は消していこう。STRICT_TRANS_TABLES な時代には似合わないstatementだ。

2. truncateしない文字数のデータをinsertする

今回は Jira API から返ってきた date っぽい値をそのまんま INSERT していた。事前に必要な文字数だけに短くして INSERT した。

2020-10-02T12:34:56.000+0900 → 2020-10-02T12:34:56

最初はやっつけでよかったコードだ。APIをそのまま渡したら入った。そのまま運用した。この判断が悪かったのだろうか。これまで一年動き続けた価値はあると思っていたが。最近こういう結果がよかったのかどうか、よくわからない。その時の僕の開発スタイルはこうだ。API結果はできるだけ残したいので、必要そうな値をまとめてINSERT IGNOREで投げてみる。warningが出ようが、重要なカラムさえ入っていればok. あとは問題になってから直せばいい。そんなこんなで全部NULLとか0000なカラムが僕のテーブルにはよくある。それが僕をハメることもしばしばだ。最初からwarningを見ていれば今回の問題は防げたのだろう。しかしsmall startにそのコストが見合うのだろうか？　答えは、一発で完璧なことができるスーパーエンジニアになるしかないような気がする。

まとめ

型を意識するのがいいエンジニアの条件らしい。php/jsから入った自分にはその意識がまったくもって希薄だ。

warningもエラーと見なす運用体制が必要だ。まずは IGNORE を消していこう。がんばろう。

で、そもそもなんでこのIGNOREがあったかというと INSERT ON DUPLICATE KEY UPDATE 的な話があってそこはそこで改めて勉強し直さないとな、というところ

我々は何故2015年にもなってSELECTして無かったらINSERTする場合のベストパターンを確立できていないのか。

— songmu (@songmu) July 16, 2015

https://songmu.jp/riji/entry/2015-07-20-insert-ignore.html

おしまい

目的

• dumpファイルをMySQLに読み込む方法をまとめる

実施環境

• ハードウェア環境

項目	情報
OS	macOS Catalina(10.15.5)
ハードウェア	MacBook Pro (13-inch, 2020, Four Thunderbolt 3 ports)
プロセッサ	2 GHz クアッドコアIntel Core i5
メモリ	32 GB 3733 MHz LPDDR4
グラフィックス	Intel Iris Plus Graphics 1536 MB

• ソフトウェア環境

項目	情報	備考
MySQLバージョン	8.0.19 for osx10.13 on x86_64	Homwbrewを用いてこちらの方法で導入→Mac HomebrewでMySQLをインストールする

前提条件

• 実施環境に準ずる環境が構築されていること。
• MySQLが導入されMySQLのrootアカウントのパスワードがわかっていること。
  • MySQLのrootアカウントのパスワードがわからなくなってしまった方はこちら→Mac ローカル環境の MySQL 8.x のrootパスワードを忘れた時のリセット方法
• dumpファイルがすでに用意されていること。

前提情報

• ~/dumpディレクトリ直下にdumpファイル「dump_yyyymmdd.sql」が存在するとする。
• MySQLのデータベース「test」にdumpファイル「dump_yyyymmdd.sql」を読み込ませる方法をまとめる。

詳細

1. 下記コマンドを実行して~/dumpディレクトリに移動する。

   $ cd ~/dump
   

2. 下記コマンドを実行してdumpファイルを読み込む。

   $ mysql -u root -pルートユーザのパスワード dumpを読み込むデータベース名 < dumpファイル名
   

3. MySQLのデータベース「test」にdumpファイル「dump_yyyymmdd.sql」を読み込ませるときは下記のようになる。

   $ mysql -u root -pルートユーザのパスワード test < dump_yyyymmdd.sql