[📕 Real MySQL 8.0 (2)] 쿼리 작성 및 최적화 - MySQL 내장함수(1)

1. NULL 값 처리

✳️ NULL 값 비교 및 대체 (IFNULL, ISNULL)

 

🔷 IFNULL()

• 컬럼이나 표현식의 값이 NULL인지 비교하고, NUnLL이면 다른 값으로 대체
• 첫 번째 인자는 NULL인지 아닌지 비교하려는 컬럼이나 표현식
• 두 번째 인자는 첫 번째 인자의 값이 NULL일 경우 대체할 값이나 컬럼 설정
• 함수의 반환값은 첫 번째 인자가 NULL이 아니면 첫 번째 인자의 값을, 첫 번째 인자의 값이 NULL이면 두 번째 인자의 값 반환

 

🔷 ISNULL()

• 인자로 전달한 표현식이나 컬럼의 값이 NULL인지 아닌지 비교
• NULL이면 TRUE(1), NULL이 아니면 FALSE(0) 반환

 

 

 

 

 

2. 날짜 및 시간 처리

✳️ 현재 시각 조회 (NOW, SYSDATE)

 

🔷 NOW()

• 하나의 SQL에서 같은 값을 가짐
• 쿼리가 실행된 시점에 값을 반환

SELECT NOW(), SLEEP(2), NOW();

👉 두 번의 NOW() 함수 결과가 같은 값 반환

 

 

 

🔷 SYSDATE()

• 하나의 SQL 내에서도 호출되는 시점에 따라 결과값이 달라짐
• 함수 호출 시점에 따른 실시간 날짜와 시간 반환

SELECT SYSDATE(), SLEEP(2), SYSDATE();

👉 SLEEP() 함수의 대기 시간인 2초 동안의 차이 발생

 

⛔ 이러한 SYSDATE() 함수의 특성 탓에 발생하는 문제점 ⛔ 

1. SYSDATE() 함수가 사용된 SQL은 레플리카 서버에서 안정적으로 복제되지 X
2. SYSDATE() 함수와 비교되는 컬럼은 인덱스를 효율적으로 사용 X
👉 함수가 호출될 때마다 다른 값을 반환하므로 상수가 아님
👉 인덱스 스캔할 때도 매번 비교되는 레코드마다 함수 실행 

꼭 필요한 때가 아니라면 SYSDATE() 함수를 사용하지 않는 편이 좋고, 이미 사용하고 있다면 설정 파일에 sysdate-is-now 시스템 변수를 넣어서 활성화 필요 !

---

✳️ 날짜와 시간의 포맷 (DATE_FORMAT, STR_TO_DATE)

 

🔷 DATE_FORMAT()

• DATETIME 타입의 컬럼이나 값을 원하는 형태의 문자열로 변환해야할 때 사용

지정문자	내용
%Y	4자리 연도
%m	2자리 숫자 표시의 월 (01 ~ 12)
%d	2자리 숫자 표시의 일자 (01 ~ 31)
%H	2자리 숫자 표시의 시 (00 ~ 23)
%i	2자리 숫자 표시의 분 (00 ~ 59)
%s	2자리 숫자 표시의 초 (00 ~ 59)

• SQL에서 표준 형태(년-월-일 시:분:초)로 입력된 문자열은 필요한 경우 자동으로 DATETIME 타입으로 변환되어 처리

 

🔷 STR_TO_DATE()

• 문자열을 DATETIME 타입으로 변환

---

✳️ 날짜와 시간의 연산 (DATE_ADD, DATE_SUB)

 

🔷 DATE_ADD()

• 특정 날짜에서 연도나 월일 또는 시간 등을 더할 때 사용 
• DATE_ADD() 함수로 더하거나 빼는 처리를 모두 할 수 있기 때문에 DATE_SUB()는 크게 필요 X
• 첫 번째 인자는 연산을 수행할 날짜
• 두 번째 인자는 더하거나 빼고자 하는 월의 수나 일자의 수 등
  • INTERVAL n [YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, ...] 형태로 입력

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS tomorrow;
SELECT DATE_ADD(NOW(), INTERVAL -1 DAY) AS yesterday;

 

---

✳️ 타임스탬프 연산 (UNIX_TIMESTAMP, FROM_UNIXTIME)

 

🔷 UNIX_TIMESTAMP()

• '1970-01-01 00:00:00'으로부터 경과된 초의 수 반환
• 인자가 없으면 현재 날짜와 시간의 타임스탬프 값 반환
• 인자로 특정 날짜를 전달하면 그 날짜와 시간의 타임스탬프 반환 

 

🔷 FROM_UNIXTIME()

• UNIX_TIMESTAMP() 함수와 반대로, 인자로 전달한 타임스탬프 값을 DATETIME 타입으로 반환하는 함수

MySQL의 TIMESTAMP 타입은 4바이트 숫자 타입으로 저장되기 때문에 실제로 가질 수 있는 값의 범위는
'1970-01-01 00:00:01' ~ '2038-01-09 03:14:07' 까지의 날짜 값만 가능하므로 위의 함수들도 이 범위의 날짜 안에서만 사용 가능

 

 

 

 

 

3. 문자열 처리

✳️ 문자열 처리 (RPAD, LPAD / RTRIM, LTRIM, TRIM)

 

🔷 RPAD(), LPAD()

• 문자열의 좌측 또는 우측에 문자를 덧붙여서 지정된 길이의 문자열로 만드는 함수
• 첫 번째 인자는 패딩 처리를 할 문자열
• 두 번째 인자는 몇 바이트까지 패딩할 것인지 (패딩 적용 후 결과로 반환될 문자열의 최대 길이)
• 세 번째 인자는 어떤 문자를 패딩할 것인지

 

🔷 RTRIM(), LTRIM()

• 문자열의 우측 또는 좌측에 연속된 공백문자를 제거하는 함수
• TRIM() 함수는 LTRIM()과 RTRIM()을 동시에 수행

---

✳️ 문자열 결합 (CONCAT)

 

🔷 CONCAT()

• 여러 개의 문자열을 연결해서 하나의 문자열로 반환하는 함수 
• 인자의 개수 제한 X
• 숫자 값을 인자로 전달하면 문자열 타입으로 자동 변환한 후 연결
• 의도된 경우가 아닌 경우에는 명시적으로 CAST() 함수를 이용해 타입을 문자열로 변환하는 편이 안전함 

---

✳️ GROUP BY 문자열 결합 (GROUP_CONCAT)

 

🔷 GROUP_CONCAT()

• 주로 GROUP BY와 함께 사용하며 GROUP BY가 없는 SQL에서 사용하면 단 하나의 결과값을 만들어냄
• 값들을 먼저 정렬한 후 연결하거나 각 값의 구분자 설정 가능
• 여러 값 중에서 중복 제거하고 연결 가능
• 지정한 컬럼의 값들을 연결하기 위해 제한적인 메모리 버퍼 공간 사용 
  • 메모리 버퍼 크기는 group_concat_max_len 시스템 변수로 조정 가능

 

---

 

🔹 윈도우 함수를 이용해 최대 5개 부서만 GROUP_CONCAT 실행

SELECT GROUP_CONCAT(dept_no ORDER BY dept_name DESC)
FROM(
	SELECT *, RANK() OVER (ORDER BY dept_no) AS rnk
    FROM departments
) as x
WHERE rnk <= 5;

• 내부 쿼리 - RANK() OVER (ORDER BY dept_no) AS rnk
  
  • RANK() 윈도우 함수를 사용하여 dept_no 값에 따라 각 행에 순위를 매김
  • departments 테이블의 모든 열과 함께 새로 생성된 rnk 열을 포함하여 출력
• 외부 쿼리 - SELECT GROUP_CONCAT(dept_no ORDER BY dept_name DESC)
  • 선택된 dept_no 값들을 하나의 문자열로 결합
  • 결합 순서는 dept_name 기준으로 내림차순 정렬

---

🌟 윈도우 함수란 ?

👉 SQL에서 데이터 집합에 대해 행별로 계산을 수행하는 함수로, 각 행에 대해 데이터를 분석, 요약하면서도 원본 행 그대로 유지

<윈도우 함수> OVER (
    [PARTITION BY <기준 열>]
    [ORDER BY <정렬 기준>]
)

• 윈도우 함수
  • 집계 함수 : SUM, AVG, COUNT, MAX, MIN 등
  • 순위 함수 : RANK, DENSE_RANK, ROW_NUMBER, NTILE 등
  • 통계 함수 : LAG, LEAD, FIRST_VALUE, LAST_VALUE 등
• OVER : 윈도우 함수를 정의하는 키워드
• PARTITION BY (선택사항) 
  • 데이터 집합을 특정 열 값 기준으로 분할
  • 지정하지 않으면 전체 데이터 집합이 하나의 윈도우로 처리됨
• ORDER BY (선택사항)
  • 계산 순서 지정
  • 순위나 통계함수에서 사용됨 

---

 

🔹 래터럴 조인을 이용해 부서별로 10명씩만 GROUP_CONCAT 실행

SELECT d.dept_no, GROUP_CONCAT(de2.emp_no)
FROM departments d
LEFT JOIN LATERAL (SELECT de.dept_no, de.emp_no
					FROM dept_emp de
                    WHERE de.dept_no=d.dept_no
                    ORDER BY de.emp_no ASC LIMIT 10) de2 ON de2.dept_no=d.dept_no
GROUP BY d.dept_no;

 

👉 departments 테이블과 dept_emp 테이블을 조인한 뒤, 각 부서(dept_no)에 속하는 최대 10명의 직원(emp_no)을 연결하여 문자열로 반환

• LATERAL 서브쿼리
  • LATERAL은 왼쪽 테이블의 각 행(departments)을 기준으로 동작하는 동적 서브쿼리를 실행할 수 있도록 함
  • dept_emp 테이블에서 WHERE de.dept_no=d.dept_no로 해당 부서(d.dept_no)에 속한 직원만 선택
  • emp_no를 오름차순으로 정렬한 후 각 부서 당 최대 10명의 직원만 선택
• LEFT JOIN
  • departments 테이블에 있는 부서는 항상 포함됨 
  • 해당 부서에 직원이 없으면(dept_emp에 해당 dept_no가 없으면) 결과는 NULL로 처리

---

🌟 LATERAL 이란 ? 
https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html

👉 일반적으로 서브쿼리는 독립적이며 왼쪽 테이블의 현재 행 값을 참조할 수 없지만(상위 쿼리의 컬럼 참조 X) 
LATERAL은 왼쪽 테이블의 각 행을 기준으로 서브쿼리를 실행할 수 있도록 허용

🔹 제한사항 
- LATERAL 파생 테이블은 FROM 절에서만 사용 가능
- FROM 절에서는 쉼표로 구분된 테이블 목록이나 JOIN 안에서 사용 가능 

 

SELECT <columns>
FROM <table1>
LEFT JOIN LATERAL (
    SELECT <columns>
    FROM <table2>
    WHERE <table2_column> = <table1_column>
) alias ON <join_condition>;

 

 

 

 

4. JSON 처리

✳️ JSON 포맷 (JSON_PRETTY)

 

🔷 JSON_PRETTY()

• 해당 함수를 사용하면 JSON 컬럼값에 대한 가독성이 떨어지는 것을 해결해줌
• JSON 컬럼의 값을 읽기 쉬운 포맷으로 변환

---

✳️ JSON 필드 크기 (JSON_STORAGE_SIZE)

 

🔷 JSON_STORAGE_SIZE()

• MySQL 서버는 디스크의 저장 공간을 절약하기 위해 JSON 데이터를 실제 디스크에 저장할 때 BSON(Binary JSON_ 포맷 사용
• BSON으로 변환됐을 때 저장 공간의 크기가 얼마나 될지 예측하기 어려우므로 해당 함수로 크기를 바이트 단위로 반환

---

✳️ JSON 필드 추출 (JSON_EXTRACT) 

 

🔷 JSON_EXTRACT()

• JSON 도큐먼트에서 특정 필드의 값 가져옴
• 첫 번째 인자는 JSON 데이터가 저장된 컬럼 또는 JSON 도큐먼트 자체 
• 두 번째 인자는 가져오고자 하는 필드의 JSON 경로

SELECT emp_no, JSON_EXTRACT(doc, "$.first_name") FROM employee_docs;
SELECT emp_no, doc -> "$.first_name" FROM employee_docs;

+--------+-------------------------+
| emp_no | JSON_EXTRACT           |
+--------+-------------------------+
| 101    | "John"                 |
| 102    | "Jane"                 |
| 103    | "Alice"                |
+--------+-------------------------+

 

SELECT emp_no, JSON_UNQUOTE(JSON_EXTRACT(doc, "$.first_name")) FROM employee_docs;
SELECT emp_no, doc ->> "$.first_name" FROM employee_docs;

+--------+-------------------------+
| emp_no | JSON_UNQUOTE           |
+--------+-------------------------+
| 101    | John                   |
| 102    | Jane                   |
| 103    | Alice                  |
+--------+-------------------------+

---

✳️ JSON 오브젝트 포함 여부 확인 (JSON_CONTAINS)

 

🔷 JSON_CONTAINS()

•  JSON 도큐먼트 또는 지정된 JSON 경로에 JSON 필드를 가지고 있는지를 확인하는 함수
• 첫 번째 인자는 JSON 데이터를 저장하고 있는 컬럼이나 JSON 도큐먼트
• 두 번째 인자는 JSON 오브젝트(도큐먼트 또는 필드값) 사용
• 첫 번째 인자로 주어진 JSON 도큐먼트에서 두 번째 인자의 JSON 오브젝트가 존재하는지를 검사
• 세 번째 인자는 JSON 경로 명시 (선택적 부여) 👉 해당 경로에 JSON 오브젝트가 존재하는지 여부 체크

SELECT emp_no FROM employee_docs
WHERE JSON_CONTAINS(doc, '{"first_name" : "Chris"}');

SELECT emp_no FROM employee_docs
WHERE JSON_CONTAINS(doc, '"Chris"', '$.first_name');

---

✳️ JSON 컬럼으로 집계 (JSON_OBJECTAGG & JSON_ARRAYAGG)

GROUP BY 절과 함께 사용되는 집계 함수로서, RDBMS 컬럼의 값들을 모아 JSON 배열 또는 도큐먼트 생성

 

🔷 JSON_OBJECTAGG()

• 첫 번째 인자는 key, 두 번째 인자는 value
• key-value 쌍의 JSON 도큐먼트 만들어 반환

SELECT dept_no, JSON_OBJECTAGG(emp_no, from_date) AS agg_manager
FROM dept_manager
WHERE dept_no IN ('d001', 'd002', 'd003')
GROUP BY dept_no;

+---------+---------------------------------------------------+
| dept_no | agg_manager                                       |
+---------+---------------------------------------------------+
| d001    | {"101": "1990-01-01", "102": "1995-06-15"}        |
| d002    | {"201": "1985-03-12"}                             |
| d003    | {"301": "2000-07-20", "302": "2005-10-10"}        |
+---------+---------------------------------------------------+

 

 

 

🔷 JSON_ARRAYAGG()

• 주어진 RDBMS 컬럼의 값을 이용해 JSON 배열을 만들어서 반환

SELECT dept_no, JSON_ARRAYAGG(emp_no) AS agg_manager
FROM dept_manager
WHERE dept_no IN ('d001', 'd002', 'd003')
GROUP BY dept_no;

+---------+-----------------------+
| dept_no | agg_manager           |
+---------+-----------------------+
| d001    | [101, 102]            |
| d002    | [201]                 |
| d003    | [301, 302]            |
+---------+-----------------------+

---

✳️ JSON 데이터를 테이블로 변환 (JSON_TABLE)

 

🔷 JSON_TABLE()

• JSON 데이터의 값들을 모아서 RDBMS 테이블을 만들어 반환 

SELECT e2.emp_no, e2.first_name, e2.gender
FROM employee_docs e1,
	JSON_TABLE(doc, "$" COLUMNS (emp_no INT PATH "$.emp_no",
    							 gender CHAR(1) PATH "$.gender",
                                 first_name VARCHAR(20) PATH "$.first_name")
               ) AS e2
WHERE e1.emp_no IN (10001, 10002);

+--------+------------+--------+
| emp_no | first_name | gender |
+--------+------------+--------+
| 10001  | John       | M      |
| 10002  | Jane       | F      |
+--------+------------+--------+

 

 

 

참고자료

https://dev.mysql.com/doc/refman/8.4/en/lateral-derived-tables.html