Angular Ansible Apache HTTP Server C C++ CMake Composer CSS Cypress date-fns Django Django REST Framework Eigen3 ESLint FastAPI Git GNU Make HTML htmx HTTP JavaScript Julia LaTeX MariaDB Node.js NumPy Octave pandas Perl PHP PostgreSQL Pygame Python PyTorch Qt R SQLite Tailwind CSS

MariaDB: PARAMETERSテーブルでストアドルーチンのパラメータを詳細に理解


Information Schema PARAMETERSテーブルは、MariaDBデータベース内のストアドルーチン（ストアドプロシージャとストアドファンクション）とそのパラメータ、およびストアドファンクションの戻り値に関する情報を格納する重要な情報テーブルです。このテーブルは、データベース内のストアドルーチンの構造と動作を理解するのに役立ち、さまざまな管理タスクを実行するために使用できます。

構造

PARAMETERSテーブルは、以下の列で構成されています。

  • ROUTINE_TYPE: ルーチンのタイプ（PROCEDUREまたはFUNCTION）。
  • DTD_IDENTIFIER: データ型の説明。
  • COLLATION_NAME: 非バイナリ文字列データ型の場合の照合順序。バイナリ文字列データ型の場合はNULLとなります。
  • CHARACTER_SET_NAME: 非バイナリ文字列データ型の場合の文字セット。バイナリ文字列データ型の場合はNULLとなります。
  • DATETIME_PRECISION: 日時パラメータの場合の小数点以下の桁数。時間データ型でない場合はNULLとなります。
  • NUMERIC_SCALE: 数値パラメータの場合の小数点以下の桁数。数値型でない場合はNULLとなります。
  • NUMERIC_PRECISION: 数値パラメータの場合の精度（有効桁数）。数値型でない場合はNULLとなります。
  • CHARACTER_OCTET_LENGTH: マルチバイト文字セットの場合の最大オクテット長。
  • CHARACTER_MAXIMUM_LENGTH: 文字列パラメータの場合の最大長。
  • DATA_TYPE: パラメータのデータ型。
  • PARAMETER_NAME: パラメータの名前。ストアドファンクションの戻り値の場合はNULLとなります。
  • PARAMETER_MODE: パラメータのモード（IN、OUT、INOUT、またはRETURNS）。
  • ORDINAL_POSITION: パラメータの順番（1から始まる）。ストアドファンクションの戻り値の場合は0となります。
  • SPECIFIC_NAME: パラメータを含むルーチンの名前。
  • SPECIFIC_SCHEMA: パラメータを含むルーチンが属するスキーマ（データベース）の名前。
  • SPECIFIC_CATALOG: パラメータを含むルーチンが属するカタログの名前。常に "def" となります。

利点

PARAMETERSテーブルは、以下の利点を提供します。

  • データベースの文書を作成する: PARAMETERSテーブルは、データベースの文書作成に使用できます。このテーブルを使用すると、ストアドルーチンのパラメータと戻り値に関する完全で正確な情報を提供することができます。
  • ストアドルーチンをデバッグする: PARAMETERSテーブルは、ストアドルーチンのデバッグに役立ちます。パラメータ値を確認することで、問題の原因を特定することができます。
  • ストアドルーチンの変更を追跡する: PARAMETERSテーブルは、ストアドルーチンのスキーマ変更を追跡するために使用できます。これにより、ストアドルーチンの互換性を維持し、潜在的な問題を特定することができます。
  • データベース内のストアドルーチンの構造と動作を理解する: PARAMETERSテーブルを使用すると、ストアドルーチンのパラメータ名、データ型、モード、および順序を確認できます。これは、ストアドルーチンの使用方法や、どのように機能するかを理解するのに役立ちます。

使用方法

PARAMETERSテーブルは、以下のSQLステートメントを使用してクエリできます。

SELECT * FROM information_schema.PARAMETERS;

このステートメントは、すべてのストアドルーチンのパラメータと戻り値に関する情報を返します。特定のストアドルーチンのパラメータ情報のみを取得するには、WHERE句を使用できます。

SELECT * FROM information_schema.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'your_schema_name'
  AND SPECIFIC_NAME = 'your_routine_name';

例

以下の例は、employeesデータベース内のget_employee_by_idストアドプロシージャのパラメータに関する情報を取得する方法を示します。

SELECT * FROM information_schema.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'employees'
  AND SPECIFIC_NAME = 'get_employee_by_id';

このクエリは次の結果を返します。

SPECIFIC_CATALOG | SPECIFIC_SCHEMA | SPECIFIC_NAME | ORDINAL_POSITION | PARAMETER_MODE | PARAMETER_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | DTD_IDENTIFIER | ROUTINE_TYPE
-----------------|-----------------|-----------------|-----------------|-----------------|-----------------|-------------|--------------------------|--------------------------|-----------------|-----------------|-----------------|-----------------|-----------------|-----------------|


SELECT * FROM information_schema.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'employees'
  AND SPECIFIC_NAME = 'get_employee_by_id';

SPECIFIC_CATALOG | SPECIFIC_SCHEMA | SPECIFIC_NAME | ORDINAL_POSITION | PARAMETER_MODE | PARAMETER_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | DTD_IDENTIFIER | ROUTINE_TYPE
-----------------|-----------------|-----------------|-----------------|-----------------|-----------------|-------------|--------------------------|--------------------------|-----------------|-----------------|-----------------|-----------------|-----------------|-----------------|
def             | employees       | get_employee_by_id | 1                | IN            | employee_id     | INT             | NULL                    | NULL                    | 10              | 0                | NULL                    | NULL                    | NULL                    | PROCEDURE

例2：特定のデータベース内のすべてのストアドルーチンのパラメータ情報を取得する

この例では、employeesデータベース内のすべてのストアドルーチンのパラメータ情報を取得する方法を示します。

SELECT * FROM information_schema.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'employees';

このクエリは、employeesデータベース内のすべてのストアドルーチンのパラメータに関する情報を返します。

例3：ストアドファンクションの戻り値情報を含むPARAMETERSテーブルをクエリする

この例では、employeesデータベース内のget_employee_salaryストアドファンクションの戻り値情報を含むPARAMETERSテーブルをクエリする方法を示します。

SELECT * FROM information_schema.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'employees'
  AND SPECIFIC_NAME = 'get_employee_salary';

SPECIFIC_CATALOG | SPECIFIC_SCHEMA | SPECIFIC_NAME | ORDINAL_POSITION | PARAMETER_MODE | PARAMETER_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | DTD_IDENTIFIER | ROUTINE_TYPE
-----------------|-----------------|-----------------|-----------------|-----------------|-----------------|-------------|--------------------------|--------------------------|-----------------|-----------------|-----------------|-----------------|-----------------|-----------------|
def             | employees       | get_employee_salary | 0                | RETURNS       | NULL             | DECIMAL(10,2) | NULL                    | NULL                    | 10              | 2                | NULL                    | NULL                    | NULL                    | FUNCTION

例4：PARAMETERSテーブルを使用してストアドルーチンのスキーマ変更を追跡する

この例では、employeesデータベース内のupdate_employee_salaryストアドプロシージャのスキーマ変更を追跡する方法を示します。

CREATE TABLE employee_salary_parameter_history (
  version_id INT PRIMARY KEY AUTO_INCREMENT,
  specific_schema VARCHAR(64) NOT NULL,
  specific_name VARCHAR(64) NOT NULL,
  ordinal_position INT NOT NULL,
  parameter_mode VARCHAR(9) NOT NULL,
  parameter_name VARCHAR(64) NULL,
  data_type VARCHAR(64) NOT NULL,
  character_maximum_length INT NULL,
  character_octet_length INT NULL,
  numeric_precision INT NULL,
  numeric_scale INT NULL,
  datetime_precision INT NULL,
  character_set_name VARCHAR(64) NULL,
  collation_name VARCHAR(64) NULL,
  dtd_identifier VARCHAR(255) NULL,
  routine_type VARCHAR(8) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO employee_salary_parameter_history (
  specific_schema,
  specific_name,
  ordinal_position,
  parameter_mode,
  parameter_name,
  data_type,
  character_maximum_length,
  character_octet_length,
  numeric_precision,
  numeric_scale,
  datetime_precision,
  character_set_name,
  collation_name,
  dtd_identifier,
  routine_type


SELECT SPECIFIC_NAME, PARAMETER_NAME, DATA_TYPE, PARAMETER_MODE
FROM information_schema.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'your_schema_name';

このクエリは、your_schema_nameスキーマ内のすべてのストアドルーチンのパラメータ名、データ型、およびモードに関する情報を返します。

ストアドルーチンのパフォーマンスを最適化する

PARAMETERSテーブルを使用して、ストアドルーチンのパラメータのデータ型とサイズを分析し、パフォーマンスを最適化することができます。たとえば、大きな文字列パラメータをバイナリデータ型に変更することで、パフォーマンスを向上させることができます。

SELECT SPECIFIC_NAME, PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'your_schema_name';

ストアドルーチンの互換性を維持する

PARAMETERSテーブルを使用して、ストアドルーチンのスキーマ変更を追跡し、互換性を維持することができます。これにより、データベースのアップグレードやマイグレーション中に問題が発生するのを防ぐことができます。

CREATE TABLE employee_salary_parameter_history (
  version_id INT PRIMARY KEY AUTO_INCREMENT,
  specific_schema VARCHAR(64) NOT NULL,
  specific_name VARCHAR(64) NOT NULL,
  ordinal_position INT NOT NULL,
  parameter_mode VARCHAR(9) NOT NULL,
  parameter_name VARCHAR(64) NULL,
  data_type VARCHAR(64) NOT NULL,
  character_maximum_length INT NULL,
  character_octet_length INT NULL,
  numeric_precision INT NULL,
  numeric_scale INT NULL,
  datetime_precision INT NULL,
  character_set_name VARCHAR(64) NULL,
  collation_name VARCHAR(64) NULL,
  dtd_identifier VARCHAR(255) NULL,
  routine_type VARCHAR(8) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO employee_salary_parameter_history (
  specific_schema,
  specific_name,
  ordinal_position,
  parameter_mode,
  parameter_name,
  data_type,
  character_maximum_length,
  character_octet_length,
  numeric_precision,
  numeric_scale,
  datetime_precision,
  character_set_name,
  collation_name,
  dtd_identifier,
  routine_type
)
SELECT * FROM information_schema.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'employees'
  AND SPECIFIC_NAME = 'update_employee_salary';

この例では、update_employee_salaryストアドプロシージャのスキーマ変更を追跡する方法を示します。

ドキュメントを生成する

PARAMETERSテーブルを使用して、データベースのストアドルーチンのドキュメントを生成することができます。これにより、開発者やデータベース管理者がストアドルーチンの使用方法を理解しやすくなります。

SELECT
  SPECIFIC_NAME AS 'Routine Name',
  PARAMETER_NAME AS 'Parameter Name',
  DATA_TYPE AS 'Data Type',
  PARAMETER_MODE AS 'Mode',
  CHARACTER_MAXIMUM_LENGTH AS 'Max Length',
  NUMERIC_PRECISION AS 'Precision',
  NUMERIC_SCALE AS 'Scale'
FROM information_schema.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'your_schema_name'
ORDER BY SPECIFIC_NAME, ORDINAL_POSITION;

このクエリは、your_schema_nameスキーマ内のすべてのストアドルーチンのパラメータに関する情報を、ストアドルーチン名とパラメータ名の順序で返します。

テストデータを生成する

PARAMETERSテーブルを使用して、ストアドルーチンのテストデータを生成することができます。これにより、ストアドルーチンの機能を十分にテストすることができます。

SELECT
  DATA_TYPE,
  RAND() * (
    CASE
      WHEN DATA_TYPE IN ('INT', 'BIGINT') THEN 10000
      WHEN DATA_TYPE IN ('DECIMAL', '