今回は、MY SQLで運用しているデータベースのテーブルに、新しいカラムを追加して自動採番を設定する必要が出たので、その処理の流れをまとめます。
PurchaseListテーブルの変更
最初に、テーブルの現在の構造を確認します。
show columns from robin.PurchaseList;
現在のテーブル構造は以下の通りです。
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ASIN | varchar(10) | NO | PRI | NULL | |
SKU | varchar(255) | NO | PRI | NULL | |
PurchaseDatetime | datetime | YES | NULL | ||
Brand | varchar(255) | YES | NULL | ||
Title | varchar(255) | YES | NULL | ||
Model | varchar(255) | YES | NULL | ||
LocalCurrency | varchar(10) | YES | NULL | ||
Quantity | double | YES | NULL | ||
LocalPrice | double | YES | NULL | ||
Price | double | YES | NULL | ||
shipped_num | double | YES | NULL | ||
seller_id | varchar(14) | NO | NULL | ||
CreateDate | datetime | NO | CURRENT_TIMESTAMP | ||
UpdateDate | datetime | NO | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
次に、新しいカラム `stock_id` を先頭に追加します。
ALTER TABLE PurchaseList ADD stock_id INT FIRST;
新しいカラム'stock_id'の値はすべてNULLなので、連番を埋め込んでいきます。
以下のクエリですべての項目に連番が振られます。
set @n:=0;
update PurchaseList set stock_id=@n:=@n+1;
'stock_id'をプライマリーキーに再設定します。
ALTER TABLE PurchaseList DROP PRIMARY KEY, ADD PRIMARY KEY (stock_id);
その後、新しいカラム `shipped_num` を `Price` の後に追加します。
ALTER TABLE PurchaseList ADD shipped_num double AFTER Price;
次に、`stock_id` に自動増分の設定を行います。
ALTER TABLE PurchaseList MODIFY stock_id INT NOT NULL AUTO_INCREMENT;
これで、PurchaseListテーブルの変更が完了しました。
Field | Type | Null | Key | Default | Extra |
stock_id | int(11) | NO | PRI | NULL | auto_increment |
ASIN | varchar(10) | NO | NULL | ||
SKU | varchar(255) | NO | NULL | ||
PurchaseDatetime | datetime | YES | NULL | ||
Brand | varchar(255) | YES | NULL | ||
Title | varchar(255) | YES | NULL | ||
Model | varchar(255) | YES | NULL | ||
LocalCurrency | varchar(10) | YES | NULL | ||
Quantity | double | YES | NULL | ||
LocalPrice | double | YES | NULL | ||
Price | double | YES | NULL | ||
shipped_num | double | YES | NULL | ||
shipped_num | double | YES | NULL | ||
seller_id | varchar(14) | NO | NULL | ||
CreateDate | datetime | NO | CURRENT_TIMESTAMP | ||
UpdateDate | datetime | NO | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
PurchaseListテーブルに自動採番が行われるか確認
最後に、実際のデータの操作が行われました。最新の5つのレコードを表示するクエリが以下です。
SELECT stock_id, SKU, ASIN FROM PurchaseList ORDER BY stock_id DESC LIMIT 5;
stock_id | SKU | ASIN |
3683 | 2023-0472 | B08833PNRN |
3682 | 2023-0471 | B07WSKKMFV |
3681 | 2023-0470 | Z1-6CEL-2X |
3680 | 2023-0469 | B0CJJ4SFVY |
3679 | 2023-0494 | B0CJJ2WQ1F |
連番が設定されていることが確認できました。
最後に、新しいデータに対して自動連番が動作するか確認します。
INSERT INTO PurchaseList (ASIN, SKU) VALUES ('test', 'test');
SELECT stock_id, SKU, ASIN FROM PurchaseList ORDER BY stock_id DESC LIMIT 5;
stock_id | SKU | ASIN |
3684 | test | test |
3683 | 2023-0472 | B08833PNRN |
3682 | 2023-0471 | B07WSKKMFV |
3681 | 2023-0470 | Z1-6CEL-2X |
3680 | 2023-0469 | B0CJJ4SFVY |
自動採番されていることが確認できました。
後処理として、確認用のデータを削除して終わり。
DELETE FROM PurchaseList WHERE ASIN='test' AND SKU='test';
以上が、MY SQLで自動採番用のカラムを追加する流れです。今後のシステムの正常な運用の参考にしてください。
Comments