PerlでMySQLを扱う

MySQL は最もよく知られているオープンソース SQL データベース管理システムです。

同じデータベースソフトには、mSQL とか PostgreSQL とか Oracle とかがあります。

MySQL ソフトウェアは、GPLライセンスに基づき、だれもが無料でインターネットからダウンロードし、使用することができます。

MySQL の機能面での利点は、

• 十分な処理速度と信頼性がある
• インストールや操作が比較的簡単
• 様々な言語で利用可能

といったところ。

ここでは、Perl で MySQL を操作する基本をメモしておきます。

と、言いつつ、しばらくシェル上での操作が続きますが。。。

MySQL のクイックインストール(公式マニュアルの日本語訳)

apt なら

# apt-get install MySQL-server
# apt-get install MySQL-Max
# apt-get install MySQL-client
# apt-get install MySQL-shared

Perl で MySQL を扱う DBD/DBIモジュール群もインストールします。

apt なら

# apt-get install perl-DBD-MySQL

環境がそろったら、早速 MySQL を起動。

# service mysql start

MySQLの管理者パスワードを設定します(もちろん管理者権限で)。
********の部分がパスワードなので、任意の文字列に書き換えましょう。

# mysqladmin -u root password '********'

一応、動くところを確認しますか。

# mysql -u root -p
Enter password: パスワード入力
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is X to server version: X.XX.XX

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

コマンドプロンプトでは、「mysql>」に続けてコマンドを打ちこんで操作します。

mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.00 sec)

mysql> \q
Bye
#

「show databases;」というのは、現在のデータベースを一覧表示するコマンドです。
「mysql」と「test」のふたつが初期状態で存在するかと思います。

「mysql」は必須ですが、「test」は別にいらないので、削除しましょう。

mysql> drop database test;

とりあえず、酷いトラブルを避けるためのウルトラ基礎知識。

• コマンドは複数行にわたってＯＫ
• コマンドの最後はセミコロン
• コマンドの取り消しは「\c」
• mysql から抜けるときは「\q」もしくは「quit」

以上(´ー｀)

MySQL データベースを作成します。

この時点では、データベースの箱を作るだけで、どういう仕切りにするかは、また後ほど設定します。

mysql> CREATE DATABASE DataBaseName;

作成したデータベースを扱う一般ユーザを作成します。
root の権限は時に強力過ぎるので、通常は、権限の限られた一般ユーザでデータベースを操作します。

mysql> GRANT ALL ON DataBaseName.* TO UserName@localhost IDENTIFIED BY "********";

DataBaseName のみを操作できるユーザ UserName が作成されました。

おまじないのごとく、ユーザテーブルをリフレッシュします。

mysql> FLUSH PRIVILEGES;

以上で、データベースの箱と、それを扱うユーザが準備できました。

データベース・テーブルの名称です。

項目１ 項目２ 項目３ 項目４                         行 ───→

列 │ │ ↓

項目はフィールドとも言います。

行は、レコードとも言い、列はカラムとも言います。

MySQL では、行のフィールドごとに、値のデータタイプを指定しておく必要があります。

データタイプには以下のようなものがあります。

■数字型 タイプ サイズ TINYINT 1バイト SMALLINT 2バイト MEDIUMINT 3バイト INT 4バイト INTEGER 4バイト BIGINT 8バイト FLOAT 4バイト DOUBLE 8バイト REAL 8バイト DECIMAL(M,D) 最大M+2バイト NUMERIC(M,D) 最大M+2バイト

■文字型 タイプ サイズ CHAR(M) 255バイト VARCHAR(M) 255バイト TINYTEXT 255バイト TINYBLOB 255バイト TEXT 65535バイト BLOB 65535バイト MEDIUMTEXT 16777215バイト MEDIUMBLOB 16777215バイト LONGTEXT 4294967295バイト LONGBLOB 4294967295バイト ENUM ('value1','value2',...) 列挙値の数(最大65535個の値)に応じて 1 または 2 バイト SET ('value1','value2',...) セット要素の数(最大64個の要素)に応じて 1、2、3、4、8 バイトのいずれか

■日付･時間型

タイプ	サイズ
DATE	3バイト(YYYY-MM-DD)
DATETIME	8バイト(YYYY-MM-DD hh:mm:ss)
TIMESTAMP	4バイト(例えばYYYYMMDDHHMMSS)
TIME	3バイト(hh:mm:ss)
YEAR	1バイト(YYYY)

最適なフィールドタイプを設定することにより、記憶領域を節約したり、処理速度の向上が望めます。

テーブルを作成するには、CREATE TABLE というコマンドを使います。

基本

mysql> CREATE TABLE TableName (Tableの構造定義);

例えば、以下のような構造のテーブル＜MemberList＞を作成します。

id	name	age	sex

以下のようにコマンドを打ちこみます(1行ずつ改行(Enter))。

mysql> CREATE TABLE MemberList
　　-> (
　　-> id INT,
　　-> name VARCHAR(20),
　　-> age INT(3),
　　-> sex VARCHAR(1),
　　-> );
Query OK, 0 rows affected (0.00 sec)

それぞれのフィールド名と、どういう数値･文字列が格納されるかを定義しています。

基本的には、上記のようでも良いのですが、実用上は、さらにオプショナルな設定を加えて、以下のようにすると良いかもしれません。

mysql> CREATE TABLE MemberList
　　-> (
　　-> id INT UNSIGEND AUTO_INCREMENT NOT NULL,
　　-> name VARCHAR(20),
　　-> age INT(3) NOT NULL,
　　-> sex ENUM('M','F') DEFAULT 'F' NOT NULL,
　　-> PRIMARY KEY(num)
　　-> )
　　-> AUTO_INCREMENT = 10000;
Query OK, 0 rows affected (0.00 sec)

UNSIGEND => 負数不可
AUTO_INCREMENT => 自動的に増加
NOT NULL => ヌル値の禁止
ENUM('M','F') => M か F のいずれか
DEFAULT 'F' => デフォルトは F
PRIMARY KEY(X) => X の項目は重複しないキーフィールド(AUTO_INCREMENT は PRIMARY KEY設定と常にニコイチ)
AUTO_INCREMENT = 10000 => 10000からスタート

DBI/DBD とは、プログラミング言語 とデータベースの間をとりもってくれる汎用インターフェイスです。

DBI を利用すれば、作成したスクリプトで利用するデータベースが変わっても、スクリプトの変更を最小限に抑えることができます(ほとんどの場合、変更の必要がない！)。

DBD は、データベースシステムごとに存在する、いわゆるドライバのような存在です。

MySQL には、DBD::mysql が用意されており、PostgreSQL には、DBD::Pg が用意されています。

DBD/DBI モジュールは、Perl5以降のオブジェクト指向な記述が求められます。

オブジェクト指向ってなに？　って人にはちょっとつらいかも。。。

Perl から MySQL を操作するために、DBI モジュールを use します。

use DBI;

データベースへの接続は、以下のように書きます。[connectメソッド]

$dbh = DBI->connect('DBI:mysql:データベース名:サーバ名:ポート番号', 'ユーザー名', 'パスワード');

小難しくは、「データベースハンドルオブジェクト($dbh)を生成する」と言います。

サーバ名や、ポート番号は省略することもできます。

# データソース
$d = 'DBI:mysql:MemberList';
# ユーザ名
$u = 'UserName';
# パスワード
$p = '********';

# データベースへ接続
$dbh = DBI->connect($d, $u, $p);

データベースの操作には、SQL という言語を使用します。

SQL ( Structured Query Language )
データベースの定義や操作などを実現するためのデータベース言語の一つ。　IBM社が開発し、アメリカ規格協会(ANSI)やJISで標準化されている ( が、バージョンは複数存在する)。　MySQL は、標準的な SQL に対応している。

ちなみに、SQL の読みは「シークェル」。MySQL は「マイエスキューエル」。なんだって。

SQL文の準備は、以下のように書きます。[prepareメソッド]

$sth = $dbh->prepare("SQL文");

小難しくは、「ステートメントハンドルオブジェクト($sth)を生成する」と言います。

言葉通り、まだ 準備(prepare)段階で、SQL文の ' 実行 ' は行われていません。

SQL文の実行は、以下のように書きます。

$sth->execute;

ステートメントハンドルオブジェクトを閉じる。

$sth->finish;

データベースハンドルオブジェクトを閉じる。

$dbh->disconnect;

ここまでで１セット。

# DBI モジュールの読みこみ
use DBI;
# データソース
$d = 'DBI:mysql:MemberList';
# ユーザ名
$u = 'UserName';
# パスワード
$p = '********';

# データベースへ接続
$dbh = DBI->connect($d, $u, $p);
# SQL文を用意
$sth = $dbh->prepare("SQL文");
# SQLを実行
$sth->execute;
# ステートメントハンドルオブジェクトを閉じる
$sth->finish;
# データベースハンドルオブジェクトを閉じる
$dbh->disconnect;

DBI のメソッドは、もちろん他にもあります。

たぶん、実用上、必須はエスケープ処理。

DBI でエスケープする quoteメソッド

$quoted_str = $dbh->quote($str);

超便利。

ここでは、シェルでやってしまいましたが、もちろん、テーブルの作成から DBI で実行可能です。

• INSERT - データの挿入

  [文法]
  INSERT INTO テーブル (フィールド1，フィールド2，...) VALUES (値1,値2, ...);
  [DBI]
  $sth = $dbh->prepare("INSERT INTO MemberList (age) VALUES (20)");

• UPDATE - データの更新

  [文法]
  UPDATE テーブル SET フィールド=値 [WHERE 条件];
  [DBI]
  $sth = $dbh->prepare("UPDATE MemberList SET age=\'18\' WHERE age=\'20\'");

• SELECT - データの検索

  [文法]
  SELECT フィールド FROM テーブル [WHERE 条件];
  [DBI]
  $sth = $dbh->prepare("SELECT name FROM MemberList WHERE id=\'1\'");

• DELETE - データの削除

  [文法]
  DELETE テーブル [WHERE 条件];
  [DBI]
  $sth = $dbh->prepare("DELETE FROM MemberList WHERE id=\'2\'");

それぞれ、もっと複雑に処理が可能です。

• 日本MySQLユーザ会＜MySQLドキュメントマニュアル＞
• DBD::mysql
• DBI