MariaDB講座 / VIEWで再利用する検索定義を保存する

MariaDB講座

VIEWはSELECTの定義を名前付きのデータベースオブジェクトとして保存します。通常のビューは結果行のコピーを別保存するものではないため、権限、依存列、更新可能性を確認して使います。

一文だけのCTEと保存定義の違い

一文だけの名前はCTE、後の別SQLでも使う定義はVIEW、実体行を保存するのは表です。複数レポートで使うpaid注文合計をビューへまとめる場合は、公開する列を明示して利用側の契約を定めます。

アカウントの権限も関係します。ビューを作っただけで結果がキャッシュされたり高速になったりするとは限らず、実行計画は別に確認します。

元の表と未使用のビュー名を確認する

course_userでshopcourseへ接続し、SELECT VERSION(), DATABASE();とSHOW TABLES;で対象を確認します。以下はこの記事専用の四表を用意する完全な例です。UTF-8ファイルでの実行もできます。同名表がある場合は定義と行を保存して用途を調べ、確認なしに置き換えません。

USE shopcourse;
CREATE TABLE c34_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c34_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c34_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c34_order_customer FOREIGN KEY (customer_id)
  REFERENCES c34_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c34_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c34_item_order FOREIGN KEY (order_id)
  REFERENCES c34_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c34_item_product FOREIGN KEY (product_id)
  REFERENCES c34_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c34_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c34_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c34_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c34_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

文字列は原文の韓国語を保っています。商品1～4はノート、ペン、マグカップ、ライト、顧客1・3はソウル、2は釜山、4は大田です。UTF-8で保存・接続し、IDと数値で結果を照合します。paidは101・102・103、104はcanceledです。

SHOW FULL TABLESでc34_paid_order_totalsが未使用か確認します。既存定義をCREATE OR REPLACEで無確認に上書きせず、変更前にはSHOW CREATE VIEWを保存します。

INVOKERで作成して読み取る

呼び出し側の権限で確認するビューです。

CREATE SQL SECURITY INVOKER VIEW c34_paid_order_totals AS
SELECT o.order_id,o.customer_id,o.ordered_at,
 SUM(i.quantity*i.unit_price) AS goods_total
FROM c34_orders o JOIN c34_items i ON i.order_id=o.order_id
WHERE o.status='paid' GROUP BY o.order_id,o.customer_id,o.ordered_at;

問い合わせと定義確認を実行します。

SELECT order_id,goods_total FROM c34_paid_order_totals ORDER BY order_id;
SHOW CREATE VIEW c34_paid_order_totals;

注文101・102・103の合計は11500・8000・19500です。利用時のORDER BYで表示を決めます。INVOKERでは呼び出すユーザーが元の表を読む権限も必要です。

元の行の変更を同じ接続で観察する

変更と取消を一つの接続で実行します。

START TRANSACTION;
UPDATE c34_items SET quantity=3 WHERE order_id=101 AND product_id=1;
SELECT goods_total FROM c34_paid_order_totals WHERE order_id=101;
ROLLBACK;
SELECT goods_total FROM c34_paid_order_totals WHERE order_id=101;

注文101の商品1の数量を2から3へ変えると、ビュー合計は15000、ROLLBACK後は11500へ戻ります。別の履歴コピーを書き換えたのでなく、その時点で見える元データから計算しています。別接続に見えるかは分離レベルと確定状態にも依存します。

権限・更新可能性・依存関係を調べる

このGROUP BYとSUMのビューは、任意の元行を更新する窓口にはしません。単純なビューの一部は更新可能でも、全ビューが可能とは限りません。DEFINERなら定義者アカウントと権限へ依存し、復元先にそのアカウントがないと問題になる場合もあります。

列の削除・改名は依存する問い合わせを壊し得ます。SELECT *を避け、利用側を確認してから定義を変えます。元表より先にビューを削除します。

DROP VIEW shopcourse.c34_paid_order_totals;

DROP VIEW自体は元表の行を削除しません。

再実習では実際のDB・表定義・保存行を調べ、必要なデータをバックアップしてから、この記事の削除可能な表だけを子から親の順に消します。DROP TABLEは定義と行を消し、通常のROLLBACKでは戻せません。

DROP TABLE shopcourse.c34_items;
DROP TABLE shopcourse.c34_orders;
DROP TABLE shopcourse.c34_products;
DROP TABLE shopcourse.c34_customers;

一文だけのCTEと保存定義の違い

元の表と未使用のビュー名を確認する

INVOKERで作成して読み取る

元の行の変更を同じ接続で観察する

権限・更新可能性・依存関係を調べる

関連する公式リファレンス