MySQLの文字列型 CHAR, VARCHAR 詳解
研修医をしているgorogoroumaruと申します。
普段は病院で働いていますが、最近ソフトウェアエンジニアへの転職活動を始めました。転職オファー等あればいつでもご連絡ください。
この記事の対象者
- MySQLのデータ型を知ってdisk効率の良いスキーマ設計をしたい方
- MySQLで各データ型がどのようにdisk上に格納されているか興味のある方
MySQL文字列カラムのタイプ
MySQLの文字列型にはさまざまな型が存在します。以下にその一覧を列挙します。
CHAR
VARCHAR
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT
この記事ではCHAR、VARCHARについての仕様と使い分けについて解説します。
CHAR
CHARは固定長の文字列を格納するデータ型です。カラムを定義する際に格納する文字列のサイズを指定する必要があります。
保存できる文字列の長さは0文字以上255文字以下の範囲となっています。
CREATE TABLE strings (
fixed_five CHAR(5),
fixed_32 CHAR(32)
);
上記の例では、2つの固定長カラムを作成しました。fixed_fiveカラムは最大で5文字を格納でき、fixed_32カラムは最大で32文字を格納できます。
固定長カラムに格納される文字数にかかわらず、常に指定されたスペースの全量を占有することに注意することが重要です。
もし文字列が指定された文字数よりも短かった場合、文字列がスペースでパディングされます。
VARCHAR
VARCHARは可変長の文字列を格納することができるデータ型です。カラムを定義する際に格納する文字列のサイズを指定する必要があります。
指定できる文字数は0文字以上65,535文字以下となっています。
必要なスペースの量は、カラムに格納されるデータに依存します。
CREATE TABLE strings (
variable_length VARCHAR(100)
);
上記の例では、最大100文字を格納できる可変長カラムを作成しました。可変長カラムは指定されたスペースの全量を占有しないため、ストレージに対して効率的です。
CHARとVARCHARのデータサイズ
CHARは「カラム定義時に指定した文字数」×「1文字あたりのバイト数」がストレージ内のデータサイズとなります。
VARCHARは可変長であるため上記に加えて、その文字列の長さをprefixとして格納する必要があり、文字列長に応じて追加バイト数が変わります。
VARCHARの長さprefixのデータサイズ
| 文字列長 | 追加サイズ |
|---|---|
| 256未満 | 1バイト |
| 256以上 | 2バイト |
VARCHARで指定した長さが
CHAR, VARCHARのデータサイズの例 (シングルバイト文字セットを使用した場合)
| 値 | CHAR(4) | 必要なストレージ | VARCHAR(4) | 必要なストレージ |
|---|---|---|---|---|
| a | a | 4バイト | a | 2バイト |
| ab | ab | 4バイト | ab | 3バイト |
| abcde | abcd | 4バイト | abcd | 5バイト |
CHAR(4)では実際に格納する文字列に関わらず４バイトが占有される一方で、VARCHAR(4)の場合は（実際の文字列の長さ）×（1文字あたりのバイト数）＋（長さprefixのデータサイズ）で計算されるバイト数の領域が占有されることが確認できます。
指定した長さを超えた分は切り捨てられます。
Character Set
Character Setは、カラムに入力できる文字を定義します。MySQLは幅広い文字セットをサポートしています。使用可能な文字セットとそのデフォルトのCollationはSHOW CHARACTER SETステートメントで確認できます。utf8とutf8mb4は、MySQLのデフォルトで使用する文字セットです。
mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| binary | Binary pseudo charset | binary | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
.
.
.
Collation
Collationは、2つ以上の文字列を比較またはソートする方法を決定します。Collationは、2つの文字列が等しいかどうかを判断するルールのグループです。
MySQLのデフォルトのCollationはutf8mb4_0900_ai_ciであり、aiは照合がアクセントを区別しないことを、ciは大文字小文字を区別しないことを示しています。
Collationの命名規則
| collation | 意味 |
|---|---|
| _ai | アクセントを区別しない (濁音、半濁音も区別しません 「は」＝「ば」＝「ぱ」) |
| _as | アクセントを区別する |
| _ci | 大文字小文字を区別しない (「A」＝「a」、「あ」＝「ぁ」) |
| _cs | 大文字小文字を区別する |
| _ks | カタカナ,ひらがなを区別する |
| _bin | バイナリ |
例
CREATE TABLE strings (
variable_length VARCHAR(100) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
);
上記の例では、utf8mb4のCharacter Setとutf8mb4_general_ciのCollationを持つVARCHARカラムを作成しました。
まとめ
MySQLで文字列を格納する際には、CHARとVARCHARの違いを理解することが肝要です。
また、Character SetとCollationも重要であり、これらはカラムに格納できる内容や文字列の比較・ソート方法を決定する役割を果たします。
さらに、一部の操作ではMySQLは最大カラムサイズに基づいてメモリを割り当てるため、格納しようとしているデータに最適なデータ型を選択することが非常に重要です。
参考資料
関連リンク
MySQLの整数値型 詳解
MySQLの小数値型 詳解
MySQLのバイナリデータ型 BINARY, VARBINARY 詳解
MySQLのTEXT、BLOB 詳解
MySQLのENUM型 詳解
MySQLの時間データ型 詳解
MySQLのJSON型 詳解
MySQL - innodbにおける各データ型のサイズまとめ
Discussion