infoqoch's 개발 블로그

mysql, 주요 내장 함수의 활용

• • •

MySQL의 내장 함수

  • DDL, DML이 ANSI 표준에 따라 일정 부분 호환 가능하다. 다만, DBMS 간 내장 함수는 사실상 호환되지 않는다.
  • MySQL은 자체적으로 제공하는 내장 함수 이외에 사용자 정의 함수(User Defined Function)를 제공한다. 사용자가 직접 함수를 구현할 수 있다.

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

  • IFNULL: 첫 번째 인자가 NULL일 경우 두 번째 인자를 넘기며 그렇지 않을 경우 첫 번째 인자를 넘긴다.
  • ISNULL: 인자의 NULL 여부를 판정한다.
select
    IFNULL(NULL, '널!') -- 널!
    , IFNULL('낫널!', '널!') -- 낫널!
    , ISNULL('낫널!') -- 0
    , ISNULL(null) -- 1
;

현재 시각 조회(NOW, SYSDATE)

  • NOW와 SYSDATE 모두 현재 시간을 제공한다.
  • 다만, NOW는 하나의 SQL에서 동일한 시간을 보장하나 SYSDATE는 함수가 호출되는 시점의 시간을 정확하게 나타낸다.
-- NOW는 동일하나 SYSDATE는 다르다.
SELECT
    NOW(), SYSDATE()
    , SLEEP (1)
    , NOW(), SYSDATE();
  • 이러한 특징으로 NOW는 상수로 사용되나 SYSDATE는 그렇지 않다. 비확정적인 값(NOT-DETERMINISTIC)은 인덱스 사용에 제한적이므로 NOW를 사용한다.
  • 인덱스 활용과 별도로 SYSDATE는 데이터 불일치 문제가 발생시킬 수 있다. WHERE SOME_DATE > SYSDATE() 형태의 쿼리를 작성할 경우, 해당 시간은 해당 함수가 호출되는 시점에 판정되며 이로 인한 예상치 못한 데이터가 추출될 수도 있다.
  • 일반적으로 NOW()만 사용하는 것을 권장한다. 이미 SYSDATE를 사용 중이며 쿼리 수정이 어려운 경우, 시스템 변수 sysdate-is-now를 통해 SYSDATE의 결과가 NOW가 동일하도록 설정할 수 있다.

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

  • DATETIME 타입의 칼럼을 원하는 형태의 문자열로 변환하거나 특정 포맷의 문자열을 DATETIME으로 변환할 수 있다.
SELECT
  DATE_FORMAT(NOW() , '%Y년 %m월 %d일 %H시 %i분 %s초')
  , STR_TO_DATE('2024년 03월 10일 00시 16분 26초', '%Y년 %m월 %d일 %H시 %i분 %s초');

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

  • 날짜를 더하거나 뺄 수 있다. DATE_ADD로 뺄 수 있으므로 보통 DATE_ADD으로 통일하여 사용한다.
  • SELECT DATE_ADD(NOW(), INTERVAL -1 DAY ); -- 어제 날짜가 출력된다

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

  • 타임스탬프를 제공하거나 혹은 타임스탬프로 현재 시간을 제공한다.
  • 32비트로 인한 2038년 문제가 발생한다.
SELECT
    UNIX_TIMESTAMP()
    , UNIX_TIMESTAMP('2023-01-01')
    , FROM_UNIXTIME(1672531200);

문자열 처리 및 결합

  • TRIM: 문자열 처음과 끝의 공백 문자를 제거한다.
  • CONCAT: 문자열을 합성한다.
SELECT
    TRIM('   hi    ') -- hi
    , CONCAT('hello', ', ', 'world', '!') -- hello, world!
;

GROUP BY 문자열 결합(GROUP_CONCAT)

  • 여러 개의 문자열 레코드를 특정 문자열로 연결하여 하나의 문자열로 만든다.
  • 중복은 DISTINCT를 사용하여 제거할 수 있다.
  • 메모리 버퍼의 크기는 group_concat_max_len에 따른다.
SELECT
  GROUP_CONCAT(dept_no)
   , GROUP_CONCAT(dept_no SEPARATOR '|')
   , GROUP_CONCAT(DISTINCT dept_no)
   , GROUP_CONCAT(dept_no ORDER BY dept_no ASC)
FROM departments;

값의 비교와 대체(CASE WHEN…THEN…END)

  • 프로그래밍에서의 switch 문장과 같다.
  • when에서 true로 판정 될 때, then 문장의 쿼리가 실행된다.
  • 아래는 지연판정을 통해 성능을 개선하였다. 성별이 F일 때만 시간이 많이 걸리는 쿼리를 호출한다.
SELECT de.dept_no, e.first_name, e.gender,
    CASE 
        WHEN e.gender = 'F' THEN (SELECT s.salary FROM salaries s WHERE s emp_no =e.emp_no ORDER BY from_date DESC LIMIT 1)
        ELSE 0 
    END AS last_salary
FROM dept_emp de, employees e
WHERE e.emp_no=de.emp_no
  AND de.dept_no='d001';

타입의 변환(CAST, CONVERT)

  • 리터럴의 타입을 명시하거나 타입의 변환이 필요할 때 사용한다.
  • CAST와 CONVERT를 사용할 수 있다. CAST와 CONVERT는 거의 동일하게 동작한다.
  • SELECT CAST('1234' AS SIGNED INTEGER) AS converted_integer;

처리 대기(SLEEP)

  • 쿼리 실행 도중 멈춰서 대기하는 기능.
  • SQL의 개발이나 디버깅 과정에서 일부러 쿼리의 실행을 오랜 시간 유지할 때 사용.
  • 아래는 레코드 갯수마다 SLEEP만큼 대기하며, 총 10건의 레코드가 있으면 총 1초를 대기한다.
SELECT SLEEP(0.1)
FROM employees
WHERE emp_no BETWEEN 10001 AND 10010;

벤치마크(BENCHMARK)

  • 개발 과정에서 성능 테스트 용으로 사용. 두 번째 인자의 작업을 첫 번째 인자의 횟수만큼 반복 수행한다. 두 번째 인자는 SELECT 쿼리만 사용 가능하다.
    • SELECT BENCHMARK(1000000, MD5('abcdefghijk'));
    • MD5로 해싱을 1000000번 수행한다.
    • 다만, 실제 어플리케이션의 쿼리 시간과 차이를 가진다. 왜냐하면 해당 작업은 수차례 수행되더라도 네트워크, 테이블 잠금 비용이 단 1회만 소모되기 때문이다.

IP 주소 변환(INET_ATON, INET_NTOA)

  • IP 주소는 4바이트의 부호 없는 정수이다.
  • 123.123.123.123 형태의 VARCHAR(15)보다는 INT나 VARBINARY가 저장 및 검색에 유리하다. 다만 그 값은 사람이 읽을 수 없다. 그러므로 변환을 위하여 INET_ATON, INET_NTOA를 사용한다.
  • IPv6도 지원하며 이 경우 INET6_ATON, INET6_NTOA를 사용한다. IPv4 값을 함께 처리할 수 있다.
create table my_ip(ip_int VARBINARY(16));
insert into my_ip values (inet6_aton('192.168.0.1')), (inet6_aton('2001:0db8:85a3:0000:0000:8a2e:0370:7334'));
select inet6_ntoa(ip_int) from my_ip;

JSON 도큐먼트 다루기

  • JSON_PRETTY: json 칼럼의 값을 읽기 쉽도록 만들어준다.
  • JSON_STORAGE_SIZE: json 칼럼의 길이(byte)를 리턴한다.
  • JSON_EXTRACT: json 도큐먼트의 특정 필드 값을 가져온다.
  • JSON_UNQUOTE: JSON_EXTRACT 결과에 대한 따옴표를 제거한다.
  • ->, `-»’: 람다로 값을 추출할 수 있으며 ->은 JSON_EXTRACT, -»은 JSON_UNQUOTE까지 수행한다.
with employee_docs as (
    select 
        '{"emp_no": 10005, "gender": "M", "salaries": [{"salary": 91453, "to_date": "2001-09-09", "from_date": "2000-09-09"}, {"salary": 94692, "to_date": "9999-01-01", "from_date": "2001-09-09"}], "hire_date": "1989-09-12", "last_name": "Maliniak", "birth_date": "1955-01-21", "first_name": "Kyoichi"}' 
            as doc
    , 10005 as emp_no
    -- union all -- 더 많은 레코드가 있다고 가정하자!
)
SELECT
    JSON_PRETTY(doc)
     , JSON_STORAGE_SIZE(doc)
     , JSON_EXTRACT(doc, '$.first_name')
     , JSON_UNQUOTE(JSON_EXTRACT(doc, '$.first_name'))
     , doc ->'$.first_name'
     , doc ->>'$.first_name'
FROM employee_docs WHERE emp_no=10005;
  • JSON_CONTAINS: json 도큐먼트 내부를 검색한다.
SELECT emp_no FROM employee_docs
WHERE JSON_CONTAINS(doc, '{"first_name" : "Kyoichi"}');

SELECT emp_no FROM employee_docs
WHERE JSON_CONTAINS(doc, '"Kyoichi"', '$.first_name');
  • JSON_OBJECTAGG, JSON_ARRAYAGG: 집합함수의 결과를 JSON 형태로 합친다.
select dept_no
     , group_concat(emp_no) -- 110022,110039....
     , json_arrayagg(emp_no) -- [110022, 110039]
     , json_objectagg(emp_no, from_date) -- {"110022": "1985-01-01", "110039": "1991-10-01"}....
from dept_manager
where dept_no in ('d001', 'd002', 'd003')
group by dept_no;
  • JSON_TABLE: json 데이터의 값을 RDBMS 테이블로 변환하여 반환한다. 임시테이블을 사용하므로 레코드가 많으면 성능 문제가 발생할 수 있다.
  • 아래는 doc의 json 도큐먼트 내부에 있었던 젠더 프로퍼티를 임시테이블의 칼럼으로 승격하여 select 하였다.
select e1.emp_no, e2.gender
from employee_docs e1
    , JSON_TABLE(doc, "$" COLUMNS (gender char(1) path "$.gender")) as e2;

RealMySQL 8.0, 11장을 참고하여 작성하였습니다.