MySQL で使える主な型一覧 #MySQL

数値型（整数型）

数値型の中でも整数を扱う型は以下の種類があり、格納できる数値の範囲に違いがあります。

型	格納できる範囲	UNSIGNED をつけた場合の格納できる範囲
TINYINT	-128 ～ 127	0 ～ 255
SMALLINT	-32,768 ～ 32,767	0 ～ 65,535
MEDIUMINT	-8,388,608 ～ 8,388,607	0 ～ 16,777,215
INT、INTEGER	-2,147,483,648 ～ 2,147,483,647	0 ～ 4,294,967,295
BIGINT	-9,223,372,036,854,775,808 ～ 9,223,372,036,854,775,807	0 ～ 18,446,744,073,709,551,615

UNSIGNED の使い方

通常、整数型は負の数も格納することができますが、UNSIGNED を指定すると正の数しか格納できなくなります。その代わりに正の格納できる範囲が広くなります。

CREATE TABLE users (age INT UNSIGNED);

ZEROFILL の使い方

整数の型に ZEROFILL を指定すると、表示桁数に満たない値を 0 で埋めることができます。また、ZEROFILL を指定する場合は、自動的に UNSIGNED が付加された状態になります。

CREATE TABLE users (age INT ZEROFILL);

数値型の表示桁数の指定方法

整数の型に表示桁数を指定するときは、型指定の際に後ろにカッコをつけて数字を指定できますが、これは ZEROFILL 使用時の 0 埋めの桁数に使用されます。表示桁数は格納できる数値の範囲ではないため、指定した桁数以上の値を格納することもできます。（格納値は型に依存します） ZEROFILL を使用しない場合は桁数を指定しなくても構いません。また、開発案件によっては、別の意味でカラム値に関連する桁数を割り当てて役割を理解しやすくするためにあえて付ける場合があります。（年齢のカラムの場合は3桁で定義上わかりやすくするや、他のテーブルの別名カラムと関連付けする際に桁をあわせて理解しやすくするなどの意味で意図的に付ける場合があります）

CREATE TABLE users (age INT(3));

数値型（BOOL, BOOLEAN）

数値型のひとつに BOOL（BOOLEAN）という型があります。使い方は以下。

型	格納できる範囲
BOOL、BOOLEAN	TINYINT(1) で指定した場合と同じ。true と false の2択を保存したいときに使うことが多い。

数値型（ビット値型）

数値型のひとつにビット値型があります。使い方は以下。

型	格納できる範囲
BIT	111 や 10000000 といったビットフィールド値を格納するのに使う。ビット値を指定するには、b'111' や b'10000000' のように指定する。

ビット数の指定方法

ビット値型のカラムに保存する値のビット数を指定するときは、型指定の際に後ろにカッコをつけて数字（1～64）を指定します。指定したビット数に満たない値を格納した場合、左側はゼロで埋められます。

CREATE TABLE users (age BIT(3));

数値型（小数点型）

数値型のひとつに小数点型があります。使い方は以下。

型	格納できる範囲
DECIMAL、DEC、NUMERIC	誤差のない正確な小数を格納できる。
FLOAT	おおよそ小数第7位まで正確な小数を格納できる。許可される値は、 -3.402823466×10³⁸ ～ -1.175494351×10³⁸、0、1.175494351×10³⁸ ～ 3.402823466×10³⁸
DOUBLE	おおよそ小数第15位まで正確な小数を格納できる。許可される値は、 -1.7976931348623157×10³⁰⁸ ～ -2.2250738585072014×10³⁰⁸、0、2.2250738585072014×10³⁰⁸ ～ 1.7976931348623157×10³⁰⁸

桁数の指定方法

小数点型は、DECIMAL(M, D) のようにして桁数を指定することができます。例えば DECIMAL(5, 2) のように指定すると、全体で5桁、小数点以下が2桁（123.45 など）の数値が保存できるようになります。

M 桁数の合計
D 小数点以下の桁数

UNSIGNED の使い方

通常、小数点型は負の数も格納することができますが、UNSIGNED を指定すると正の数しか格納できなくなります。

CREATE TABLE users (age DECIMAL(5,3) UNSIGNED);

ZEROFILL の使い方

小数点型に ZEROFILL を指定すると、表示桁数に満たない値を 0 で埋めることができます。また、ZEROFILL を指定する場合は、自動的に UNSIGNED が付加された状態になります。

CREATE TABLE users (age DECIMAL(5,3) ZEROFILL);

日付型・時間型

日付や時間を扱う型は以下の種類があり、それぞれ用途が違います。

型	用途	フォーマット
DATE	日付	'年-月-日' （例: '2020-01-01'）
DATETIME	日付と時間	'年-月-日時:分:秒' （例: '2020-01-01 12:15:03'）
TIMESTAMP	タイムスタンプ明示的に値が割り当てられていなければ、もっとも新しい変更日時に自動的に設定される。	'年-月-日時:分:秒' （例: '2020-01-01 12:15:03'）
TIME	時間	'時:分:秒' （例: '12:15:03'）
YEAR	年	'年' （例: '2020'）

値の指定方法

日付や時間を扱う型のカラムに値を挿入する場合、以下のような基本フォーマット以外の形も使えます。

'2020-01-01 12:15:05' （基本）
'20-1-1 12:15:5'
'2020/01/01 12:15:05'
'20200101121505'

文字列型

文字列を扱う型は以下の種類があり、それぞれ用途が違います。

型	格納できる範囲
CHAR	固定長文字列を格納。格納されるデータは、指定された長さになるように右側がスペースで埋められる。 `CHAR(10)` のようにして格納できる文字数（0～255・デフォルトは1）を指定できる。
VARCHAR	可変長文字列を格納。 CHAR型とは違い、右側にスペースはつかない。 `VARCHAR(10)` のようにして格納できるバイト数（0～65,535）を指定できる。
BINARY	固定長バイナリバイト文字列を格納。格納されるデータは、指定された長さになるように調整される。 `BINARY(10)` のようにして格納できる文字数（0～255・デフォルトは1）を指定できる。
VARBINARY	可変長バイナリバイト文字列を格納。 `VARBINARY(10)` のようにして格納できるバイト数（0～65,535）を指定できる。
TINYBLOB	バイナリデータを格納。最大長は 255 (2⁸ - 1) バイト。
BLOB	バイナリデータを格納。最大長は 65,535 (2¹⁶ - 1) バイト。BLOB(10) のようにして格納できるバイト数を指定できる。
MEDIUMBLOB	バイナリデータを格納。最大長は 16,777,215 (2²⁴ - 1) バイト。
LONGBLOB	バイナリデータを格納。最大長は 4,294,967,295 または 4G バイト (2³² - 1) バイト。
TINYTEXT	文字列を格納。最大長は 255 (2⁸ - 1) 文字。
TEXT	文字列を格納。最大長は 65,535 (2¹⁶ - 1) 文字。`TEXT(10)` のようにして格納できる文字数を指定できる。
MEDIUMTEXT	文字列を格納。最大長は 16,777,215 (2²⁴ - 1) 文字。
LONGTEXT	文字列を格納。最大長は 4,294,967,295 または 4G バイト (2³² - 1) 文字。
ENUM	指定した文字列リストの中の1つを格納できる。例えば、`ENUM('value1','value2','value3')` と型を指定した場合、「value1」、「value2」、「value3」、または NULL のいずれかを格納することができる。
SET	指定した文字列リストの中の複数を格納できる。例えば、`ENUM('value1','value2','value3')` と型を指定した場合、「value1」、「value2」、「value3」の中からゼロ個以上の複数の値を格納できる（複数の値を格納する場合はカンマ（,）区切りで指定する）。
JSON	JSONフォーマットを格納。最大長は 4,294,967,295 または 4G バイト (2³² - 1) 文字。MySQLにおいてJSON型は厳密にJSON規格で検証され、無効な値が入力された場合はエラーになります。

JSON型について

JSON型の特徴

JSON型はMySQLでJSONドキュメントを保存することができる型です。単にJSON形式の文字列を文字列型に格納するのと比べて以下の利点があります。

JSONカラムに格納するJSONドキュメントが自動で検証され、不正な場合にはエラーとなる。
JSONカラムに格納されたJSONドキュメントは、ドキュメント要素へ素早くアクセスできるように最適化される。 MySQLはJSONドキュメント全体を解析することなく、キーまたは配列インデックスによりネストされた値を直接参照できる。

JSONとして有効な値

MySQLにおいてJSON型は厳密にJSON規格で検証され、無効な値が入力された場合はエラーになります。以下にJSONとして有効な値を列挙します。配列、オブジェクトはネストさせることができます。

リテラル

JSON配列およびオブジェクトには、文字列または数値,NULL,true,falseといったリテラルを含むことができます。
配列

JSONの配列は、カンマで区切られ、[]で囲まれた値のリストが含まれます。

["abc", 10, null, true, false]

JSONオブジェクト

JSONのオブジェクトには、カンマで区切られ、{} で囲まれたキーと値のペアのセットが含まれます。キーは文字列でなければなりません。

{"k1": "value", "k2": 10}

JSONオブジェクトの作成と検証

まず、MySQLデータベース内でJSONオブジェクトを作成および検証するための構文について説明します。JSONカラムの定義は以下のようにできます。

CREATE TABLE json_test (
  json JSON NULL
);

JSONデータは前出のJSON規格に厳密に従っていなければなりません。例えば、次のように無効なJSONオブジェクトをjson_testテーブルに挿入しようとした場合

INSERT INTO json_test (json) VALUES ('[1,2');

このデータを挿入しようとすると、配列の括弧が閉じられていないため下記のエラーが発生します。

> ERROR 3140 (22032) at line 5: Invalid JSON text: "Missing a comma or ']' after an array element." at position 4 in value for column 'json_test.json'.

有効なJSONオブジェクトを挿入した場合、問題なく処理できます。

INSERT INTO json_test (json) VALUES ('{"name": "Taro"}');

SELECT json FROM json_test;
> {"name": "Taro"}

JSONカラムからデータを取得する方法

MySQLデータベース内に有効なJSONデータがある場合、さまざまな関数と演算子を使用してデータを取得および操作できます。このJSONオブジェクトから特定のキーに対応する値だけを取得したい場合、次のように->>演算子を使用できます。->>演算子は、特定のパスにあるJSONオブジェクトを抽出するために使用されます。

SELECT `json`->>"$.name" as name FROM json_test;

| name |
|------|
| Taro |

JSONカラムのインデックス化

MySQLでJSONカラムを使用する際の重要な考慮事項の1つは、JSONカラムを直接インデックス化できないことです。代わりに、JSONオブジェクト内の特定のキーに対してインデックスを作成できます。

たとえば、次のコードを考えてみましょう。

ALTER TABLE json_test ADD INDEX my_index ((`json`->>"$.name"));

ここでは、カラム内のJSONオブジェクトのnameフィールドにインデックスを作成しています。これにより、この特定のJSONパスに関連するクエリの高速な検索をMySQLが実行できるようになります。

JSONカラムを使用する際のベストプラクティス

MySQLのJSONカラムは非常に強力ですが、従来のスキーマ設計の代替手段として使用すべきではありません。 スキーマがよく定義されていない場合やスキーマが頻繁に変更される場合にはJSONカラムを使用することが有用です。 たとえば、JSONカラムは、異なる応答形式を持つサードパーティサービスやAPIからのペイロードを保存する場合に適しています。また、リレーショナルデータベース設計に収まらないネストされたデータや階層データを保存する場合にも役立ちます。

JSONカラムを使用する際の注意点として、JSONデータの大量保存時に処理が重くなる可能性があることが挙げられます。MySQLの任意のデータ型と同様に、クエリを実行する際に必要なデータのみを選択し、不必要なJSONデータを取得しないようにしましょう。