# [ 살펴보기 ] MySQL - Data types 해당 포스트는 그 중 대표적인 data type과 특징을 살펴본다. Mysql이 지원하는 모든 data type은 documentation을 통해 확인할 수 있다. ( Reference - ) ## Numeric types MySQL에서 사용할 수 있는 numeric data type은 다음과 같다. * INT, INTEGER : –2,147,483,648에서 2,147,483,647 범위의 integer를 저장할 수 있는 4 bytes 크기의 data type. UNSIGNED keyword를 사용하면 0에서 4,294,967,295 범위의 integer를 저장할 수 있다. ```sql CREATE TABLE test ( id INT ); INSERT INTO test (id) VALUES (2147483647); // 허용범위 INSERT INTO test (id) VALUES (2147483648); // 허용범위 초과 INSERT INTO test (id) VALUES (-2147483648); // 허용범위 INSERT INTO test (id) VALUES (-2147483649); // 허용범위 초과 ``` ```sql CREATE TABLE test ( id INT UNSIGNED ); INSERT INTO test (id) VALUES (4294967295); // 하용범위 INSERT INTO test (id) VALUES (4294967296); // 허용범위 초과 INSERT INTO test (id) VALUES (0); // 하용범위 INSERT INTO test (id) VALUES (-1); // 허용범위 초과 ``` * BIGINT : `–9,223,372,036,854,775,808`에서 `9,223,372,036,854,775,807` 범위의 integer를 저장할 수 있는 8 byte 크기의 data type. UNSIGNED keyword를 사용하면 `0`에서 `18,446,744,073,709,551,615` 범위의 integer를 저장할 수 있다. * TINYINT : `-128`에서 `127` 범위의 integer를 저장할 수 있는 1 byte 크기의 data type. UNSIGNED keyword를 사용하면 0에서 255 범위의 integer까지 저장할 수 있다. * SMALLINT : `-32,768`에서 `32,767` 범위의 integer를 저장할 수 있는 2 bytes 크기의 data type. UNSIGNED keyword를 사용하면 `0`에서 `65,535` 범위의 integer까지 저장할 수 있다. * MEDIUMINT : `-8,388,608`에서 `8,388,607` 범위의 integer를 저장할 수 있는 3 bytes 크기의 data type. UNSIGNED keyword를 사용하면 `0`에서 `16,777,215`범위의 integer까지 저장할 수 있다. * SERIAL : SERIAL data type은 `BIGINT UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT`를 적용한 결과와 동일한 역할을 한다. * BIT : Bit 값을 저장하기 위한 data type. BIT(2) 또는 BIT(5)와 같이 저장할 수 있는 bit number를 지정할 수 있다. ```sql CREATE TABLE test ( my_bit BIT (2) ); INSERT INTO test (my_bit) VALUES (3); SELECT my_bit FROM test; /* b'11' */ INSERT INTO test (my_bit) VALUES (4); /* 위에서 BIT(2)를 통해 2라는 bit number를 설정했고 4는 binary로 100이므로 bit number가 3이상이여야 하므로 허용 범위 초과 */ ``` ## Bool ( Boolean ) types MySQL에서 boolean type은 TINYINT type으로 취급되므로 TINYINT data type에 허용된 범위의 값을 저장할 수 있으나 0은 false로 취급되고 0외에 나머지 숫자는 true로 취급된다. 1과 0 대신 TRUE, FALSE를 값으로 사용할 수 있으며 TRUE는 1, FALSE는 0으로 저장된다. ```sql CREATE TABLE test ( joined BOOL ); INSERT INTO test (joined) VALUES (FALSE); /* result : 0 */ INSERT INTO test (joined) VALUES (TRUE); /* result : 1 */ ``` ## Fixed-point types 소수점을 포함할 수 있으며 정확한 값을 표현하기 위해 사용한다. Fixed-point type에 사용할 수 있는 있는 type은 DECIMAL와 NUMERIC이 있다. * DECIMAL : Fixed-point number를 저장하기 위해 사용하며 DECIMAL(precision, scale) 형식으로 type을 지정할 수 있다.예를 들어 DECIAL( 5,2 ) type에 저장 할 수 있는 범위는 -999.99에서 999.99가 된다. decimals에 설정하는 값은 width에 설정하는 값을 초과할 수 없으며 width에 설정할 수 있는 최대 값은 65, decimals에 설정할 수 있는 최대 값은 30이다. ```sql CREATE TABLE test ( my_decimal DECIMAL(10,5) ); INSERT INTO test ( my_decimal ) VALUES (1234.56789); /* result : 1234.56789 */ INSERT INTO test ( my_decimal ) VALUES (1234.5678912); /* result : 1234.56789 */ INSERT INTO test ( my_decimal ) VALUES (1234.5677799); /* result : 1234.56778 */ INSERT INTO test ( my_decimal ) VALUES (123456.5677799); /* 허용 범위 초과 */ SELECT CAST(0.1 AS DECIMAL(10,1)) + CAST(0.2 AS DECIMAL(10,1)) AS decimal_result; /* result : 0.3 */ ``` scale없이 다음과 precision만 선언하면 scale은 default로 0으로 적용된다. ```sql CREATE TABLE test ( my_decimal DECIMAL (5) ); /* 위의 예제는 DECIMAL (5,0)와 같다. */ ``` 다음과 같이 precision과 scale없이 DECIMAL만 사용한다면 default precision은 10, 그리고 scale은 0이 설정된다. ```sql CREATE TABLE test ( my_decimal DECIMAL ); /* 위의 예제는 DECIMAL (10,0)와 같다. */ ``` 만약 positive value만 허용하고 싶다면 다음과 같이 UNSIGNED keyword를 사용한다. ```sql CREATE TABLE test ( my_decimal DECIMAL (5,2) UNSIGNED ); ``` ## Floating-point types 소수점을 포함할 수 있으며 Fixed-point type과는 달리 대략적인 값을 표현하기 위해 사용되는 data type이다. floating-point type에 사용할 수 있는 있는 type은 FLOAT와 DOUBLE이 있다. * Float : Floating-point number를 저장하기 위해 사용하는 4 bytes 크기의 data type다. ```sql CREATE TABLE test ( my_decimal FLOAT ); INSERT INTO test ( my_float ) VALUES (1234.56789); /* result : 1234.57 */ INSERT INTO test ( my_float ) VALUES (123456789.77); /* result : 123457000 */ SELECT CAST(0.1 AS FLOAT) + CAST(0.2 AS FLOAT) AS float_result; /* result : 0.30000000447034836 */ ``` 만약 positive value만 허용하고 싶다면 다음과 같이 UNSIGNED keyword를 사용한다. ```sql CREATE TABLE test ( my_float FLOAT(5,2) UNSIGNED ); ``` FLOAT 또는 DOUBLE type을 사용할 때 `FLOAT(M,D)`과 `DOUBLE(M,D)`와 같은 syntax는 8.0.17 version부터 deprecated되었으니 주의하자. ## String types MySQL에서 사용할 수 있는 string data type은 CHAR, VARCHAR, BINARY, TEXT, ENUM 등이 있다. * CHAR : `CHAR(length)`형식으로 선언하고 length에 선언한 값이 저장할 수 있는 string의 최대 length가 된다. 선언할 수 있는 최대 length는 255다. ```sql CREATE TABLE test ( my_char CHAR(3) ); INSERT INTO test ( my_char ) VALUES ("abc"); /* result : abc */ INSERT INTO test ( my_char ) VALUES ("abcd"); /* 허용 범위 초과 */ ``` * VARCHAR : `VARCHAR(length)` 형식으로 선언하고 length에 선언한 값이 저장할 수 있는 string의 최대 length가 된다. width에 선언할 수 있는 최대 값은 65535이다. ```sql CREATE TABLE test ( my_varchar VARCHAR(3) ); INSERT INTO test ( my_varchar ) VALUES ("abc"); /* result : abc */ INSERT INTO test ( my_varchar ) VALUES ("abcd"); /* 허용 범위 초과 */ ``` * TEXT : CHAR, VARCHAR와는 달리 특정 제한 length를 설정하지 않고 사용하는 string type이다. 저장할 수 있는 최대 length는 65535이며 TEXT type은 default 값을 설정할 수 없다. ```sql CREATE TABLE test ( my_text TEXT ); INSERT INTO test ( my_text ) VALUES ("abc"); /* result : abc */ ``` TEXT type 뿐만 아니라 `TINYTEXT`, `MIDIUMTEXT`, `LONGTEXT` type 역시 사용할 수 있다. TINYTEXT는 최대 length 255, MIDIUMTEXT는 최대 length 16777215, LONG TEXT는 최대 length 4294967295까지 저장할 수 있다. TEXT type은 매우 긴 length의 string data를 저장할 수 있기에 다음과 같은 제약 사항이 따른다. ( *Reference* - [The BLOB and TEXT Types](https://dev.mysql.com/doc/refman/8.4/en/blob.html) ) 1. TEXT type의 column을 기준으로 sorting을 진행할 때 max\_sort\_length에 설정된 값에 해당 하는 범위가 sorting을 수행할 때 사용되며 default 값은 1024다. 2. client와 db server 사이 전달할 수 있는 최대 크기의 data는 memory와 buffer size에 따라 달라질 수 있다. * ENUM : 특정 column에 추가할 수 있는 값을 미리 정해 놓고 사용할 때 적용할 수 있다. 아래는 my\_enum이라는 column에 저장할 수 있는 값을 small, medium, large로 제한하는 enum type을 적용한다. ENUM의 각 element에 설정할 수 있는 값의 최대 length는 255다. ```sql CREATE TABLE test ( my_enum ENUM('small', 'medium', 'large') ); INSERT INTO test ( my_enum ) VALUES ("small"); /* result : small */ INSERT INTO test ( my_enum ) VALUES ("extra-small"); /* 허용되지 않는 값 */ ``` ENUM type이 적용된 column에 별도의 NOT NULL constraint이 없다면 NULL 값이 허용된다. ```sql CREATE TABLE test ( my_enum ENUM('small', 'medium', 'large') ); INSERT INTO test ( my_enum ) VALUES (NULL); /* result : NULL */ ``` ## Date, Time types MySQL에서 사용할 수 있는 date, time 관련 data type은 DATE, TIME, DATETIME, TIMESTAMP, YEAR type이 있다. * DATE : YYYY-MM-DD 형식의 data를 저장할 수 있다. 1000-01-01에서 9999-12-31까지의 범위를 저장할 수 있다. DATE type의 data를 추가할 때는 아래의 예제와 같이 number형식으로 추가할 수도 있고 string 형식으로 추가할 수도 있다. ```sql CREATE TABLE test ( test_field DATE ); INSERT INTO test ( test_field ) VALUES (20241210); /* result : 2024-12-10 */ INSERT INTO test ( test_field ) VALUES ('20241210'); /* result : 2024-12-10 */ INSERT INTO test ( test_field ) VALUES ('2024-12-10'); /* result : 2024-12-10 */ ``` * DATETIME : YYYY-MM-DD hh:mm:ss\[.fraction\] 형식의 data를 저장할 수 있다. `.fraction` 부분은 microseconds 단위까지 사용할 수 있으며 1000-01-01 00:00:00.000000에서 9999-12-31 23:59:59.499999까지의 범위를 저장할 수 있다. DATETIME type의 data를 추가할 때는 아래의 예제와 같이 number형식으로 추가할 수도 있고 string 형식으로 추가할 수도 있다. ```sql CREATE TABLE test ( test_field DATETIME(3) ); INSERT INTO test ( test_field ) VALUES (20241210201030); /* result : 2024-12-10 20:10:30 */ INSERT INTO test ( test_field ) VALUES ('20241210201030'); /* result : 2024-12-10 20:10:30 */ INSERT INTO test ( test_field ) VALUES ('2024-12-10 20:10:30'); /* result : 2024-12-10 20:10:30 */ INSERT INTO test ( test_field ) VALUES ('2024-12-10 20:10:30.123'); /* result : 2024-12-10 20:10:30.123 */ INSERT INTO test ( test_field ) VALUES ('2024-12-10 20:10:30.12345'); /* result : 2024-12-10 20:10:30.123 */ ``` * TIME : hh:mm:ss\[.fraction\] 형식의 data를 저장할 수 있다. `.fraction` 부분은 microseconds 단위까지 사용할 수 있으며 저장할 수 있는 범위는 -838:59:50.000000에서 838:59:59.000000다. TIME type의 data를 추가할 때는 아래의 예제와 같이 number형식으로 추가할 수도 있고 string 형식으로 추가할 수도 있다. ```sql CREATE TABLE test ( test_field TIME(3) ); INSERT INTO test ( test_field ) VALUES (201220); /* result : 20:12:20.000 */ INSERT INTO test ( test_field ) VALUES ('201220'); /* result : 20:12:20.000 */ INSERT INTO test ( test_field ) VALUES ('20:12:20'); /* result : 20:12:20.000 */ INSERT INTO test ( test_field ) VALUES ('20:12:20.123'); /* result : 20:12:20.123 */ INSERT INTO test ( test_field ) VALUES ('20:12:20.12345'); /* result : 20:12:20.123 */ ``` * YEAR : YYYY 형식의 data를 저장할 수 있다. YEAR type의 data를 추가할 때는 아래의 예제와 같이 number형식으로 추가할 수도 있고 string 형식으로 추가할 수도 있다. ```sql CREATE TABLE test ( test_field YEAR ); INSERT INTO test ( test_field ) VALUES (2024); /* result : 2024 */ INSERT INTO test ( test_field ) VALUES ('2024'); /* result : 2024 */ ``` 추가로 MySQL Date type을 사용할 때 유의해야 할 점은 다음과 같다. * Date type에 특정 값을 추가할 때는 day-month-year 또는 month-day-year 형식이 아닌 year-month-day 형식으로 추가해야 한다. * Date type을 추가할 때 00 또는 99와 같이 두 자리수를 year로 사용하면 MySQL은 다음과 같이 해석한다. 70 - 99는 1970 - 1999 그리고 00 - 69는 2000 - 2069로 해석한다. ## JSON type MySQL은 json type data type 역시 지원하기에 json type data를 저장할 수 도 있다. JSON type data 저장을 위해 필요한 공간은 대략 LONGTEXT type과 같다. 또한 JSON type column data가 가질 수 있는 최대 size는 `max_allowed_packet` system variable에 의해 한정된다. 다음은 table을 생성할 때 JSON data type을 가진 column을 포함해서 생성하는 예제다. ```sql CREATE TABLE test ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(20), detail JSON ); ``` 그리고 데이터를 추가할 때는 다음과 같이 추가해준다. ```sql INSERT INTO test (title, detail) VALUES ("test", '{"name": "test name", "address": "test address"}'); ``` 또는 다음과 같이 `JSON_OBJECT` function을 사용해 json data를 추가할 수도 있다. ```sql INSERT INTO test (title, detail) VALUES ("test", JSON_OBJECT('name', 'test name2', 'address', 'test address2')); ``` json data의 특정 property가 array value를 가질 때는 다음과 같이 추가할 수 있다. ```sql INSERT INTO test (title, detail) VALUES ("test", '{"name": "test name", "address": "test address", "hobbies":["sports", "painting"]}'); INSERT INTO test (title, detail) VALUES ("test", JSON_OBJECT('name', 'test name2', 'address', 'test address2', 'hobbies', JSON_ARRAY("reading" , "movie"))); ``` JSON type data를 추가할 때 추가하는 value가 valid json type이 아닐 경우 오류가 발생한다. 추가한 data를 조회해보면 다음과 같이 json data가 return되는 것을 확인할 수 있다. ```sql SELECT * FROM test; /* id title detail 1 test {"name": "test name", "address": "test address"} */ ``` 그리고 만약 json data 중에 특정 property의 data가 search result에 포함하고 싶다면 다음과 같이 `JSON_EXTRACT` function을 사용할 수 있다. 다음 예제는 json data 중 address property value만 search result에 포함하는 예제다. ```sql SELECT id, title, JSON_EXTRACT(detail, '$.address') AS address FROM test; /* id title address 1 test "test address" */ ``` 또한 `WHERE`를 통해 json data에서 특정 value를 가진 row를 search할 때도 다음과 같이 `JSON_EXTRACT` function을 사용할 수 있다. 예를 들어 다음은 json data 중 address property의 value가 test address2인 data row를 return한다. ```sql SELECT * FROM test WHERE JSON_EXTRACT(detail, '$.address') = 'test address2'; ``` 이제 json type data를 수정하는 방법을 살펴보자. 다음은 id column이 1인 data row의 detail json data에 age property를 추가하는 예제다. ```sql UPDATE test SET detail = JSON_INSERT(detail, '$.age', '20') WHERE id = 1; SELECT * FROM test; /* 1 test {"age": "20", "name": "test name", "address": "test address"} */ ``` 또한 json data property에 array data를 추가할 때는 다음과 같이 `JSON_ARRAY` function을 사용해서 추가할 수 있다. ```sql UPDATE test SET detail = JSON_INSERT(detail, '$.hobbies', JSON_ARRAY("game")) WHERE id = 1; /* 1 test {"age": "20", "name": "test name", "address": "test address", "hobbies": ["game"]} */ ``` 기존에 있는 json data의 특정 property value를 다른 value로 변경하고자 한다면 다음과 같이 `JSON_REPLACE`를 통해 변경한다. ```sql UPDATE test SET detail = JSON_REPLACE(detail, '$.age', '25') WHERE id = 1; SELECT * FROM test; /* 1 test {"age": "25", "name": "test name", "address": "test address"} */ ``` array value를 가진 property 역시 다음과 같이 수정할 수 있다. ```sql UPDATE test SET detail = JSON_REPLACE(detail, '$.hobbies', JSON_ARRAY('reading')) WHERE id = 1; /* 1 test {"age": "25", "name": "test name", "address": "test address", "hobbies": ["reading"]} */ ``` `JSON_INSERT` function은 추가하는 data가 이미 존재하지 않을 때만 data를 추가하며 추가하려는 data가 이미 존재하면 별도로 추가 작업을 하진 않는다. 만약 data가 존재하지 않을 때 data를 새로 추가하거나 기존 data가 있다면 새로운 data로 update하고자 한다면 `JSON_INSERT` 대신 `JSON_SET` function을 사용할 수 있다. 아래 statement를 실행하면 json data에 age property가 없으면 새로 추가하고 age proeprty가 있으면 20이라는 value로 update한다. ```sql UPDATE test SET detail = JSON_SET(detail, '$.age', '20') WHERE id = 1; ``` json data의 특정 property를 삭제할 때는 `JSON_REMOVE` function을 사용한다. 다음은 id가 1인 data row의 detail column에 저장된 json data에서 age property를 삭제하는 예제다. ```sql UPDATE test SET detail = JSON_REMOVE(detail, '$.age') WHERE id = 1; ``` 또는 다음과 같이 json data를 기준으로 data row를 삭제할 수도 있다. 다음 예제는 detail column의 json data 중 address proeprty의 value가 test address인 모든 row를 삭제한다. ```sql DELETE FROM test WHERE JSON_EXTRACT(detail, '$.address') = "test address"; ```