Mysql の varchar 型に関する注意点

varchar の保存ルール

4.0 未満のバージョンでは、varchar(20) は 20 バイトを意味します。UTF8 中国語文字を保存する場合、保存できるのは 6 文字 (中国語文字ごとに 3 バイト) だけです。
バージョン5.0以降では、varchar(20)は20文字を意味します。数字、文字、UTF8中国語文字（各中国語文字は3バイト）に関係なく、20文字を保存でき、最大サイズは65532バイトです。
varchar フィールドには、クラスター化インデックスの外部に実際のコンテンツが別途保存され、コンテンツの先頭では実際の長さを示すために 1 ～ 2 バイトが使用されます。
公式声明では次のように述べられています。

VARCHAR 列の値は可変長文字列です。長さは、MySQL 5.0.3 より前では 0 から 255 まで、5.0.3 以降のバージョンでは 0 から 65,535 までの値で指定できます。
CHAR とは対照的に、VARCHAR 値は 1 バイトまたは 2 バイトの長さプレフィックスとデータとして保存されます。長さプレフィックスは値のバイト数を示します。
列は、値に必要なバイト数が 255 バイト以下の場合は 1 バイトの長さを使用し、値に必要なバイト数が 255 バイトを超える場合は 2 バイトの長さを使用します。

varcharとcharの違い

違い1: 固定長と可変長

char は固定長を意味し、varchar は可変長を意味します。挿入された文字列が長さを超えた場合、状況に応じて処理されます。厳密モードの場合、挿入は拒否され、エラーメッセージが表示されます。緩いモードの場合、インターセプトされてから挿入されます。挿入した文字列の長さが定義した長さより短い場合は、char(10)のように10文字が格納されているという意味で異なる扱いになります。挿入する文字数に関係なく10文字になります。10文字未満の場合はスペースで埋められます。 varchar(10)が10未満の場合、挿入された文字数と同じ数の文字が格納されます。
varchar は保存された文字列の長さをどのようにして知るのでしょうか?実際、varchar フィールドの場合、文字列の長さを格納するには 1 バイト (文字列の長さが 255 未満の場合) または 2 バイト (長さが 255 を超える場合) が必要です。しかし、特定のバイト数を示すプレフィックスが必要です (varchar は可変長なので、この長さの値がないとデータの読み方がわかりません)。

2つ目の違いはストレージ容量です。

char の場合、エンコードに関係なく、保存できる文字の最大数は 255 です。
varchar は最大 65532 文字を保存できます。 VARCHAR の最大有効長は、最大行サイズと使用される文字セットによって決まります。全体の最大長は65,532バイトです

VARCHAR エンコードの長さ制限

文字タイプが gbk の場合、各文字は最大 2 バイトを占め、最大長は 32766 を超えることはできません。文字タイプが utf8 の場合、各文字は最大 3 バイトを占め、最大長は 21845 を超えることはできません。 定義中に上記の制限を超えると、varchar フィールドは強制的にテキスト型に変換され、警告が生成されます。

行の長さ制限

実際のアプリケーションで varchar の長さ制限が発生する原因は、行定義の長さです。 MySQL では、行の定義長が 65535 を超えないようにする必要があります。定義されたテーブルの長さがこの値を超えると、エラー 1118 (42000): 行サイズが大きすぎます。使用されているテーブル タイプの最大行サイズは、BLOB を除いて 65535 です。一部の列を TEXT または BLOB に変更する必要があります。
つまり、たとえば、テーブル構造に 2 つの varhcar タイプのフィールドを持つテーブルを作成する場合、これら 2 つのフィールドの合計長は 65535 を超えることはできません。
公式の説明は次のとおりです。

各テーブルの最大行サイズは 65,535 バイトです。
この最大値はすべてのストレージ エンジンに適用されますが、特定のエンジンには追加の制約があり、有効な最大行サイズが低くなる場合があります。

varchar の制御ビット

MySQL の Varchar 文字型では、他の制御情報用に 1 バイトも予約されています。

例

例 1: テーブルに VARCHAR(N) 型と utf8 エンコードのフィールドが 1 つだけある場合、N の最大値はいくらですか?

たとえば、create table tb_name1(a varchar(N)) default charset=utf8 の場合、N の最大値は (65535-1-2)/3=21844 になります。
マイナス 1 の理由は、実際の行の格納が 2 番目のバイトから始まるためです。
2 を減算する理由は、varchar ヘッダーの 2 バイトが長さを表すためです。
3 で割る理由は、文字エンコードが utf8 だからです。
SQL テスト:

テーブル tb_name1(a varchar(21844)) を作成します。デフォルトの文字セットは utf8 です。
クエリは正常、影響を受けた行は 0 行 (0.38 秒)

テーブル tb_name1 を削除します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

テーブル tb_name1(a varchar(21845)) を作成します。デフォルトの文字セットは utf8 です。
エラー 1118 (42000): 行サイズが大きすぎます。使用されているテーブルタイプの最大行サイズは、BLOB を除いて 65535 です。いくつかの列を変更する必要があります。

例 2: テーブルに VARCHAR(N) 型のフィールドと、utf8 でエンコードされた他のフィールド型がある場合、N の最大値はいくらでしょうか。

たとえば、create table tb_name2(a int, b char(20), c varchar(N)) default charset=utf8; です。
したがって、Nの最大値 = (65535-1-2-4-203)/3 = 21822
マイナス 1 の理由は、実際の行の格納が 2 番目のバイトから始まるためです。
2 を減算する理由は、varchar ヘッダーの 2 バイトが長さを表すためです。
4 を減算する理由は、a フィールドの int 型が 4 バイトを占めるためです。
203 を減算する理由は、char(20) が 60 バイトを占め、エンコーディングが utf8 であるためです。

SQL テスト:

テーブル tb_name2(a int, b char(20), c varchar(21822)) を作成します。デフォルトの文字セットは utf8 です。
クエリは正常、影響を受けた行は 0 行 (0.28 秒)

テーブル tb_name2 を削除します。
クエリは正常、影響を受けた行は 0 行 (0.20 秒)

テーブル tb_name2(a int, b char(20), c varchar(21823)) を作成します。デフォルトの文字セットは utf8 です。
エラー 1118 (42000): 行サイズが大きすぎます。使用されているテーブル タイプの最大行サイズは、BLOB を除いて 65535 です。一部の列を TEXT または BLOB に変更する必要があります。

例 3: テーブルに VARCHAR(N) タイプの複数のフィールドと他のフィールド タイプ、gbk エンコーディングがある場合、N の最大値はいくらですか?

たとえば、create table tb_name3(a int, b char(20), c varchar(50), d varchar(N)) default charset=gbk; です。
したがって、Nの最大値 = (65535-1-1-2-4-202-502)/2 = 32693
最初のマイナス 1 の理由は、実際の行の格納が 2 番目のバイトから始まるためです。
2番目から1を引いた値は、2番目のvarchar(50)に長さを示す1バイトのヘッダー(255未満)があることを意味します。
2 を減算する理由は、varchar ヘッダーの 2 バイトが長さを表すためです。
202 を減算する理由は、char(20) が 40 バイトを占め、エンコーディングが gbk であるためです。
502 を減算する理由は、varchar(50) が 100 バイトを占め、エンコーディングが gbk であるためです。

SQL テスト:

テーブル tb_name3(a int, b char(20), c varchar(50), d varchar(32694)) を作成します。デフォルトの文字セットは gbk です。
エラー 1118 (42000): 行サイズが大きすぎます。使用されているテーブル タイプの最大行サイズは、BLOB を除いて 65535 です。一部の列を TEXT または BLOB に変更する必要があります。

テーブル tb_name3(a int, b char(20), c varchar(50), d varchar(32693)) を作成します。デフォルトの文字セットは gbk です。
クエリは正常、影響を受けた行は 0 行 (0.18 秒)

上記はMysqlのvarchar型で注意すべき点の詳細です。Mysqlのvarchar型の詳細については、123WORDPRESS.COMの他の関連記事にも注目してください。