MySQL データ型のストレージ要件と正しい型の選択方法

1. カラムタイプのストレージ要件

MySQL でサポートされている各カラムタイプのストレージ要件をカテゴリ別にリストします。

MyISAM テーブルの行の最大サイズは 65,534 バイトです。各 BLOB 列と TEXT 列は、そのうちの 5 ～ 9 バイトのみを占めます。

MyISAM テーブルに可変長の列タイプが含まれる場合、レコード形式も可変長になります。テーブルを作成するとき、MySQL は特定の条件下でカラムを可変長タイプから固定長タイプに、またはその逆に変更できます。

数値型の記憶域要件

列型の記憶域要件

TINYINT 1バイト

SMALLINT 2バイト

MEDIUMINT 3バイト

INT、INTEGER 4バイト

BIGINT 8バイト

FLO AT(p) 0

FLOAT 4 バイト

DOUBLE [PRECISION]、項目 REAL 8 バイト

DECIMAL(M,D), NUMERIC(M) ,D) 可変長; 以下の説明を参照

BIT(M) 約 (M+7)/8 バイト

DECIMAL(および NUMERIC) ストレージ要件はバージョン固有です:

10 進数 9 を圧縮するためにバイナリ形式を使用します (10- based) 数値を 4 バイトに分割して DECIMAL 列の値を表します。各値の整数部と小数部の格納方法は個別に決定されます。 9 桁の各倍数には 4 バイトが必要で、「残りの」ビットには 4 バイトの一部が必要です。次の表は、余分なビットのストレージ要件を示しています:

残りのバイト

ビット数

0 0

1 1 1

2 1

3 2

4 2

5

6 3

7 4

8 4

9 4

日付と時刻型の記憶域要件

列型 記憶域要件

DATE 3バイト

DATE TIME 8バイト

TIMESTAMP 4バイト

TIME 3バイト

年1 バイト

文字列型の記憶域要件

列型の記憶域要件

CHAR(M) M バイト、0

VARCHAR (M) L+1 バイト、L

BINARY(M) M バイト、0

VARBINARY(M) L+1 バイト、L

TINYBLOB、TINYTEXT L+1 バイト、L

BLOB、TEXT L+2 バイト、うち L

MEDIUMBLOB、MEDIUMTEXT L+3 バイト、そのうち L

LONGBLOB、LONGTEXT L+4 バイト、うち L

ENUM('value1','value2',...) 列挙の数に応じて 1 バイトまたは 2 バイト値 (最大 65,535 個の値)

SET('value1','value2',.. .) セット メンバーの数に応じて 1、2、3、4 または 8 バイト (最大 64 メンバー)

VARCHAR、BLOB、TEXT クラスは可変長型です。各型の記憶域要件は、型の最大許容サイズではなく、列値の実際の長さ (上の表では L で示されています) によって決まります。たとえば、VARCHAR(10) 列には、最大長 10 の文字列を保持できます。実際のストレージ要件は、文字列の長さ (L) に、文字列の長さを記録するバイトを加えたものです。文字列「abcd」の場合、L は 4 であり、記憶域には 5 バイトが必要です。

CHAR、VARCHAR、TEXT 型の場合、前の表の値 L と M は文字数として解釈され、列定義内のこれらの型の長さが文字数を表します。たとえば、TINYTEXT 値を保存するには、L 文字 + 1 バイトが必要です。

特定の CHAR、VARCHAR、または TEXT 列の値を格納するために使用されるバイト数を計算するには、その列で使用されている文字セットを考慮する必要があります。 Unicode を使用する場合、すべての Unicode 文字が同じバイト数を使用することに留意する必要があります。

注: VARCHAR 列の有効な最大長は 65,532 文字です。

NDBCLUSTER エンジンは固定幅の列のみをサポートします。これは、MySQL Cluster のテーブル内の VARCHAR カラムが CHAR 型のように動作することを意味します (ただし、各レコードにはまだ余分なバイトのスペースが残っています)。たとえば、Cluster テーブルでは、VARCHAR(100) として宣言された列の各レコードは、実際に格納されるレコードの文字列の長さに関係なく、格納時に 101 バイトを占有します。

BLOB クラスと TEXT クラスでは、クラスの最大可能長に応じて、列値の長さを記録するために 1、2、3、または 4 バイトが必要です。

NDB Cluster ストレージ エンジンでは、TEXT 列と BLOB 列の実装が異なり、TEXT 列の各レコードは 2 つの別個の部分で構成されます。 1 つは固定サイズ (256 バイト) で、実際には元のテーブルに保存されます。もう 1 つは、暗黙的なテーブルに保持される 256 バイトを超えるデータを含みます。 2 番目のテーブルのレコードの長さは常に 2,000 バイトです。これは、size

ENUM オブジェクトのサイズは、異なる列挙値の数によって決まります。列挙には 1 バイトが使用され、255 個の可能な値を含めることができます。列挙値が 256 ～ 65,535 の場合、2 バイトが使用されます。

SET オブジェクトのサイズは、異なるセット メンバーの数によって決まります。設定サイズが N の場合、オブジェクトは (N+7)/8 バイトを占め、1、2、3、4、または 8 バイトに四捨五入されます。 SET には最大 64 のメンバーを含めることができます。

2. 正しいデータ型を選択します

ストレージを最適化するには、どのような場合でも最も正確な型を使用する必要があります。たとえば、列の値の範囲が 1 ～ 99999 の場合、整数を使用する場合は MEDIUMINT UNSIGNED が適切な型です。この型は、列値を表すことができるすべての型の中で最も少ないストレージを使用します。

DECIMAL 列のすべての基本的な計算 (+、-、​​、/) を、10 進数 65 桁 (10 を基準) の精度で実行します。

倍精度演算を使用して、DECIMAL 値の計算を実行します。精度をあまり重視しない場合、または速度を最優先する場合は、DOUBLE タイプで十分です。高精度を実現するために、BIGINT に格納されている固定小数点型への変換を実行できます。これにより、すべての計算を 64 ビット整数で実行できるようになり、必要に応じて結果を浮動小数点値に変換し直すことができます。

3. 他のデータベース エンジンのカラム タイプを使用する

他のベンダーによって作成された SQL 実行コードを使用するために、MySQL は次の表に示すようにカラム タイプをマップします。テーブル定義は、次のマッピングを通じて他のデータベース エンジンから MySQL に簡単にインポートできます:

その他の販売者のタイプ MySQL のタイプ

BOOL、TINYINT

BOOLEAN TINYINT

CHAR VARYING(M) VARCHAR(M)

DEC DECIMAL

FIXED DECIMAL

FLOAT4 FLOAT

FLOAT8 DOUBLE

INT1 TINYINT

INT2 SMALLINT

INT3 MEDIUMINT

INT4 INT

INT8 BIGINT

LONG INARY MEDIUMBLOB

LONG VARCHAR MEDIUMTEXT

LONG MEDIUMTEXT

MIDDLEINT MEDIUMINT

NUMERIC

DECIMAL

テーブルの作成時に列の型がマップされ、元の型定義は破棄されます。別のベンダーのタイプを使用してテーブルを作成し、DESCRIBE tbl_name ステートメントを実行すると、MySQL は同等の MySQL タイプを使用してテーブルの構造をレポートします。例:

mysql> CREATE TABLE t (a BOOL, b FLOAT8, c LONG, d NUMERIC);Query OK, 0 rows affected (0.08 sec);
mysql> DESCRIBE t;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| a     | tinyint(1)    | YES  |     | NULL    |       |
| b     | double        | YES  |     | NULL    |       |
| c     | mediumtext    | YES  |     | NULL    |       |
| d     | decimal(10,0) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)