MySQLのAUTO_INCREMENTで挿入した行の番号はLAST_INSERT_ID()関数で取得できます。
MySQLでは「Multiple row insert」という複数行をまとめてINSERTすることができるのですが、この場合はLAST_INSERT_ID()関数は最初の行のみが取得できます。
InnoDBではロック機能によってMultiple row insert時に割り振られるIDは連番となるのですが、NDBではどうか気になったので試してみました。
結論から言うと、MySQL Clusterでは複数行INSERTしたときに、LAST_INSERT_IDからすべてのIDを取得することはできないようです。
テストテーブルの作成
AUTO_INCREMENTを指定したINNODBとNDBCLUSTERのTABLEを作成します。
検証用にLAST_INSERT_ID()の結果を保存するTABLEも作成しました。
DROP TABLE ndb_table;
DROP TABLE idb_table;
DROP TABLE update_table;
CREATE TABLE ndb_table (
id int AUTO_INCREMENT NOT NULL PRIMARY KEY,
type int
) engine=ndbcluster;
CREATE TABLE idb_table (
id int AUTO_INCREMENT NOT NULL PRIMARY KEY,
type int
) engine=innodb;
CREATE TABLE update_table (
id int,
type int,
PRIMARY KEY (id,type)
) engine=innodb;
テストプログラムの作成
テストプログラムはPythonで書きました。先日、MySQL接続テストを行ったので。
http://qiita.com/estaro/items/0e92cd25e7ae2ca69b29
import mysql.connector
cnx = mysql.connector.connect(
host='192.168.0.10',
user='hoge',
password='hoge',
database='mydb'
)
cursor = cnx.cursor();
for index in range (0,1000):
cursor.execute("INSERT INTO ndb_table (id, type) VALUES (NULL, 1),
(NULL, 1),(NULL, 1),(NULL, 1),(NULL, 1),(NULL, 1),
(NULL, 1),(NULL, 1),(NULL, 1),(NULL, 1)")
last_incert_id = cursor.lastrowid
cursor.execute("INSERT INTO update_table (id, type) VALUES (%s, 1)", (last_incert_id,));
cnx.commit();
cursor.close()
cnx.close()
10件の複数行INSERTを1000回実行します。LAST_INSERT_ID()ではなく、connector提供のlastrowidを使用しています。
同様にtypeと対象テーブルを分けたプログラムを4つ用意しました。
これをまとめてシェルで実行します。
# !/bin/bash
python3.6 ai_test1.py &
python3.6 ai_test2.py &
python3.6 ai_test3.py &
python3.6 ai_test4.py &
実行
./ai_test_n.sh
結果の確認
挿入されたデータです。想定した件数になっています。
mysql> SELECT type, count(type) FROM ndb_table GROUP BY type;
+------+-------------+
| type | count(type) |
+------+-------------+
| 1 | 10000 |
| 2 | 10000 |
+------+-------------+
2 rows in set (0.05 sec)
mysql> SELECT type, count(type) FROM idb_table GROUP BY type;
+------+-------------+
| type | count(type) |
+------+-------------+
| 3 | 10000 |
| 4 | 10000 |
+------+-------------+
2 rows in set (0.00 sec)
mysql> SELECT type, count(type) FROM update_table GROUP BY type;
+------+-------------+
| type | count(type) |
+------+-------------+
| 1 | 1000 |
| 2 | 1000 |
| 3 | 1000 |
| 4 | 1000 |
+------+-------------+
4 rows in set (0.01 sec)
続いて、データの検証です。
検証用のSQLは以下です。ちょっとスマートではないですが、検証用なので良しとします。
SELECT
type, count(type)
FROM
ndb_table
WHERE
id IN (SELECT id FROM update_table WHERE type=1)
OR id IN (SELECT id+1 FROM update_table WHERE type=1)
OR id IN (SELECT id+2 FROM update_table WHERE type=1)
OR id IN (SELECT id+3 FROM update_table WHERE type=1)
OR id IN (SELECT id+4 FROM update_table WHERE type=1)
OR id IN (SELECT id+5 FROM update_table WHERE type=1)
OR id IN (SELECT id+6 FROM update_table WHERE type=1)
OR id IN (SELECT id+7 FROM update_table WHERE type=1)
OR id IN (SELECT id+8 FROM update_table WHERE type=1)
OR id IN (SELECT id+9 FROM update_table WHERE type=1)
GROUP BY type;
実行結果（INNODB）
これが期待の結果です。LAST_INSERT_ID()から+1～+9の連番となっていることがわかります。
+------+-------------+
| type | count(type) |
+------+-------------+
| 3 | 10000 |
+------+-------------+
+------+-------------+
| type | count(type) |
+------+-------------+
| 4 | 10000 |
+------+-------------+
実行結果(NDBCLUSTER)
ズレています。
+------+-------------+
| type | count(type) |
+------+-------------+
| 1 | 9832 |
| 2 | 168 |
+------+-------------+
+------+-------------+
| type | count(type) |
+------+-------------+
| 1 | 173 |
| 2 | 9827 |
+------+-------------+
MySQLのAUTO_INCREMENTと複数INSERTはすごく便利なので困りますね。
終わりです。
参考
https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html
https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id