ど素人から毛を生やす。<延>

[MySQL]親ごとに枝番をAUTO_INCREMENT

Web > Other 2019年7月19日(最終更新:5年前)

2019年7月19日に作成されたページです。
情報が古かったり、僕が今以上のど素人だった頃の記事だったりする可能性があります。

どもです。
ちょっとした小話。

MySQLにて。


+-----------+---------+
| 親 |  枝番 | detail  |
+-----------+---------+
|  1 |    1 | ***     |
|  1 |    2 | ***     |
|  2 |    1 | ***     |
|  2 |    2 | ***     |
|  2 |    3 | ***     |
|  3 |    1 | ***     |
+-----------+---------+

この枝番を、AUTO_INCREMENTで振りたいときがあります。

CREATE TABLE `子テーブル` (
`親` INT(3),
`枝番` INT(3) AUTO_INCREMENT,
PRIMARY KEY (`親`, `枝番`)
);

こうすると、

Incorrect table definition; there can be only one auto column and it must be defined as a key

というエラーになり、テーブルが作れません。

auto_incrementを複数列索引の対象とする場合、auto_incrementを設定した列が先頭に来る必要があります。
開発の風景 〜KKZのSE日記〜[auto_incrementを設定する場合の制約]

ということらしいので、

CREATE TABLE `子テーブル` (
`親` INT(3),
`枝番` INT(3) AUTO_INCREMENT,
PRIMARY KEY (`枝番`, `ID`)
);

と、してみました。
するとエラーは発生せずテーブルを作ることができました。

しかし、


+-----------+---------+
| 親 |  枝番 | detail  |
+-----------+---------+
|  1 |    1 | ***     |
|  1 |    2 | ***     |
|  2 |    3 | ***     |
|  2 |    4 | ***     |
|  2 |    5 | ***     |
|  3 |    6 | ***     |
+-----------+---------+

これでは要件を満たしません。

MyISAM テーブルには、マルチカラムインデックス内のセカンダリカラムに AUTO_INCREMENT を指定することができます。この場合、AUTO_INCREMENT カラムに生成される値は、MAX(auto_increment_column) + 1 WHERE prefix=given-prefix として計算されます。これは、データを順序付きのグループに分割する場合に便利です。
MySQL 5.6 リファレンスマニュアル[3.6.9 AUTO_INCREMENT の使用]

どうやら、テーブルの作り方が間違っていたようです。

CREATE TABLE `子テーブル` (
`親` INT(3),
`枝番` INT(3) AUTO_INCREMENT,
PRIMARY KEY (`親`, `枝番`)
)
ENGINE=MyISAM;

エンジンをMyISAMに設定すると、


+-----------+---------+
| 親 |  枝番 | detail  |
+-----------+---------+
|  1 |    1 | ***     |
|  1 |    2 | ***     |
|  2 |    1 | ***     |
|  2 |    2 | ***     |
|  2 |    3 | ***     |
|  3 |    1 | ***     |
+-----------+---------+

無事に親ごとの枝番をAUTO_INCREMENTできました。


本題はここまでなのですが、この「エンジンをMyISAMにする」とはどういうことでしょうか。

そもそも、MyISAMは昔のMySQLではデフォルトで、MySQL5.5以降にInnoDBに切り替わっています。
切り替わっているということは、InnoDBの方が高性能ということです。

InnoDBの大きな利点は、トランザクションが備わっていること、データが壊れにくいこととされています。
あとは更新中に閲覧ができる(MyISAMではできない)ことも大きい。

MyISAMはいわば旧型なので、その分シンプルで速い、バックアップが楽などの利点があります。

ともあれ、頻繁に更新される想定のテーブルでは、MyISAMで解決しない方が良い。のでしょう。
面倒ですが、PHPを噛ませて連番を作成するなど、MyISAMに頼らないで本件を解決することは可能です。
用途や更新頻度・アクセス頻度などを踏まえて、採用するエンジンを決めたいところです。

参考サイト:
LexTech[運用視点なMyISAMとInnoDBと。]
有限工房[WordPressにはMyISAMとInnoDBどちらが良いのか?]

この記事は役に立ちましたか?
  • _(:3」∠)_ 面白かった (0)
  • (・∀・) 参考になった (0)
  • (`・ω・´) 役に立った (0)