データベース 記事一覧

データベースの選択と導入

公開日時:2016/07/07 19:00

さて、コンピューターを利用していると、様々なデータを管理したくなる事があります。

例えば、Raspberry Piに温度センサーを接続している場合、定期的に計測した温度をどこかで保存管理する必要があります。それはやはり、データベースを用いるのが適切でしょう。

現在ではデータベースを導入するのはとても簡単になっていて、勿論、Raspberry Piでもとても簡単に利用できます。

以下に、主要なオープンソースデータベースについの簡単な説明を示します。どれも、個人的に利用するにあたっては無料で導入できます。貴方にとってどのデータベースがウマが合うかが何となく分かっていただければ幸いです。

MySQL

世界で最も普及しているとも言われるオープンソースデータベースです。現在は買収されてオラクルの製品となっており、そこから派生したプロダクトであるMariaDBというデータベースも存在します。

PHPの普及の原動力ともなったデータベースで、PHPの現場では頻繁に利用されています。

とにかくアバウトな操作でもMySQLが勝手に判断して実行してくれるので、PHPのお手軽さが好きな人はウマが合うかもしれません。

>> MySQL の導入

PostgreSQL

オープンソースであり、正統派なデータベースとして人気のあるデータベースです。Oracleの代替手段という位置づけだと個人的には思います。

MySQLとは違って厳格な操作が必要な場面が多く、几帳面な人がウマが合うかもしれません。

追記型のデータベースであり、データの追加や更新が高速に行える場面が多くありますが、反面、定期的な不要データ領域の削除を行う必要があります。

>> PostgreSQL の導入

SQLite

PHPの標準的なデータベースとしての位置づけであった他、スマフォアプリの組み込みデータベースとしても頻繁に利用される、とてもお手軽なデータベースです。

データベースは1つのファイルで構成されていて、このファイルさえあれば、SQLiteがインストールされているどのコンピューターでも操作が可能です。

お手軽なデータベースである割にちゃんとトランザクション処理もできたりします。

一方、お手軽さを目指したためか、データ型はかなり大雑把です。

初めてのデータベースで簡単なデータを記録するだけであり、あんまり難しいことを考えたくない方がウマが合うかもしれません。

>> SQLite の導入

まとめ

以上、MySQL、PostgreSQL、SQLiteの三大オープンソースデータベースを簡単に説明してみました。

どのデータベースが自分に合っていそうかわかったら、各導入記事を読んでみてください。

ソケットサーバー「HAL」では、MySQL、PostgreSQL、SQLiteを簡単に扱うためのライブラリであるDataMapper Liteを導入する事が決定しました。

DataMapper Liteを利用すれば、簡単な記述でSQLが発行できるようになります。近日中にリリースいたしますし、既にソケットサーバー「HAL」をご購入されている方には無償で追加ファイルをご提供いたします。

また、さらに簡単にSQLが発行できる製品DataMapper(有償)も少し遅れてリリースされます。

ぜひ、DataMapper Liteをご利用いただき、データベースというものが如何に簡単に扱える便利なものであるのかをご実感ください。

SQLite の導入

公開日時:2016/07/07 19:00

SQLite はオープンソースの RDBMS(リレーショナル・データベース・マネジメント・システム)の一つです。PHP の標準的なデータベースとして用いられることは多く、また、アプリケーションなどの組み込みデータベースとしても利用されます。

データベースを構成するのは単一のファイルのみであり、それを動作させるサーバーが必要ないため管理がとても簡単なのが理由でしょう。iOS や Android などのアプリケーションの組み込みデータベースとしても、よく利用されています。

簡単なデータベースでありながら、RDB に求められる大抵の処理に対応しており、トランザクションまで備えているところが魅力です。

ただし、サーバーが動作するわけではなく単一のデータベースファイルとして存在しているため、複数のコンピューターからアクセスされるような、一般的な RDBMS の利用には向きません。

SQLite のインストール

現在の SQLite は SQLite3 が主流です。インストールについては、やはり apt-get です。現在(2016-07-07)インストールされる SQLite3 のバージョンは 3.8.7.1 のようです。

sudo apt-get -y install sqlite3 php5-sqlite

以下のコマンドで .ini ファイルとそれに対するシンボリックリンクが生成されます。

sudo php5enmod sqlite3

インストールが終わったらバージョンを確認してみましょう。

pi@raspberrypi:~ $ sqlite3 --version
3.8.7.1 2014-10-29 13:59:56

Apache でライブラリを有効にするため、再起動を行って下さい。

sudo service apache2 restart

データベースの作成

冒頭で述べたとおり、SQLite はサーバーが存在しないため、ユーザー管理などもありません。いきなりデータベースを作って動作させる事になります。

では、データベースを作ってみましょう。まず pi ユーザーのホームディレクトリで sqlite3 ディレクトリを作成してその中に入ります。

cd ~

mkdir sqlite3

cd sqlite3

作成した SQLite3 ディレクトリに入ったらデータベースの作成です。.sqlite という拡張子にしましたが、これは何でも構いません。ファイル名部分と併せて、あなたの好きな名前をつけて下さい。

sqlite3 feijoa.sqlite
pi@raspberrypi:~/sqlite3 $ sqlite3 feijoa.sqlite
SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter ".help" for usage hints.
sqlite>

sqlite のプロンプトが表示されました。これでデータベースが作成されています。試しにテーブルを作成してみましょう。sqlite プロンプトに以下の SQL を入力してみてください。

CREATE TABLE test_table1(id INTEGER PRIMARY KEY AUTOINCREMENT, name);

テーブルができているか確認してみましょう。

.mode line

としてラインモードに変更してから

SELECT * FROM sqlite_master;

で、確認できます。.mode については.modeコマンド(DBOnline)を参照して下さい。

sqlite> SELECT * FROM sqlite_master;
    type = table
    name = test_table1
tbl_name = test_table1
rootpage = 2
     sql = CREATE TABLE test_table1(id INTEGER PRIMARY KEY AUTOINCREMENT, name)

    type = table
    name = sqlite_sequence
tbl_name = sqlite_sequence
rootpage = 3
     sql = CREATE TABLE sqlite_sequence(name,seq)

なんか、出来てるみたいですね…。

レコードをインサートしてみましょう。

INSERT INTO test_table1(name) VALUES('テスト タロウ');
INSERT INTO test_table1(name) VALUES('テスト 二郎');
INSERT INTO test_table1(name) VALUES('テスト saburou');

では、今 INSERT したデータを見てみましょう。

SELECT * FROM test_table1 ORDER BY id ASC;

line モードだと見にくいので column モードにします。

.mode column

これで SELECT してみましょう。

SELECT * FROM test_table1 ORDER BY id ASC;

以下のようにレコードが登録されているはずです。

1           テスト タロウ
2           テスト 二郎
3           テスト saburou

このように、非常に簡単にデータベースを作ってデータを管理できるのが SQLite の最大の魅力です。

さて、ではこの sqlite のシェルから抜けてみましょう。

.exit

すると、Raspbian のシェルに戻ります。ファイルを確認してみましょう。

ls -la
pi@raspberrypi:~/sqlite3 $ ls -la
合計 12
drwxr-xr-x  2 pi pi 4096  7月  7 17:35 .
drwxr-xr-x 30 pi pi 4096  7月  7 17:42 ..
-rw-r--r--  1 pi pi 3072  7月  7 17:35 feijoa.sqlite

このように、feijoa.sqlite ファイルしかありません。これがデータベースの全てなのです。

ですから、このファイルを sqlite3 がインストールされている別のコンピューターに移してそのディレクトリで sqlite3 feijoa.sqlite を実行すれば、そこでデータベースの操作が出来てしまうのです。お手軽ですね。

と、いうことは、セキュリティなんてものは全く存在しないわけです。ですから、顧客情報等の重要なデータなどを管理する用途には全く向かない事に注意しましょう。

GUI でのデータベースの操作

さて、このように簡単な SQLite ですが、せっかく簡単なのですから操作をするのにいちいち SQL 文を入力するのなんてナンセンスです。何か GUI(グラフィカル・ユーザー・インタフェース)が欲しくなりますね。

SQLite を操作する GUI ツールはいろいろあるようですが、私は Firefox のアドオンである SQLite Manager を利用しています。

SQLite Manager 0.8.3.1-signed.1-signed

SQLite Manager アドオン入手画面

この、青い円筒のアイコンです。

インストールされたアドオン

勿論、Raspbian に Firefox をインストールしても良いですが、別の作業用コンピューターがあるならそちらで行っても構いません。先程も述べたように、SQLite のデータベースは単一ファイルで、それを別のコンピューターに移せばそこで管理できますから、使い慣れている OS で作業をした方が楽かもしれませんね。

青い円筒のアイコンをクリックすると SQLite Manager 画面が立ち上がるので、データベース > データベースに接続 で、データベースファイルを指定します。

データベース選択

データベースが選択されると、GUI でデータベースが操作できるようになります。

GUI 画面

あとは、適当に操作した後、データベースを Raspberry Pi に戻せば再び Raspberry Pi で管理できます。

そういうわけですから、データベースのバックアップなども非常に簡単に出来るわけです。

まとめ

SQLite の導入から利用までを簡単に纏めてみました。

ソケットサーバー「HAL」では、MySQL、PostgreSQL、SQLiteを簡単に扱うためのライブラリである DataMapper Liteを導入する事が決定しました。

DataMapper Lite を利用すれば、簡単な記述で SQL が発行できるようになります。近日中にリリースいたしますし、既にソケットサーバー「HAL」をご購入されている方には無償で追加ファイルをご提供いたします。

また、さらに簡単に SQL が発行できる製品 DataMapper(有償)も少し遅れてリリースされます。

ぜひ、DataMapper Lite をご利用いただき、データベースというものが如何に簡単に扱える便利なものであるのかをご実感ください。


SQL のバイブルを最後に紹介して締めさせて頂きます。

PostgreSQL の導入

公開日時:2016/07/06 23:10

PostgreSQL は、オープンソースの RDBMS(リレーショナル・データベース・マネジメント・システム)ではとても有名なデータベースです。主に「ポスグレ」または「ポストグレス(postgres)」という略称で呼ばれるこのデータベースは「Oracle」を使うほど厳密ではない商業の現場等で比較的よく利用されています。それは Oracle に劣る、という意味ではなく、メーカーによる様々なサポートを必要としない場合、という意味です。Oracle を使ったからといって手厚いサポートが受けられるわけではないようですが、少なくとも、メーカーのサポートが受けられるという名目は、商業の場ではかなりの意味を持ったりします。

PostgreSQL は MySQL と違って振る舞いがとても紳士的です。勝手に型を変換して「よしなに」処理をしてくれることはなく、間違っている時は「あなた、多分間違ってますよ?」と教えてくれます。

また、MySQL との大きな違いは PostgreSQL が追記型であるということです。

PostgreSQLのストレージアーキテクチャ(基本編)| PostgreSQL Deep Dive

をご覧いただくと良いかもしれませんが、レコードの更新を行う際には、前のレコードを上書きするのではなく、別のレコードとして追加します。このため、新規レコードの追加や更新が MySQL に比べて、多くの場合に早くなります。テーブルの生成なども MySQL よりも高速であったりします。

その代わりに古いレコードがどんどん残っていくので「バキューム」と呼ばれる、不要なレコードの一括削除を行う必要があります。

MySQL はテーブル構造の変更などがとても容易です。あるテーブルにおいて、カラムとカラムの間に新しいカラムを追加したい場合などMySQL ではとても簡単で1つの SQL を実行するだけで済みますが、PostgreSQL や他の多くの RDBMS では一大作業であったりします。

カジュアルな MySQL、トラディショナルな PostgreSQL という印象が適切かもしれません。

PostgreSQL のインストール

さて、では PostgreSQL をインストールしてみましょう。やはり apt-get です。現在(2016-07-06)apt-get でインストールされる PostgreSQL のバージョンは 9.4.8 のようです。

sudo apt-get install postgresql

バージョン確認は以下です。

psql --version
pi@raspberrypi:~ $ psql --version
psql (PostgreSQL) 9.4.8

アカウント情報の作成

さて、インストールが確認できたらルートユーザーにパスワードを設定しましょう。PostgreSQL のルートユーザーは、デフォルトでは postgres です。そこで、この postgres ユーザーに強固なパスワードを設定しましょう。

sudo passwd postgres

2 回パスワードの入力が求められるので、強固なパスワードを入力して下さい。絶対に忘れないようにしてくださいね。

正しくパスワードが更新されたら、su(substitute user=ユーザーの変更)コマンドで postgres ユーザーになってみましょう。

su - postgres

sudo をつけていないのでパスワードの入力を求められます。先ほど設定したパスワードを入力しましょう。

pi@raspberrypi:~ $ su - postgres
パスワード:
postgres@raspberrypi:~$

このように、プロンプトが pi から postgres に変われば postgres ユーザーとして OS にログインできました。

では今度は DB を操作するテストユーザーを作ってみましょう。次の文の feijoa のところはお好きな名前にして下さい。その名前でアカウントが作成されます。最初は、この後で作成するデータベース名と同じにしておくと便利です。

createuser -P feijoa

2 回パスワードの入力が求められ、一致するとユーザーが作成されます。

postgres@raspberrypi:~$ createuser -P feijoa
新しいロールのためのパスワード:
もう一度入力してください:
postgres@raspberrypi:~$

これで PostgreSQL にユーザーが作成されました。

データベースの作成

次はデータベースの作成です。まず、postgres ユーザーで、

psql

を実行し、PostgreSQL のシェルに入ります。ここで SQL が実行できるので、以下のようにデータベースを作成します。feijoa のところはデータベース名が入りますが、最初は先ほど作ったユーザーと同じ名前にしておくと良いでしょう。

CREATE DATABASE feijoa LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8' ENCODING 'UTF8' TEMPLATE template0;

少し時間がかかりますが、プロンプトが戻ってきたらデータベース生成完了です。確認してみましょう。

SELECT datname, datcollate FROM pg_database;

上記で、既存のデータベース名とその Collation(文字の照合順序)が表示されます。feijoa は無事有ったでしょうか?

なお、もっと簡単な方法として postgres=# のプロンプトが出ている状態で

\l

とするだけでリストが確認できます。(¥えん、lえる です)

なお、ターミナル等で先程から PostgreSQL のシェルに入ったままであった場合は

\q

で抜けることができます。(¥えん、qキュー です)

設定ファイルの編集

さて、PostgreSQL のシェルから抜けたら、今のうちに設定ファイルを修正しておきましょう。

sudo vi /etc/postgresql/9.4/main/postgresql.conf
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)

という箇所を見つけて下さい。ここは、外部から TCP/IP で接続された場合の、内部での待受け IP アドレスです。カンマ区切りで複数記述できるので、この PostgreSQL がインストールされている Raspberry Pi の IP アドレスを指定しておいてください。(**の部分はお使いの Raspberry Pi にあわせてください)

listen_addresses = 'localhost,192.168.**.**'

保存したら次は pg_hba.conf です。

sudo vi /etc/postgresql/9.4/main/pg_hba.conf
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

という箇所があるので、この次の行に

host    all             all             192.168.**.**/32          trust

を追加して下さい。この IP アドレスは、Raspberry Pi の PostgreSQL に接続することのできるコンピューターに割り当てられている IP アドレスです。この後、GUI ツールをインストールしますが、その GUI ツールを動作させるコンピューターの IP アドレスです。

GUI ツールの利用

SQL はデータベースを操作するためのとても便利な言語ですが、とても沢山の命令がありすぐには覚えられないでしょう。そこで、GUI(グラフィカル・ユーザー・インタフェース)のデータベース操作アプリケーションを使うのが便利です。

PostgreSQL で有名な GUI ツールは、 PgAdminⅢ でしょう。Ⅳ は現在ベータ版しかないようなので、とりあえずは Ⅲ を使うことにします。

PgAdmin

PgAdminサイト

ここの Download のメニューからお使いの OS 用の PgAdmin 3 をダウンロードして下さい。Windows 版の最新は pgadmin3-1.22.1.zip のようですね。

バージョン選択

ダウンロード画面

ダウンロードしたら解凍しましょう。中に msi が入っていますので、それをダブルクリックです。

インストール1

インストール2

インストール3

インストール4

インストール5

インストールが終わったら起動してみましょう。

PgAdmin での DB 操作

PgAdmin 3 の起動画面はこんな感じです。(当サイトでは既にローカルに PostgreSQL がインストールされていて、それに対する接続が登録されているのでサーバに 3 つの接続が登録されています)

PgAdmin3の起動後の画面

まず、PostgreSQL サーバーへの接続ですが、画面左上に有るコンセントのアイコンが、データベースへの接続情報の新規作成です。クリックすると次のような画面が開きます。各設定項目を入力しましょう。

新しいサーバ登録

OK をクリックすると、オブジェクトブラウザで Raspberry Pi の PostgreSQL の情報が確認できるはずです。

※本当は SSH で接続したいのですが、どうも上手く生きませんでした。後日、良い方法が分かったら追記します。

登録されたサーバー情報

テーブルの作成

では、現在接続している feijoa データベースにテーブルを作成してみましょう。PostgreSQL にはデータベースの下に SCHEMA(スキーマ)という要素を持つことが出来ます。

例えば、プログラムで使うテーブルは public スキーマに入れ、ログを記録するテーブルは logs スキーマに入れるといった、一種のディレクトリ分けのようなことが可能です。

今回はとりあえず、public スキーマ内に test_table1 テーブルを作成してみましょう。public のテーブルを右クリックして「新しいテーブル...」です。

まず、プロパティタブでテーブルの概要を入力します。

テーブルの概要

次に、列タブに移ってカラムの定義をしていきます。id カラムと name カラムを定義しましょう。

id カラムに設定する serial 型は特殊な型で、レコードを追加する度に 1 ずつ自動で値が増えていきます。

id カラム

name カラムは character varying(45) としました。character varying は可変長の文字列が格納できる型で、この場合、最大で 45 文字までが格納できます。マルチバイトの文字も 1 文字としてカウントされます。

altテキスト

最後に、制約タブでこのテーブルの id カラムに対して「主キー」(プライマリ・キー)を設定します。プライマリ・キー(primary key)は、そこに記録される情報は一意であり、更にヌル(null)を許さないという特別な制約です。このため、あるプライマリ・キーを持つレコードは必ず 1 つであることが保証されます。

制約の登録

これで OK で確定させると、テーブルが登録されます。

登録された test_table1 テーブル

SQL の実行

次は SQL を発行してみましょう。画面上にある SQL ボタンをクリックして下さい。Query を入力する画面が開きます。

Query ウィンドウ

この SQL エディタに、次のように入力して下さい。

INSERT INTO test_table1(name) VALUES('テスト タロウ');
INSERT INTO test_table1(name) VALUES('テスト 二郎');
INSERT INTO test_table1(name) VALUES('テスト saburou');

入力したら、画面上の緑の▲ボタンで実行です。

画面下のメッセージタブに

Query returned successfully: one row affected, 31 msec execution time.

と表示されればクエリの実行は成功です。PgAdmin 3 の画面に戻って test_table1 が選択されている状態で、画面上の水色のテーブルアイコンをクリックしてみましょう。次の画像のようにレコードが追加されている事を確認して下さい。id についても、何も入力していなくても連番が自動でふられているのを確認して下さい。

テーブル内容確認

この後はいろいろと自由に操作してみましょう。

まとめ

PostgreSQL の導入から利用までを簡単に纏めてみました。

ソケットサーバー「HAL」では、MySQL、PostgreSQL、SQLite を簡単に扱うためのライブラリである DataMapper Lite を導入する事が決定しました。

DataMapper Lite を利用すれば、簡単な記述で SQL が発行できるようになります。近日中にリリースいたしますし、既に ソケットサーバー「HAL」をご購入されている方には無償で追加ファイルをご提供いたします。

また、さらに簡単に SQL が発行できる本製品 DataMapper(有償)も少し遅れてリリースされます。

ぜひ、DataMapper Lite をご利用いただき、データベースというものが如何に簡単に扱える便利なものであるのかをご実感ください。


SQL のバイブルを最後に紹介して締めさせて頂きます。

MySQL の導入

公開日時:2016/07/06 14:11

MySQL は PHP の現場で頻繁に利用されているデータベースです。世界で最も普及している RDBMS(リレーショナル・データベース・マネジメント・システム)とも言われます。

オープンソースであり、一時はサン・マイクロシステムズに買収されて維持管理されていましたが、現在は更にサン・マイクロシステムズ自体がオラクルに買収されたため、オラクルの所有物となりました。

ライセンス形態は GNU GPL と商用ライセンスのどちらかを選べるデュアルライセンスとなっています。

MySQL の特徴

とにかく気楽に使えます。他の RDB ではカラムの型を意識しないとエラーになったりしますが、MySQL では、 MySQL が「よしなに」判断して、多分、ユーザーが望んでいるであろう処理を勝手にやってくれます。そこが良い所でもあり、恐ろしいところでもあります。そんなところが PHP とよく似ているから PHP の現場で頻繁に利用されるのかもしれないと勝手に考えています。

初期の MySQL は文字コードやストレージエンジンの問題でいろいろと不備がありましたが、MySQL 5.5 あたりからはかなりの問題が解消され、とても良い RDB になりました。

しかし、オラクル社に買収されたことは多少なりとも悲劇でした。

オラクル社は、その名を冠する有名な RDB である「Oracle」を別に所有しているため、MySQL が飼い殺しにあうかもしれないという危機感が生まれることになりました。IT の世界ではよくあることです。

そこで、MySQL のオリジナルコードの作者自らが MySQL をフォーク(あるプロダクトから別の分岐を派生させること)し、MariaDB というプロダクトを立ち上げました。ライセンスは GPL v2 です。

この2つの RDB はとても良く似ていて、現在はどちらでも同じコードがほぼそのまま動きますが、将来的には別々の拡張がなされるかもしれません。

MySQL のインストール

さて、では MySQL をインストールしてみましょう。インストールはやはり apt-get です。Raspbian Jessie の apt-get で現在(2016-07-06)インストールされるのは MySQL 5.5.44 のようです。

sudo apt-get install mysql-server

すると、root パスワード入力画面が表示されます。ここは強固なパスワードを指定しましょう。絶対に忘れないようにして下さい。

rootパスワード設定

入力すると、確認用にもう一度パスワードの入力を求められます。パスワードが一致すればインストールが続行されます。

{※2016/07/10 追記}(red) インストールが終わったら、mysqlとクライアントのデフォルトの文字コードを設定します。

sudo vi /etc/mysql/my.cnf

下記のように、[mysqld] と [client] の記述の下に、キャラセットを指定します。{異なるパラメター名}(red)ですので注意して下さい。

[mysqld]
...
character-set-server=utf8mb4

[client]
...
default-character-set=utf8mb4

以上、インストールと設定が終わったら MySQL にログインしてみましょう。

mysql -u root -p

パスワードを求められるので、先ほどのパスワードを入力して下さい。以下のようにプロンプトが mysql> に変わればログイン成功です。

ログイン後

ではちょっと SQL コマンドを入力してみましょう。MySQL には SHOW DATABASES; という命令があります。これは、MySQL に登録されているデータベースのリストを表示する MySQL 専用の SQL です。実行してみましょう。なお、SQL では基本的には大文字と小文字を区別しません。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

今度はデータベースを作成してみましょう。※追記:デフォルトキャラクタセットの指定を追加しました。

mysql> CREATE SCHEMA `pi_test` DEFAULT CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.00 sec)

再び SHOW DATABASES; で確認してみましょう。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| pi_test            |
+--------------------+
4 rows in set (0.00 sec)

新しいデータベース pi_test が作成されましたね。

次は新規ユーザーを1人作ってみましょう。データベース名を pi_test にしたので、ユーザー名も pi_test にしてみます。パスワードの ******** は適宜変えて下さい。なお、指定したパスワードは忘れないように。

CREATE USER 'pi_test'@'localhost' IDENTIFIED BY '********';

うまくユーザーが作られれば

Query OK, 0 rows affected (0.01 sec)

といったメッセージが返ります。ところで、@'localhost' ですが、MySQL では接続するプロトコル毎にユーザーが作成できます。

例えば 'pi_test'\@'192.168.1.10' などとした場合は、TCP/IP 接続でのアドレス 192.168.1.10 に対してユーザーを作成しますが、'pi_test'\@'localhost' の場合は UNIX ソケットでのユーザーを作成します。ですから 'pi_test'@'localhost' と 'pi_test'\@'127.0.0.1' は、全く違うユーザーアカウントを指す事になります。詳しくは「とみたまさひろ」さんが作成されたスライド MySQLの始め方 を参照して下さい。

そういうわけで、このあと、GUI(グラフィカル・ユーザー・インタフェース)を導入して Raspberry Pi の MySQL を遠隔操作するため、TCP/IP 用のアカウントも同時に作っておきます。IP アドレスの ** はお使いの環境に合わせて下さい。

CREATE USER 'pi_test'@'192.168.1**.**' IDENTIFIED BY '********';

ちなみに「とみたまさひろ」さんとは Postfix や MySQL の本を何冊も書かれ、OSS貢献者賞の受賞歴もある方です。

さて、ユーザーを作ったら、そのユーザーに権限を与えましょう。pi_test.* は、「pi_test データベースの全てのテーブルについて」という意味です。*(アスタリスク)はワイルドカードといって、全ての値を表現する特殊な文字です。(これまでセキュリティのために伏せ字にしてきた ** などとは別の意味ですので注意して下さい)

GRANT ALL PRIVILEGES ON pi_test.* TO 'pi_test'@'localhost';
GRANT ALL PRIVILEGES ON pi_test.* TO 'pi_test'@'192.168.**.**';

これ以降の様々な SQL についてはここで説明すると膨大になり過ぎるので省きます。SQL については別途、検索エンジンなどを使って調べて下さい。

最後に quit を入力して MySQL のシェルから抜けましょう。

mysql> quit
Bye
pi@raspberrypi:~ $

MySQL Workbench の導入

さて、先程はコマンドラインから MySQL を操作しましたが、全てのコマンドをタイプするのはとても面倒です。そこで、GUI(グラフィカル・ユーザーズ・インターフェイス)を導入して MySQL の操作を簡単にしてしまいましょう。

MySQL Workbench

MySQL Workbench サイト

「ダウンロードはこちら >>」を選ぶと、ダウンロードする OS を選択する画面に遷移します。

ダウンロード画面

MySQL Workbench 6.3.7 の Select Platform から お使いの OS を選択してダウンロードして下さい。x64 Windows(64bit OS) の方は 「Windows (x86, 64-bit), MSI Installer 6.3.7 (mysql-workbench-community-6.3.7-winx64.msi)」あたりが良いのではないかと思います。x86(32bit OS)の方は、そちらのパッケージを選んでください。

すると、こんな画面になります。「ログインするか、アカウントを作ってくれ」という画面です。

ダウンロードを開始する

あまりおおっぴらには推奨できないのですが、画面下に小さく「No thanks, just start my download.」というリンクがあります。「いいえ、結構です。ダウンロードを開始します」と書いてありますので、そういう意味です。

さて、ダウンロードが終わったらセットアップを行いましょう。

インストーラースタート

インストールするディレクトリ指定

インストールの仕方指定

確認画面

インストール中

インストール完了

これでインストール完了です。

では立ち上げてみましょう。

MySQL Workbench 起動直後

先ほど作ったデータベースに接続するには、画面上の◯の中に+のアイコンをクリックして下さい。Setup New Connection ダイアログが開かれます。

新規接続設定画面

Connection Name には自由な接続名をつけて下さい。

Connection Method には、Standard TCP/IP over SSH を指定します。これで秘密鍵を使った SSH 通信が使えます。

SSH Hostname には SSH接続する時と同じ、Raspberry Pi の IP アドレス : ポート番号を指定します。例えば

192.168.1.10:54321

といった感じです。

SSH Username は変更してなければ pi です。

SSH Password の Store in Vault... で SSH 接続用のフレーズを入力します。

SSH Key File に、SSH 接続用の id_rsa ファイルを指定して下さい。

MySQL Hostname、MySQL Server Port、Username は、今回は SSH 接続先と同じなので特に変更する必要はありません。

MySQL Hostname: 127.0.0.1
MySQL Server Port: 3306
Username: pi_test

Password については、先ほどユーザーを作成した時に設定したパスワードを入力してください。

以上を設定したら、「Test Connection」をクリックして接続テストをします。

パスフレーズ確認画面

次のような画面が出れば成功です。エラーが表示された場合は入力項目を再確認して下さい。特に WiFi 接続している時などは、WiFi に繋がっていない場合があります。

テスト接続成功

接続テストに成功したら OK をクリックして下さい。Workbench の画面に接続が登録されます。

接続登録成功

登録された接続をクリックして、実際に RDB にログインしてみましょう。次の様な画面が開くはずです。画面左上の SCHEMAS を確認して下さい。先ほどコマンドラインで作成した pi_test データベースが確認できるはずです。

データベース操作画面

pi_test の左側にある▲をクリックして情報を開いてみましょう。Tables、Views、Stored Procedures、Functions がありますが、まだ何も登録されていないのでどれも空です。

では、テーブルを作成してみましょう。

Tables を右クリックして Create Table... です。テーブル作成ウィンドウが開くので以下のように適当に入力してみましょう。

テーブル作成画面

id カラム(Column)の PK はプライマリ・キー(primary key)と言って、そこに記録される情報は一意であり、更にヌル(null)を許さないという特別な制約です。このため、ある PK を持つレコードは必ず 1 つであることが保証されます。NN は Not Null のことで、null を許可しないという意味です。

AI は MySQL での連番処理のことで、オート・インクリメント(auto_increment)の略です。これが指定してあるカラムは、レコードを追加する度に連番を1ずつ繰り上げて記録していきます。

一つ注意して欲しいのは Collation の項です。MySQL では様々な文字コードを扱うことができるのでここで、このテーブルのデフォルト文字コードを指定します。

utf8mb4 というのは MySQL でのユニコード(Unicode)の charset のうちの一つで、世界中の言語の文字を利用できる文字コードですが、別に、utf8 という charset も存在します。

utf8 はマルチバイトになっていて、その文字によって何バイトを利用するかが変化します。例えば a ~ z や 0 ~ 9 などは 1 バイトしか使いませんが、一般的な日本語の文字は 3 バイトを必要とします。utf8 自体では最大で ~~6 バイト~~(とみたさんから「ユニコードとしての UTF-8 は現在、最大 4 バイトです」というご指摘を頂きました。感謝して訂正します)までが規定されているのですが、MySQL の utf8 は 3 バイトまでの文字しか対応していませんでした。

ところが、例えば携帯電話等で使われる絵文字は 4 バイトを必要とする物があり、これが問題となっていました。

そこで MySQL では utf8mb4 という、4 バイト対応の utf8 を用意したのです。ですから今後は、特別な場合を除いて utf8mb4 を使ったほうが良さそうです。なお、この utf8mb4 は MySQL 5.5.3 以降で導入され利用できるようになりました。

また、MySQL の Collation(照合順序)は様々な言語に対応しているのですが、日本語を扱うにあたっては general_ci を利用することをお勧めします。

unicode_ci という Collation があってこれが頻繁に使われているのですが、この unicode_ci では類似検索(LIKE 検索)する際に、半角数字の 1 と全角数字の1と丸付き数字の①などを同じ文字とみなします。

もっと凄いところでは、パパとハハとババも同じ文字だとみなします。

これは、ユニコードに規定されている照合レベルのうちの一部だけを MySQL が実装しているためなのですが、あまり好ましい事ではありませんよね?

ですから、これらをきちんと区別してくれるよう、general_ci を使いましょう、ということです。

ところで、携帯絵文字の 4 バイトに対応した utf8mb4 ですが、それでもまだ問題がありました。

参考:MySQL と寿司ビール問題

大元の発信者はとみたまさひろさんです。このスライドが参考になるでしょう。

参考:寿司ビール問題

そこで、もっと厳密に区別したい場合は bin という、完全なバイトの一致の検査を行う Collation もあります。ただ、これは厳密すぎて逆に不便なので、必要なところだけそのようにするのがお奨めです。MySQL は データベース、テーブル、カラムのそれぞれに独立した Collation が指定できるので心配ありません。

2021/06/09追記

現在のMySQL 8.0では、utf8mb4_ja_0900_as_cs_ksのように厳密な検査を行うCollationが導入されています。

参考:MySQL 8.0 でも utf8mb4_general_ci を使い続けたい僕らは

さて、このように設定を行ったら Apply をクリックしてみましょう。

確認画面

確認画面が表示されます。実際に発行される SQL が表示されているのがわかりますか? 最初のコマンドラインでこれと同じ SQL 文を書いて実行してもテーブルは作成されますが、このように GUI で操作したほうが簡単ですよね?

さて、確認が終わったら Apply をクリックして SQL を適用しましょう。

最終確認画面

さらに確認画面が表示されます。ここで Finish をクリックするとテーブルが作成されます。Navigator で確認してみてください。

作成されたテーブル

さて、テーブルが作成されたら今度はレコードを追加してみましょう。

今度は SQL を書いてみましょうか。

File > New Query Tab

で、クエリ記述画面を開きます。ここに、次のように記述して下さい。

use pi_test;
INSERT INTO test_table1(name) VALUES('テスト タロウ');
INSERT INTO test_table1(name) VALUES('テスト 二郎');
INSERT INTO test_table1(name) VALUES('テスト saburou');

記述したら、クエリ記述画面の上にある実行ボタン(Execute)をクリックします。

クエリ実行

正常に実行されると、画面下の Output にクエリの実行状況が表示されます。

Output

そうしたら、先ほど作成したテーブル test_table1 を Navigator で右クリックして Select Rows - Limit 1000 を選びましょう。INSERT したレコードが登録されているのを確認して下さい。id についても、何も入力していなくても連番が自動でふられているのを確認して下さい。

レコード確認

ところで、name は何文字まで格納できると思いますか?

答えは、テーブル作成の時に

VARCHAR(45)

と指定してあったので 45 文字です。VARCHAR は文字数可変のデータ型で、( ) で指定した文字数までのテキストを格納することができます。

ここでいう 1 文字とは、utf8(及び他の文字コード全て)における 1 文字なので、1 バイト文字の英数字だろうが、3 バイト文字の漢字だろうが 1 文字です。

なお、MySQL の場合、デフォルトでは、指定されている文字数を超えた分については何も言わずにばっさりと切り捨てられます。注意しましょう。

後はあなたのお好きなようにいじってみてください。GUI なのでとても簡単です。(英語を調べる必要は多少ありますが)

まとめ

MySQL の導入から利用までを簡単に纏めてみました。

ソケットサーバー「HAL」では、MySQL、PostgreSQL、SQLiteを簡単に扱うためのライブラリであるDataMapper Liteを導入する事が決定しました。

DataMapper Liteを利用すれば、簡単な記述で SQL が発行できるようになります。近日中にリリースいたしますし、既にソケットサーバー「HAL」をご購入されている方には無償で追加ファイルをご提供いたします。

また、さらに簡単に SQL が発行できる製品 DataMapper(有償)も少し遅れてリリースされます。

ぜひ、DataMapper Lite をご利用いただき、データベースというものが如何に簡単に扱える便利なものであるのかをご実感ください。


とみたさんの書籍(共著)と SQL のバイブルを最後に紹介して締めさせて頂きます。

記事リンク