MariaDB 또는 MySQL에서 사용하는 Data type 정리

관계형 데이터 베이스에서 처리하는 데이터 타입은 C, Python, Java와 같은 프로그래밍 언어의 데이터 타입보다 세부적으로 정의되어 있습니다. Maria DB에서 정의하는 데이터 타입은 아래와 같습니다.

Numeric Data Type
String Data Types
Date & Time Data Types
Other Data Types: Geometry Types

1. Numeric Data Types

Numeric Data Type은 숫자를 표현합니다. 프로그래밍 언어의 Integer, Float, Double형과 동일합니다. TinyInt는 1 Byte, SmallInt는 2 Byte, Medium Int는 3 Byte, Int는 4 Byte로 표현됩니다. 소수점 실수의 정밀도에 따라서 Float과 Double으로 표현되고, Bit(m)는 m자리수의 Bit를 표현합니다.

데이터 타입	의미	크기	설명
TINYINT	매우 작은 정수	1 byte	-128 ~ 127 (부호없이 0 ~ 255)
BOOLEAN	Synonym for TINYINT(1)		A value of zero is considered false. Non-zero values are considered true.
INT1	Synonym for TINYINT
SMALLINT	작은 정수	2 byte	The signed range is -32768 to 32767. The unsigned range is 0 to 655
INT2	Synonym for SMALLINT
MEDIUMINT	중간 크기의 정수	3 byte	The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.
INT3	Synonym for MEDIUMINT
INT	표준 정수	4 byte	When marked UNSIGNED, it ranges from 0 to 4294967295, otherwise its range is -2147483648 to 2147483647 (SIGNED is the default).
INT4	Synonym for INT
BIGINT	큰 정수	8 byte	The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.
INT8	Synonym for BIGINT
DECIMAL(M,D)	DEC, NUMERIC, FIXED 고정 소수	m과 n에 따라 다르다	The maximum number of digits (M) for DECIMAL is 65. The maximum number of supported decimals (D) is 30
FLOAT(M,D)	Sinlge precision Float-point Number	4 byte	-3.402823466E+38 to -1.175494351E-38 0 1.175494351E-38 to 3.402823466E+38
DOUBLE(M,D)	Double precision Float-point Number	8 byte	-1.7976931348623157E+308 to -2.2250738585072014E-308 0 2.2250738585072014E-308 to 1.7976931348623157E+30
BIT(M)	비트 필드	m에 따라 다르다	M indicates the number of bits per value, from 1 to 64

2. String Data Types

String Data Type은 문자열과 Binary 데이터를 표현합니다. 문자열을 표시하기 위한 Char, Text가 있고, 이진 파일을 표시하기 위한 Binary, Blob이 있습니다.

데이터 타입	의미	설명 (예제)
String Literals	""로 표현된 문자열	'The MariaDB String'
CHAR(M)	A fixed-length string	c CHAR(10)
VARCHAR(M)	A variable-length string. M represents the maximum column length in characters.	VARCHART (30) CHARACTER SET utf8
BINARY(M)	The BINARY type is similar to the CHAR type, but stores binary byte strings rather than non-binary character strings. M represents the column length in bytes.	a BINARY(10)
VARBINARY(M)	he VARBINARY type is similar to the VARCHAR type, but stores binary byte strings rather than non-binary character strings. M represents the maximum column length in bytes.	CREATE TABLE varbins (a VARBINARY(10));
TINYBLOB	A BLOB column with a maximum length of 255 (28 - 1) bytes. Each TINYBLOB value is stored using a one-byte length prefix that indicates the number of bytes in the value.
BLOB(M)	Blob 데이터: A BLOB column with a maximum length of 65,535 (2^16 - 1) bytes.	최대크기 65,535 (2^16 - 1) bytes.
MEDIUMBLOB,	중간 크기 BLOB : A BLOB column with a maximum length of 16,777,215 (2^24 - 1) bytes. Each MEDIUMBLOB value is stored using a three-byte length prefix that indicates the number of bytes in the value.	최대크기 16,777,215 (2^24 - 1) bytes
LONGBLOB	큰 BLOB: A BLOB column with a maximum length of 4,294,967,295 bytes or 4GB (232 - 1).	최대크기 4GB
TINYTEXT	최대 8 byte 문자열	A TEXT column with a maximum length of 255 (2^8 - 1) characters.
TEXT	최대 64kb 문자열	A TEXT column with a maximum length of 65,535 (216 - 1) characters.
MEDIUMTEXT	최대 16MB 문자열	A TEXT column with a maximum length of 16,777,215 (224 - 1) characters.
LONGTEXT	최대크기 4GB 문자열	A TEXT column with a maximum length of 4,294,967,295 or 4GB (232 - 1) characters. T
INET6	IPv6 address (MariaDB 10.05 에서 신규 추가)	Values are stored as a 16-byte fixed length binary string, with most significant byte first. Storage engines see INET6 as BINARY(16). Clients see INET6 as CHAR(39) and get text representation on retrieval. INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');
JSON Data Type	LONGTEXT와 동일	CREATE TABLE t2 ( j JSON CHECK (JSON_VALID(j)) ); INSERT INTO t2 VALUES ('invalid'); ERROR 4025 (23000): CONSTRAINT `j` failed for `test`.`t2` INSERT INTO t2 VALUES ('{"id": 1, "name": "Monty"}'); Query OK, 1 row affected (0.13 sec)
ROW	ROW is a data type for stored procedure variables.

3. Date & Time Types

Date와 Time 은 날짜와 시간을 표시합니다.

데이터 타입	의미	설명 (예제)
DATE	YYYY-MM-DD 날짜 표시 (연도-월-날짜)	CREATE TABLE t1 (d DATE); INSERT INTO t1 VALUES ("2010-01-12"), ("2011-2-28"), ('120 314'),('130421');
TIME	HH:MM:SS 시간 표시 (시-분-초)	INSERT INTO time VALUES ('90:00:00'), ('800:00:00'), (800), (22), (151413), ('9:6:3'), ('12 09'); SELECT * FROM time;
DATETIME	YYYY-MM-DD hh:mm:ss (Date + Time )	CREATE TABLE t1 (d DATETIME); INSERT INTO t1 VALUES ("2011-03-11"), ("2012-04-19 13:08:22"), ("2013-07-18 13:44:22.123456");
TIMESTAMP	YYYY-MM-DD HH:MM:SS.ffffff.	MariaDB stores values that use the TIMESTAMP data type as the number of seconds since '1970-01-01 00:00:00' (UTC). This means that the TIMESTAMP data type can hold values between '1970-01-01 00:00:01' (UTC) and '2038-01-19 03:14:07' (UTC).
YEAR	년도 표시- 4자리 숫자 ( deprecated since MariaDB 5.5.27.)	A year in two-digit or four-digit format. The default is four-digit format. INSERT INTO y VALUES (1990),('2012');

3. Geometry Types

Geometry Type은 위치 정보를 표현합니다.

데이터 타입	의미
POINT	포인트 값(한 쌍의 x,y 좌표) CREATE TABLE gis_point (g POINT); SHOW FIELDS FROM gis_point; INSERT INTO gis_point VALUES (PointFromText('POINT(10 10)')), (PointFromText('POINT(20 10)')), (PointFromText('POINT(20 20)')), (PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));
LINESTRING	커브 값(하나 이상의 POINT값) CREATE TABLE gis_line (g LINESTRING); SHOW FIELDS FROM gis_line; INSERT INTO gis_line VALUES (LineFromText('LINESTRING(0 0,0 10,10 0)')), (LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')), (LineStringFromWKB(AsWKB(LineString(Point(10, 10), Point(40, 10)))));
POLYGON	다각형 CREATE TABLE gis_polygon (g POLYGON); SHOW FIELDS FROM gis_polygon; INSERT INTO gis_polygon VALUES (PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')), (PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')), (PolyFromWKB(AsWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0))))));
MULTIPOINT	POINT 값의 집합 CREATE TABLE gis_multi_point (g MULTIPOINT); SHOW FIELDS FROM gis_multi_point; INSERT INTO gis_multi_point VALUES (MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')), (MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')), (MPointFromWKB(AsWKB(MultiPoint(Point(3, 6), Point(4, 10)))));
MULTILINESTRING	LINESTRING 값의 집합 CREATE TABLE gis_multi_line (g MULTILINESTRING); SHOW FIELDS FROM gis_multi_line; INSERT INTO gis_multi_line VALUES (MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')), (MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')), (MLineFromWKB(AsWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7))))));
MULTIPOLYGON	다각형 값의 집합 CREATE TABLE gis_multi_polygon (g MULTIPOLYGON); SHOW FIELDS FROM gis_multi_polygon; INSERT INTO gis_multi_polygon VALUES (MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')), (MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')), (MPolyFromWKB(AsWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3)))))));
GEOMETRY	공간 값 CREATE TABLE gis_geometry (g GEOMETRY); SHOW FIELDS FROM gis_geometry; INSERT into gis_geometry SELECT * FROM gis_point; INSERT into gis_geometry SELECT * FROM gis_line; INSERT into gis_geometry SELECT * FROM gis_polygon; INSERT into gis_geometry SELECT * FROM gis_multi_point; INSERT into gis_geometry SELECT * FROM gis_multi_line; INSERT into gis_geometry SELECT * FROM gis_multi_polygon; INSERT into gis_geometry SELECT * FROM gis_geometrycollection;
GEOMETRYCOLLECTION	기하학(GEOMETRY) 값의 집합 CREATE TABLE gis_geometrycollection (g GEOMETRYCOLLECTION); SHOW FIELDS FROM gis_geometrycollection; INSERT INTO gis_geometrycollection VALUES (GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')), (GeometryFromWKB(AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))))), (GeomFromText('GeometryCollection()')), (GeomFromText('GeometryCollection EMPTY'));