MySQL サーバーとデータ型

MySQL は、サーバーの初期化、スキーマ定義、接続設定を分離しています。本番環境へのインストールでは、空のデータディレクトリを一度だけ初期化し、そのディレクトリの外部に設定を保存し、プラットフォームのサービスマネージャーを通じてサーバーを管理する必要があります。mysqld --initialize はデータディクショナリと初期管理アカウントを作成しますが、アプリケーションスキーマや権限は設定しません。

アカウント、権限付与、ロール #

MySQL はユーザーとホストの両方でアカウントを識別するため、'app'@'localhost' と 'app'@'10.%' は別個のIDです。明示的な認証ポリシーを指定してアカウントを作成します。

sql

CREATE USER IF NOT EXISTS 'app'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'generated-secret';

MySQL 8 では、CREATE USER がIDを作成し、GRANT が権限を割り当てます。GRANT はアカウントを作成しません。ホストの照合は認証前に行われ、その後、ロールと直接付与された権限によって認可が決まります。実効権限を確認するには SHOW GRANTS を使用します。ロールは権限をグループ化し、複数のアカウントで再利用できるようにします。

sql

CREATE ROLE 'order_reader';
GRANT SELECT ON app.orders TO 'order_reader';
GRANT 'order_reader' TO 'app'@'localhost';
SET DEFAULT ROLE 'order_reader' TO 'app'@'localhost';

アカウント管理はアプリケーションのマイグレーションテーブルの外部で行います。アプリケーションスキーマは、場当たり的な権限変更が行われても有効性を維持できるように設計してください。

数値型 #

整数型は表示幅ではなく、値の範囲から選択します。TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT は、それぞれ 1、2、3、4、8 バイトを使用します。符号なし型は範囲を非負値に移しますが、データベース間の移植性に関する懸念が生じます。BOOLEAN と BOOL は TINYINT(1) のエイリアスです。

通貨など、10進演算が必要な数量には DECIMAL(p, s) を使用します。p は10進数全体の桁数、s は小数点以下の桁数です。FLOAT と DOUBLE は2進浮動小数点値を表すため、10進小数を丸めることがあります。等価性が重要な金額には使用しないでください。

時間型と構造化された値 #

DATE は暦日を格納し、TIME は時刻または期間を格納します。
DATETIME は、タイムゾーン変換を行わず、文字どおりの日付と時刻を格納します。
TIMESTAMP は瞬間として格納され、サポートされる値について、セッションのタイムゾーンと UTC の間で変換されます。
JSON は JSON を検証し、JSON 関数を提供しますが、その値は結合やインデックスで使用するリレーショナル列の代替にはなりません。

ローカルの予定を表す値には、DATETIME と明示的な業務上のタイムゾーンを使用します。イベントの発生時点には UTC の TIMESTAMP または UTC の DATETIME を使用し、アプリケーションで変換する境界を明示します。

時間型の列では、DATETIME(6) のように、0 から 6 までの小数秒精度を宣言できます。厳格な SQL モードでは無効な日付を拒否する必要があります。ゼロ日付を制御するモードはバージョンによって異なるため、スキーマでは欠損値のセンチネルとして '0000-00-00' を使用しないでください。代わりに NULL または明示的なステータスを使用します。

テキスト、バイナリ、JSON、空間値 #

CHAR と VARCHAR は、文字セットと照合順序を通じて解釈される文字列を格納します。BINARY と VARBINARY はバイト列を格納し、TEXT と BLOB の各ファミリーは、より大きな文字値とバイナリ値を提供します。インデックス幅はバイト単位で測定されるため、utf8mb4 のキーは文字数から想定されるより多くのインデックス領域を消費する場合があります。

MySQL は JSON をバイナリ形式で格納し、パス、抽出、変更、検証の各関数を公開します。頻繁にクエリされるスカラーのパスには、生成列またはサポートされている関数インデックスを使用してインデックスを作成します。POINT、LINESTRING、POLYGON などの空間型はジオメトリを保持します。地理的な意味が重要な場合は、空間参照系を宣言して検証します。

スキーマの検査と制限 #

テーブルの実効 DDL を確認するには SHOW CREATE TABLE を使用し、移植可能なメタデータクエリには INFORMATION_SCHEMA を使用します。

sql

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'app' AND table_name = 'orders'
ORDER BY ordinal_position;

ストレージの制限は、ストレージエンジン、ページサイズ、行形式、文字セット、MySQL のリリースによって異なります。SHOW ENGINES、SHOW TABLE STATUS、SHOW CREATE TABLE を使用すると、実効エンジンとテーブルのプロパティを確認できます。スキーマジェネレーターに固定的な前提を組み込むのではなく、デプロイするバージョンの行制限とインデックス制限を確認してください。関連する設計の詳細については、MySQL の文字列型と文字セット、MySQL のインデックス、MySQL のテーブルとビューを参照してください。