概要

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

環境

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
	`127` (3)	`255` (3)	1
`SMALLINT[(M)]`	`-32768`	`0`	2
	`32767` (5)	`65535` (5)	2
`MEDIUMINT[(M)]`	`-8388608`	`0`	3
	`8388607` (7)	`16777215` (8)	3
`INT[(M)]`	`-2147483648`	`0`	4
	`2147483647` (10)	`4294967295` (10)	4
`BIGINT[(M)]`	`-9223372036854775808`	`0`	8
	`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

各種数値型の使用例

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)
);

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);

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の使用

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フィールドには指定できません。

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);

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のチェック

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 D:30	M(精度)のデフォルトは10. Dのデフォルトは0.

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

固定小数点のシノニム

DEC / NUMERIC / FIXED

DECIMALのシノニムです。

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)
);

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)

example

insert into test_dec_tbl (d1, d2) values (1234567890.987654321, 1234567890);

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

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'と表記します。

exmaple

insert into test_bit_tbl (b1, b2) values (b'00001111', b'1111111111111111');

example

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

example

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

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. 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. 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. 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

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

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)
);

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)

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);

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

example

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

CURRENT_DATE

example

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

UNIX_TIMESTAMP

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

example

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

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

行の最大サイズについて

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バイトで計算します。

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)

各種文字列型の使用例

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)
);

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

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)