MySQL、SQLiteでのautoincrementの取り扱いについて(SQLAlchemy)

flask+SQLAlchemyでwebアプリを作成しています。買い物リスト(ShoppingListA)を作成し、買い物が終わった際には、テーブルのデータを削除し、次の買い物リストを作成する際には、idに再度１からの連番になる様に考えています。
SQLiteで以下の様にテーブルを作成すると、毎回idはテーブル情報を削除するたびに、１からの連番が付与されるのですが、全く同じコードでMySQLを動かそうとすると、idは１からではなく、前回の買い物リストの続きの番号が付与されてしまいます。
MySQLでも同様にテーブル内の情報を削除した際にidに再度１からの連番が付与される様にするにはどの様にすれば良いでしょうか。（データベースが異なると扱い方法が違うのでしょうか？）

python
1class ShoppingListA(db.Model):
2    id = db.Column(db.Integer, primary_key=True)
3    name = db.Column(db.String(20), nullable = False)
4    amount = db.Column(db.String(20), nullable = True)

python
1db.session.query(ShoppingListA).delete()
2db.session.commit()

quickquip

2021/10/15 06:18

> 毎回idはテーブル情報を削除するたびにここについて、具体的にどのような操作をしているかを書いた方がいいように思います。

行動規範の内容に同意します

回答2件

回答

flask+SQLAlchemyでwebアプリを作成しています。買い物リスト(ShoppingListA)を作成し、買い物が終わった際には、テーブルのデータを削除し、次の買い物リストを作成する際には、idに再度１からの連番になる様に考えています。

殆どの場合、テーブルのデータを毎回削除する様なテーブルの使い方をするのは誤ったデータベース設計です。
（例えば複数のユーザーが同時に使用する場合に困ると思うのですが、
ユーザーが増えるとその分テーブルが増えたりするのでしょうか？）

一度、入門書等で一般的なショッピングカート(質問中の買い物リストとは機能が違うかもしれませんが、買い物が終了したらデータを削除すると言うところは同じです)のデータベース設計を調べてみて、データベース設計の方を変更されることを強くお勧めします。

MySQLでも同様にテーブル内の情報を削除した際にidに再度１からの連番が付与される様にするにはどの様にすれば良いでしょうか。（データベースが異なると扱い方法が違うのでしょうか？）

前述の通り全くお勧めしませんが、
どうしてもAutoIncrementなIDをリセットしたいのであれば
MySQL AutoIncrement リセット
あたりで素のMySQLで必要な操作を調べた後
SQLAlchemy {↑で調べたSQL}で検索してみると良いかと思います。

AutoIncrement周りの処理はRDBMSによって異なるので、
全RDBMSで同じコードで実現したい場合は該当のテーブルを一旦削除して作り直すような操作になると思います。（が、これも特別な理由がない限りはアプリケーション内で実施するべき操作ではありません）

投稿2021/10/15 06:41

tanat

総合スコア18713

sandalwalk

2021/10/15 06:54

御助言ありがとうございます。今は１ユーザーだけの想定で最低限のアプリを作りながらflask+DBを学習している段階です。今回の様に問題に直面しながらで学習している状況ですが、公開する様なアプリにする場合には、利用するDBを決めて基礎を再度学習してみます。

tanat

2021/10/15 07:10

「強くお勧めする」の詳細としては - AutoIncrementのIDをアプリケーション中でリセットする様な操作は、所謂バッドノウハウなので学習としても、今後のノウハウとしても役に立つことは無い＆こういった設計の歪みは他のところにも大きな悪影響を与え得るので設計を直してしまった方が、「最低限のアプリ」の範疇であっても効率的になるという感じであることを補足しておきますね。（それくらい明確に誤った設計です。）とは言え、学習中にリファクタリングをするのが大変と言うのも事実なので「公開する様なアプリ」と言わず、今作っているところが一段落ついたらリファクタリングにチャレンジしてみるくらいをお勧めします。

sandalwalk

2021/10/15 07:19

そうします。。。DBについて全く知識が無いので、どの様な方向に変更するのか想像も付かないですが、相当ダメなのですね。例えば、今週の晩御飯のメニューなど、１週間しか利用しないテーブルを作成する場合、その週が終わったらテーブル内容を一旦消去する事しか思い付かないのですが、これも間違えた考え方でしょうか？消去しないと、テーブルデータが増える一方になってしまう気がするのですが。。

sandalwalk

2021/10/15 07:23

また、同じコードで書いたのに、SQLiteでは何も指示を与えなくても勝手にid=1から番号を振り直してくれて、MySQLでは今回の様にリセットの指示を出さないと連番で振り続けるのはデータベースによって振る舞いが違うという理解で正しいでしょうか。

tanat

2021/10/15 07:35

> そうします。。。DBについて全く知識が無いので、どの様な方向に変更するのか想像も付かないですが、相当ダメなのですね。アプリケーションの方から学習した初学者にありがちな設計ミスではあると思います。 > 例えば、今週の晩御飯のメニューなど、１週間しか利用しないテーブルを作成する場合、その週が終わったらテーブル内容を一旦消去する事しか思い付かないのですが、これも間違えた考え方でしょうか？この場合だと、使用するユーザーと期間(例えば開始日と終了日)のデータをフィールドに持って、WHERE句で期間指定で必要なデータを取り出すというのが典型的な設計ですね。 > 消去しないと、テーブルデータが増える一方になってしまう気がするのですが。。個別のデータ削除自体は問題ありません。前述のような設計であれば、例えば一カ月に一回バッチ処理で先月分のレコードを削除するような運用はあり得ると思います。駄目なのは「テーブルの中身を全部削除して、AutoIncrementのIDをアプリケーション中の操作としてリセットすることを前提とした設計」です。また、適正にテーブル設計がされていれば、極端にレコード量が多い（DBサーバのスペック次第ですが、数千万～数億件とかそういったレコード数）場合や、ディスクが枯渇しているな状況でない限りは慌ててデータを削除する必要もありません。

tanat

2021/10/15 07:40

> また、同じコードで書いたのに、SQLiteでは何も指示を与えなくても勝手にid=1から番号を振り直してくれて、MySQLでは今回の様にリセットの指示を出さないと連番で振り続けるのはデータベースによって振る舞いが違うという理解で正しいでしょうか。はい、これはご認識の通りです。 AutoIncrementIDのリセットはテーブル構造の変更に相当するので、 RDBMSによって実装が異なる＆通常はアプリケーションから触るべき箇所では無いので、sqlalchemyでは吸収していないんだと思いますよ。 (おそらく、sqlalchemy-migrateの守備範囲なんだと思います)

sandalwalk

2021/10/15 07:48

>アプリケーションの方から学習した初学者まさに私です。。アプリを作成するにつれて、細々とした、１つのテーブルとして準備する必要があるのか疑問に思うテーブルを次々に作ってアプリを動かしている中で、薄々ですが何かが違っている気がしていました。DBの基礎とアプリでの利用方法が学習できる入門書で勉強してみます。

tanat

2021/10/15 08:03

アプリケーションについてはそれなりに学習した状態であれば、一度アプリケーションから離れて、 DB設計とSQLだけを扱うような入門書籍（超入門とあるようなページが少ないもので十分だと思います）を一通りクリアしてみるだけでも色々と噛み合ってくると思います。 RDBMSの用語なども学習できるので、アプリケーションから使用する時も検索しやすくなりますし、基本が分かっていれば具体的な設計（例えば今回の場合だとショッピングカート）についての設計をwebで検索してみてその内容を把握することも出来るようになると思います。

行動規範の内容に同意します

ベストアンサー

SQLiteは特殊だったような記憶があります。(詳細は覚えてません・・・)
確かdelete tableとかtruncate tableするとidがリセットされますよね。
MySQLとMariaDBではされなかった記憶が(PosgreSQLとかSQLServerは気にしたことがないので記憶にないです)

MySQLでも同様にテーブル内の情報を削除した際にidに再度１からの連番が付与される様にするにはどの様にすれば良いでしょうか。（データベースが異なると扱い方法が違うのでしょうか？）

idをリセットしたいなら以下のような事をすると良いと思います。
mysqlで連番を1からにリセットする方法

記事が古いですが、sqlalchemyでは以下の様にALTERできるようです。
SQLAlchemy で Alter するには？

flask-sqlalchemyでのalterの実行方法が見当たりませんが、最悪でも以下で実行できると思います。

python
1result = db.session.execute('ここにSQL文');
2
3# この[db]はたぶん、[db = SQLAlchemy()]とかしてるの前提だと思う。

投稿2021/10/15 06:36

FiroProchainezo

総合スコア2401

tanat

2021/10/15 06:45

> MySQLとMariaDBではされなかった記憶が MySQLでもIDはリセットされますね。 https://dev.mysql.com/doc/refman/5.6/ja/truncate-table.html https://dev.mysql.com/doc/refman/8.0/ja/truncate-table.html AUTO_INCREMENT 値はすべて、その開始値にリセットされます。これは、通常はシーケンス値を再利用しない MyISAM や InnoDB にも当てはまります。

FiroProchainezo

2021/10/15 07:00

お、そうなんですね。わざわざありがとうございます。適当な記憶で適当に発言したので、非常に不適切な発言でした。申し訳ございませんm(_ _)m deleteだけかな・・・？(そもそもdelete tableってなんだって話でしたねm(_ _)m)

sandalwalk

2021/10/15 07:03

SQLAlchemyで db.session.execute('ALTER TABLE `テーブル名` auto_increment = 1;') としたところ、問題無く動作しました。ありがとうございました。

行動規範の内容に同意します

あなたの回答