[ 살펴보기 ] MySQL

해당 포스트는 그 중 대표적인 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를 저장할 수 있다.

  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);
  // 허용범위 초과

  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를 지정할 수 있다.

  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으로 저장된다.

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

  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으로 적용된다.

  CREATE TABLE test ( my_decimal DECIMAL (5) );
  /* 위의 예제는 DECIMAL (5,0)와 같다.  */

다음과 같이 precision과 scale없이 DECIMAL만 사용한다면 default precision은 10, 그리고 scale은 0이 설정된다.

  CREATE TABLE test ( my_decimal DECIMAL );
  /* 위의 예제는 DECIMAL (10,0)와 같다.  */

만약 positive value만 허용하고 싶다면 다음과 같이 UNSIGNED keyword를 사용한다.

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

  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를 사용한다.

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

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

  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 값을 설정할 수 없다.
```
  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 )
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다.
```
  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 값이 허용된다.
```
  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 형식으로 추가할 수도 있다.

  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 형식으로 추가할 수도 있다.

  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 형식으로 추가할 수도 있다.

  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 형식으로 추가할 수도 있다.

  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을 포함해서 생성하는 예제다.

CREATE TABLE test (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    title VARCHAR(20), 
    detail JSON
);

그리고 데이터를 추가할 때는 다음과 같이 추가해준다.

INSERT INTO test (title, detail) VALUES ("test", '{"name": "test name", "address": "test address"}');

또는 다음과 같이 JSON_OBJECT function을 사용해 json data를 추가할 수도 있다.

INSERT INTO test (title, detail) VALUES ("test", JSON_OBJECT('name', 'test name2', 'address', 'test address2'));

json data의 특정 property가 array value를 가질 때는 다음과 같이 추가할 수 있다.

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되는 것을 확인할 수 있다.

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에 포함하는 예제다.

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

SELECT * FROM test WHERE JSON_EXTRACT(detail, '$.address') = 'test address2';

이제 json type data를 수정하는 방법을 살펴보자. 다음은 id column이 1인 data row의 detail json data에 age property를 추가하는 예제다.

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을 사용해서 추가할 수 있다.

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를 통해 변경한다.

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 역시 다음과 같이 수정할 수 있다.

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

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를 삭제하는 예제다.

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를 삭제한다.

DELETE FROM test WHERE JSON_EXTRACT(detail, '$.address') = "test address";