MySQL データタイプのメモ

# 概要

データタイプの特徴や使用方法のメモです。


**環境**

* Windows7 (64bit)
* MySQL 5.7.7 rc

**参考**

* [MySQL 5.7 Reference Manual :: 11 Data Types] (https://dev.mysql.com/doc/refman/5.7/en/data-types.html)
* [MySQL 5.6 リファレンスマニュアル :: 11 データ型] (http://dev.mysql.com/doc/refman/5.6/ja/data-types.html)


## データタイプ

### 数値

[11.2 Numeric Types] (https://dev.mysql.com/doc/refman/5.7/en/numeric-types.html)

#### 整数型

|type             |符号付きの範囲 (min/max)      |符号無しの範囲 (min/max)      |bytes|
|:----------------|:----------------------------|:---------------------------|:----|
|`TINYINT[(M)]`   |`-128`                       |`0`                         |1    |
|                 |&nbsp;`127` (3)              |`255` (3)                   |1    |
|`SMALLINT[(M)]`  |`-32768`                     |`0`                         |2    |
|                 |&nbsp;`32767` (5)            |`65535` (5)                 |2    |
|`MEDIUMINT[(M)]` |`-8388608`                   |`0`                         |3    |
|                 |&nbsp;`8388607` (7)          |`16777215` (8)              |3    |
|`INT[(M)]`       |`-2147483648`                |`0`                         |4    |
|                 |&nbsp;`2147483647` (10)      |`4294967295` (10)           |4    |
|`BIGINT[(M)]`    |`-9223372036854775808`       |`0`                         |8    |
|                 |&nbsp;`9223372036854775807` (19) |`18446744073709551615` (20) |8    |

* ()内の数字は最大の桁数です。
* `M`には整数の表示桁を指定します。整数値の範囲とは関係ありません。

> MySQL では、整数データ型の基本キーワードに続く括弧内で、その型の表示幅をオプションで指定する拡張をサポートしています。たとえば、INT(4) は、4 桁の表示幅の INT を指定しています。このオプションの表示幅は、左側をスペースでパディングすることによって、カラムに対して指定された幅よりも狭く整数値を表示するために、アプリケーションで使用される場合があります。(つまり、この幅は結果セットで返されるメタデータの中にあります。これを使用するかどうかは、アプリケーションしだいです。)


**整数型のシノニム、別名**

***BOOL / BOOLEAN***

* `TINYINT(1)`のシノニムです。
* 値が0は`false`を、0以外は`true`を意味します。

***INTEGER***

* INTのシノニムです。
 
***SERIAL***

* `BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE`の別名です。


##### example

**各種数値型の使用例**

```sql:example
drop table test_int_tbl;
create table if not exists test_int_tbl (
  id int not null auto_increment,
  t tinyint not null default 0,
  s smallint not null default 0,
  m mediumint not null default 0,
  i int not null default 0,
  b bigint not null default 0,
  primary key(id)
);
```

```sql:example
insert into test_int_tbl(t, s, m, i, b) values (127, 32767, 8388607, 2147483647, 9223372036854775807);
insert into test_int_tbl(t, s, m, i, b) values (-128, -32768, -8388608, -2147483648, -9223372036854775808);
```

```sql:example
select * from test_int_tbl;
+----+------+--------+----------+-------------+----------------------+
| id | t    | s      | m        | i           | b                    |
+----+------+--------+----------+-------------+----------------------+
|  1 |  127 |  32767 |  8388607 |  2147483647 |  9223372036854775807 |
|  2 | -128 | -32768 | -8388608 | -2147483648 | -9223372036854775808 |
+----+------+--------+----------+-------------+----------------------+
2 rows in set (0.00 sec)
```

**serialの使用**

```sql:example
drop table test_serial_tbl;
create table if not exists test_serial_tbl (
  id int not null,
  b bool not null default true,
  s serial,
  primary key(id)
);
```

* 1つのテーブルに`auto_increment`を指定したフィールドは1つしか持てないため、primary keyにするidフィールドには指定できません。

```sql:example
insert into test_serial_tbl (id, b) values (1, true);
insert into test_serial_tbl (id, b) values (2, 1);
insert into test_serial_tbl (id, b) values (3, 0);
insert into test_serial_tbl (id, b) values (4, -1);
insert into test_serial_tbl (id, b) values (5, 2);
```

```sql:example
select * from test_serial_tbl;
+----+----+---+
| id | b  | s |
+----+----+---+
|  1 |  1 | 1 |
|  2 |  1 | 2 |
|  3 |  0 | 3 |
|  4 | -1 | 4 |
|  5 |  2 | 5 |
+----+----+---+
5 rows in set (0.00 sec)
```

**boolのチェック**

```sql:example
select b, if(b, 'true', 'false') from test_serial_tbl;
+----+------------------------+
| b  | if(b, 'true', 'false') |
+----+------------------------+
|  1 | true                   |
|  1 | true                   |
|  0 | false                  |
| -1 | true                   |
|  2 | true                   |
+----+------------------------+
5 rows in set (0.00 sec)
```


[11.2.1 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT] (https://dev.mysql.com/doc/refman/5.7/en/integer-types.html)


#### 固定小数点型

|type                       |最大桁数       |備考                                    |
|:--------------------------|:-------------|:---------------------------------------|
|`DECIMAL[(M[,D])]`         |M:65<br/>D:30 |M(精度)のデフォルトは10. Dのデフォルトは0. |

* `M`は精度(全体の桁数)、`D`はスケール(小数点以下の桁数)を指定します。

**固定小数点のシノニム**

***DEC / NUMERIC / FIXED***

* DECIMALのシノニムです。


##### example

```sql:example
drop table test_dec_tbl;
create table if not exists test_dec_tbl (
  id int not null auto_increment,
  d1 decimal(20,10) not null default 0,
  d2 decimal(15) not null default 0,
  d3 decimal not null default 0,
  d4 decimal,
  primary key(id)
);
```

```sql:example
desc test_dec_tbl;
+-------+----------------+------+-----+--------------+----------------+
| Field | Type           | Null | Key | Default      | Extra          |
+-------+----------------+------+-----+--------------+----------------+
| id    | int(11)        | NO   | PRI | NULL         | auto_increment |
| d1    | decimal(20,10) | NO   |     | 0.0000000000 |                |
| d2    | decimal(15,0)  | NO   |     | 0            |                |
| d3    | decimal(10,0)  | NO   |     | 0            |                |
| d4    | decimal(10,0)  | YES  |     | NULL         |                |
+-------+----------------+------+-----+--------------+----------------+
5 rows in set (0.00 sec)
```

```sql:example
insert into test_dec_tbl (d1, d2) values (1234567890.987654321, 1234567890);
```

```sql:example
select * from test_dec_tbl;
+----+-----------------------+------------+----+------+
| id | d1                    | d2         | d3 | d4   |
+----+-----------------------+------------+----+------+
|  1 | 1234567890.9876543210 | 1234567890 |  0 | NULL |
+----+-----------------------+------------+----+------+
1 row in set (0.00 sec)
```

[11.2.2 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC] (https://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html)


#### 浮動小数点数型

|type               |許容値                                                   |bytes|
|:------------------|:--------------------------------------------------------|:----|
|`FLOAT[(M,D)]`     |`-3.402823466E+38` to `-1.175494351E-38`                 |4    |
|                   |`0`                                                      |     |
|                   |`1.175494351E-38` to `3.402823466E+38`                   |     |
|`DOUBLE[(M,D)]`    |`-1.7976931348623157E+308` to `-2.2250738585072014E-308` |8    |
|                   |`0`                                                      |     |
|                   |`2.2250738585072014E-308` to `1.7976931348623157E+308`   |     |

* `M`は全体の桁数、`D`は小数点以下の桁数を指定します。
* `M`,`D`を省略した場合、格納できる値はハードウェアが許可する限度になります。


**浮動小数点数のシノニム**

***DOUBLE PRECISION / REAL***

* DOUBLEのシノニムです。
* 例外: REAL_AS_FLOAT SQLモードが有効な場合、REALはFLOATのシノニムになります。

> REAL_AS_FLOAT
> REALをFLOAT のシノニムとして扱う。デフォルトでは、MySQLがREALをDOUBLE のシノニムとして扱う。

**FLOAT(P)**

* `P`の値が0から24の場合、データ型は`FLOAT`になります。
* `P`の値が25から53の場合、データ型は`DOUBLE`になります。

> FLOAT(p) 構文は ODBC との互換性を確保するために用意されています。

[11.2.3 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE] (https://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html)


#### ビット型

|type       |指定できるビット数 |
|:----------|:----------------|
|`BIT(M)`   |1 to 64.         |


##### example

```sql:example
drop table test_bit_tbl;
create table if not exists test_bit_tbl (
  id int not null auto_increment,
  b1 bit(8) not null default b'00000000',
  b2 bit(16),
  primary key(id)
);
```

ビット値は`b'value'`と表記します。

```sql:exmaple
insert into test_bit_tbl (b1, b2) values (b'00001111', b'1111111111111111');
```

```sql:example
select b1+0,b2+0 from test_bit_tbl;
+------+-------+
| b1+0 | b2+0  |
+------+-------+
|   15 | 65535 |
+------+-------+
1 row in set (0.00 sec)
```

```sql:example
select bin(b1), bin(b2) from test_bit_tbl;
+---------+------------------+
| bin(b1) | bin(b2)          |
+---------+------------------+
| 1111    | 1111111111111111 |
+---------+------------------+
1 row in set (0.00 sec)
```

```sql:example
select hex(b1), hex(b2) from test_bit_tbl;
+---------+---------+
| hex(b1) | hex(b2) |
+---------+---------+
| F       | FFFF    |
+---------+---------+
1 row in set (0.00 sec)
```

[11.2.4 Bit-Value Type - BIT] (https://dev.mysql.com/doc/refman/5.7/en/bit-type.html)



### 日付と時刻

[11.3 Date and Time Types] (https://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html)

|type               |range (from / to)                    |fsp                    |zero value           |bytes|
|:------------------|:------------------------------------|:----------------------|:--------------------|:----|
|`DATE`             |`1000-01-01`                         |-                      |`0000-00-00`         |3    |
|                   |`9999-12-31`                         |                       |                     |     |
|`DATETIME[(fsp)]`  |`1000-01-01 00:00:00.000000`         |0 to 6.<br/>default 0. |`0000-00-00 00:00:00`|8    |
|                   |`9999-12-31 23:59:59.999999`         |                       |                     |     |
|`TIMESTAMP[(fsp)]` |`1970-01-01 00:00:01.000000 UTC`     |0 to 6.<br/>default 0. |`0000-00-00 00:00:00`|4    |
|                   |`2038-01-19 03:14:07.999999 UTC`     |                       |                     |     |
|`TIME[(fsp)]`      |`-838:59:59.000000`                  |0 to 6.<br/>default 0. |`00:00:00`           |3    |
|                   |`838:59:59.000000`                   |                       |                     |     |
|`YEAR[(4)]`        |`1901`                               |-                      |`0000`               |1    |
|                   |`2155`                               |                       |                     |     |
|                   |and `0`                              |                       |                     |     |


#### fsp (fractional seconds part)

* `DATETIME`,`TIME`,`TIMESTAMP`は最大6桁のマイクロ秒(100万分の1秒)をサポートします。デフォルトは0です。

[11.3.6 Fractional Seconds in Time Values] (https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html)


#### 自動初期化と自動更新

* テーブル内のすべての`TIMESTAMP`型に自動初期化と自動更新機能が付きました。(MySQL 5.6.5以降)
* `DATETIME`型にも`TIMESTAMP`型と同じ機能を付けることが可能です。

**カラム定義**

* 自動初期化は`DEFAULT CURRENT_TIMESTAMP`カラム定義句を使用します。
* 自動更新は`ON UPDATE CURRENT_TIMESTAMP`カラム定義句を使用します。


##### example

**各種日付、時刻型の使用例**

```sql:example
drop table test_dt_tbl;
create table if not exists test_dt_tbl (
  id int not null auto_increment,
  d1 date not null default '1000-01-01',
  d2 date,
  dt1 datetime(6) not null default '1000-01-01T00:00:00.000000',
  dt2 datetime(6) not null default current_timestamp(6) on update current_timestamp(6),
  dt3 datetime(6),
  ts1 timestamp(6) not null default '1970-01-01 09:00:01.000000',
  ts2 timestamp(6) not null default current_timestamp(6) on update current_timestamp(6),
  ts3 timestamp(6),
  t1 time(6) not null default '00:00:00',
  t2 time(6),
  y1 year(4) not null default '1901',
  y2 year(4),
  primary key(id)
);
```

```sql:example
desc test_dt_tbl;
+-------+--------------+------+-----+----------------------------+--------------------------------+
| Field | Type         | Null | Key | Default                    | Extra                          |
+-------+--------------+------+-----+----------------------------+--------------------------------+
| id    | int(11)      | NO   | PRI | NULL                       | auto_increment                 |
| d1    | date         | NO   |     | 1000-01-01                 |                                |
| d2    | date         | YES  |     | NULL                       |                                |
| dt1   | datetime(6)  | NO   |     | 1000-01-01 00:00:00.000000 |                                |
| dt2   | datetime(6)  | NO   |     | CURRENT_TIMESTAMP(6)       | on update CURRENT_TIMESTAMP(6) |
| dt3   | datetime(6)  | YES  |     | NULL                       |                                |
| ts1   | timestamp(6) | NO   |     | 1970-01-01 09:00:01.000000 |                                |
| ts2   | timestamp(6) | NO   |     | CURRENT_TIMESTAMP(6)       | on update CURRENT_TIMESTAMP(6) |
| ts3   | timestamp(6) | YES  |     | NULL                       |                                |
| t1    | time(6)      | NO   |     | 00:00:00.000000            |                                |
| t2    | time(6)      | YES  |     | NULL                       |                                |
| y1    | year(4)      | NO   |     | 1901                       |                                |
| y2    | year(4)      | YES  |     | NULL                       |                                |
+-------+--------------+------+-----+----------------------------+--------------------------------+
13 rows in set (0.00 sec)
```

```sql:example
insert into test_dt_tbl(d2, dt3, ts3, t2, y2) values('2015-07-16', '2015-07-16 11:11:11.123456', '2015-07-16 11:11:11.123456','11:11:00.000000',2015);
```

```sql:example
select d1,d2,t1,t2,y1,y2 from test_dt_tbl;
+------------+------------+-----------------+-----------------+------+------+
| d1         | d2         | t1              | t2              | y1   | y2   |
+------------+------------+-----------------+-----------------+------+------+
| 1000-01-01 | 2015-07-16 | 00:00:00.000000 | 11:11:00.000000 | 1901 | 2015 |
+------------+------------+-----------------+-----------------+------+------+
1 row in set (0.00 sec)

select dt1,dt2 from test_dt_tbl;
+----------------------------+----------------------------+
| dt1                        | dt2                        |
+----------------------------+----------------------------+
| 1000-01-01 00:00:00.000000 | 2015-07-16 11:11:45.307605 |
+----------------------------+----------------------------+
1 row in set (0.00 sec)

select ts1,ts2 from test_dt_tbl;
+----------------------------+----------------------------+
| ts1                        | ts2                        |
+----------------------------+----------------------------+
| 1970-01-01 09:00:01.000000 | 2015-07-16 11:11:45.307605 |
+----------------------------+----------------------------+
1 row in set (0.00 sec)
```


**日付関連の関数**

***NOW***

```sql:example
select now();
+---------------------+
| now()               |
+---------------------+
| 2015-07-16 11:08:17 |
+---------------------+
1 row in set (0.00 sec)
```

***CURRENT_DATE***

```sql:example
select current_date();
+----------------+
| current_date() |
+----------------+
| 2015-07-16     |
+----------------+
1 row in set (0.00 sec)
```

***UNIX_TIMESTAMP***

```sql:example
SELECT unix_timestamp("1970-01-01 00:00:01");
+---------------------------------------+
| unix_timestamp("1970-01-01 00:00:01") |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.02 sec)
```

***UTC_TIMESTAMP***

```sql:example
SELECT utc_timestamp();
+---------------------+
| utc_timestamp()     |
+---------------------+
| 2015-07-16 01:27:47 |
+---------------------+
1 row in set (0.01 sec)
```

```sql:example
SET SESSION time_zone = '+09:00';
```


[11.3.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME] (https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html)



### 文字列

[11.4 String Types] (https://dev.mysql.com/doc/refman/5.7/en/string-types.html)

|type                       |range                                                          |bytes |
|:--------------------------|:--------------------------------------------------------------|:-----|
|`[NATIONAL] CHAR[(M)]`     |Mにはカラムの長さを文字数で指定。Mの範囲は0から255。省略すると1が指定される。|    |
|`[NATIONAL] VARCHAR(M)`    |Mにはカラムの最大長を文字数で指定。Mの範囲は0から65,535。           |    |
|`BINARY(M)`                |Mにはカラムの長さをバイト数で指定。                                |    |
|`VARBINARY(M)`             |Mにはカラムの最大長をバイト数で指定。                              |    |
|`TINYBLOB`                 |最大長が255bytesのBLOBカラム。                                    |    |
|`TINYTEXT`                 |最大長が255文字のTEXTカラム。                                     |    |
|`BLOB[(M)]`                |最大長が65,535bytesのBLOBカラム。                                 |    |
|`TEXT[(M)]`                |最大長が65,535文字のTEXTカラム。                                   |    |
|`MEDIUMBLOB`               |最大長が16,777,215bytesのBLOBカラム。                             |    |
|`MEDIUMTEXT`               |最大長が16,777,215文字のTEXTカラム。                               |    |
|`LONGBLOB`                 |最大長が4,294,967,295bytesのBLOBカラム。                           |    |          
|`LONGTEXT`                 |最大長が4,294,967,295文字のTEXTカラム。                            |    |


**BLOB**

バイナリ文字列（バイトの文字列）を格納するデータ型です。
BLOB型には`TINYBLOG`,`BLOB`,`MEDIUMBLB`,`LONGBLOB`があります。

**TEXT**

非バイナリ文字列を格納するデータ型です。文字セットがありソートは文字セットの照合順序によって行われます。
TEXT型には`TINYTEXT`.`TEXT`,`MEDIUMTEXT`,`LONGTEXT`があります。

##### example

**行の最大サイズについて**

```sql:example
drop table test_vc_tbl;
create table if not exists test_vc_tbl (
  id int not null auto_increment,
  vc1 varchar(65535),
  primary key(id)
);
ERROR 1074 (42000): Column length too big for column 'vc1' (max = 21845); use BLOB or TEXT instead

create table if not exists test_vc_tbl (
  id int not null auto_increment,
  vc1 varchar(21845),
  primary key(id)
);
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

create table if not exists test_vc_tbl (
  id int not null auto_increment,
  vc1 varchar(21842),
  primary key(id)
);
Query OK, 0 rows affected (0.04 sec)
```

* 1行あたりの最大サイズは65,535バイトです。
* キャラクターセットに`utf8`を使用する場合、1文字あたり3バイトで計算します。

```sql:example
desc test_vc_tbl;
+-------+----------------+------+-----+---------+----------------+
| Field | Type           | Null | Key | Default | Extra          |
+-------+----------------+------+-----+---------+----------------+
| id    | int(11)        | NO   | PRI | NULL    | auto_increment |
| vc1   | varchar(21842) | YES  |     | NULL    |                |
+-------+----------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
```

**各種文字列型の使用例**

```sql:example
drop table test_char_tbl;
create table if not exists test_char_tbl (
  id int not null auto_increment,
  c1 char,
  c2 char(10),
  vc1 varchar(1),
  vc2 varchar(2000),
  tt tinytext,
  t text(2000),
  mt mediumtext,
  lt longtext,
  primary key(id)
);
```

```sql:example
desc test_char_tbl;
+-------+---------------+------+-----+---------+----------------+
| Field | Type          | Null | Key | Default | Extra          |
+-------+---------------+------+-----+---------+----------------+
| id    | int(11)       | NO   | PRI | NULL    | auto_increment |
| c1    | char(1)       | YES  |     | NULL    |                |
| c2    | char(10)      | YES  |     | NULL    |                |
| vc1   | varchar(1)    | YES  |     | NULL    |                |
| vc2   | varchar(2000) | YES  |     | NULL    |                |
| tt    | tinytext      | YES  |     | NULL    |                |
| t     | text          | YES  |     | NULL    |                |
| mt    | mediumtext    | YES  |     | NULL    |                |
| lt    | longtext      | YES  |     | NULL    |                |
+-------+---------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
```


**文字列関連の関数**

***CHAR***

```sql:example
select char(0x0042);
+--------------+
| char(0x0042) |
+--------------+
| B            |
+--------------+
1 row in set (0.00 sec)
```

### デフォルト値

* dafaultには定数しか指定できない。
* 例外として`TIMESTAMP`,`DATETIME`に`CURRENT_TIMESTAMP`を指定できる。
* `BLOB`,`TEXT`にはデフォルト値を指定できない。


[11.6 Data Type Default Values] (https://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html)