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` を指定すると正の数しか格納できなくなります。その代わりに正の格納できる範囲が広くなります。

```sql
CREATE TABLE users (age INT UNSIGNED);
```

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

```sql
CREATE TABLE users (age INT ZEROFILL);
```

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

```sql
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）を指定します。指定したビット数に満たない値を格納した場合、左側はゼロで埋められます。

```sql
CREATE TABLE users (age BIT(3));
```


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

| 型 | 格納できる範囲 |
| :---- | :---- |
| **DECIMAL、DEC、NUMERIC** | 誤差のない正確な小数を格納できる。 |
| FLOAT | おおよそ小数第7位まで正確な小数を格納できる。<br><br>許可される値は、<br>-3.402823466×10<sup>38</sup> ～ -1.175494351×10<sup>38</sup>、0、1.175494351×10<sup>38</sup> ～ 3.402823466×10<sup>38</sup> |
| DOUBLE | おおよそ小数第15位まで正確な小数を格納できる。<br><br>許可される値は、<br>-1.7976931348623157×10<sup>308</sup> ～ -2.2250738585072014×10<sup>308</sup>、0、2.2250738585072014×10<sup>308</sup> ～ 1.7976931348623157×10<sup>308</sup> |

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

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

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

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

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

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


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

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

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

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


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

| 型 | 格納できる範囲 |
| :---- | :---- |
| CHAR | 固定長文字列を格納。<br>格納されるデータは、指定された長さになるように右側がスペースで埋められる。<br><br>`CHAR(10)` のようにして格納できる文字数（0～255・デフォルトは1）を指定できる。 |
| **VARCHAR** | 可変長文字列を格納。<br>CHAR型とは違い、右側にスペースはつかない。<br><br>`VARCHAR(10)` のようにして格納できるバイト数（0～65,535）を指定できる。 |
| BINARY | 固定長バイナリバイト文字列を格納。<br>格納されるデータは、指定された長さになるように調整される。<br><br>`BINARY(10)` のようにして格納できる文字数（0～255・デフォルトは1）を指定できる。 |
| VARBINARY | 可変長バイナリバイト文字列を格納。<br><br>`VARBINARY(10)` のようにして格納できるバイト数（0～65,535）を指定できる。 |
| TINYBLOB | バイナリデータを格納。<br><br>最大長は 255 (2<sup>8</sup> - 1) バイト。 |
| BLOB | バイナリデータを格納。<br><br>最大長は 65,535 (2<sup>16</sup> - 1) バイト。BLOB(10) のようにして格納できるバイト数を指定できる。 |
| MEDIUMBLOB | バイナリデータを格納。<br><br>最大長は 16,777,215 (2<sup>24</sup> - 1) バイト。 |
| LONGBLOB | バイナリデータを格納。<br><br>最大長は 4,294,967,295 または 4G バイト (2<sup>32</sup> - 1) バイト。 |
| TINYTEXT | 文字列を格納。<br><br>最大長は 255 (2<sup>8</sup> - 1) 文字。 |
| **TEXT** | 文字列を格納。<br><br>最大長は 65,535 (2<sup>16</sup> - 1) 文字。`TEXT(10)` のようにして格納できる文字数を指定できる。 |
| MEDIUMTEXT | 文字列を格納。<br><br>最大長は 16,777,215 (2<sup>24</sup> - 1) 文字。 |
| LONGTEXT | 文字列を格納。<br><br>最大長は 4,294,967,295 または 4G バイト (2<sup>32</sup> - 1)  文字。 |
| ENUM | 指定した文字列リストの中の1つを格納できる。<br><br>例えば、`ENUM('value1','value2','value3')` と型を指定した場合、「value1」、「value2」、「value3」、または NULL のいずれかを格納することができる。 |
| SET | 指定した文字列リストの中の複数を格納できる。<br><br>例えば、`ENUM('value1','value2','value3')` と型を指定した場合、「value1」、「value2」、「value3」の中からゼロ個以上の複数の値を格納できる（複数の値を格納する場合はカンマ（,）区切りで指定する）。 |
| **JSON** | JSONフォーマットを格納。<br><br>最大長は 4,294,967,295 または 4G バイト (2<sup>32</sup> - 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の配列は、カンマで区切られ、[]で囲まれた値のリストが含まれます。
```json
["abc", 10, null, true, false]
```

- JSONオブジェクト

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

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

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

```sql
CREATE TABLE json_test (
  json JSON NULL
);
```

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

```sql
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オブジェクトを挿入した場合、問題なく処理できます。

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

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

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

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

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

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

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

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

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

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

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