MariaDB講座 / ALTER TABLEで既存の表構造を段階的に変更する
ALTER TABLEは既存表の列、型や制約を変更します。既存行が新しい規則を満たすか確認し、列追加・データ補完・制約適用を分けると、失敗した段階や失われる情報を把握しやすくなります。
元の定義と復旧方法を保存する
型の縮小、NOT NULL、UNIQUEは既存値で失敗したり情報を変えたりします。SHOW CREATE TABLEとデータのバックアップ、依存する問い合わせを保存し、別環境で試します。NULL件数、重複グループ、最大文字数、削除列の利用先を先に調べます。
入力制約で規則を定め、損失のある変更はバックアップと復元確認を準備します。「新列を消す」だけで元の情報状態へ戻るとは限りません。
既存商品を用意してSKUを追加する
course_userでshopcourseと表名を確認し、同名表があれば先に内容を調べます。以下は専用表の完全な例です。
USE shopcourse; CREATE TABLE products_38_lab ( product_id INT NOT NULL AUTO_INCREMENT, product_name VARCHAR(100) NOT NULL, category VARCHAR(30) NOT NULL, price DECIMAL(10,2) NOT NULL, stock INT NOT NULL DEFAULT 0, note VARCHAR(100) NULL DEFAULT NULL, PRIMARY KEY (product_id), CONSTRAINT ck_p38_price CHECK (price >= 0), CONSTRAINT ck_p38_stock CHECK (stock >= 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO products_38_lab (product_id,product_name,category,price,stock,note) VALUES (1, '노트', '문구', 3500.00, 20, NULL), (2, '볼펜', '문구', 1500.00, 0, ''), (3, '머그컵', '생활', 8000.00, 12, '선물 포장'), (4, '스탠드', '생활', 25000.00, 5, NULL), (5, '파일', '문구', 1500.00, 50, ''), (6, '케이블', '전자', 8000.00, 0, '호환 확인');
原文の韓国語商品名を維持し、ID1～6で照合します。UTF-8で実行します。skuはこの六行へ後から追加する商品コードで、実際のコード長や安定性・一意性範囲は別に定義します。
NULL許可で追加して既存行を補う
定義を読み、列追加と補完を分けます。
SHOW CREATE TABLE products_38_lab;
ALTER TABLE products_38_lab ADD COLUMN sku VARCHAR(20) NULL;
UPDATE products_38_lab SET sku=CONCAT('P',LPAD(product_id,3,'0'))
WHERE sku IS NULL;
SELECT COUNT(*) AS rows_total,COUNT(sku) AS known_sku,
COUNT(DISTINCT sku) AS unique_sku FROM products_38_lab;
追加直後は六行のskuがNULL、補完後はP001～P006でrows_total、known_sku、unique_skuがすべて6です。欠損や重複が残れば最終制約へ進みません。DDLと後続UPDATEが一つの取消可能な処理だとは考えず、どの段階が完了したか記録します。
制約を適用して重複を拒否する
既存値を確認してから実行します。
ALTER TABLE products_38_lab MODIFY COLUMN sku VARCHAR(20) NOT NULL, ADD CONSTRAINT uq_p38_sku UNIQUE(sku), ADD CONSTRAINT ck_p38_name CHECK(CHAR_LENGTH(product_name)>0); SHOW CREATE TABLE products_38_lab; SELECT product_id,product_name,sku FROM products_38_lab ORDER BY product_id;
skuが必須・一意となり、商品名は文字数が正である規則になります。MODIFYでは型、長さ、NULL、既定値など保持したい属性を照合して再指定します。省略属性が何でも維持されるとは限りません。
次の失敗例を別に実行します。
UPDATE products_38_lab SET sku='P001' WHERE product_id=2;
商品1のP001を商品2へ入れようとしてUNIQUE違反になります。商品2がP002のままか読み直し、空名の検査も有効性を確認します。検査を切って通す方法にしません。
ロックと運用時の負担を評価する
版、エンジン、変更内容、表の大きさでコピーやロックが必要になります。ALGORITHMやLOCKを指定しても全変更が無停止になるわけではありません。対応する組、失敗時の状態とアプリケーションへの待ちを調べます。
長いトランザクションのメタデータロックがALTERを待たせることもあります。小表の所要時間を本番へ当てはめず、データと索引量、ディスク、復旧時間、保守時間や互換的な段階移行を考えます。元の六行の名前・価格・在庫も確認します。
やり直すときは、実際のDB・定義・行を確認し、必要なデータを保存して、この記事の削除可能な表だけを子から親の順に消します。DROP TABLEは定義とデータを削除し、通常のROLLBACKでは戻せません。
DROP TABLE shopcourse.products_38_lab;
