今回は MySQL の AUTO_INCREMENT の属性が付けられたカラムの値の取得について説明します。
AUTO_INCREMENT 属性を付けられるカラムはデータ型が整数型です。
INSERT SQLにより AUTO_INCREMENT 属性カラムを指定しない場合、順次＋１された値が割り当てられます。
以下の様に、テスト用のテーブルを作成します。
「id」と「name」の2つのみのカラムを持ち、「id」に AUTO_INCREMENT を設定します。
MariaDB [pdo]> CREATE TABLE `test_auto` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `name` varchar(100) DEFAULT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.91 sec)
MariaDB [pdo]> show columns from `test_auto`;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.05 sec)
MariaDB [pdo]>
このテーブルにデータを追加してみます。
MariaDB [pdo]> INSERT INTO `test_auto` (`name`) VALUES ('test001');
Query OK, 1 row affected (0.05 sec)
MariaDB [pdo]> select * from `test_auto`;
+----+---------+
| id | name |
+----+---------+
| 1 | test001 |
+----+---------+
1 row in set (0.00 sec)
MySQL には最後の AUTO_INCREMENT カラムの値を取得できる LAST_INSERT_ID() 関数があります。
MariaDB [pdo]> SELECT LAST_INSERT_ID() FROM `test_auto`; +------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec)
MySQL の LAST_INSERT_ID() 関数と同じ機能が PDO クラスの lastInsertId メソッドで行えます。
上記の「test_auto」テーブルにデータを登録し、 lastInsertId メソッドを呼出してみます。
<?php /* [pdo17.php] */
ob_start(function($buf){ return mb_convert_encoding($buf, 'SJIS', 'UTF-8'); });
// MySQLデータベースに接続
$dsn = "mysql:host=localhost;dbname=pdo;";
$user = 'root';
$password = 'password';
try {
// PDOクラス生成（データベース接続）
$pdo = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
// エラー発生
die('データベース接続ERROR:'.$e->getMessage()."\n");
}
// レコード登録(INSERT)SQL:[id]は AUTO_INCREMENT なので指定しない
$sql = "insert into `test_auto` (name) VALUE (:name)";
// SQL文の準備
$pdostmt = $pdo->prepare($sql);
// SQL文の実行
$ret = $pdostmt->execute(array(":name" => 'test002'));
// [test_auto.id]の取得
$id = $pdo->lastInsertId();
echo "lastInsertId() = $id \n";
// 接続を閉じる
$pdo = null;
?>
これを実行すると以下の様に表示されます。2件目のレコードが追加されたので「id」に「2」が返されるのがわかります。
C:\xampp\htdocs\_test>php pdo17.php lastInsertId() = 2
テーブルの中身を見てみます。
MariaDB [pdo]> select * from `test_auto`; +----+---------+ | id | name | +----+---------+ | 1 | test001 | | 2 | test002 | +----+---------+ 2 rows in set (0.05 sec) MariaDB [pdo]>
この lastInsertId メソッドは、登録した直後のデータの「id」を取得しますが、これの必要な場面とは何があるのでしょうか。
例えば、売上データで、売上ヘッダテーブルと売上明細テーブルを分けて持つ場合です。
売上ヘッダデータと売上明細データは 1:N の関係で、同じ「id」で紐づけられると思います。
最初に売上ヘッダデータを登録し、その直後に lastInsertId メソッドで「id」を取得し、 売上明細にはその「id」と明細の連番で売上明細テーブルに登録するといった手順になると思います。
■トランザクション処理の中の AUTO_INCREMENT について
トランザクション処理の中で1件データを追加し、その後で明示的にエラーが発生するSQL文を実行し、ロールバックを行わせてみます。 以下にそのスクリプトを示します。
<?php /* [pdo18.php] */
ob_start(function($buf){ return mb_convert_encoding($buf, 'SJIS', 'UTF-8'); });
// MySQLデータベースに接続
$dsn = "mysql:host=localhost;dbname=pdo;";
$user = 'root';
$password = 'password';
try {
// PDOクラス生成（データベース接続）
$pdo = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
// エラー発生
die('データベース接続ERROR:'.$e->getMessage()."\n");
}
// トランザクション開始
$ret = $pdo->beginTransaction();
if ($ret == true) {
// トランザクション開始OK
try {
// レコード登録(INSERT)SQL:[id]は AUTO_INCREMENT なので指定しない
$sql = "insert into `test_auto` (name) VALUE (:name)";
$pdostmt = $pdo->prepare($sql);
$ret = $pdostmt->execute(array(":name" => 'test003'));
// [test_auto.id]の取得
$id = $pdo->lastInsertId();
echo "lastInsertId() = $id \n";
// エラーの発生するレコード登録(INSERT)SQL
$sql = "insert into `test_auto` (name) VALUE ()";
$pdostmt = $pdo->prepare($sql);
$ret = $pdostmt->execute(); // この時点でエラー発生
} catch (Exception $e) {
// 何かスクリプト上のエラーが在った!!
$ret = false;
}
// トランザクション終了処理
if ($ret == true) {
// コミット
$pdo->commit();
echo "commit() \n";
} else {
// ロールバック
$pdo->rollback();
echo "rollback() \n";
}
// [test_auto.id]の取得
$id = $pdo->lastInsertId();
echo "lastInsertId() = $id \n";
} else {
// トランザクション開始NG
}
// 接続を閉じる
$pdo = null;
?>
これを実行すると以下の様に表示されます。 登録実行後、最初の lastInsertId メソッドでは「3」が返されますが、 次の INSERT 処理ではエラーが発生し、ロールバック処理が行われます。 この後で、 lastInsertId メソッドを実行してもトランザクション開始前の状態に戻るため、「0」が返ります。
C:\xampp\htdocs\_test>php pdo18.php lastInsertId() = 3 rollback() lastInsertId() = 0
この状態で、テーブルの属性を SHOW TABLE コマンドで見てみます。
MariaDB [pdo]> SHOW TABLE STATUS LIKE 'test_auto'\G
*************************** 1. row ***************************
Name: test_auto
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 3
Avg_row_length: 5461
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 4
Create_time: 2020-12-04 18:39:53
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
test_auto の Auto_increment は「4」となり、次の追加処理が行われた時にはその値「4」が設定されます。
トランザクション中に Auto_increment の変化が在ったのですがロールバックしても値は「4」のままとなります。
上記のソースでは「3」の値でINSERTしたのですが、その後のエラーSQLでロールバックされ「3」は欠番となります。
この結果は少し、不都合な感じもしますが、複数のマシンから同じスクリプトが起動され、 それぞれが別々の Auto_increment の値を取る様にするためには必要なことだと思います。
コメント