MySQL CHARとVARCHARの保存と読み取りの違い

導入

保存時と読み取り時に CHAR 型と VARCHAR 型の違いを本当にご存知ですか?

まずいくつかの結論を述べたいと思います。

1. CHAR は、ユーザーがデータを挿入するときに末尾にスペースを含めるかどうかに関係なく、保存する前に常にスペースで埋められます。

2. VARCHAR は格納時にスペースを埋めて格納しませんが、挿入時にユーザーが明示的にスペースを追加した場合は、そのまま格納され、削除されません。

3. データを読み取るとき、CHAR は常に末尾のスペースを削除します (書き込み時にスペースが含まれている場合でも)。

4. データを読み取るとき、VARCHAR は常に以前に格納された値を忠実に取得します (格納時に末尾のスペースがある場合、それは保持され続け、CHAR のように末尾のスペースは削除されません)。

以下はテスト検証プロセスです。

1. CHAR型をテストする

テーブル構造:

テーブル `tchar` を作成します (
 `id` int(10) unsigned NOT NULL DEFAULT '0',
 `c1` char(20) NOT NULL デフォルト ''
 主キー (`id`)
)ENGINE=InnoDB デフォルト文字セット=utf8mb4;

いくつかのレコードを挿入します:

tchar値に挿入します（1、concat（'a'、repeat（' '、19）））;
tchar値に挿入します（2、concat（' '、repeat（'a'、19）））;
tchar値に挿入します（3、 'a'）;
tchar値に挿入します（4、 ' '）;
tchar値に挿入（5、''）;

ストレージ構造を表示します。

(1) INFIMUMレコードオフセット:99 ヒープ番号:0 ...
(2) SUPREMUMレコードオフセット:112 ヒープ番号:1 ...
（３）通常レコード オフセット：126 ヒープ番号：2 ... <- id=1
(4) 通常レコード オフセット:169 ヒープ番号:3 ... <- id=2
(5) 通常レコード オフセット:212 ヒープ番号:4 ... <- id=3
(6) 通常レコード オフセット:255 ヒープ番号:5 ... <- id=4
(7) 通常レコード オフセット:298 ヒープ番号:6 ... <- id=5

これを見ると少し混乱しますか? 私がお勧めしたツールを覚えていますか? こちらをご覧ください: innblock | InnoDB ページ監視ツール。

ご覧のとおり、文字列の長さに関係なく、各レコードは実際には 43 (169-126=43) バイトを占めます。したがって、結論1が成り立ちます。
簡単に言うと、43 バイトの起源は次のとおりです。
DB_TRX_ID、6 バイト。
DB_ROLL_PTR、7 バイト。
id、int、4 バイト。
c1、char(20)、20バイト。CHAR型なので、追加のバイトが必要です。
各レコードには、常に追加の 5 バイトのヘッダー情報 (行ヘッダー) が必要です。
合計は 43 バイトになります。

tchar テーブルを読み取った結果を見てみましょう。

tcharからid、concat('000'、c1、'$$$')、length(c1)を選択します。
+----+----------------------------+------------+
| id | 連結('000',c1,'$$$') | 長さ(c1) |
+----+----------------------------+------------+
| 1 | 000a$$$ | 1 | <- 末尾のスペースを削除 | 2 | 000 aaaaaaaaaaaaaaaaaaaa$$$ | 20 |
| 3 | 000a$$$ | 1 |
| 4 | 000$$$ | 0 | <- 末尾のスペースを削除すると、結果は id=5 と同じになります | 5 | 000$$$ | 0 |
+----+----------------------------+------------+

2. VARCHAR型のテスト

テーブル構造:

テーブル `tvarchar` を作成します (
 `id` int(10) unsigned NOT NULL DEFAULT '0',
 `c1` varchar(20) NOT NULL デフォルト ''
 主キー (`id`)
) エンジン=InnoDB デフォルト文字セット=utf8mb4

いくつかのレコードを挿入します:

tvarchar値に挿入します（1、concat（'a'、repeat（' '、19）））;
tvarchar値に挿入します（2、concat（' '、repeat（'a'、19）））;
tvarchar値に挿入します（3、 'a'）;
tvarchar値に挿入します（4、 ' '）;
tvarchar値に挿入（5、''）;
tvarchar値に挿入（6、''）;

ストレージ構造を表示します。

(1) INFIMUMレコードオフセット:99 ヒープ番号:0 ...
(2) SUPREMUMレコードオフセット:112 ヒープ番号:1 ...
（３）通常レコード オフセット：126 ヒープ番号：2 ... <- id=1
(4) 通常レコード オフセット:169 ヒープ番号:3 ... <- id=2
(5) 通常レコード オフセット:212 ヒープ番号:4 ... <- id=3
(6) 通常レコード オフセット:236 ヒープ番号:5 ... <- id=4
(7) 通常レコード オフセット:260 ヒープ番号:6 ... <- id=5
(8) 通常レコード オフセット:283 ヒープ番号:7 ... <- id=6

いくつかのレコードのバイト数は 43、43、24、24、23、23 であることがわかります (最後のレコードは id=5 のレコードと同じです)。
上記の結果は少し意外ですね。特に、id=1 のレコード ('a... の後に 19 個のスペースが挿入されています) は 43 バイトを消費します。これは、上記の結論 2 を証明しています。
同様に、id=3 と id=4 の 2 つのレコードはどちらも 24 バイトを占め、id=5 と id=6 の 2 つのレコードはどちらも 23 バイトを占めます (文字列を格納するための追加のバイト数はなく、id 列用の 4 バイトのみです)。

tvarchar テーブルを読み取った結果を見てみましょう。

tvarchar から id、concat('000'、c1、'$$$')、length(c1) を選択します。
+----+----------------------------+------------+
| id | 連結('000',c1,'$$$') | 長さ(c1) |
+----+----------------------------+------------+
| 1 | 000a $$$ | 20 | <- 読み取り結果では末尾のスペースは削除されません | 2 | 000 aaaaaaaaaaaaaaaaaa$$$ | 20 |
| 3 | 000a$$$ | 1 |
| 4 | 000 $$$ | 1 | <- このスペースは読み取り結果では削除されません | 5 | 000$$$ | 0 |
| 6 | 000$$$ | 0 |
+----+----------------------------+------------+

一般的に、2 つの結論を導き出すことができます。
1. 読み取り結果から、CHAR 型の列は保存時にスペースを食っているように見えますが、実際には読み取り時にのみ食われています (表示レベルでスペースは削除されます)。
2. 読み取り結果から、VARCHAR 型の列に余分なスペースが残っているように見えます。実際には、これらのスペースは読み取り時にのみ復元されます (ただし、実際の物理ストレージではこれらのスペースは削除されます)。

最後に、ドキュメントに何が書かれているか見てみましょう。

CHAR値が保存されるとき、右側にスペースが埋め込まれ、
つまり、CHAR 列は長さを補うために末尾にスペースが入った状態で保存されます。
CHAR値が取得されると、末尾のスペースは削除されます。
PAD_CHAR_TO_FULL_LENGTH SQL モードが有効になっています。
つまり、sql_mode 値 PAD_CHAR_TO_FULL_LENGTH=1 が設定されていない限り、CHAR 列は末尾のスペースが削除されて読み取られます。
VARCHAR 値は保存時にパディングされません。
つまり、VARCHAR を保存するときに末尾のスペースは追加されません。
末尾のスペースは、値が保存および取得されるときに保持されます。
標準 SQL に準拠しています。つまり、VARCHAR が読み取られるときにスペースが表示されます。

上記のテストで使用されたバージョンと環境は次のとおりです。

mysql> バージョンを選択()\G
...
バージョン(): 8.0.15

mysql> @@sql_mode\G を選択
...
@@sql_mode: ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_ENGINE_SUBSTITUTION

参照ドキュメント

11.4.1 CHAR 型と VARCHAR 型、https://dev.mysql.com/doc/refman/5.7/en/char.html

上記はMySQL CHARとVARCHARストレージの違いの詳細な内容です。MySQL CHARとVARCHARの詳細については、123WORDPRESS.COMの他の関連記事に注目してください。

以下もご興味があるかもしれません: