[TIL] 데이터분석 데브코스 13일차 - 다양한 SQL 데이터 타입: 숫자/문자/이진(binary)/배열(array)/구조체

📌 학습주제

1. 데이터타입 - 숫자
2. 데이터타입 - 문자
3. 데이터타입 - 이진
4. 데이터타입 - 배열
5. 데이터타입 - 구조체

 

 

 

데이터 타입 (1) - 숫자

BIT(M)

: 0과 1로만 구성된 것으로 컴퓨터가 데이터를 저장하는 가장 기본 단위이다. 

- M : 비트의 자릿수 (1 <= M <= 64, 기본값 : M = 1)

 

ex. b'111' = 7

 

 

TINYINT

: 매우 작은 정수를 담는 타입

 

- Signed 범위 : -127 ~ 127

- Unsigned 범위 : 0 ~ 255

- 정수 타입이고 끝 값이 명확하게 정해져있는 경우 사용하면 좋다.

- 기존 INT 타입에 비해서 저장공간을 훨씬 더 절약할 수 있다.

- 0부터 256(=2^8)까지 나타낼 수 있다. 즉, 8비트까지 사용 가능하다는 의미.

 

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int unsigned NOT NULL,
  `category` varchar(40) NOT NULL,
  `name` varchar(50) NOT NULL,
  `price` tinyint unsigned NOT NULL,
  PRIMARY KEY (`product_id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `products` (`product_id`, `category`, `name`, `price`) VALUES
  (0, '키즈', '어린이칫솔', 1500),
  (1, '스포츠', '손목보호대', 10000),
  (2, '주방용품', '밥그릇', 2000),
  (3, '디지털', '마우스', 15000);

** TINYINT는 Unsigned 타입이더라도 0 ~ 255까지 담을 수 있는데,  'price' 컬럼값들이 모두 255 이상이기 때문에 Build Schema를 했을 때 "Data truncation: Out of range value for column 'price' at row1" 에러가 발생한다.

 

 

'price' 컬럼값을 255 이하의 값들로 수정해보자.

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int unsigned NOT NULL,
  `category` varchar(40) NOT NULL,
  `name` varchar(50) NOT NULL,
  `price` tinyint unsigned NOT NULL,
  PRIMARY KEY (`product_id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `products` (`product_id`, `category`, `name`, `price`) VALUES
  (0, '키즈', '어린이칫솔', 150),
  (1, '스포츠', '손목보호대', 100),
  (2, '주방용품', '밥그릇', 200),
  (3, '디지털', '마우스', 255);

 

BOOL, BOOLEAN

: 참/거짓 여부를 담는 타입

 

- TINYINT(1)과 같다.

- TINYINT(1) = 0 : FALSE

- TINYINT(1) = 1 : TRUE

- On/Off 여부를 확인해야 할 때 사용하면 좋다.

 

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int unsigned NOT NULL,
  `category` varchar(40) NOT NULL,
  `name` varchar(50) NOT NULL,
  `price` tinyint unsigned NOT NULL,
  `sales_yn` BOOL NOT NULL,
  PRIMARY KEY (`product_id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `products` (`product_id`, `category`, `name`, `price`, `sales_yn`) VALUES
  (0, '키즈', '어린이칫솔', 150, true),
  (1, '스포츠', '손목보호대', 100, true),
  (2, '주방용품', '밥그릇', 200, false),
  (3, '디지털', '마우스', 255, true);

 

 

쿼리문을 실행시켜서 컬럼이 제대로 생성되었는지 확인해보자.

SELECT *
FROM products
WHERE sales_yn = TRUE

 

 

 

 

 

TINYINT타입의 'price' 컬럼값을 실수(FLOAT)로 변경하면 결과는 어떻게 될까?

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int unsigned NOT NULL,
  `category` varchar(40) NOT NULL,
  `name` varchar(50) NOT NULL,
  `price` tinyint unsigned NOT NULL,
  `sales_yn` BOOL NOT NULL,
  PRIMARY KEY (`product_id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `products` (`product_id`, `category`, `name`, `price`, `sales_yn`) VALUES
  (0, '키즈', '어린이칫솔', 150.7, true),
  (1, '스포츠', '손목보호대', 100, true),
  (2, '주방용품', '밥그릇', 200, false),
  (3, '디지털', '마우스', 255, true);

SELECT *
FROM products
WHERE sales_yn = TRUE

 

 

소수부분이 자동으로 반올림되어 결과로 출력되는 것을 알 수 있다. DB 종류에 따라 약간씩 다르긴 하지만 거의 비슷하다. 하지만 사용하기 전에 꼭 알아보도록 하자!

 

 

 

BOOLEAN 타입은 IF 조건문과도 많이 사용한다. 위의 테이블 예제를 통해 확인해보자.

SELECT *, IF(sales_yn, 'ON sale!', 'NA') as sales_string
FROM products

 

 

 

 

 

 

SMALLINT

: 약 6만개의 정수를 나타낼 수 있는 정수 타입 (-32768 ~ 32767, 0 ~ 65535)

 

- 2^16(=65236)이므로 SMALLINT 타입은 16비트까지 사용 가능하다.

 

 

MEDIUMINT

: 약 1670만개의 정수를 표현할 수 있는 정수 타입 ( -8388608 ~ 8388607, 0 ~ 16777215)

 

- 2^24(=약 16770000)이므로 MEDIUMINT 타입은 24비트까지 사용 가능하다.

 

 

INT, INTEGER

: 일반적으로 사용하는 정수 타입

 

2^32(=약 42.9억)이므로 INT, INTEGER 타입은 32비트까지 사용 가능하다.

 

 

BIGINT, SERIAL

: INT타입 보다 2배 많은 비트수를 사용하는 정수 타입

 

- 64비트까지 사용 가능하다.

- BIGINT 중에서도 부호가 없는 타입을 SERIAL이라 한다.

 

 

DECIMAL(M, D), DEC, FIXED

: 고정소수점 타입

 

- M : 숫자 전체의 자릿수 (M <= 65)

- D : 소수점 이하의 자릿수 (0 <= D <= 30)

- 소수점 이하의 자릿수를 미리 고정해두고, 고정된 자릿수로만 소수부분을 표현한다

 

 

FLOAT

: 부동소수점 타입

 

- 4바이트(=32비트)를 사용한다.

 

 

DOUBLE

: FLOAT보다 더 큰 부동소수점 타입

 

- 8바이트(=64비트)를 사용한다.

 

 

 

-- 0은 FALSE로 간주되기 때문에 FALSE반환값인 'false'가 출력된다.
SELECT IF(0, 'true', 'false')

 

-- 1은 TRUE로 간주되기 때문에 TRUE반환값인 'true'가 출력된다.
SELECT IF(1, 'true', 'false')

 

-- TRUE = 1, FALSE = 0
-- 1이 아닌 숫자도 TRUE로 간주되기 때문에 TRUE반환값인 'true'가 출력된다.
SELECT IF(2, 'true', 'false')

 

 

-- TRUE를 표현하는 1이 2와 같다는 의미이기 때문에 'false'를 출력한다.
SELECT IF(2=TRUE, 'true', 'false')

 

 

 

 

 

 

 

 

데이터 타입 (2) - 문자

CHAR

: 고정된 길이의 문자열로 길이 0에서 255 사이를 갖는다.

 

- 선언된 값보다 짧은 길이의 문자열이 들어오면, 빈 문자열로 나머지 공간을 채운다.

- 단, SELECT 문을 통해 값을 가져올 때는 채워진 빈 문자열은 제외한채로 반환된다.

- CHAR() 괄호 안의 값은 byte가 아닌 문자열의 고유한 길이(글자수)이다. (한글, 영문 동일)

+) LENGTH() 함수에서는 해당 문자열이 차지하는 byte수를 반환한다.

 

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int unsigned NOT NULL,
  `category` varchar(40) NOT NULL,
  `name` char(4) NOT NULL,
  `price` int unsigned NOT NULL,
  PRIMARY KEY (`product_id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `products` (`product_id`, `category`, `name`, `price`) VALUES
  (0, '키즈', '어린이칫솔', 1500),
  (1, '스포츠', '손목보호대', 10000),
  (2, '주방용품', '밥그릇', 2000),
  (3, '디지털', '마우스', 15000);

** 'name'컬럼의 '어린이칫솔'과 '손목보호대'의 경우 4글자가 아니기 때문에

'Data truncation: Data too long for column 'name' at row 1' 에러가 발생한다.

 

 

 

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int unsigned NOT NULL,
  `category` varchar(40) NOT NULL,
  `name` char(4) NOT NULL,
  `price` int unsigned NOT NULL,
  PRIMARY KEY (`product_id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `products` (`product_id`, `category`, `name`, `price`) VALUES
  (0, '키즈', '어린   ', 1500),
  (1, '스포츠', '손목   ', 10000),
  (2, '주방용품', '밥  ', 2000),
  (3, '디지털', '마우 ', 15000);

이 경우 테이블이 정상적으로 생성된다. 즉, CHAR()은 공백을 무시하고 글자수를 체크한다는 의미이다.

 

 

 

 

 

VARCHAR

: 변동 가능한 길이의 문자열로 길이 0에서 65535 바이트 사이를 갖는다.

 

(* 65535byte = MySQL에서 하나의 행이 가질 수 있는 최대 길이)

- 한 행에 다른 컬럼들의 데이터도 저장하려면 VARCHAR 컬럼 하나가 한 행이 쓸 수 있는 전체 공간을 전부 사용할 수 없다. 따라서 실제로는 65535byte보다 짧은 길이를 담아야 문제가 발생하지 않는다.

- 실제 데이터 뿐만 아니라 1byte 혹은 2byte짜리의 prefix를 갖는다. 이 prefix는 실제 문자열 크기가 얼마인지(=데이터가 몇 byte짜리 인지) 저장하기 위해 사용된다. 저장된 문자열 크기가 255byte 이하이면 prefix는 1byte를 사용하고

255byte 초과이면 prefix는 2byte를 사용한다. 만약 지정한 길이를 초과하는 문자열이 들어오면 공백이 아닌 문자열이 지정된 길이를 초과해서 들어왔을 때 INSERT시에 에러가 발생한다. 공백이 추가로 들어온 경우에는 공백 뒤쪽이 잘려서 저장된다. 예를 들어 CHAR(5)로 지정했을 때 길이 7짜리 문자열이 들어오면 초과된 문자열이 공백일 경우 뒤쪽 공백이 잘려서 저장되고 공백이 아닌 문자열일 경우 에러가 발생한다.

- 데이터 관리 측면에서는 장점이다. 일정 길이를 초과하면 잘리거나 에러가 발생하기 때문에 문자열 데이터가 원하는 형태로 잘 들어왔는지 확인 가능하다. 따라서 현업에서는 TEXT 타입보다 더 많이 사용된다.

- 기본 값을 지정할 수 있다.

 

 

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int unsigned NOT NULL,
  `category` varchar(40) NOT NULL,
  `name` varchar(10) NOT NULL,
  `price` int unsigned NOT NULL,
  PRIMARY KEY (`product_id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `products` (`product_id`, `category`, `name`, `price`) VALUES
  (0, '키즈', '손목 ', 1500),
  (1, '스포츠', '손목', 10000),
  (2, '주방용품', '밥 ', 2000),
  (3, '디지털', '밥', 15000);

 

공백이 잘 저장되었는지 SQL 쿼리문을 실행해서 확인해보자.

-- LENGTH() 함수는 byte수를 세기 때문에 공백도 카운트한다.
SELECT LENGTH(name) as l
from products

 

 

 

공백까지 저장되었음을 확인할 수 있다.

즉, 공백이 맨 뒤에 들어간 문자열이더라도 지정된 타입 길이 이내라면 공백 또한 함께 저장된다.

참고로 한글은 한글자에 3byte 영어는 1byte, 공백은 1byte를 차지한다.

 

위의 예제를 보았을때,

'손목 '은 한글 2개 + 공백 1칸 = 3 * 2 + 1 = 7 이 저장되고

'손목'은 한글 2개 = 3 * 2 = 6 이 저장되고

'밥 '은 한글 1개 + 공백 1칸 = 3 * 1 + 1 = 4 이 저장되고

'밥'은 한글 2개 + 공백 1칸 = 3 * 1 = 3 이 저장된다.

 

 

 

TEXT

: 변동 가능한 길이의 문자열(길이 지정 불가)

 

- 최대 길이만 넘지 않으면 문자열 덩어리를 길이에 상관없이 집어넣는 방식으로 사용한다.

- 문자열 데이터가 원하는 형태로 잘 들어왔는지 확인이 불가능하다.

- 기본값 지정이 불가능하다.

- 쿼리 시에 메모리가 아닌 디스크를 사용하기 때문에, VARCHAR 또는 CHAR 타입보다 쿼리문 실행 반응이 느리다.

- 일반적으로 메모리에 무언가 올려서 가져다 쓸때 디스크에서 읽어서 사용하는 것보다 훨씬 빠르기 때문이다.

 

 

TINYTEXT

: 짧은 길이의 TEXT (최대 255byte까지 저장 가능)

 

 

 

MEDIUMTEXT

: 중간 길이의 TEXT (최대 1670만byte까지 저장 가능)

 

 

 

 

LONGTEXT

: 긴 길이의 TEXT (최대 42.9억byte까지 저장 가능)

 

 

 

ENUM

: 최초에 지정해 둔 리스트에 포함된 값만 저장

 

- 제한된 값 리스트를 미리 저장해두고 그 안에 있는 값만 삽입할 수 있도록 하는 타입

- 컬럼 타입을 들어갈 수 있는 값 리스트를 지정해준다.

- 만약 해당 리스트에 없는 값을 테이블에 INSERT하려고 하면 에러가 발생한다.

- 미리 지정한 리스트에 없는 값을 테이블에 INSERT하려고 하면 빈 문자열이 들어가는 경우도 있다. (에러 발생은 DB 종류에 따라 다르다.)

- 훨씬 적은 용량으로 동일한 데이터를 저장할 수 있는 장점

ex. 강아지, 고양이, 원숭이 

 

이처럼 들어올 수 있는 값이 절대 바뀌지 않는 경우 ENUM 타입은 저장소 공간을 효율적으로 사용하는 데 도움이 된다.

하지만 현업에서 ENUM타입을 사용할 수 있는 경우는 많지 않다. 유연성과 확장성이 낮기 때문에 데이터 내용에 변경사항이 있는 경우 작업이 번거로워지기 때문이다.

 

 

만약 ENUM 타입 컬럼에 미리 지정해둔 값이 아닌 데이터가 삽입되면 어떻게 될까?

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int unsigned NOT NULL,
  `category` varchar(40) NOT NULL,
  `name` varchar(10) NOT NULL,
  `price` int unsigned NOT NULL,
  `size` ENUM('XS', 'S', 'M', 'L', 'XL'),
  PRIMARY KEY (`product_id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `products` (`product_id`, `category`, `name`, `price`, `size`) VALUES
  (0, '키즈', '손목 ', 1500, 'S'),
  (1, '스포츠', '손목', 10000, 'M'),
  (2, '주방용품', '밥 ', 2000, 'L'),
  (3, '디지털', '밥', 15000, 'XXL');

 

** ' WARN_DATA_TRUNCATED: Data truncated for column 'size' at row 4 ' 에러가 발생한다.

 

 

 

 

 

SET

: 최초에 지정해 둔 리스트에 포함된 값들을 중복으로 저장

 

- ENUM과 달리, 0부터 64개까지의 값들이 다양하게 들어올 수 있다.

- 가능한 값 리스트의 모든 조합이 들어올 수 있다.

- 순서를 무시한다. 

 

ex. 강아지, 고양이, 원숭이 3개의 데이터가 들어올 수 있다고 지정했을 때 빈 문자열이 들어와도 되고 강아지, 고양이, 원숭이가 들어와도 된다. 들어오는 순서가 바뀌어도 상관 없다.

 

 

 

 

 

 

 

 

 

 

 

데이터 타입 (3) - 이진(Binary)

** Binary 파일

: 데이터를 저장하거나 활용하기 위해 0과 1의 이진 형식으로 인코딩한 파일

 

** Binary 타입

: 다른 데이터를 포함하는 개념으로 텍스트, 이미지 등이 포함될 수 있다.

 

BLOB

: Binary Large Object로 데이터 타입에 상관없이 모두 담을 수 있다.

 

- 어떤 데이터이든 Binary 타입으로 최대 길이 내에 담을 수 있는 타입

- TEXT와 마찬가지로 최대 길이는 65535로 기본값은 지정할 수 없다.

 

 

 

TINYBLOB

: 짧은 길이의 BLOB (최대 길이 255byte)

 

MEDIUMBLOB

: 중간 길이의 BLOB (최대 길이 255byte)

 

LONGBLOB

: 긴 길이의 BLOB (최대 길이 255byte)

 

 

 

 

 

BINARY

:  고정된 길이의 Binary strings (CHAR의 Binary 버전)

 

- BINARY() 괄호 안의 값은 해당 문자열이 차지하는 byte수를 나타낸다.

(한 글자 기준) 한글 = 3byte, 영어 = 1byte, 공백 = 1byte

- 맨 뒤쪽이 공백이어도 괄호 안 기준 byte수를 초과하면 에러가 발생한다.

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int unsigned NOT NULL,
  `category` varchar(40) NOT NULL,
  `name` binary(6) NOT NULL,
  `price` int unsigned NOT NULL,
  PRIMARY KEY (`product_id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `products` (`product_id`, `category`, `name`, `price`) VALUES
  (0, '키즈', '키즈 ', 1500),
  (1, '스포츠', '손목', 10000),
  (2, '주방용품', '밥 ', 2000),
  (3, '디지털', '밥', 15000);

 

ER_DATA_TOO_LONG: Data too long for column 'name' at row 1

 

 

 

올바른 형식으로 입력했을 때 어떻게 결과가 출력되는지 예제를 통해 살펴보자.

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int unsigned NOT NULL,
  `category` varchar(40) NOT NULL,
  `name` binary(7) NOT NULL,
  `price` int unsigned NOT NULL,
  PRIMARY KEY (`product_id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `products` (`product_id`, `category`, `name`, `price`) VALUES
  (0, '키즈', '키즈 ', 1500),
  (1, '스포츠', '손목', 10000),
  (2, '주방용품', '밥 ', 2000),
  (3, '디지털', '밥', 15000);

 

SELECT *
FROM products

 

 

데이터 또한 문자열이 아니라 binary strings로 출력된다.

 

 

뭐지 ;;

 

 

CAST() 함수를 사용해서 다시 문자열 형태로 바꿀수 있다.

 

** CAST(컬럼명 AS 변환할 데이터 타입)

SELECT CAST(name as char) as str_name
FROM products

 

 

 

 

 

 

VARBINARY

: 변동 가능한 길이의 Binary strings (VARCHAR의 Binary 버전)

 

- VARBINARY와 BLOB의 관계는 VARCHAR와 TEXT의 관계와 유사하다. 

 

 

 

 

 

 

 

 

 

데이터 타입 (4) - 배열(Array)

Array(배열)

: 데이터가 저장된 리스트

 

ex. ['a', 'b', 'c'], [1, 2, 3], ['apple', '100', 3400]

 

- 문자와 숫자 데이터 타입을 섞어서 담을 수 있다.

- MySQL 5.7.8에서는 컬럼에 배열 데이터를 저장할 수 있는데, 이때 JSON타입으로 배열을 저장한다.

JSON타입으로 저장된 경우 JSON_ARRAY 라고도 부른다.

- JSON타입으로 저장할 수있는 데이터 형태가 다양하기 때문에 JSON 타입 또한 기본값을 설정할 수 없다.

 

Element(원소)

: 배열에 저장된 각 데이터

 

 

 

배열 함수 (1) JSON_ARRAY()

: 입력을 JSON 배열로 반환하는 함수

 

- INSERT, SELECT 구문에서 주로 사용된다.

 

 

배열 함수 (2) JSON_TYPE()

: JSON 데이터의 타입을 반환하는 함수

 

 

예제를 통해 살펴보자.

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int unsigned NOT NULL,
  `category` varchar(40) NOT NULL,
  `name` varchar(10) NOT NULL,
  `price` int unsigned NOT NULL,
  `options` JSON NULL,
  PRIMARY KEY (`product_id`)
);

INSERT INTO `products` (`product_id`, `category`, `name`, `price`, `options`) VALUES
  (0, '키즈', '어린이칫솔 ', 1500, JSON_ARRAY("빨강", "파랑")),
  (1, '스포츠', '손목보호대', 10000, JSON_ARRAY("S", "M", "L")),
  (2, '주방용품', '밥그릇', 2000, JSON_ARRAY("소", "중", "대")),
  (3, '디지털', '마우스', 15000, NULL);

 

테이블이 정상적으로 생성되었음을 알 수 있다.

 

 

배열의 데이터 타입을 확인해보자.

SELECT options, JSON_TYPE(options)
FROM products

 

 

마지막 행인 NULL 값을 제외하고는 JSON 데이터 타입이 정상적으로 출력되는 것을 알 수 있다.

 

 

 

 

JSON배열을 입력할 때 JSON_ARRAY() 함수를 사용하지 않고 대괄호( [ ] )를 활용할 수도 있다.

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int unsigned NOT NULL,
  `category` varchar(40) NOT NULL,
  `name` varchar(10) NOT NULL,
  `price` int unsigned NOT NULL,
  `options` JSON NULL,
  PRIMARY KEY (`product_id`)
);

INSERT INTO `products` (`product_id`, `category`, `name`, `price`, `options`) VALUES
  (0, '키즈', '어린이칫솔 ', 1500, '["빨강", "파랑"]'),
  (1, '스포츠', '손목보호대', 10000, '["S", "M", "L"]'),
  (2, '주방용품', '밥그릇', 2000, '["소", "중", "대"]'),
  (3, '디지털', '마우스', 15000, NULL);

- ' ' (홑따옴표) : 배열 전체를 감쌀 때 사용한다.

- " " (쌍따옴표) : 배열 내의 원소가 문자열일때, 해당 문자열을 감쌀 때 사용한다.

 

 

 

 

Nested Array

: 배열의 원소가 배열인 Array

 

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int unsigned NOT NULL,
  `category` varchar(40) NOT NULL,
  `name` varchar(10) NOT NULL,
  `price` int unsigned NOT NULL,
  `options` JSON NULL,
  PRIMARY KEY (`product_id`)
);

INSERT INTO `products` (`product_id`, `category`, `name`, `price`, `options`) VALUES
  (0, '키즈', '어린이칫솔 ', 1500, JSON_ARRAY("빨강", "파랑")),
  (1, '스포츠', '손목보호대', 10000, JSON_ARRAY("S", "M", "L")),
  (2, '주방용품', '밥그릇', 2000, JSON_ARRAY("소", "중", "대")),
  (3, '디지털', '마우스', 15000, JSON_ARRAY(JSON_ARRAY("흰색", "파랑"), JSON_ARRAY("초록", "보라"), JSON_ARRAY("갈색", "검정")));

 

 

배열 함수 (3) - JSON_EXTRACT(컬럼명, '$[인덱스]')

: 배열 내부 데이터에 접근할 수 있다.

 

 

** INDEXING

: 배열 내부 데이터에 인덱스를 통해 접근하는 방법

 

 

** $

: key를 지정해준다.

 

- $ : 모든 원소를 가져온다.

- $[인덱스] : 인덱스에 해당하는 원소를 가져온다. (인덱스는 0번부터 시작한다.)

 

SELECT options, JSON_EXTRACT(options, '$') AS all_elements
FROM products

 

 

 

 

SELECT options, JSON_EXTRACT(options, '$[0]') AS idx_elements -- 첫번째 원소 리턴
FROM products

 

 

 

 

 

데이터 타입 (5) - 구조체 (Key-Value)

Key-Value

: Key(키)와 Value(값)로 이루어진 데이터

 

- Key(키) : 데이터를 찾을 수 있는 기준이 되는 값

- Value(값) : 키에 대응되는 값. 하나의 값 뿐만 아니라 배열도 들어갈 수 있다.

 

- Key를 통해 Value에 접근할 수 있다.

- MySQL에서는 JSON 타입으로 key-value를 저장한다.

 

ex. {'이름' : '이순신', '부서' : '분석팀', '직택' : 부장', '근무지' : '판교'} , {'과일' : ['사과', '바나나'], '동물' : ['판다', '원숭이']}

 

 

Key-Value 함수 (1) - JSON_OBJECT(키1, 값1, 키2, 값2, ... )

: key-value 입력값을 JSON 객체로 반환하는 함수

 

- INSERT, SELECT 구문에서 주로 사용된다.

 

 

Key-Value 함수 (2) - JSON_EXTRACT(컬럼명, '$.키')

: key에 해당하는 value에 접근할 수 있다.

 

- $ : 모든 원소를 가져온다.

- $.키 : 키에 해당하는 값을 가져온다.

 

-- managers_v2 테이블 생성
CREATE TABLE IF NOT EXISTS `managers_v2` (
  `id` int unsigned NOT NULL,
  `name` varchar(40) NOT NULL,
  `managing` varchar(50) NOT NULL,
  `info` JSON NULL,
  PRIMARY KEY (`id`)
);

-- managers_v2 테이블에 데이터 삽입
INSERT INTO `managers_v2` (`id`, `name`, `managing`, `info`) VALUES
  (0, '영희', '스포츠', JSON_OBJECT('off', JSON_ARRAY('일', '월'), 'substitute', '민수')),
  (1, '철수', '주방용품', JSON_OBJECT('off', JSON_ARRAY('화', '수'), 'substitute', '길순')),
  (2, '민수', '디지털', JSON_OBJECT('off', JSON_ARRAY('목', '금'), 'substitute', '철수')),
  (3, '길순', '키즈', JSON_OBJECT('off', JSON_ARRAY('금', '토'), 'substitute', '영희'));

 

 

 

쿼리문을 실행해서 결과 테이블을 확인해보자.

SELECT *
FROM managers_v2

 

 

 

 

SELECT JSON_EXTRACT(info, '$.off') AS off
FROM managers_v2

 

 

 

 

 

Value에 해당하는 부분은 Array(배열) 타입일 경우, 인덱싱이 가능하다.

SELECT JSON_EXTRACT(info, '$.off[0]') AS off_idx
FROM managers_v2

 

 

 

 

 

Key-Value 함수 (3) - JSON_INSERT(컬럼명, '$.키', 값 )

: 새로운 (키-값) 쌍을 삽입할 때 사용한다.

 

 

Key-Value 함수 (4) - JSON_REPLACE(컬럼명, '$.키', 변경값)

: 이미 존재하는 컬럼값을 변경한다.

 

 

UPDATE managers_v2 SET info = JSON_INSERT(info, '$.education_time', JSON_ARRAY(5, 10))

 

 

 

SELECT *
FROM managers_v2

 

 

 

 

 

UPDATE managers_v2 SET info = JSON_REPLACE(info, '$.education_time', JSON_ARRAY(0, 10))

 

SELECT *
FROM managers_v2

 

 

Value에 해당하는 부분은 Array(배열) 타입일 경우, 배열 내에서 특정 값만 인덱싱할 수도 있다.

UPDATE managers_v2 SET info = JSON_REPLACE(info, '$.education_time[0]', 10);

 

 

SELECT *
FROM managers_v2

 

 

 

Nested Key-Value

: (키-값) 쌍에서 값에 해당하는 부분에 또 다른 (키-값) 쌍이 오는 형태

-- managers_v2 테이블 생성
CREATE TABLE IF NOT EXISTS `managers_v2` (
  `id` int unsigned NOT NULL,
  `name` varchar(40) NOT NULL,
  `managing` varchar(50) NOT NULL,
  `info` JSON NULL,
  PRIMARY KEY (`id`)
);

-- managers_v2 테이블에 데이터 삽입
INSERT INTO `managers_v2` (`id`, `name`, `managing`, `info`) VALUES
  (0, '영희', '스포츠', JSON_OBJECT('off', JSON_ARRAY('일', '월'), 'substitute', '민수', 'education_time', JSON_OBJECT('데이터', 3, '소방', 2))),
  (1, '철수', '주방용품', JSON_OBJECT('off', JSON_ARRAY('화', '수'), 'substitute', '길순', 'education_time', JSON_OBJECT('인사', 3, '데이터', 3))),
  (2, '민수', '디지털', JSON_OBJECT('off', JSON_ARRAY('목', '금'), 'substitute', '철수', 'education_time', JSON_OBJECT('보안', 3, '회계', 1))),
  (3, '길순', '키즈', JSON_OBJECT('off', JSON_ARRAY('금', '토'), 'substitute', '영희', 'education_time', JSON_OBJECT('인공지능', 1, '소방', 1)));

 

 

SELECT *
FROM managers_v2

 

 

 

 

SELECT JSON_EXTRACT(info, '$.education_time')
FROM managers_v2

 

 

 

 

SELECT name, JSON_EXTRACT(JSON_EXTRACT(info, '$.education_time'), '$.데이터')
FROM managers_v2

 

** 해당되는 데이터가 없으면 빈칸이 나온다.