refirio.org

Memo

■グループごとに連番を作成

カテゴリごとにAUTO_INCREMENTな値を登録するメモ

■InnoDBで対応する場合

「最大値+1」で連番を作成する（AUTO_INCREMENTを使わない） | PHPプログラミングの教科書 [php1st.com]
https://php1st.com/806

MySQLメモ： INSERT時に最大値＋1を使いたいけど、auto_incrementは使いたくない - Qiita
https://qiita.com/EmikoKishi/items/7f05ef6289a85b525c55CREATE TABLE test (
  category VARCHAR(5),
  id       INT,
  text     VARCHAR(10),
  PRIMARY KEY (category, id)
) ENGINE=InnoDB;

INSERT INTO test SELECT 'A', LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1), 'TEST' FROM test WHERE category = 'A';
INSERT INTO test SELECT 'A', LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1), 'TEST' FROM test WHERE category = 'A';
INSERT INTO test SELECT 'B', LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1), 'TEST' FROM test WHERE category = 'B';
INSERT INTO test SELECT 'C', LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1), 'TEST' FROM test WHERE category = 'C';
INSERT INTO test SELECT 'B', LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1), 'TEST' FROM test WHERE category = 'B';
INSERT INTO test SELECT 'B', LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1), 'TEST' FROM test WHERE category = 'B';

SELECT * FROM test;
+----------+----+------+
| category | id | text |
+----------+----+------+
| A        |  1 | TEST |
| A        |  2 | TEST |
| B        |  1 | TEST |
| B        |  2 | TEST |
| B        |  3 | TEST |
| C        |  1 | TEST |
+----------+----+------+


ただし MAX(id) で値を取得した直後にデータが挿入された場合に正しく連番が作られない
…のような問題が無いかどうかは要検証
ロック、トランザクション、トランザクション分離レベルなどを気にしながら処理する必要があるかもしれない

なおデータの挿入は、以下のように記述してもいい

INSERT INTO test (category, id, text) SELECT 'A', LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1), 'TEST' FROM test WHERE category = 'A';


以下だとうまく行かなかった（0から開始されてしまう）

INSERT INTO test (category, id, text) SELECT 'A', MAX(id) + 1, 'TEST' FROM test WHERE category = 'A';
■MyISAMで対応する場合

[MySQL]親ごとに枝番をAUTO_INCREMENT @餅。
http://doshiroutonike.com/web/other-web/3740

以下のようにすると、categoryごとにidの連番を作ることができる
ただしInnoDBではなくMyISAMにする必要があり、トランザクションが使えない問題があるので注意

CREATE TABLE test (
  category VARCHAR(5),
  id       INT AUTO_INCREMENT,
  text     VARCHAR(10),
  PRIMARY KEY (category, id)
) ENGINE=MyISAM;

INSERT INTO test VALUES('A', NULL, 'TEST');
INSERT INTO test VALUES('A', NULL, 'TEST');
INSERT INTO test VALUES('B', NULL, 'TEST');
INSERT INTO test VALUES('C', NULL, 'TEST');
INSERT INTO test VALUES('B', NULL, 'TEST');
INSERT INTO test VALUES('B', NULL, 'TEST');

SELECT * FROM test;
+----------+----+------+
| category | id | text |
+----------+----+------+
| A        |  1 | TEST |
| A        |  2 | TEST |
| B        |  1 | TEST |
| C        |  1 | TEST |
| B        |  2 | TEST |
| B        |  3 | TEST |
+----------+----+------+
■MyISAMで対応する場合: 検証メモ

以下は実際に検証した内容
前提として、MySQL5.5移行はデフォルトでInnoDBとなっている
よって「ENGINE=InnoDB」を省略してもInnoDB扱いになる

以下のコードでは、そもそもテーブルを作成できない

CREATE TABLE `test1` (
  category VARCHAR(5),
  id       INT AUTO_INCREMENT,
  text     VARCHAR(10),
  PRIMARY KEY (category, id)
) ENGINE=InnoDB;

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key


以下のコードなら作成できるが、idが項目ごとの連番にならない

CREATE TABLE `test2` (
  category VARCHAR(5),
  id       INT AUTO_INCREMENT,
  text     VARCHAR(10),
  PRIMARY KEY (id, category)
) ENGINE=InnoDB;

INSERT INTO test2 VALUES('A', NULL, 'TEST');
INSERT INTO test2 VALUES('A', NULL, 'TEST');
INSERT INTO test2 VALUES('B', NULL, 'TEST');
INSERT INTO test2 VALUES('C', NULL, 'TEST');
INSERT INTO test2 VALUES('B', NULL, 'TEST');
INSERT INTO test2 VALUES('B', NULL, 'TEST');

SELECT * FROM test2;
+----------+----+------+
| category | id | text |
+----------+----+------+
| A        |  1 | TEST |
| A        |  2 | TEST |
| B        |  3 | TEST |
| C        |  4 | TEST |
| B        |  5 | TEST |
| B        |  6 | TEST |
+----------+----+------+


PRIMARY KEYの指定順が違っているので、やはりidが項目ごとの連番にならない

CREATE TABLE `test3` (
  category VARCHAR(5),
  id       INT AUTO_INCREMENT,
  text     VARCHAR(10),
  PRIMARY KEY (id, category)
) ENGINE=MyISAM;

INSERT INTO test3 VALUES('A', NULL, 'TEST');
INSERT INTO test3 VALUES('A', NULL, 'TEST');
INSERT INTO test3 VALUES('B', NULL, 'TEST');
INSERT INTO test3 VALUES('C', NULL, 'TEST');
INSERT INTO test3 VALUES('B', NULL, 'TEST');
INSERT INTO test3 VALUES('B', NULL, 'TEST');

SELECT * FROM test3;
+----------+----+------+
| category | id | text |
+----------+----+------+
| A        |  1 | TEST |
| A        |  2 | TEST |
| B        |  3 | TEST |
| C        |  4 | TEST |
| B        |  5 | TEST |
| B        |  6 | TEST |
+----------+----+------+


以下ならOK

CREATE TABLE `test4` (
  category VARCHAR(5),
  id       INT AUTO_INCREMENT,
  text     VARCHAR(10),
  PRIMARY KEY (category, id)
) ENGINE=MyISAM;

INSERT INTO test4 VALUES('A', NULL, 'TEST');
INSERT INTO test4 VALUES('A', NULL, 'TEST');
INSERT INTO test4 VALUES('B', NULL, 'TEST');
INSERT INTO test4 VALUES('C', NULL, 'TEST');
INSERT INTO test4 VALUES('B', NULL, 'TEST');
INSERT INTO test4 VALUES('B', NULL, 'TEST');

SELECT * FROM test4;
+----------+----+------+
| category | id | text |
+----------+----+------+
| A        |  1 | TEST |
| A        |  2 | TEST |
| B        |  1 | TEST |
| C        |  1 | TEST |
| B        |  2 | TEST |
| B        |  3 | TEST |
+----------+----+------+
■表示での対応

このファイル内の「データ取得 > グループ」を参照
Memo

Advertisement
