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

[MySQL]親ごとに枝番をAUTO_INCREMENT

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

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

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)