LoginSignup
26
29

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@rubytomato@github(渡邉 真)

MySQL データタイプのメモ

Last updated at Posted at 2015-07-16

概要

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

環境

  • Windows7 (64bit)
  • MySQL 5.7.7 rc

参考

データタイプ

数値

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

26
29
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
26
29

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?