ビューとストアドプロシージャ — 列を消したら壊れた

ビューとストアドプロシージャ。データベースを眺める窓の枠と再利用する処理カード、歯車。

列を1つ変えただけで壊れることがあります。ビューとストアドプロシージャの依存関係を確かめます。

前回: CTE(WITH)と再帰クエリ — サブクエリ3段は読めない これは深掘りです。本線の順路ではありません。 本線のプロセスを止めても消えない場所 — MySQLとSQLの基本で SELECT を書けるようになったところの、その先だけを扱います。読まなくても本線は先に進めます。 作業場所はVMの中(Ubuntu)です。MySQL が動いていれば、他に用意するものはありません。練習用のテーブルはこの記事の中で作ります。

作っているアプリが3つに増えたときに、これが起きました。

  • 管理画面が「有料プランで、キャンセル以外の合計が1万円以上の顧客」を出す
  • 夜間の集計バッチが、同じ条件で件数を数える
  • API が、同じ条件で顧客一覧を返す

3か所に、同じ SELECT が書いてありました。

SELECT c.id, c.name, c.plan, SUM(o.amount) AS total
FROM d_vw_customer c
JOIN d_vw_order o ON o.customer_id = c.id
WHERE c.active = 1 AND o.status <> 'canceled'
GROUP BY c.id, c.name, c.plan
HAVING SUM(o.amount) >= 10000;

動きます。3か所とも正しい答えを返します。

そして、条件が変わった日に壊れます。

「キャンセルだけでなく、返金済みも除いてください」と言われて、管理画面とバッチは直したのに、APIを直し忘れました。 エラーは出ません。 APIだけが、静かに違う数を返し続けます。

同じ意味のSQLが2か所以上にある時点で、いつか片方だけ直します。 これは注意力の問題ではありません。置き場所の問題です。

SQL には、これを1か所にまとめる道具が2つあります。

道具何をまとめるか
ビュー(VIEW)SELECT そのものに名前を付けて、テーブルのように使えるようにする
ストアドプロシージャ / ファンクション手続き(複数の文・条件分岐・繰り返し)に名前を付けて、データベースの中に置いておく

この記事は、その2つを両方とも手で作って、両方とも壊してから消します。

先に結論を言っておきます。 この記事の最後で、「ストアドはこのサイトでは使わない」と決めます。それでも作り方を全部やるのは、使わないと決めるためには、動くところを見ていないといけないからです。

対象レベル: 中級〜上級 / 区分: 発展

目次

この記事でわかること

  • ビューは「名前を付けた SELECT」であって、データを持っていないこと。SELECT するたびに、裏で元のテーブルを読み直していること
  • CTE(WITH)とビューの違いは、寿命だけだということ。その場限りか、データベースに保存されるか
  • 元のテーブルの列を消すと、ビューは黙って壊れること。壊れたことは SELECT した瞬間に ERROR 1356 で分かるが、DROP や ALTER の時点では何も言われないこと
  • 単純なビューは UPDATE できること。それは「ビューを更新している」のではなく、元のテーブルが書き換わっていること
  • 集計や DISTINCT を含むビューは更新できないこと(ERROR 1288)。「元の行がどれか特定できない」からであること
  • WITH CHECK OPTION が無いと、ビューの条件から外れる行を、そのビュー経由で作れてしまうこと
  • ビューは MERGE と TEMPTABLE の2通りで実行されること。重ねると WHERE が中まで届かなくなって遅くなること
  • ストアドは動く。動くが、Gitに残らないこと。だから使わないと決めたこと

この記事でやること

  1. MySQL のバージョンを確認する。 8.0 未満だと、この記事は途中から動きません
  2. 練習用のテーブルを2枚作る(顧客と注文)。appdb の中に d_vw_ で始まる名前で作り、既存のテーブルには一切触りません
  3. ビューを作ってテーブルのように使い、SHOW CREATE VIEW で中身を見る。 そのあと元の列をわざと消して ERROR 1356 を出し、戻す
  4. 単純なビューを UPDATE して元のテーブルが変わることを確かめ、集計ビューで ERROR 1288 を出す。 WITH CHECK OPTION を付けて、条件から外れる更新が弾かれるところを見る
  5. EXPLAIN で MERGE と TEMPTABLE の違いを見る。 ビューを重ねて遅くなるところまでやる
  6. DELIMITER を変えてストアドプロシージャと関数とトリガーを作り、CALL して動かす
  7. 作ったものを全部消して、元通りにする

先に知る言葉

この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。

言葉意味
ビュー(VIEW)名前を付けて保存した SELECT。 テーブルのように SELECT できるが、データは持っていない
実テーブル実際にデータが入っているほうのテーブル。ビューの下にあるもの。この記事では「元のテーブル」と書きます
更新可能ビューUPDATE / INSERT / DELETE ができるビュー。 元の行が1対1で特定できる形のときだけ
WITH CHECK OPTIONビューの WHERE 条件から外れる更新を、ビュー経由では通さないという指定
MERGEビューの中身を、呼び出し側のSQLに展開して1本にしてから実行するやり方
TEMPTABLEビューの中身を先に全部計算して一時的な表にしてから、外側の条件を掛けるやり方
ストアドプロシージャデータベースの中に保存した手続き。 CALL で呼ぶ。値を返さず、引数で受け渡す
ストアドファンクション値を1つ返すもの。SELECT f(x) のように、SQLの式の中で呼べる
トリガーINSERT / UPDATE / DELETE が起きたときに、自動で走る手続き。呼び出す側のコードに出てこない
DELIMITERmysql クライアントが「文の終わり」と見なす記号を変えるコマンド。SQL ではなくクライアントの機能

ビューは「保存された SELECT」です。 これを「集計結果を溜めておく箱」と誤解すると、全部おかしくなります。 箱ではありません。 SELECT するたびに、その場で元のテーブルを読み直しています。

① まずバージョンを確認する

VM の中で打ちます。

bash
$ mysql --version
mysql  Ver 8.0.43-0ubuntu0.24.04.1 for Linux on x86_64 ((Ubuntu))

クライアントの版とサーバーの版は別物です。本番はサーバー側なので、そちらを見ます。

bash
$ sudo mysql
SELECT VERSION();
+-------------------------+
| VERSION()               |
+-------------------------+
| 8.0.43-0ubuntu0.24.04.1 |
+-------------------------+

ビュー自体は MySQL 5.0 からあります。 ただし、この記事ではCTE(WITH)と比べる場面があるので、8.0 以降を前提にします。

使うデータベースを選びます。

USE appdb;

appdb が無いと言われたら、本線のMySQLとSQLの基本で作ったものです。 無い場合は CREATE DATABASE appdb CHARACTER SET utf8mb4; で作ってから USE appdb; してください。

② 練習用のテーブルを作る

この記事で作るものは、すべて d_vw_ で始めます。 後片づけのときに、消していいものを名前だけで見分けられるようにするためです。

同じ名前が残っていると失敗するので、先に落としておきます。

DROP TABLE IF EXISTS d_vw_order;
DROP TABLE IF EXISTS d_vw_customer;

顧客です。

CREATE TABLE d_vw_customer (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(50)  NOT NULL,
  email      VARCHAR(120) NOT NULL,
  plan       VARCHAR(20)  NOT NULL DEFAULT 'free',
  memo       VARCHAR(100) NULL,
  active     TINYINT(1)   NOT NULL DEFAULT 1,
  created_at DATE         NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

注文です。

CREATE TABLE d_vw_order (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  amount      INT          NOT NULL,
  status      VARCHAR(20)  NOT NULL,
  ordered_at  DATE         NOT NULL,
  KEY idx_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

email と memo を入れてあるのは、あとで「隠す」ためです。 ビューの使いどころの1つが列を見せないことなので、隠す対象が要ります。

中身を入れます。

INSERT INTO d_vw_customer (name, email, plan, memo, active, created_at) VALUES
('山田',   'yamada@example.com',  'pro',  '請求先が別',   1, '2026-01-10'),
('佐藤',   'sato@example.com',    'free', NULL,           1, '2026-01-15'),
('鈴木',   'suzuki@example.com',  'pro',  '要フォロー',   1, '2026-02-01'),
('高橋',   'takahashi@example.com','free', NULL,          1, '2026-02-20'),
('田中',   'tanaka@example.com',  'pro',  NULL,           0, '2026-03-05'),
('伊藤',   'ito@example.com',     'team', '年契約',       1, '2026-03-11'),
('渡辺',   'watanabe@example.com','free', NULL,           1, '2026-04-02'),
('中村',   'nakamura@example.com','team', NULL,           1, '2026-04-18');
INSERT INTO d_vw_order (customer_id, amount, status, ordered_at) VALUES
(1,  4800, 'paid',     '2026-05-01'),
(1,  4800, 'paid',     '2026-06-01'),
(1,  4800, 'canceled', '2026-06-15'),
(1,  4800, 'paid',     '2026-07-01'),
(2,   980, 'paid',     '2026-05-03'),
(2,   980, 'paid',     '2026-06-03'),
(3, 12000, 'paid',     '2026-05-09'),
(3,  3000, 'refunded', '2026-06-09'),
(4,   980, 'canceled', '2026-05-20'),
(5,  9800, 'paid',     '2026-05-22'),
(5,  9800, 'paid',     '2026-06-22'),
(6, 24000, 'paid',     '2026-04-01'),
(6,  6000, 'paid',     '2026-07-05'),
(7,   980, 'paid',     '2026-06-11'),
(8, 15000, 'paid',     '2026-05-30'),
(8,  5000, 'canceled', '2026-06-30');

入ったか確かめます。

SELECT COUNT(*) AS customers FROM d_vw_customer;
SELECT COUNT(*) AS orders FROM d_vw_order;
+-----------+
| customers |
+-----------+
|         8 |
+-----------+
+--------+
| orders |
+--------+
|     16 |
+--------+

8 と 16 が出ていれば、以降のSQLはこの記事のとおりに動きます。

③ ビューは「名前を付けた SELECT」

冒頭の、3か所に散っていたSQLです。まず、そのまま打ってみます。

SELECT c.id, c.name, c.plan, SUM(o.amount) AS total
FROM d_vw_customer c
JOIN d_vw_order o ON o.customer_id = c.id
WHERE c.active = 1 AND o.status <> 'canceled'
GROUP BY c.id, c.name, c.plan
HAVING SUM(o.amount) >= 10000;
+----+--------+------+-------+
| id | name   | plan | total |
+----+--------+------+-------+
|  1 | 山田   | pro  | 14400 |
|  3 | 鈴木   | pro  | 15000 |
|  6 | 伊藤   | team | 30000 |
|  8 | 中村   | team | 15000 |
+----+--------+------+-------+

4件です。 ここに、名前を付けます。

CREATE VIEW d_vw_v_big_customer AS
SELECT c.id, c.name, c.plan, SUM(o.amount) AS total
FROM d_vw_customer c
JOIN d_vw_order o ON o.customer_id = c.id
WHERE c.active = 1 AND o.status <> 'canceled'
GROUP BY c.id, c.name, c.plan
HAVING SUM(o.amount) >= 10000;
Query OK, 0 rows affected (0.01 sec)

0 rows affected に注目してください。 1行もコピーしていません。 保存されたのは SELECT の文章だけです。データは1バイトも増えていません。

使います。

SELECT * FROM d_vw_v_big_customer;

さっきと同じ4件が返ります。 3か所のアプリは、これからはこの1行を書くだけになります。

絞り込みも、テーブルと同じようにできます。

SELECT * FROM d_vw_v_big_customer WHERE plan = 'team' ORDER BY total DESC;
+----+--------+------+-------+
| id | name   | plan | total |
+----+--------+------+-------+
|  6 | 伊藤   | team | 30000 |
|  8 | 中村   | team | 15000 |
+----+--------+------+-------+

ビューの定義には WHERE plan = 'team' は書いてありません。 それでも通ります。ビューは「表のように振る舞うもの」なので、外側から条件を足せます。

データを変えると、ビューも変わる

ビューが箱ではないことを、1回で確かめます。 新しい注文を1件足します。

INSERT INTO d_vw_order (customer_id, amount, status, ordered_at)
VALUES (2, 30000, 'paid', '2026-07-20');

ビューには何もしていません。 それでも、

SELECT * FROM d_vw_v_big_customer;
+----+--------+------+-------+
| id | name   | plan | total |
+----+--------+------+-------+
|  1 | 山田   | pro  | 14400 |
|  2 | 佐藤   | free | 31960 |
|  3 | 鈴木   | pro  | 15000 |
|  6 | 伊藤   | team | 30000 |
|  8 | 中村   | team | 15000 |
+----+--------+------+-------+

佐藤が増えています。 ビューは毎回、元のテーブルを読み直しています。

戻しておきます。

DELETE FROM d_vw_order WHERE amount = 30000 AND customer_id = 2;

中身を見る — SHOW CREATE VIEW

半年後に「このビューは何を見ているのか」を調べる方法です。

SHOW CREATE VIEW d_vw_v_big_customer\G

末尾の \G は、; の代わりに使う「縦に並べて表示」です。横に長い結果は \G でないと読めません。

*************************** 1. row ***************************
                View: d_vw_v_big_customer
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `d_vw_v_big_customer` AS select `c`.`id` AS `id`,`c`.`name` AS `name`,`c`.`plan` AS `plan`,sum(`o`.`amount`) AS `total` from (`d_vw_customer` `c` join `d_vw_order` `o` on((`o`.`customer_id` = `c`.`id`))) where ((`c`.`active` = 1) and (`o`.`status` <> 'canceled')) group by `c`.`id`,`c`.`name`,`c`.`plan` having (sum(`o`.`amount`) >= 10000)
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci

書いたはずのSQLと、見た目が違います。 3つ、覚えておくと得です。

出てきたもの意味
ALGORITHM=UNDEFINED実行のしかたをMySQLに任せたという意味。指定しないとこうなります(⑦で扱います)
DEFINER=root@localhostこのビューを作った人。sudo mysql で入ったので root になっています
SQL SECURITY DEFINERビューの中身を実行するときの権限を、作った人のものにするという指定。呼ぶ人ではありません

SQL SECURITY DEFINER が既定であることは、覚えておいて損がありません。 元のテーブルを直接読む権限が無い人でも、ビュー経由なら読めるようになります。それが便利で、そこが危ないところです。

改行が消えて1行になっているのは、MySQL が定義を「文字列」ではなく「解析した結果」で持っているからです。あなたが書いた整形は保存されません。 元の読みやすい形は、リポジトリの中のSQLファイルにしか残りません。

一覧はこうやって出します。

SHOW FULL TABLES WHERE Table_type = 'VIEW';
+-------------------------+------------+
| Tables_in_appdb         | Table_type |
+-------------------------+------------+
| d_vw_v_big_customer     | VIEW       |
+-------------------------+------------+

SHOW TABLES だけだと、ビューと実テーブルが混ざって出ます。 見た目では区別が付きません。 区別したいときは SHOW FULL TABLES です。

④ CTE(WITH)との違いは、寿命だけ

前々回のCTEと、やっていることがそっくりです。 どちらもSELECT に名前を付けて、上に出しています。

WITH big_customer AS (
  SELECT c.id, c.name, c.plan, SUM(o.amount) AS total
  FROM d_vw_customer c
  JOIN d_vw_order o ON o.customer_id = c.id
  WHERE c.active = 1 AND o.status <> 'canceled'
  GROUP BY c.id, c.name, c.plan
  HAVING SUM(o.amount) >= 10000
)
SELECT * FROM big_customer WHERE plan = 'team';

結果は、ビューを使ったときと1行も違いません。 では何が違うのか。

CTE(WITH)ビュー(VIEW)
寿命そのSQL1本のあいだだけ消すまでデータベースに残る
他のSQLから使えるか使えない(そのSQLの中だけ)使える(誰からでも、何回でも)
どこに書いてあるかアプリのコードの中。Git に残るデータベースの中。Git には残らない
作るのに要るもの何も要らないCREATE VIEW 権限
再帰できるかできる(WITH RECURSIVE)できない
元のテーブルが変わったら次に実行したとき、その場でエラー壊れたまま残る(⑤で見ます)

選び方は、これで足ります。

1本のSQLの中を読みやすくしたいだけなら CTE。 複数のアプリで同じ定義を共有したいならビュー。

冒頭の「3つのアプリが同じSQLを書いている」は、CTEでは解けません。 CTEは3か所に3回書くことになるからです。共有したいときだけ、ビューの出番です。

⑤ 元の列を消して、ビューを壊す

ここがこの記事で一番大事なところです。

まず、memo 列を使うビューを作ります。顧客の一覧に、メモを添えるだけのものです。

CREATE VIEW d_vw_v_memo AS
SELECT id, name, memo FROM d_vw_customer WHERE memo IS NOT NULL;
SELECT * FROM d_vw_v_memo;
+----+--------+-----------------+
| id | name   | memo            |
+----+--------+-----------------+
|  1 | 山田   | 請求先が別      |
|  3 | 鈴木   | 要フォロー      |
|  6 | 伊藤   | 年契約          |
+----+--------+-----------------+

動いています。 ここで、「memo はもう使っていないので消しましょう」という、よくある片づけをします。

ALTER TABLE d_vw_customer DROP COLUMN memo;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

警告が0件です。 ビューがその列を見ていることを、MySQL は教えてくれません。 通ります。何も起きません。

壊れたことが分かるのは、次に誰かが SELECT したときです。

SELECT * FROM d_vw_v_memo;
ERROR 1356 (HY000): View 'appdb.d_vw_v_memo' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

ERROR 1356。 これがビューの一番の落とし穴です。

このメッセージの読み方が、また難物です。

「テーブルか、列か、関数か、権限か、そのどれかがおかしい」としか言っていません。 どの列が無いのかは書いてありません。

だから、この記事を読んだ人が最初にやるべきは SHOW CREATE VIEW で定義を出して、列名を1つずつ現物と突き合わせることです。

SHOW CREATE VIEW d_vw_v_memo\G

定義には memo があります。 実物を見ます。

DESCRIBE d_vw_customer;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(50)  | NO   |     | NULL    |                |
| email      | varchar(120) | NO   |     | NULL    |                |
| plan       | varchar(20)  | NO   |     | free    |                |
| active     | tinyint(1)   | NO   |     | 1       |                |
| created_at | date         | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

memo がありません。 原因が確定しました。

壊れているビューを、まとめて洗う — CHECK TABLE

1本ずつ SELECT して回るのは、本数が増えると無理です。 CHECK TABLE はビューにも効きます。

CHECK TABLE d_vw_v_memo;
+------------------+-------+----------+---------------------------------------------------------------+
| Table            | Op    | Msg_type | Msg_text                                                      |
+------------------+-------+----------+---------------------------------------------------------------+
| appdb.d_vw_v_memo| check | Error    | View 'appdb.d_vw_v_memo' references invalid table(s) or col... |
| appdb.d_vw_v_memo| check | error    | Corrupt                                                       |
+------------------+-------+----------+---------------------------------------------------------------+

Corrupt(壊れている)と出ます。 無事なビューだと、こうなります。

CHECK TABLE d_vw_v_big_customer;
+--------------------------+-------+----------+----------+
| Table                    | Op    | Msg_type | Msg_text |
+--------------------------+-------+----------+----------+
| appdb.d_vw_v_big_customer| check | status   | OK       |
+--------------------------+-------+----------+----------+

カンマで並べれば、まとめて見られます。

CHECK TABLE d_vw_v_memo, d_vw_v_big_customer;

ALTER TABLE で列を消したり名前を変えたりしたら、その直後に CHECK TABLE を全ビューに掛ける。 これを手順に入れておかないと、壊れたビューは「誰かが困るまで」見つかりません。

直す — CREATE OR REPLACE VIEW と ALTER VIEW

直し方は2つあります。 まず、列を戻して直すほうです。

ALTER TABLE d_vw_customer ADD COLUMN memo VARCHAR(100) NULL AFTER plan;
SELECT * FROM d_vw_v_memo;
Empty set (0.00 sec)

エラーは消えましたが、0行です。 列は戻っても、中身は戻りません。 DROP COLUMN でデータごと消えています。

入れ直します。

UPDATE d_vw_customer SET memo = '請求先が別' WHERE id = 1;
UPDATE d_vw_customer SET memo = '要フォロー' WHERE id = 3;
UPDATE d_vw_customer SET memo = '年契約'     WHERE id = 6;

これが「列を消すのは取り消せない」ということです。詳しくは深掘りのALTER TABLEは取り消せないにあります。

もう1つの直し方は、ビューのほうを書き換えることです。列を戻さずに済ませたいときは、こちらになります。

CREATE OR REPLACE VIEW d_vw_v_memo AS
SELECT id, name, plan FROM d_vw_customer WHERE active = 1;

CREATE OR REPLACE VIEW は、同じ名前のビューがあれば作り直します。 DROP してから CREATE するのと違い、「一瞬ビューが存在しない時間」ができません。

ALTER VIEW も、ほぼ同じことができます。

ALTER VIEW d_vw_v_memo AS
SELECT id, name, memo FROM d_vw_customer WHERE memo IS NOT NULL;
CREATE OR REPLACE VIEWALTER VIEW
無いときは新しく作るERROR 1146 で落ちる
あるときは作り直す作り直す
どう使い分けるかスクリプトに書いて、何回流しても同じ結果にしたいとき既にあるはずのものだけを直したいとき

ファイルに書いて流すSQLでは CREATE OR REPLACE VIEW を使います。 ALTER VIEW だと、初回だけ落ちるからです。 「何回実行しても同じ状態になる」ことを冪等(べきとう)といいます。本線のAnsibleでも同じ考え方が出てきます。

消すときは、必ず IF EXISTS を付けます。

DROP VIEW IF EXISTS d_vw_v_nothing;
Query OK, 0 rows affected, 1 warning (0.00 sec)
SHOW WARNINGS;
+-------+------+-------------------------------------------+
| Level | Code | Message                                   |
+-------+------+-------------------------------------------+
| Note  | 4092 | Unknown view 'appdb.d_vw_v_nothing'       |
+-------+------+-------------------------------------------+

警告になるだけで、止まりません。 付けないと ERROR 1051 で落ちて、そこから後ろのSQLが全部流れなくなります。

DROP VIEW と DROP TABLE を間違えないでください。 DROP TABLE ビュー名 は ERROR 1051 で拒否されます(ビューには効きません)。逆はもっと怖いです。 DROP VIEW 実テーブル名 も拒否されますが、似た名前を打ち間違えたときに守ってくれる保証はありません。 名前を目で見てください。

⑥ ビューは更新できるのか

「ビューはデータを持っていない」なら、UPDATE はどうなるのか。 ここは、やってみないと腑に落ちません。

単純なビューは、更新できる

有効な顧客だけを見せるビューを作ります。

CREATE OR REPLACE VIEW d_vw_v_active AS
SELECT id, name, plan, active FROM d_vw_customer WHERE active = 1;
SELECT * FROM d_vw_v_active;
+----+--------+------+--------+
| id | name   | plan | active |
+----+--------+------+--------+
|  1 | 山田   | pro  |      1 |
|  2 | 佐藤   | free |      1 |
|  3 | 鈴木   | pro  |      1 |
|  4 | 高橋   | free |      1 |
|  6 | 伊藤   | team |      1 |
|  7 | 渡辺   | free |      1 |
|  8 | 中村   | team |      1 |
+----+--------+------+--------+

田中(id=5, active=0)が出ていません。 ここに UPDATE を打ちます。

UPDATE d_vw_v_active SET plan = 'team' WHERE id = 4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

通りました。 何が起きたのかを、元のテーブルで確かめます。

SELECT id, name, plan FROM d_vw_customer WHERE id = 4;
+----+--------+------+
| id | name   | plan |
+----+--------+------+
|  4 | 高橋   | team |
+----+--------+------+

ビューを更新したのではありません。 d_vw_customer が書き換わっています。 ビューへの UPDATE は、元のテーブルへの UPDATE に書き換えられて実行されただけです。

更新できるビューかどうかは、事前に調べられます。

SELECT table_name, is_updatable
FROM information_schema.views
WHERE table_schema = 'appdb' AND table_name LIKE 'd\_vw\_%';
+---------------------+--------------+
| TABLE_NAME          | IS_UPDATABLE |
+---------------------+--------------+
| d_vw_v_active       | YES          |
| d_vw_v_big_customer | NO           |
| d_vw_v_memo         | YES          |
+---------------------+--------------+

IS_UPDATABLE が NO のものは、UPDATE できません。

集計を含むビューは、更新できない

d_vw_v_big_customer は SUM() と GROUP BY を含んでいます。打ってみます。

UPDATE d_vw_v_big_customer SET plan = 'free' WHERE id = 1;
ERROR 1288 (HY000): The target table d_vw_v_big_customer of the UPDATE is not updatable

ERROR 1288。 理由は、考えれば当たり前でした。

1行の total = 14400 は、元の注文3行を足したものです。 これを 10000 に書き換えろと言われても、3行をどう振り分ければいいのか決められません。 「元の行が特定できない」から、更新できないのです。

DISTINCT でも同じことが起きます。

CREATE OR REPLACE VIEW d_vw_v_plans AS
SELECT DISTINCT plan FROM d_vw_customer;
UPDATE d_vw_v_plans SET plan = 'x' WHERE plan = 'free';
ERROR 1288 (HY000): The target table d_vw_v_plans of the UPDATE is not updatable

free の行は2人ぶんが1行にまとめられています。 どの人を書き換えるのか決まりません。

更新できなくなる主なものは、この形です。

ビューに入っていると更新できないもの
集計関数(SUM / COUNT / AVG / MAX / MIN)
GROUP BY / HAVING
DISTINCT
UNION / UNION ALL
選択リストの中のサブクエリ
FROM の中に、更新できないビューがある

共通しているのは1つです。「ビューの1行が、元のテーブルの1行に戻せるか」。 戻せるなら更新できる。戻せないならできない。 丸暗記より、これを覚えるほうが早いです。

WITH CHECK OPTION が無いと、消える行を作れる

ここが、更新可能ビューの本当の落とし穴です。

d_vw_v_active は WHERE active = 1 のビューです。ここに、active = 0 になる更新を打ちます。

UPDATE d_vw_v_active SET active = 0 WHERE id = 7;
Query OK, 1 row affected (0.00 sec)

通ります。 そして、

SELECT * FROM d_vw_v_active WHERE id = 7;
Empty set (0.00 sec)

さっき自分が更新した行が、ビューから消えました。 行は消えていません。 d_vw_customer にはあります。ビューの条件から外れただけです。

INSERT でも、もっとはっきり出ます。ビュー経由の INSERT では、ビューに無い元テーブルの列(email と created_at)に既定値が要るので、先に付けておきます。

ALTER TABLE d_vw_customer MODIFY email VARCHAR(120) NOT NULL DEFAULT '';
ALTER TABLE d_vw_customer MODIFY created_at DATE NOT NULL DEFAULT (CURRENT_DATE);
INSERT INTO d_vw_v_active (name, plan, active) VALUES ('新規A', 'free', 0);

通ってしまいます。 「有効な顧客のビュー」に、無効な顧客を追加できました。 追加した本人には、追加した行が見えません。

これを止めるのが WITH CHECK OPTION です。

DROP VIEW IF EXISTS d_vw_v_active;
CREATE VIEW d_vw_v_active AS
SELECT id, name, plan, active FROM d_vw_customer WHERE active = 1
WITH CHECK OPTION;

email と created_at の既定値は、さっき付けたものがそのまま効いています。 付いていないと、WITH CHECK OPTION を試す前に既定値のエラーで止まるので、念のため確かめておきます。

DESCRIBE d_vw_customer;

もう一度、同じことをやります。

UPDATE d_vw_v_active SET active = 0 WHERE id = 1;
ERROR 1369 (HY000): CHECK OPTION failed 'appdb.d_vw_v_active'
INSERT INTO d_vw_v_active (name, plan, active) VALUES ('新規B', 'free', 0);
ERROR 1369 (HY000): CHECK OPTION failed 'appdb.d_vw_v_active'

弾かれました。 ERROR 1369 は、「そのビューの条件から外れる行は、このビュー経由では作らせない」という意味です。

条件に合う更新は、通ります。

UPDATE d_vw_v_active SET plan = 'pro' WHERE id = 4;
Query OK, 1 row affected (0.00 sec)

更新できるビューを作るなら、WITH CHECK OPTION は原則として付けます。 付けないビューは、「自分では見えない行を作れる窓口」になります。

さっき混ぜてしまった行を、片づけておきます。

DELETE FROM d_vw_customer WHERE name = '新規A';
UPDATE d_vw_customer SET active = 1 WHERE id = 7;

CASCADED と LOCAL

ビューの上にビューを作ったとき、どこまでの条件を検査するかを選べます。

CREATE OR REPLACE VIEW d_vw_v_active_pro AS
SELECT id, name, plan, active FROM d_vw_v_active WHERE plan = 'pro'
WITH CASCADED CHECK OPTION;
指定検査する範囲
WITH CASCADED CHECK OPTION(既定)このビューの条件 + 下にあるビュー全部の条件
WITH LOCAL CHECK OPTIONこのビューの条件だけ

書かずに WITH CHECK OPTION とだけ書くと CASCADED です。厳しいほうが既定になっています。そのままで構いません。

⑦ 性能 — MERGE と TEMPTABLE

ビューは魔法ではありません。 実行のされ方は2通りしかなく、どちらになるかで速さが桁で変わります。

やり方何をするか
MERGEビューの定義を、呼び出し側のSQLに展開して1本にする。 外側の WHERE が中まで届く
TEMPTABLEビューの中身を先に全部計算して一時的な表を作り、そのあと外側の WHERE を掛ける

MERGE のほうが、ほぼ常に速いです。外側の条件が中に届くので、索引が使えるからです。

見て確かめます。まず MERGE になるほうです。

EXPLAIN SELECT * FROM d_vw_v_active WHERE id = 3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: d_vw_customer
         type: const
possible_keys: PRIMARY
          key: PRIMARY
          rows: 1
        Extra: NULL

table が d_vw_customer になっています。 ビューの名前は消えました。 WHERE id = 3 が主キーまで届いて、rows: 1 です。ビューを挟んだコストは、実質ゼロでした。

次に、わざと TEMPTABLE にしたビューを作ります。

CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW d_vw_v_active_tmp AS
SELECT id, name, plan, active FROM d_vw_customer WHERE active = 1;
EXPLAIN SELECT * FROM d_vw_v_active_tmp WHERE id = 3\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
         rows: 7
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: d_vw_customer
         type: ALL
         rows: 8
        Extra: Using where

2行になりました。 読み方はこうです。

下の行(DERIVED)で、まず有効な顧客を全部作ります(8行読んで7行)。 上の行で、その7行から id = 3 を探します。 type: ALL は全部見たという意味です。 WHERE id = 3 は、中まで届いていません。

8行なら誤差ですが、これが100万行だと致命的です。 「まず100万行の一時表を作ってから、1行を探す」ことになります。

ビューを重ねると、勝手に TEMPTABLE になる

ALGORITHM を書かなければ MERGE になる、とは限りません。 集計や DISTINCT を含むビューは、MERGE にできません。

そして、それを土台に別のビューを作ると、上のビューまで巻き込まれます。

CREATE OR REPLACE VIEW d_vw_v_big_team AS
SELECT * FROM d_vw_v_big_customer WHERE plan = 'team';
EXPLAIN SELECT * FROM d_vw_v_big_team WHERE id = 6\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ref
          key: <auto_key0>
         rows: 2
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: c
         type: ALL
         rows: 8
        Extra: Using temporary

<derived2> が出ています。 id = 6 の1行が欲しいだけなのに、まず全顧客の合計を計算しています。

GROUP BY を含むビューは、MERGE にできません。 集計は、行を全部見終わらないと出せないからです。 その上にビューを重ねると、外側の条件はどこまで行っても中に届きません。

この記事の8行では体感できません。 効くのは行数が増えたときだけです。判断は EXPLAIN でやります。 測り方は本線の速くする前に必ず測ると、深掘りの索引を張ったのに使われないにあります。

実務での持ち方は、これで足ります。

ビューは1段まで。 2段目を作りたくなったら、それは「元のSQLを書いたほうが速い」という合図です。

⑧ ビューの使いどころは3つ

ここまでで、ビューが「速くする道具ではない」ことは分かりました。 では何のために使うのか。3つあります。

1. 列を隠す — 権限の代わり

email を見せたくない相手に、顧客一覧を渡したいときです。

CREATE OR REPLACE VIEW d_vw_v_public AS
SELECT id, name, plan, created_at FROM d_vw_customer WHERE active = 1;
SELECT * FROM d_vw_v_public LIMIT 3;
+----+--------+------+------------+
| id | name   | plan | created_at |
+----+--------+------+------------+
|  1 | 山田   | pro  | 2026-01-10 |
|  2 | 佐藤   | free | 2026-01-15 |
|  3 | 鈴木   | pro  | 2026-02-01 |
+----+--------+------+------------+

email は、このビューからは絶対に出てきません。

ただし、これは「権限そのもの」ではありません。 元のテーブルへの権限が別に残っていれば、SELECT * FROM d_vw_customer で全部見えます。 ビューで隠すのは、「ビューしか触れない相手」を作ったときだけ意味があります。 権限そのものの話は、本線の安全にはできない、被害を小さくするの考え方が土台になります。

行を隠すことも同じようにできます。WHERE active = 1 が、すでにそれです。

2. 複雑な結合に、名前を付ける

冒頭の3つのアプリの話が、これです。「大口顧客とは何か」の定義が1か所になります。

条件が変わったときに、直すのは1か所です。

CREATE OR REPLACE VIEW d_vw_v_big_customer AS
SELECT c.id, c.name, c.plan, SUM(o.amount) AS total
FROM d_vw_customer c
JOIN d_vw_order o ON o.customer_id = c.id
WHERE c.active = 1 AND o.status NOT IN ('canceled', 'refunded')
GROUP BY c.id, c.name, c.plan
HAVING SUM(o.amount) >= 10000;

refunded を除く、という変更を1回だけしました。 3つのアプリは、何も直さずに新しい定義になります。

SELECT * FROM d_vw_v_big_customer;
+----+--------+------+-------+
| id | name   | plan | total |
+----+--------+------+-------+
|  1 | 山田   | pro  | 14400 |
|  3 | 鈴木   | pro  | 12000 |
|  6 | 伊藤   | team | 30000 |
|  8 | 中村   | team | 15000 |
+----+--------+------+-------+

鈴木の合計が 15000 から 12000 に変わりました。 これが「1か所を直した」効果です。

裏返すと、これは怖いことでもあります。 ビューを1行直すと、それを使っている全員の結果が黙って変わります。 誰が使っているかは、データベースの中を見ても分かりません。 使う側のコードを検索するしかありません。

3. 互換性を保つ — 列名を変えたのに、古いアプリを動かす

name を full_name に変えたい。でも古いアプリが name を読んでいる、という場面です。

CREATE OR REPLACE VIEW d_vw_v_legacy AS
SELECT id, name AS full_name, plan FROM d_vw_customer;

元のテーブルは変えずに、新しい名前で読める窓口を1つ増やしました。 逆向き(テーブルの列名を変えて、古い名前のビューを残す)もできます。

これは「移行のあいだだけ置く足場」です。 消す予定を決めてから作ってください。 消す予定のない足場は、5年後もそこにあります。

⑨ ストアドプロシージャと関数

ビューは SELECT 1本にしか名前を付けられません。 「3つの UPDATE を順番に実行して、途中で条件によって分岐する」は書けません。

それを保存するのが、ストアドプロシージャです。

まず DELIMITER を変える理由

手続きの中には ; が何個も入ります。 そのまま打つと、こうなります。

CREATE PROCEDURE d_vw_p_test()
BEGIN
  SELECT 1;
END;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual ... near '' at line 3

理由はこうです。

mysql クライアントは、; を見た瞬間に「ここまでが1文だ」と判断してサーバーに送ります。 だから BEGIN SELECT 1; までを送ってしまいます。 サーバーは「END はどこだ」と言って落ちます。書いたSQLは正しいのに、途中で切られているのです。

そこで、「文の終わり」の記号を一時的に変えます。

DELIMITER //

これ以降、; は文の区切りではなくなります。 区切りは // になります。

CREATE PROCEDURE d_vw_p_test()
BEGIN
  SELECT 1;
END //

通ります。 終わったら戻します。

DELIMITER ;

DELIMITER は SQL ではありません。 mysql クライアントのコマンドです。 Node.js やアプリのコードから送ると、ERROR 1064 になります。 サーバーは DELIMITER を知りません。 ファイルを mysql < file.sql で流すときは効きます(同じクライアントが読むため)。

作った練習用のものを消しておきます。

DROP PROCEDURE IF EXISTS d_vw_p_test;

IN と OUT のパラメータ

顧客IDを渡すと、キャンセル以外の合計を返す手続きを作ります。

DELIMITER //
CREATE PROCEDURE d_vw_p_total(IN p_customer_id INT UNSIGNED, OUT p_total INT)
BEGIN
  SELECT COALESCE(SUM(amount), 0) INTO p_total
  FROM d_vw_order
  WHERE customer_id = p_customer_id
    AND status NOT IN ('canceled', 'refunded');
END //
DELIMITER ;

3つ、見ておくところがあります。

書いたもの意味
IN p_customer_id受け取る値。中で書き換えても呼び出し元には返りません
OUT p_total返す値。手続きの中で入れたものが、呼び出し元に見えます
INTO p_totalSELECT の結果を、変数に入れる書き方。画面には出ません

引数名を p_ で始めているのは、列名との衝突を避けるためです。 引数を customer_id という名前にすると、WHERE customer_id = customer_id になって、全行が一致します。 気づきにくい事故です。

呼びます。

CALL d_vw_p_total(1, @t);
SELECT @t AS total;
+-------+
| total |
+-------+
| 14400 |
+-------+

@t は「ユーザー定義変数」です。接続ごとに持っていて、切れば消えます。 OUT の値は、この変数を受け皿にして受け取ります。

COALESCE を入れてあるのは、注文が1件も無い顧客のためです。

CALL d_vw_p_total(999, @t);
SELECT @t AS total;
+-------+
| total |
+-------+
|     0 |
+-------+

COALESCE が無ければ NULL が返ります。 NULL の扱いは深掘りのCONCATにNULLが1つで全部消えるに書きました。

一覧は、こう出します。

SHOW PROCEDURE STATUS WHERE Db = 'appdb';

中身は、こう見ます。

SHOW CREATE PROCEDURE d_vw_p_total\G

CREATE FUNCTION は、たいてい1回目に怒られる

関数は値を1つ返します。 だから SELECT の中で呼べます。 ここが手続きとの決定的な違いです。

素直に書くと、こうなります。

DELIMITER //
CREATE FUNCTION d_vw_f_total(p_customer_id INT UNSIGNED) RETURNS INT
BEGIN
  DECLARE v_total INT;
  SELECT COALESCE(SUM(amount), 0) INTO v_total
  FROM d_vw_order
  WHERE customer_id = p_customer_id
    AND status NOT IN ('canceled', 'refunded');
  RETURN v_total;
END //
DELIMITER ;
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

長いですが、言っていることは1つです。

「この関数が何をするのか宣言していないので、バイナリログに記録できません」

バイナリログは、更新の履歴です。レプリケーション(本線の書いた直後に読むと無い)とバックアップに使います。MySQL 8.0 では、既定で有効です。

確かめます。

SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

なぜ怒られるのか。 こういうことです。

同じ関数を、別のサーバーで実行したときに同じ結果になるか、MySQL には分かりません。 RAND() や NOW() を使っていたら、違う結果になります。 違う結果になるものを履歴に残すと、複製したサーバーの中身がずれます。

直し方は2つあります。

1つ目(推奨)— 何をするのか宣言する。

DELIMITER //
CREATE FUNCTION d_vw_f_total(p_customer_id INT UNSIGNED) RETURNS INT
READS SQL DATA
BEGIN
  DECLARE v_total INT;
  SELECT COALESCE(SUM(amount), 0) INTO v_total
  FROM d_vw_order
  WHERE customer_id = p_customer_id
    AND status NOT IN ('canceled', 'refunded');
  RETURN v_total;
END //
DELIMITER ;
Query OK, 0 rows affected (0.01 sec)

READS SQL DATA の1行を足しただけです。 宣言は3種類あります。

宣言意味
DETERMINISTIC同じ引数なら必ず同じ結果。RAND() や NOW() を使っていたら嘘になります
READS SQL DATAデータを読むが、書かない
NO SQLSQLを一切使わない。計算だけ

2つ目(非推奨)— 検査を切る。

SET GLOBAL log_bin_trust_function_creators = 1;

これは「作った人を信じる」という設定です。エラーは消えますが、理由は消えていません。 ネットで検索すると、この1行だけが答えとして出てきます。 本番のサーバーで打つ前に、上の3つの宣言で済まないか考えてください。

使ってみます。ここが関数の価値です。

SELECT id, name, d_vw_f_total(id) AS total
FROM d_vw_customer
WHERE active = 1
ORDER BY total DESC;
+----+--------+-------+
| id | name   | total |
+----+--------+-------+
|  6 | 伊藤   | 30000 |
|  8 | 中村   | 15000 |
|  1 | 山田   | 14400 |
|  3 | 鈴木   | 12000 |
|  2 | 佐藤   |  1960 |
|  7 | 渡辺   |   980 |
|  4 | 高橋   |     0 |
+----+--------+-------+

SELECT の中に自作の関数が入っています。 手続きではこれができません。

ただし、この書き方は罠です。 7行それぞれについて、関数が1回ずつ走ります。 顧客が10万人いれば、10万回の SELECT が走ります。 JOIN と GROUP BY で書けば1回で済みます。 「関数だから軽い」ということはありません。 中身は普通のSQLです。

トリガー — 見えないところで動く

INSERT されたら自動で走るものです。1つだけ作ります。

CREATE TABLE d_vw_audit (
  id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id INT UNSIGNED NOT NULL,
  amount   INT          NOT NULL,
  logged_at DATETIME    NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
DELIMITER //
CREATE TRIGGER d_vw_tr_order_insert
AFTER INSERT ON d_vw_order
FOR EACH ROW
BEGIN
  INSERT INTO d_vw_audit (order_id, amount, logged_at)
  VALUES (NEW.id, NEW.amount, NOW());
END //
DELIMITER ;

NEW は、いま入った行です。UPDATE のトリガーなら OLD も使えます。

注文を1件入れます。トリガーのことは一切書きません。

INSERT INTO d_vw_order (customer_id, amount, status, ordered_at)
VALUES (7, 1980, 'paid', '2026-08-01');
SELECT * FROM d_vw_audit;
+----+----------+--------+---------------------+
| id | order_id | amount | logged_at           |
+----+----------+--------+---------------------+
|  1 |       18 |   1980 | 2026-09-20 11:04:22 |
+----+----------+--------+---------------------+

書いていない INSERT が実行されました。 便利です。そして、ここが一番危ないところです。

アプリのコードのどこを読んでも、d_vw_audit に書く処理は出てきません。 「なぜこの表に行が増えるのか」を調べるには、データベースの中を見に行くしかありません。 さらに、トリガーが落ちると、元の INSERT ごと失敗します。 注文が入らなくなった原因が、監査ログの表にあるという状況が起きます。

存在を確かめる方法は、これです。

SHOW TRIGGERS FROM appdb\G

トリガーを使うなら、「そこにトリガーがある」ことをリポジトリのどこかに必ず書いてください。 それが唯一の防御です。

⑩ ストアドを使うべきか — 使わないと決めた

ここまで全部動きました。 そのうえで、このサイトではストアドを使いません。 理由を正直に書きます。

問題中身
Git に残らないコードはデータベースの中にあります。 git log に出ません。誰がいつ何を変えたか、追えません
差分レビューができないPull Request に出てきません。 本線の一人でもmainに直接コミットしないでやったレビューが、丸ごと効きません
テストしにくい動かすにはデータベースが要ります。 単体テストの中で呼べません
移植できないMySQL のストアドは PostgreSQL では動きません。 文法が違います。SQL標準ではない部分です
開発環境と本番がずれる手で CREATE PROCEDURE した本番だけ、違う中身になります。 気づく方法がありません
エラーが読みにくい落ちたときにアプリのスタックトレースに何も出ません

まとめると1つです。

アプリのコードに書いたロジックは、Git が守ってくれます。 データベースに書いたロジックは、誰も守ってくれません。

このサイトの方針を、はっきり書いておきます。

どこに書くか何を
アプリ側(Node.js のコード)業務のロジック全部。 条件分岐、計算、順番
データベース側データの形と、壊れない保証だけ。 テーブル定義、制約、索引
ビューは使ってよい。 ただし1段までで、定義は必ずリポジトリの .sql に置く

ビューだけは残す理由は、ビューが「ロジック」ではなく「見せ方」だからです。それでも、定義をリポジトリに置かないなら、ビューも同じ穴に落ちます。

ではストアドがいつ正しいのかも、書いておきます。否定するだけでは嘘になります。

  • アプリが複数の言語で書かれていて、どれも同じ処理をする必要があるとき
  • 数百万行を1文で処理したくて、アプリとデータベースの往復が耐えられないとき
  • データベースにしか権限が無く、アプリを触れないとき(既存の大きなシステム)

どれも「アプリ側に書けない事情がある」という形をしています。 書けるなら、アプリ側に書く。 それがこのサイトの結論です。 ロジックをアプリ側に寄せる話は、本線のALTER TABLE を手で打たないとも地続きです。

つまずいたところ

1. ビューを「集計結果の保存場所」だと思っていた

最初、「重い集計をビューにしておけば速くなる」と思っていました。 完全に逆でした。

CREATE VIEW の応答が 0 rows affected だったところで、おかしいと気づきました。 1行もコピーしていないなら、速くなる理由がありません。 保存されているのはSQLの文章だけです。

「計算結果を保存しておく」ものは、CREATE TABLE ... AS SELECT で作る実テーブルか、MySQL の外にあるキャッシュです。ビューではありません。

2. ERROR 1356 の原因が権限だと思って30分溶かした

メッセージに definer/invoker of view lack rights と書いてあるので、権限を疑いました。 GRANT を見直して、root で入り直して、それでも直りませんでした。

原因は消した memo 列でした。

ERROR 1356 は、「テーブルが無い / 列が無い / 関数が無い / 権限が無い」を全部まとめて1つのメッセージにしています。 最初に疑うのは権限ではなく、列です。 SHOW CREATE VIEW と DESCRIBE を並べるのが最短でした。

3. ALTER TABLE のあとに CHECK TABLE をしていなかった

列を消したときに警告が出なかったので、問題が無いと思い込みました。 壊れていることに気づいたのは、そのビューを使ったときです。

手順に入れました。 列を消したか名前を変えたら、その場で全ビューに CHECK TABLE を掛けます。

4. UPDATE できるビューとできないビューの境目が覚えられなかった

DISTINCT は駄目、GROUP BY も駄目、JOIN は場合による——丸暗記しようとして無理でした。

ERROR 1288 を実際に出してから、考え方が分かりました。

「ビューの1行を、元のテーブルの1行に戻せるか」だけです。 SUM は3行を1行にしています。戻せません。 それだけの話でした。

5. WITH CHECK OPTION を「読み取りの条件」だと思っていた

名前から「読むときに検査する」のかと思っていました。 違いました。書くときだけです。

active = 0 に UPDATE して、その行が自分の目の前から消えたときに、何のための機能なのか腹に落ちました。

6. DELIMITER を戻し忘れて、以降のSQLが全部おかしくなった

DELIMITER // のまま SELECT 1; と打って、何も返ってこなくなりました。 プロンプトが -> のまま止まります。

; は、もう文の終わりではありません。 // を打てば流れます。

mysql> SELECT 1;
    -> //

DELIMITER ; を打つのを、CREATE PROCEDURE とセットにするしかありません。

7. ERROR 1418 で log_bin_trust_function_creators を打ちそうになった

エラーメッセージの中に、その変数名が書いてあります。 しかも (you *might* want to use ...) と、MySQL 自身が勧めているように読めます。

よく読むと the less safe(より安全でない)と書いてありました。

READS SQL DATA の1行で済みました。 エラーメッセージが提案してくる回避策を、そのまま打たない——これはSQLに限らない話でした。エラーの読み方は本線のエラーは下から読むにまとめています。

8. トリガーで作った監査ログのせいで、INSERT が落ちた

トリガーの INSERT から logged_at を外した版を試していたときに(d_vw_audit の logged_at は NOT NULL で既定値なし)、注文の INSERT そのものが落ちました。

ERROR 1364 (HY000): Field 'logged_at' doesn't have a default value

エラーの中に d_vw_order の文字がありません。 打ったのは INSERT INTO d_vw_order なのにです。

トリガーの中で落ちると、元の文ごと失敗します。 これがトリガーの怖さでした。 「注文が登録できない」の原因が、注文の表にないのです。

9. ビューを重ねて、原因不明の遅さを作った

「大口顧客」ビューの上に「teamプランの大口顧客」ビューを作ったところで、id で1行引くだけのSQLが妙に重くなりました。

EXPLAIN に <derived2> が出ていました。 集計を含むビューは MERGE にできないので、外側の WHERE が中に届いていませんでした。

ビューは1段まで、と決めました。

後片づけ

この記事で作ったものは、すべて d_vw_ で始まっています。 既存のテーブルには触っていません。

消す順番があります。 ビューが実テーブルを見ているので、ビューから先に消します。

まず、消す対象を目で確認します。

SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'appdb' AND table_name LIKE 'd\_vw\_%';
+---------------------+------------+
| TABLE_NAME          | TABLE_TYPE |
+---------------------+------------+
| d_vw_audit          | BASE TABLE |
| d_vw_customer       | BASE TABLE |
| d_vw_order          | BASE TABLE |
| d_vw_v_active       | VIEW       |
| d_vw_v_active_pro   | VIEW       |
| d_vw_v_active_tmp   | VIEW       |
| d_vw_v_big_customer | VIEW       |
| d_vw_v_big_team     | VIEW       |
| d_vw_v_legacy       | VIEW       |
| d_vw_v_memo         | VIEW       |
| d_vw_v_plans        | VIEW       |
| d_vw_v_public       | VIEW       |
+---------------------+------------+

この12個だけであることを確認してから消します。ビューです。

DROP VIEW IF EXISTS d_vw_v_big_team;
DROP VIEW IF EXISTS d_vw_v_active_pro;
DROP VIEW IF EXISTS d_vw_v_active_tmp;
DROP VIEW IF EXISTS d_vw_v_active;
DROP VIEW IF EXISTS d_vw_v_big_customer;
DROP VIEW IF EXISTS d_vw_v_legacy;
DROP VIEW IF EXISTS d_vw_v_memo;
DROP VIEW IF EXISTS d_vw_v_plans;
DROP VIEW IF EXISTS d_vw_v_public;

トリガーです。 テーブルより先に消します。

DROP TRIGGER IF EXISTS d_vw_tr_order_insert;

手続きと関数です。

DROP PROCEDURE IF EXISTS d_vw_p_total;
DROP FUNCTION IF EXISTS d_vw_f_total;

残っていないか確かめます。

SHOW PROCEDURE STATUS WHERE Db = 'appdb';
SHOW FUNCTION STATUS WHERE Db = 'appdb';
SHOW TRIGGERS FROM appdb;

3つとも Empty set なら、消えています。

最後にテーブルです。

DROP TABLE IF EXISTS d_vw_audit;
DROP TABLE IF EXISTS d_vw_order;
DROP TABLE IF EXISTS d_vw_customer;

設定を触っていたら、戻します。

SET GLOBAL log_bin_trust_function_creators = 0;

残っているものを確認します。

SHOW FULL TABLES;

tasks・users など、本線で作ったものがそのまま残っていれば正常です。 d_vw_ で始まるものが1つも無ければ、元通りです。

抜けます。

EXIT;

DROP は確認を聞いてきません。 打つ前に、名前が d_vw_ で始まっていることを必ず目で見てください。 DROP DATABASE appdb; と打ってはいけません。 この記事では、データベースは作っていません。

まとめ — 理解できたこと

ビューは、テーブルの仲間ではありませんでした。 保存された SELECT の文章でした。

  • CREATE VIEW は 0 rows affected を返す。 データは1行もコピーされない
  • SELECT するたびに、その場で元のテーブルを読み直している。 元のデータが変われば、ビューの結果も変わる
  • CTE(WITH)との違いは寿命だけ。 その場限りか、保存されるか。共有したいときだけビュー
  • 元の列を消してもビューは何も言わない。 壊れたと分かるのは、次に SELECT した瞬間の ERROR 1356
  • ERROR 1356 は「テーブル・列・関数・権限」を1つにまとめたメッセージ。 最初に疑うのは権限ではなく列
  • ALTER TABLE で列を触ったら、その場で CHECK TABLE を全ビューに掛ける。 Corrupt が壊れている印
  • SHOW CREATE VIEW に、書いた整形は残らない。 読める形はリポジトリの .sql にしか無い
  • スクリプトに書くなら CREATE OR REPLACE VIEW。 ALTER VIEW は、無いときに落ちる
  • 単純なビューは UPDATE できる。 ただし書き換わっているのは元のテーブル
  • 集計・DISTINCT・GROUP BY・UNION を含むと ERROR 1288。 「1行を元の1行に戻せるか」がすべて
  • WITH CHECK OPTION が無いと、ビューの条件から外れる行を、そのビューから作れてしまう
  • WITH CHECK OPTION 違反は ERROR 1369。 既定は CASCADED(厳しいほう)でよい
  • MERGE なら外側の WHERE が中まで届く。 EXPLAIN にビュー名が出ず、実テーブル名が出る
  • TEMPTABLE になると <derived> が出て、先に全部作ってから絞る。 行数が増えると致命的
  • 集計を含むビューは MERGE にできない。 その上に重ねると、条件はどこまでも届かない。ビューは1段まで
  • ビューの使いどころは、列を隠す・複雑な結合に名前を付ける・互換性の足場を作る、の3つ
  • 列を隠すのは権限の代わりにならない。 元のテーブルへの権限が残っていれば全部見える
  • DELIMITER を変えるのは、; でクライアントに切られないため。 SQL ではなくクライアントの機能
  • DELIMITER はアプリのコードから送れない。 サーバーは知らない
  • IN は受け取る、OUT は返す。 SELECT ... INTO 変数 で入れて、CALL の @変数 で受ける
  • 引数名は p_ で始める。 列名と同じにすると、WHERE が常に真になる
  • 関数は値を返すので SELECT の中で呼べる。 ただし行の数だけ実行される
  • ERROR 1418 は READS SQL DATA の1行で直る。 log_bin_trust_function_creators を打つのは最後の手
  • トリガーは呼ぶ側のコードに出てこない。 落ちると、元の INSERT ごと失敗する
  • ストアドは Git に残らず、レビューできず、テストしにくく、移植できない。 このサイトはアプリ側に書く

一番効いたのは、⑤ で memo 列を消したときに、ALTER TABLE が警告を1つも出さなかったところでした。 壊れたことを、データベースは教えてくれません。 ビューは便利ですが、「元のテーブルを変える人が、ビューの存在を知らない」という前提で運用しないと壊れます。

ストアドについては、動くところまで全部やってから使わないと決めました。 触らずに否定するのと、触ってから選ばないのは、違う結論だと思っています。

参考リンク


次に読む

この記事は 【深掘り】SQL の 12 / 17 本目です。

記事
← 前の記事CTE(WITH)と再帰クエリ — サブクエリ3段は読めない
→ 次の記事トランザクション分離レベル — 同じSELECTが2回目で変わる

上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。

全体の順路は はじめに にあります。

roadmap.sh のどこをやっているか

Backend›SQL — Views & Procedures
  • ビューの作成・更新・壊れ方
  • ストアドプロシージャと関数を使わない判断

この記事は、上の項目を実際に手を動かして確かめる回です。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次