MySQL の文字列型と文字セット

MySQL の文字列カラムには、論理的な文字数制限と物理的なバイト表現の両方があります。これらの考えを分けて扱うと、多言語テキストやインデックスに関する多くの問題を防げます。

`CHAR` と `VARCHAR` #

CHAR(n) は固定長の文字列です。MySQL はストレージ上の意味として値を宣言された長さにパディングするため、国コードなど、本当に固定幅の値に適しています。VARCHAR(n) は可変長であり、名前、ラベル、その他の長さが変化するテキストには通常こちらが適しています。

どちらの場合も、n はバイト数ではなく最大 文字数 です。したがって、文字セットが対応していれば、VARCHAR(10) カラムには 10 個の絵文字または 10 個の中国語文字を格納できます。ただし、これらの文字はディスク上で 10 バイトを超えて使用します。

sql

CREATE TABLE profile (
  country_code CHAR(2) NOT NULL,
  display_name VARCHAR(100) NOT NULL
) CHARACTER SET utf8mb4;

`TEXT` 型 #

大きな可変長テキストには、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT を使用します。制限はバイト単位で測定されるため、実際に格納できる文字数はカラムの文字セットによって異なります。TEXT カラムにはインデックスを付けられますが、大きな構成や古い MySQL 構成ではインデックスプレフィックスが必要になる場合があります。長いテキストを中心にインデックスを設計する前に、エンジンと MySQL のバージョンを確認してください。

文字セットと照合順序 #

文字セットは、文字をバイトとしてエンコードする方法を定義します。新しい MySQL アプリケーションでは、Unicode の全範囲をサポートする utf8mb4 が一般に安全な選択です。MySQL の歴史的な utf8 という名前は 3 バイト実装を指すことがあり、すべての Unicode 文字を表すものではありません。

照合順序は、文字セットの比較とソートの規則を定義します。比較で大文字と小文字、アクセントを区別するかどうか、および文字列のソート方法を決定します。文字セットと照合順序は、サーバー、データベース、テーブル、カラムのレベルで設定できます。テーブルまたはカラムに明示的な設定を行うと、アプリケーションの期待が明確になります。

sql

CREATE TABLE article (
  title VARCHAR(255) CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci NOT NULL
);

アプリケーションが必要とする比較に基づいて照合順序を選択します。識別子にはバイナリまたは大文字と小文字を区別する比較が適切な場合があり、人間向けの検索にはアクセントと大文字と小文字を区別しない照合順序が適している場合があります。

バイナリ文字列型 #

BINARY と VARBINARY はテキストではなくバイトを格納します。binary 文字セットと照合順序を使用するため、MySQL は文字の変換や言語的な比較規則の適用を行いません。BINARY(n) は格納値を 0x00 でパディングしますが、VARBINARY(n) はパディングしません。より大きなバイト列には BLOB 型を使用します。接続の変換と照合順序の規則によって解釈が変わる可能性があるため、暗号化データ、ダイジェスト、プロトコルバイトを文字カラムに格納しないでください。

sql

CREATE TABLE artifact (
  sha256 BINARY(32) NOT NULL,
  payload LONGBLOB NOT NULL,
  PRIMARY KEY (sha256)
);

デフォルト、接続設定、インデックス #

文字セットと照合順序のデフォルトは、サーバー、データベース、テーブル、カラムの設定から解決されます。文字列リテラルにも文字セットと照合順序があります。接続は character_set_client、character_set_connection、character_set_results を宣言します。ドライバーの utf8mb4 設定を使用するか、設定しないクライアントには SET NAMES utf8mb4 を発行します。

パッケージのデフォルトを仮定せず、有効な設定を確認します。

sql

SHOW CREATE TABLE article;
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

インデックスの制限はバイト単位で測定されます。utf8mb4 の VARCHAR(255) は、インデックスメタデータの前に、文字データだけで最大 1,020 バイトを必要とする可能性があります。現在の MySQL リリースの InnoDB は大きなインデックスキーをサポートしますが、設計では選択した行形式、ページサイズ、MySQL バージョンを考慮する必要があります。上限のある検索キーにインデックスを設定し、プレフィックスが選択性を維持する場合にのみプレフィックスインデックスを使用してください。