mysql 기본 데이터 타입

1. 숫자형

Data Type	Storage Used (Bytes)	Min. Signed	Max. Signed	Min. Unsigned	Max. Unsigned
TINYINT	1	-128 127	127	0	255
SMALLINT	2	-32,768	32,767	0	65,535
MEDIUMINT	3	-8,388,608	8,388,607	0	6,777,215
INTEGER	4	-2,147,483,648	2,147,483,647	0	4,294,967,295
BIGINT	8	-9,223,372,	9,223,372,	0	18,446,744,
		036,854,775,	036,854,775,		073,709,551,
		808	807		615

2. 문자형 타입

Data Type	Storage Used (Bytes)	Maximum
CHAR(m)	m × maximum-size character in the character set	255 chars
VARCHAR(m)	up to 2 bytes + m × maximum-size character in the character set	65,535 chars
TEXT	size of string in bytes + 2	65,535 chars
TINYTEXT	size of string in bytes + 1	255 chars
MEDIUMTEXT	size of string in bytes + 3	16,777,215 chars
LONGTEXT	size of string in bytes + 4	4,294,967,295 chars
BINARY(m)	m	255 bytes
VARBINARY(m)	up to 2 bytes + m	65,535 bytes
BLOB	size of string in bytes + 2	65,535 bytes
TINYBLOB	size of string in bytes + 1	255 bytes
MEDIUMBLOB	size of string in bytes + 3	16,777,215 bytes
LONGBLOB	size of string in bytes + 4	4,294,967,295 bytes
ENUM	up to 2 bytes	65,535 values
SET	up to 8 bytes	64 members

3. Date 타입

Data Type	Storage Used (Bytes)	Minimum	Maximum
DATETIME	8	1000-01-01 00:00:00	9999-12-31 23:59
TIMESTAMP	4	1970-01-01 00:00:01 UTC	2038-01-19 03:14:07 UTC
DATE	3	1000-01-01	9999-12-31
TIME	3	-838:59:59	838:59:59
YEAR	1	1901	2155

4. 스토리지엔진별 지원내역

Feature	InnoDB	MyISAM	Archive	Blackhole	CSV	Federated	Merge	Memory	NDB
Storage limit	64TB	256TB	No Limit	N/A	File system	N/A	No Limit	RAM	384EB
ACID	Yes	No	No	No	No	?	No	No	Yes
Foreign keys enforced	Yes	No	No	No	No	?	No	No	Yes
Indexes	Yes	Yes	No	N/A	No	No	Yes	Yes	Yes
Concurrent inserts	Yes	Yes	Yes	Yes	Yes	?	Yes	No	Yes
UNIQUE enforced	Yes	Yes	No	No	No	?	No	Yes	Yes
Fulltext search	Yes	Yes	No	N/A	No	?	No	No	No
Lock level	Row	Table	Table	Table	Table	?	Table	Table	Row
Transactions	Yes	No	No	No	No	?	No	No	Yes
Spatial data types	Yes	Yes	Yes	Yes	No	?	No	No	Yes
Encryption	Yes	Yes	Yes	N/A	Yes	Yes	Yes	Yes	Yes
Replication	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Clustering	No	No	No	No	No	No	No	No	Yes

5. 데이터 사용예제

CREATE TABLE employee (

    employee_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

    last_name VARCHAR(30) NOT NULL,

    first_name VARCHAR(30) NOT NULL,

    email VARCHAR(100) NOT NULL,

    hire_date DATE NOT NULL,

    notes MEDIUMTEXT,

    PRIMARY KEY (employee_id),

    INDEX (last_name),

    UNIQUE (email)

)

ENGINE=InnoDB;

CREATE TABLE address (

    employee_id INTEGER UNSIGNED NOT NULL,

    address VARCHAR(50) NOT NULL,

    city VARCHAR(30) NOT NULL,

    state CHAR(2) NOT NULL,

    postcode CHAR(5) NOT NULL,

    FOREIGN KEY (employee_id)

        REFERENCES employee (employee_id)

)

ENGINE=InnoDB;