TOP > スクリプト制作メモ > PerlでMySQLを扱う

≫MySQL とは

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

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

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

MySQL の機能面での利点は、
  • 十分な処理速度と信頼性がある
  • インストールや操作が比較的簡単
  • 様々な言語で利用可能
といったところ。

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

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

≫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;

≫mysql コマンドの超基礎

とりあえず、酷いトラブルを避けるためのウルトラ基礎知識。
  • コマンドは複数行にわたってOK
  • コマンドの最後はセミコロン
  • コマンドの取り消しは「\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;

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

≫データベース・フィールドについて

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

項目1項目2項目3項目4
    
    
    
行 ───→




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

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

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

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

数字型
タイプサイズ
TINYINT1バイト
SMALLINT2バイト
MEDIUMINT3バイト
INT4バイト
INTEGER4バイト
BIGINT8バイト
FLOAT4バイト
DOUBLE8バイト
REAL8バイト
DECIMAL(M,D)最大M+2バイト
NUMERIC(M,D)最大M+2バイト
文字型
タイプサイズ
CHAR(M)255バイト
VARCHAR(M)255バイト
TINYTEXT255バイト
TINYBLOB255バイト
TEXT65535バイト
BLOB65535バイト
MEDIUMTEXT16777215バイト
MEDIUMBLOB16777215バイト
LONGTEXT4294967295バイト
LONGBLOB4294967295バイト
ENUM
('value1','value2',...)
列挙値の数(最大65535個の値)に応じて
1 または 2 バイト
SET
('value1','value2',...)
セット要素の数(最大64個の要素)に応じて
1、2、3、4、8 バイトのいずれか

日付・時間型
タイプサイズ
DATE3バイト(YYYY-MM-DD)
DATETIME8バイト(YYYY-MM-DD hh:mm:ss)
TIMESTAMP4バイト(例えばYYYYMMDDHHMMSS)
TIME3バイト(hh:mm:ss)
YEAR1バイト(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からスタート

≫DBD/DBI とは

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

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

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

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

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

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

≫DBI モジュール

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;
ここまでで1セット。
# 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 で実行可能です。

≫SQL の超基礎

  • 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\'");
それぞれ、もっと複雑に処理が可能です。

≫参考サイト


TOP > スクリプト制作メモ > PerlでMySQLを扱う
(C) bayashi.net