> For AI agents: the complete documentation index is available at /resource/ucidm/docs/on-premise/dev/llms.txt, the full documentation bundle is available at /resource/ucidm/docs/on-premise/dev/llms-full.txt.

# MySQL / MariaDB ストアドルーチンの開発

[UCIDM が呼び出すオブジェクト](/resource/ucidm/docs/on-premise/dev/tech/storedProcedure/index.md#ucidm-objects) を MySQL と MariaDB のストアドルーチンで実装する方法を説明します。JSON 型と JSON 関数を使うため、MySQL 8.0 以降と MariaDB 10.9 以降が対象です。

外部連携設定の「データベースタイプ」は、MySQL と MariaDB のどちらも同じ値を選びます。UCIDM が発行する SQL も同じです。ただし JSON 関数の書き方には差があるので、[MariaDB での違い](#mariadb) にまとめました。

呼び出しの仕様そのものは [ストアドプロシージャ／ファンクション](/resource/ucidm/docs/on-premise/dev/tech/storedProcedure/index.md) にまとめています。本ページは MySQL 固有の書き方に絞ります。

## UCIDM が発行する SQL \{#generated-sql}

実装の制約は、UCIDM が発行する SQL から決まります。MySQL に対しては次の3種類を発行します。

```sql
-- 反映プロシージャの呼び出し
CALL ucidm_apply_user(?);

-- 取得ファンクションの呼び出し
SELECT ucidm_get_user(?);

-- インストール前の削除 (名前だけで指定する)
DROP PROCEDURE IF EXISTS ucidm_apply_user;
DROP FUNCTION IF EXISTS ucidm_get_user;
```

ここから、MySQL での実装は次の形に決まります。

- 引数はちょうど1つで、型は `JSON` にします
- 取得ファンクションの戻り値は `SELECT` から参照できる型にします
- オブジェクトは、外部連携設定の「データベース名」で指定したデータベースに作られます

MySQL はルーチンのオーバーロードに対応していないため、DROP は名前だけで指定します。引数の型を変えても古い定義が残ることはありません。

## 接続ユーザーの権限 \{#privileges}

[接続ユーザーに要る権限](/resource/ucidm/docs/on-premise/dev/tech/storedProcedure/index.md#privileges) は、MySQL では3つの権限に対応します。作成が `CREATE ROUTINE`、削除が `ALTER ROUTINE`、呼び出しが `EXECUTE` です。外部連携設定の「データベース名」に指定したデータベースに対して与えます。

ルーチンを作成したユーザーには `ALTER ROUTINE` と `EXECUTE` が自動で与えられます (`automatic_sp_privileges` が既定の `ON` のとき)。それでも、別のユーザーが作った同名のオブジェクトを最初のインストールで削除する場合があるため、3つとも明示的に与えておくほうが確実です。

ルーチンは既定で `SQL SECURITY DEFINER` として動きます。読み書きするテーブルへの権限は `DEFINER` に要りますが、6つのオブジェクトを作成するのは接続ユーザー自身なので、`DEFINER` も接続ユーザーになります。

```sql
GRANT CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON empdb.* TO 'ucidm'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON empdb.* TO 'ucidm'@'%';
```

バイナリログを有効にしたサーバーでは、ファンクションの作成にもう1つ条件が付きます。`log_bin_trust_function_creators` が既定の `OFF` のとき、データアクセス特性を宣言していないファンクションの作成が `ERROR 1418` で拒否されます。本ページの実装例は [ユーザー取得ファンクション](#get-user) のように `READS SQL DATA` を宣言することでこの条件を満たしています。宣言しない方針を採る場合は、作成するユーザーに `SUPER` 権限を与えるか、`log_bin_trust_function_creators` を `ON` にすることになります。

## 実装例の前提 \{#example-schema}

社員管理の既存スキーマに ID 連携する例で説明します。[Oracle PL/SQL の開発](/resource/ucidm/docs/on-premise/dev/tech/storedProcedure/oracle.md#example-schema) と同じスキーマを MySQL の型で用意します。

```sql
-- ユーザーの基本情報
CREATE TABLE emp_user (
  user_name    VARCHAR(255) PRIMARY KEY,
  display_name TEXT NOT NULL,
  status       VARCHAR(50),
  emp_no       INT,
  pwd_changed  DATETIME,
  dept_code    VARCHAR(50)
) DEFAULT CHARSET=utf8mb4;

-- メールアドレスは別テーブル
CREATE TABLE emp_contact (
  user_name VARCHAR(255) PRIMARY KEY,
  email     VARCHAR(255)
) DEFAULT CHARSET=utf8mb4;

-- パスワードも別テーブル
CREATE TABLE emp_cred (
  user_name VARCHAR(255) PRIMARY KEY,
  password  VARCHAR(255)
) DEFAULT CHARSET=utf8mb4;

-- グループは連番の代理キーが主キー
CREATE TABLE emp_group_seq (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  primary_id VARCHAR(255) UNIQUE
) DEFAULT CHARSET=utf8mb4;
CREATE TABLE emp_group (
  id    INT PRIMARY KEY,
  label VARCHAR(255)
) DEFAULT CHARSET=utf8mb4;
CREATE TABLE emp_member (
  group_name VARCHAR(255),
  user_name  VARCHAR(255),
  PRIMARY KEY (group_name, user_name)
) DEFAULT CHARSET=utf8mb4;

-- 部署名から部署コードへの変換マスタ
CREATE TABLE emp_dept_code (
  name VARCHAR(255) PRIMARY KEY,
  code VARCHAR(255)
) DEFAULT CHARSET=utf8mb4;
INSERT INTO emp_dept_code(name, code)
  VALUES ('営業部', 'D001'), ('開発部', 'D002'), ('人事部', 'D003');
```

主キーになるカラムに `TEXT` は使えないので `VARCHAR(255)` にしています。文字セットを `utf8mb4` にしているのは、絵文字などの4バイト文字を含む名前を格納するためです。サーバーの既定値が `utf8mb3` の環境では、指定しないと反映時にエラーになります。

属性マッピングも Oracle の例と同じです。左の列が反映プロシージャの `attrs` のキーになります。

| 連携先の属性名            | 連携元の属性名            | 用途                           |
| :----------------- | :----------------- | :--------------------------- |
| `cn`               | `cn`               | `emp_user.display_name`      |
| `mail`             | `mail`             | `emp_contact.email`          |
| `empNo`            | `employeeNumber`   | `emp_user.emp_no` (数値)       |
| `pwdChanged`       | `pwdChangedTime`   | `emp_user.pwd_changed` (日時)  |
| `department`       | `ou`               | `emp_user.dept_code` (マスタ変換) |
| `ucidmUserEnabled` | `ucidmUserEnabled` | `emp_user.status`            |

グループの属性マッピングは `label` に `cn` を割り当てます。

最後の `ucidmUserEnabled` は、JSON の `enabled` を解決するために必要です。動作設定で「ユーザーステータス設定情報を使用する」を off にした場合、UCIDM は「ステータス用属性名」に指定した名前を**マッピング後の属性**から探します。属性マッピングにこの行がないと `enabled` が解決されず、JSON に含まれません。「ユーザーステータス設定情報を使用する」を on にした場合はマッピングは不要です。

## ユーザー反映プロシージャ \{#apply-user}

`opCode` で4つの操作を分岐します。

```sql
CREATE PROCEDURE ucidm_apply_user(p JSON)
BEGIN
  DECLARE op  VARCHAR(8);
  DECLARE pid VARCHAR(255);
  SET op  = p->>'$.opCode';
  SET pid = p->>'$.primaryID';

  IF op = 'I' THEN
    INSERT INTO emp_user(user_name, display_name, status, emp_no, pwd_changed, dept_code)
      VALUES (pid,
              p->>'$.attrs.cn',
              IF(p->>'$.enabled' = 'true', 'active', 'inactive'),
              CAST(p->>'$.attrs.empNo' AS SIGNED),
              STR_TO_DATE(p->>'$.attrs.pwdChanged', '%Y%m%d%H%i%sZ'),
              (SELECT code FROM emp_dept_code WHERE name = p->>'$.attrs.department'));
    INSERT INTO emp_contact(user_name, email) VALUES (pid, p->>'$.attrs.mail');

  ELSEIF op = 'U' THEN
    UPDATE emp_user SET
      display_name = COALESCE(p->>'$.attrs.cn', display_name),
      emp_no       = COALESCE(CAST(p->>'$.attrs.empNo' AS SIGNED), emp_no),
      pwd_changed  = COALESCE(STR_TO_DATE(p->>'$.attrs.pwdChanged', '%Y%m%d%H%i%sZ'), pwd_changed),
      dept_code    = COALESCE((SELECT code FROM emp_dept_code
                                 WHERE name = p->>'$.attrs.department'), dept_code),
      status       = CASE WHEN p->>'$.enabled' IS NOT NULL
                          THEN IF(p->>'$.enabled' = 'true', 'active', 'inactive')
                          ELSE status END
      WHERE user_name = pid;
    IF p->>'$.attrs.mail' IS NOT NULL THEN
      INSERT INTO emp_contact(user_name, email) VALUES (pid, p->>'$.attrs.mail') AS new
        ON DUPLICATE KEY UPDATE email = new.email;
    END IF;

  ELSEIF op = 'P' THEN
    INSERT INTO emp_cred(user_name, password) VALUES (pid, p->>'$.attrs.password') AS new
      ON DUPLICATE KEY UPDATE password = new.password;

  ELSEIF op = 'D' THEN
    DELETE FROM emp_contact WHERE user_name = pid;
    DELETE FROM emp_cred WHERE user_name = pid;
    DELETE FROM emp_user WHERE user_name = pid;
  END IF;
END
```

分岐ごとの要点を順に見ます。

`I` では、`attrs` の文字列を `CAST` と `STR_TO_DATE` でカラムの型に変換しています。`pwdChanged` の元になる LDAP の `pwdChangedTime` は `20260220213129Z` という generalizedTime 形式なので、書式指定子 `%Y%m%d%H%i%sZ` で解釈します。部署は `emp_dept_code` を副問い合わせで引いてコードに変換しています。

`status` は `enabled` から導いています。この書き方では、`enabled` が渡らなかったときも `IF` の偽側に落ちて `inactive` で登録される点に注意してください。属性マッピングの設定漏れで `enabled` が解決されないと、全員が無効な状態で登録されます。未解決のときに別の値を入れたい場合は、`p->>'$.enabled' IS NULL` の場合を分けてください。

`U` では、すべてのカラムを `COALESCE` で包んでいます。更新イベントで届く `attrs` には変更のあった属性しか入らないため、これがないと今回渡されなかったカラムが NULL で上書きされます。`status` だけは `COALESCE` ではなく `CASE` で書いています。`enabled` は真偽値なので、`false` を「値なし」と区別する必要があるためです。

`P` はパスワード更新です。`emp_cred` に行があるとは限らないので `ON DUPLICATE KEY UPDATE` で挿入と更新を兼ねています。パスワードマッピングを設定していない場合、`$.attrs.password` には平文が入ります。連携先の要件にあわせて、パスワードマッピングでハッシュ化するか、プロシージャ内で変換してください。

`D` では3つのテーブルから順に削除します。外部キー制約がある場合は、参照している側から先に削除します。

JSON からの値の取り出しには `p->>'$.opCode'` を使っています。`JSON_UNQUOTE(JSON_EXTRACT(p, '$.opCode'))` の省略形で、引用符を外した文字列を返します。パスが存在しなければ NULL を返すので、`IS NOT NULL` で「キーが渡されたかどうか」を判定できます。

## ユーザー取得ファンクション \{#get-user}

ファンクションは問い合わせるだけなので、`RETURN` に副問い合わせを1つ書く形にできます。

```sql
CREATE FUNCTION ucidm_get_user(p JSON) RETURNS JSON READS SQL DATA
RETURN (
  SELECT JSON_OBJECT(
           'cn',         u.display_name,
           'mail',       c.email,
           'empNo',      u.emp_no,
           'pwdChanged', DATE_FORMAT(u.pwd_changed, '%Y%m%d%H%i%sZ'),
           'department', d.name)
  FROM emp_user u
         LEFT JOIN emp_contact c ON c.user_name = u.user_name
         LEFT JOIN emp_dept_code d ON d.code = u.dept_code
  WHERE u.user_name = p->>'$.primaryID'
)
```

該当する行がなければ、この副問い合わせの結果は NULL になります。Oracle のような例外処理は要りません。

`READS SQL DATA` は省略できません。バイナリログを有効にしたサーバーでは、データアクセスの特性を宣言していないルーチンの作成が `ERROR 1418` で拒否されます。

JSON のキーは、属性マッピングの「連携先の属性名」にあわせています。名前だけでなく値の形もあわせると、連携履歴の差分が読める形になります。この例では `department` を `emp_dept_code` と結合して部署名に戻しています。カラムに入っている `dept_code` をそのまま返すと、変更前がコード、変更後が部署名という差分になり、比較になりません。`pwdChanged` を `DATE_FORMAT` で generalizedTime の書式に戻しているのも同じ理由です。`JSON_OBJECT` に `DATETIME` をそのまま渡すと `2026-02-20 21:31:29.000000` という MySQL 独自の形式になります。

## グループの実装 \{#group}

グループはユーザーと同じくエントリです。`primaryID` で識別し、属性マッピングの結果を `attrs` で受け取ります。ユーザーとの違いは2つあります。`enabled` が渡らないことと、パスワード更新の `P` が来ないことです。反映プロシージャが分岐するのは `I`、`U`、`D` の3つになります。

グループの反映プロシージャは、外部の識別子と連番の代理キーを対応づけます。`AUTO_INCREMENT` の採番結果は `LAST_INSERT_ID()` で受け取ります。

```sql
CREATE PROCEDURE ucidm_apply_group(p JSON)
BEGIN
  DECLARE op  VARCHAR(8);
  DECLARE pid VARCHAR(255);
  DECLARE gid INT;
  SET op  = p->>'$.opCode';
  SET pid = p->>'$.primaryID';

  IF op = 'I' THEN
    INSERT INTO emp_group_seq(primary_id) VALUES (pid);
    SET gid = LAST_INSERT_ID();
    INSERT INTO emp_group(id, label) VALUES (gid, p->>'$.attrs.label');
  ELSEIF op = 'U' THEN
    UPDATE emp_group SET label = COALESCE(p->>'$.attrs.label', label)
      WHERE id = (SELECT id FROM emp_group_seq WHERE primary_id = pid);
  ELSEIF op = 'D' THEN
    DELETE FROM emp_member WHERE group_name = pid;
    DELETE FROM emp_group WHERE id = (SELECT id FROM emp_group_seq WHERE primary_id = pid);
    DELETE FROM emp_group_seq WHERE primary_id = pid;
  END IF;
END
```

グループ取得ファンクションはユーザーと同じ形です。返す JSON のキーは、グループの属性マッピングの「連携先の属性名」にあわせます。

```sql
CREATE FUNCTION ucidm_get_group(p JSON) RETURNS JSON READS SQL DATA
RETURN (
  SELECT JSON_OBJECT('id', s.id, 'label', g.label)
  FROM emp_group_seq s JOIN emp_group g ON g.id = s.id
  WHERE s.primary_id = p->>'$.primaryID'
)
```

## メンバーの実装 \{#member}

メンバーはエントリではなく、グループとユーザーの関係です。属性を持たないため `attrs` も `enabled` も渡らず、グループとメンバーの2つの `primaryID` の組だけで識別します。`opCode` も追加の `I` と削除の `D` の2つだけで、更新に相当する操作はありません。

メンバーの追加と削除では、UCIDM はまずグループ取得ファンクションでグループの存在を確認します。グループが見つからなければエラーになり、メンバー反映プロシージャは呼ばれません。

反映プロシージャは追加と削除を分岐するだけです。UCIDM は追加前にメンバー取得ファンクションで重複を確認しますが、他の経路で登録された行と衝突しないように `INSERT IGNORE` を使っています。

```sql
CREATE PROCEDURE ucidm_apply_member(p JSON)
BEGIN
  DECLARE op VARCHAR(8);
  SET op = p->>'$.opCode';

  IF op = 'I' THEN
    INSERT IGNORE INTO emp_member(group_name, user_name)
      VALUES (p->>'$.groupPrimaryID', p->>'$.memberPrimaryID');
  ELSEIF op = 'D' THEN
    DELETE FROM emp_member
      WHERE group_name = p->>'$.groupPrimaryID' AND user_name = p->>'$.memberPrimaryID';
  END IF;
END
```

メンバー取得ファンクションの戻り値は存在確認にしか使われません。行の有無さえ判別できれば、JSON の中身は何でもかまいません。追加時に JSON が返れば登録済みとしてスキップし、削除時に NULL が返れば何もしません。

```sql
CREATE FUNCTION ucidm_get_member(p JSON) RETURNS JSON READS SQL DATA
RETURN (
  SELECT JSON_OBJECT('group_name', m.group_name, 'user_name', m.user_name)
  FROM emp_member m
  WHERE m.group_name = p->>'$.groupPrimaryID' AND m.user_name = p->>'$.memberPrimaryID'
)
```

## mysql クライアントで動作を確認する \{#verify}

管理画面に登録する前に、mysql クライアントで作成して手で呼び出すのが確実です。

```bash
$ mysql -h 127.0.0.1 -u root -p verify
```

プロシージャの本体にはセミコロンが含まれるため、mysql クライアントで作成するときは文の区切り文字を一時的に変更します。

```sql
DELIMITER $$
CREATE PROCEDURE ucidm_apply_user(p JSON)
BEGIN
...
END$$
DELIMITER ;
```

作成した6つのオブジェクトは `information_schema.routines` で確認します。

```sql
SELECT routine_name, routine_type FROM information_schema.routines
 WHERE routine_schema = DATABASE() AND routine_name LIKE 'ucidm\_%';
```

反映と取得を手で呼び出して、期待どおりのデータが入るかを確認します。

```sql
CALL ucidm_apply_user('{"opCode":"I","primaryID":"user01","enabled":true,
  "attrs":{"cn":"山田太郎","mail":"taro@example.com","empNo":"1042",
  "pwdChanged":"20260220213129Z","department":"営業部"}}');

SELECT ucidm_get_user('{"primaryID":"user01"}');
```

`opCode` を `U`、`P`、`D` に変えて、更新と削除も同じ手順で確認してください。存在しない `primaryID` に対して取得ファンクションが NULL を返すことも確認します。

## 管理画面に登録する \{#register}

動作を確認できたら、CREATE 文を管理画面の対応する項目に貼り付けます。**`DELIMITER` の行は含めないでください**。`DELIMITER` は mysql クライアントの機能であり、サーバーに送る SQL ではありません。UCIDM は登録された定義をそのまま1つの文としてサーバーに送るため、区切り文字を変える必要がありません。同じ理由で、末尾の `END` の後に `$$` を付けたままにしないでください。

UCIDM が先に DROP するので `CREATE OR REPLACE` にする必要はありません。MySQL にはそもそもこの構文がありません。

登録後に UCIDM がインストールを実行するので、[実施しない操作の設定](/resource/ucidm/docs/on-premise/dev/administration/admin-ui/destination/rdbms.md#skip-operations) やドライランモードとあわせて、まず限定した範囲で連携を試すことを勧めます。ドライランモードでは反映プロシージャは呼ばれませんが、取得ファンクションは呼ばれます。

## つまずきやすい点 \{#pitfalls}

### 型変換の失敗が黙って通る \{#cast-silently-fails}

`CAST('abc' AS SIGNED)` はエラーになりません。0 を返して警告を出すだけです。`STR_TO_DATE` も解釈できない文字列に対して NULL を返します。Oracle や PostgreSQL では例外になる操作が、MySQL では既定値のまま格納されて連携が成功してしまいます。

値の妥当性を連携側で保証できない場合は、変換前に検査して、不正なら明示的にエラーにしてください。

```sql
IF p->>'$.attrs.empNo' IS NOT NULL AND p->>'$.attrs.empNo' NOT REGEXP '^[0-9]+$' THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'empNo must be numeric';
END IF;
```

### 複数の文をまとめてロールバックするには明示的な指定が要る \{#transaction}

MySQL の自動コミットは文ごとに働きます。プロシージャの中に複数の `INSERT` を書いても、それぞれが独立したトランザクションになります。途中で失敗すると、それより前の文の結果は残ります。

ユーザーを3つのテーブルに分配する例のように、まとめて成功か失敗かにしたい場合は、プロシージャの中でトランザクションを制御します。`RESIGNAL` を忘れると、エラーが UCIDM に伝わらず連携が成功したことになります。

```sql
CREATE PROCEDURE ucidm_apply_user(p JSON)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;
  -- ここに反映処理を書く
  COMMIT;
END
```

### 真偽値は TINYINT で表す \{#boolean}

MySQL に真偽値型はありません。`TINYINT(1)` のカラムを取得ファンクションでそのまま返すと `0` や `1` という数値になります。JSON の真偽値として返したい場合は `u.active = 1` のように比較式にします。

### エラーメッセージに位置情報が付かない \{#error-message}

Oracle や PostgreSQL と違い、MySQL はプロシージャのどの文で失敗したかを返しません。連携履歴に残るのはデータベースのエラーメッセージだけです。どこで失敗したかを絞り込むには、mysql クライアントで同じ JSON を渡して呼び出し、文を分けて確かめてください。

`SIGNAL SQLSTATE '45000'` で返した `MESSAGE_TEXT` はそのまま履歴に残るので、検査を入れる箇所ではメッセージに文脈を含めておくと調査が楽になります。

### MariaDB での違い \{#mariadb}

MariaDB でも同じ設計で動きますが、2箇所を書き換えます。

1つ目は JSON の値の取り出しです。`p->>'$.opCode'` の代わりに `JSON_VALUE(p, '$.opCode')` を使います。

2つ目は `ON DUPLICATE KEY UPDATE` の書き方です。MySQL 8.0.20 以降で使える行エイリアス (`... VALUES (...) AS new ON DUPLICATE KEY UPDATE email = new.email`) は MariaDB にないので、`VALUES()` 関数の形にします。

```sql
INSERT INTO emp_contact(user_name, email) VALUES (pid, JSON_VALUE(p, '$.attrs.mail'))
  ON DUPLICATE KEY UPDATE email = VALUES(email);
```

残りの構文 (`ELSEIF`、`STR_TO_DATE`、`INSERT IGNORE`、`LAST_INSERT_ID()`、`READS SQL DATA`) はどちらでも同じです。