@blog.justoneplanet.info

日々勉強

PHP MySQL Improved Extension(mysqli)

mysqli拡張サポートによって、MySQL4.1以降で使える機能を利用できる。但し、個人的には全くこの手の関数を使わない。何故ならばMySQLに特化した関数であり、仮に他のDBに移行する場合、保守のコストがとっても高くなってしまうと考えているからだ。それにラーニングコストもかかるし、PDOのメソッド名とmysqliの関数名がごっちゃになる。

■mysqliを使ったDBへの接続

<?php
$dbh = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
if(mysqli_connect_errorno()){
    //code for error
}
mysqli->close();
?>

mysqliは例外をthrowしない。

<?php
$dbh = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);
if(!$dbh){
    //code for error
}
mysqli_close($dbh);
?>

■mysqliを使ったDBへのクエリ発行

<?php
$name = mysqli->real_escape_string($_POST['name']);
$sql = "SELECT `id`, `name` FROM `person` WHERE `name` = '$name'";
if(!$dbh->real_query($sql)){
    //print($dbh->error);
}
if($result = mysql->store_result()){
    while($row = $result->fetch_assoc()){
        print($row['id'] . ': ' . $row['name']);
    }
    $result->close();
}
?>

$resultもcloseしなきゃならんとは。。。めんどい。。。

<?php
$name = mysqli_real_escape_string($_POST['name']);
$sql = "SELECT `id`, `name` FROM `person` WHERE `name` = '$name'";
if(mysqli_real_query($dbh, $sql)){
    //print(mysqli_error());
}
if($result = mysql_store_result($dbh)){
    while($row = $result_fetch_assoc($result)){
        print($row['id'] . ': ' . $row['name']);
    }
    mysqli_free_result($result);
}
?>

うーむ。実に不愉快だ。やはりmysqliは性に合わん!

■mysqliを使ったプリペアドステートメント

<?php
$sql = "INSERT INTO `person`(`id`, `name`, `country_id`) VALUES(?, ?, ?)";
if($stmt = $dbh->prepare($sql)){
    $stmt->bindParam('s', $id, $_POST['name'], $_POST['country_id']);
    $stmt->execute();
    $stmt->bind_result($result_id, $result_name);
    while($stmt->fetch()){
        print($result_id . ': ' . $result_name);
    }
    $stmt->close();
}
?>

これじゃ、ソースを書きたくならないな。

<?php
$sql = "INSERT INTO `person`(`id`, `name`, `country_id`) VALUES(?, ?, ?)";
if($stmt = mysqli_prepare($dbh, $sql)){
    mysqli_stmt_bind_param($stmt, 's', $id, $_POST['name'], $_POST['country_id']);
    mysqli_stmt_execute()$stmt;
    mysqli_stmt_bind_result($dbh, $result_id, $result_name);
    while(mysqli_stmt_fetch()){
        print($result_id . ': ' . $result_name);
    }
    mysqli_stmt_close($stmt);
}
?>

■mysqliを使ったトランザクション

<?php
$dbh->autocommit(false);
$dbh->query("INSERT INTO `person`(`id`, `name`, `country_id`) VALUES('4', 'Jack', '2')");
$dbh->query("INSERT INTO `person`(`id`, `name`, `country_id`) VALUES('5', 'Emily', '1')");
if(!$dbh->commit()){
    $dbh->rollback();
}
?>
<?php
mysqli_autocommiti($dbh, false);
mysqli_query($dbh, "INSERT INTO `person`(`id`, `name`, `country_id`) VALUES('4', 'Jack', '2')");
mysqli_query($dbh, "INSERT INTO `person`(`id`, `name`, `country_id`) VALUES('5', 'Emily', '1')");
if(!mysqli_commit($dbh)){
    mysqli_rollback($dbh);
}
?>

mysqliの特徴

「手続き型」と「オブジェクト指向型」の記述が可能である。

サーバによってはインストールされていない事もしばしばあるようだ。ますます使わんぞ、コリャ=3・・・

命名

余計なお世話型関数。

PHP Data Object(PDO)

PDOには様々なDBに対する各々のドライバがある。一旦、インストールすれば(PDOを使用してDBへ接続している場合、)コードを殆ど書き換えることなく、DBを変更することが可能になる。

■PDOを使ったデータベースへの接続

<?php
$dsn = 'mysql:host=localhost;dbname=dbname';
$dbh = new PDO($dsn, DB_USER, DB_PASS);
?>

通常、PDOはエラーが発生しても何も表示しない(PDO::ERRMODE_SILENT)。これはセキュリティ上安全ではあるが、一方でデバッグを非常に困難にする。従って、例外を発生させ(ファイルに書き込みさせ)るようにすることも可能である。

<?php
try {
    $dsn = 'mysql:host=localhost;dbname=dbname';
    $dbh = new PDO($dsn, DB_USER, DB_PASS);
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(Exception $e){
    //code
}
?>

■PDOを使ったデータベースへのクエリ発行

SELECT文

SELECT文のような、結果セットを得ることを主目的としている場合は、以下のようにqueryメソッドを用いる。また、queryメソッドを用いた場合、戻り値はPDOStatementオブジェクトである。

<?php
$sql = "SELECT * FROM `person` WHERE `name` = 'John'";
$result = $dbh->query($sql);
foreach($result as $row){
    print($row['name'] . ': ' . $row['country_id']);
}
?>

但し、以下のように書いた場合、$rowには「数字キー」と「カラム名のキー」が両方含まれているので値が重複する。これはデフォルトのデータ取り出しモードがPDO::FETCH_BOTHであり、「数字キー」と「カラム名のキー」での配列になっているためである。

<?php
$sql = "SELECT * FROM `person` WHERE `name` = 'John'";
$result = $dbh->query($sql);
foreach($result as $row){
    foreach($row as $value){
        print($value . PHP_EOL);
    }
}
/*
1
1
John
John
2
2
*/
?>
データ取り出しモードの変更

データ取り出しモードを変更するには、以下のように結果セットが含まれるPDOStatementオブジェクト$resultに対して、setFetchModeメソッドを実行せねばならない。

<?php
$sql = "SELECT * FROM `person` WHERE `name` = 'John'";
$result = $dbh->query($sql);
$result->setFetchMode(PDO::FETCH_OBJ);
foreach($result as $row){
    print($row->name . ': ' . $row->country_id);
}
?>
ユーザ入力の値をSQL文に組み込むためのメソッド

通常、SQLインジェクションを回避するために、ユーザ入力の値をSQL文に含める際にはサニタイジングしなければならない。以下のように、quoteメソッドを用いることで可能である。

<?php
$name = $dbh->quote($_POST['name']);
$sql = "SELECT * FROM `person` WHERE `name` = '$name'";
$result = $dbh->query($sql);
?>

但し、いくつかのドライバはこのメソッドを実装していないので、後述のプリペアドステートメントを用いたほうが良い。

INSERT文、UPDATE文、DELETE文

INSERT文、UPDATE文、DELETE文には変更(処理)した行数を返すexecメソッドを用いる。

<?php
$sql = "INSERT INTO `person`(`id`, `name`, `country_id`) VALUES('4', 'Jack', '2')";
$num = $dbh->exec($sql);
print($num);//1
?>

■プリペアドステートメント(Prepared Statement)を用いたクエリの発行

プリペアドステートメントとは

  • 1回のリクエストに対する処理の中で、SQL文の再利用を可能にする
  • DBがプリペアドステートメントをサポートしてない場合、PDOが内部の関数で可能にする
  • DBがプリペアドステートメントをサポートしている場合、DBの機能を使ってアプリケーションの性能の向上を可能にする
  • (SQLインジェクションを誘発させる)クォート漏れに対するリスク回避を可能にする
<?php
$sql = "INSERT INTO `person`(`id`, `name`, `country_id`) VALUES(?, ?, ?)";
$stmt = $dbh->prepare($sql);
$stmt->execute(array(
    $id,
    $_POST['name'],
    $_POST['country_id']
));
?>

INSERT文、UPDATE文、DELETE文には、ユーザ入力の値をSQL文に含めることが非常に多い。とはいえ、以下のようにSELECT文でも当然ながら、プリペアドステートメントを用いることはある。

<?php
$sql = "SELECT * FROM `person` WHERE `name` = ?";
$stmt = $dbh->prepare($sql);
$stmt->execute(array('John'));
//$stmt->setFetchMode(PDO::FETCH_OBJ);
$result = $stmt->fetchAll();
var_dump($result);
/*
array(1) {
  [0]=>
  array(6) {
    ["id"]=>
    string(1) "1"
    [0]=>
    string(1) "1"
    ["name"]=>
    string(4) "John"
    [1]=>
    string(4) "John"
    ["country_id"]=>
    string(1) "2"
    [2]=>
    string(1) "2"
  }
}
*/
?>

但し、PHPのバージョンによって、(MySQL独自拡張の)LIMIT句に対するプリペアドステートメントにバグがあるため、それ(LIMIT句)以外での使用が望ましいと個人的には思う。また、LIMIT句は数値の値しか許容しないため、PHPで(型変換などを使って)サニタイジングすることは比較的に容易である。さらに、PDOStatementクラスに(結果を1行だけ返す)fetchメソッドが「あるバージョン」と「ないバージョン」があるため、このメソッドの使用はなるべく避けたほうが良い。

■PDOを使ったトランザクションの実行

以下のようにして、トランザクションを利用する。

<?php
try {
    $dsn = 'mysql:host=localhost;dbname=dbname';
    $dbh = new PDO($dsn, DB_USER, DB_PASS);
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $dbh->beginTransaction();
    $dbh->exec("INSERT INTO `person`(`id`, `name`, `country_id`) VALUES('4', 'Jack', '2')");
    $dbh->exec("INSERT INTO `person`(`id`, `name`, `country_id`) VALUES('5', 'Emily', '1')");
    $dbh->commit();
}
catch(Exception $e){
    $dbh->rollBack();
}
?>

各メソッドについて

beginTransaction
トランザクションを開始する
commit
確定する
rollBack
破棄して最初の状態に戻す

トランザクションとは

「全て成功する」か「全て失敗する」かのどちらかしか存在しないことを保証する機能。この機能が必要な例として、たいていの参考書では「銀行の振込み」などが扱われる。

SQL Joins(SQLにおけるテーブル結合)

テーブル結合とは、複数のテーブルから一つのレコードセットをつくり出す。多くのアプリケーションは非常に複雑なテーブル結合を使っている。

サンプルテーブル

personテーブル
id name country_id
1 John 2
2 Mike 1
3 Nick 99
countryテーブル
id name
1 USA
2 Japan
3 Canada

■内部結合(inner join)

内部結合とは、一歩のテーブルに存在するcountry.idが、もう一方のテーブルに含まれていないとき、行の結合が失敗し結果セットから除外される結合である。

SELECT
    `person`.`id`,
    `person`.`name`,
    `country`.`name`
FROM
    `person`
INNER JOIN
    `country`
ON
    `person`.`country_id` = `country`.`id`;

ONキーワードで結合条件「`person`.`country_id` = `country`.`id`」と指定するとことができる。上述の場合、「INNER JOIN」でなく「JOIN」でも結果は等しいが、可読性の問題などが生じるため、結合の種類はSQL文に明記することをお勧めする。

実行結果

id name name
1 John Japan
2 Mike USA

■左外部結合(left outer join)

外部結合とは、どちらか一方のテーブルに存在する行が、結果セットに含まれる結合である。「left outer join」はFROM節の左側のテーブル(person)に合わせて結合する。以下のようにすると左外部結合ができる。

SELECT
    `person`.`id`,
    `person`.`name`,
    `country`.`name`
FROM
    `person`
LEFT OUTER JOIN
    `country`
ON
    `person`.`country_id` = `country`.`id`;

実行結果

id name name
1 John Japan
2 Mike USA
3 Nick NULL

■右外部結合(right outer join)

「right outer join」はテーブル(country)に合わせて結合する。以下のようにすると右外部結合ができる。

SELECT
    `person`.`id`,
    `person`.`name`,
    `country`.`name`
FROM
    `person`
RIGHT OUTER JOIN
    `country`
ON
    `person`.`country_id` = `country`.`id`;

実行結果

id name name
2 Mike USA
1 John Japan
NULL NULL Canada

LEFTキーワードとRIGHTキーワードはどちらのデータに空白部分が含まれていても良いかを決めるワードである。

Relational Database and SQL(RDBとSQL)

■データベースの作成

以下のように、「CREATE DATABASE」文や「CREATE SCHEMA」文を使用する。

CREATE DATABASE <dbname>;
CREATE SCHEMA <dbname>;

■テーブルの作成

以下のように、「CREATE TABLE」文を使ってテーブルを作成する。

CREATE TABLE <tablename> (
    <colname> <coltype> [<colattributes>],
    [...
    <colname> <coltype> [<colattributes>]]    
);

例えば以下のようになる。

CREATE TABLE book (
    id INT NOT NULL PRIMARY KEY,
    isbn VARCHAR(13),
    title VARCHAR(255),
    author VARCHAR(255),
    publisher VARCHAR(255)
)

■インデックスの作成

CREATE INDEX <indexname> ON <tablename> (<column>[, ... <column>]);

例えば以下のようになる。

CREATE INDEX book_isbn ON book (isbn);

但し、一般的には以下のようにテーブルの作成と同時に行うことが多い。

CREATE TABLE `book` (
    `id` int(11) NOT NULL auto_increment,
    `isbn` varchar(13) default NULL,
    `title` varchar(255) default NULL,
    `author` varchar(255) default NULL,
    `publisher` varchar(255) default NULL,
    PRIMARY KEY  (`id`),
    KEY `book_isbn` (`isbn`)
);

■データベースの削除

DROP SCHEMA <dbname>;

■テーブルの削除

DROP TABLE <tablename>;

■データの操作

追加

データを追加するには、以下のようにINSERT( INTO)文を使用する。テーブルのカラム名は運用上のメリットが非常に大きいため、記述することを強くお勧めする(カラムの追加に対するSQL文の耐性)。

INSERT INTO <tablename>(<colname> [, ... , <colname>]) VALUES(<value> [, ... , <value>]);

更新

データを更新するためには、以下のようにUPDATE(~SET~)文を使用する。但し、以下のコードは全レコードが特定の値に上書きされてしまう。

UPDATE <tablename> SET <colname> = <value> [, ... , <colname> = <value>];

通常は、以下のようにWHERE節などと組み合わせて使用し、特定のレコードのみを更新する。

UPDATE `address_book` SET `name` = 'Mike' WHERE `id` = '1';

削除

データを削除するには、以下のようにDELETE文を使う。但し、以下のコードは全レコードが削除される。

DELETE  FROM <tablename>;

通常は以下のようにWHERE節などと組み合わせて使用する。

DELETE  FROM `address_book` WHERE `name` = 'John';

■データの表示

データを表示するには、以下のようにSELECT文を使用する。

SELECT * FROM `address_book`;

通常、アプリケーションで全件のデータが必要なときというのは稀である。そして全件をSQLから抽出すると、サーバのメモリを圧迫することになる。従って、以下のようにWHERE節などと組み合わせて使うことが多い。

SELECT `tel` FROM `address_book` WHERE `name` = 'John';

WHERE節について

WHERE節では、条件をANDやORで複数指定できる。また、LIKE演算子を使用し大文字/小文字を区別しないようにしたり、「%(任意の複数文字)」や「_(任意の一文字)」の記号を使用して検索できる。

SELECT `tel` FROM `address_book` WHERE (`name` = 'John') OR (`name` LIKE '%Joh%' AND `age` = '27')';

正規表現と記法が異なるのが、個人的にはシックリこない。

インデックス

日本語に直訳すると「索引」。リソース内の特定の項目を検索するためのメカニズム。一般的にはアプリケーションがWHERE節などで検索に使用するカラムに対して指定される。

  • 「インデックス」はデータベースサーバがテーブルの行を特定するために使われる
  • 「インデックス」は特殊なテーブルである
  • 「インデックス」は行を特定するために使用される列と、行が物理的に配置されている場所を示す情報のみを含んでいる

主キー(primary key)

一組のデータレコードを一意に識別するための特殊なインデックス

テーブルの関係性

テーブルの関係性として「1:1」と「1:複数」の場合は簡単に処理できるが、「複数:複数」の場合はintermediate tableなどを使用しなくてはならない。

1:1
子テーブルの1レコードが、親テーブルの1レコードと関連する
1:複数
子テーブルの複数レコードが、親テーブルの1レコードと関連する
複数:複数
子テーブルの複数レコードが、親テーブルの複数レコードと関連する

関係性を意識したテーブルの作成

1:1

以下のように、primary keyを共通にしてみると良い。

CREATE TABLE `book` (
    `id` int(11) NOT NULL,
    `isbn` varchar(13) default NULL,
    `title` varchar(255) default NULL,
    `author` varchar(255) default NULL,
    `publisher` varchar(255) default NULL,
    PRIMARY KEY  (`id`),
    KEY `book_isbn` (`isbn`)
);
CREATE TABLE `book_author` (
    `id` int(11) NOT NULL PRIMARY KEY,
    `author_id` int(11) NOT NULL
);
1:多

以下のように、REFERENCESを使用してbookテーブルを参照するようにする。

CREATE TABLE `book` (
    `id` int(11) NOT NULL auto_increment,
    `isbn` varchar(13) default NULL,
    `title` varchar(255) default NULL,
    `author` varchar(255) default NULL,
    `publisher` varchar(255) default NULL,
    PRIMARY KEY  (`id`),
    KEY `book_isbn` (`isbn`)
);
CREATE TABLE `book_chapter` (
    `id` int(11) NOT NULL PRIMARY KEY auto_increment,
    `isbn` varchar(13) REFERENCES `book`(`isbn`),
    `chapter_number` INT NOT NULL,
    `chapter_title` varchar(255)
);