close
프로필 배경
프로필 로고

< Hyun / Log >

◼ DB · 2025. 1. 23. fullscreen 넓게보기

MySQL의 내장 함수 종류에 대해 알아보자

반응형

DBMS 종류와 관계없이 기본적인 기능의 SQL 함수는 대부분 동일하게 제공되지만

함수의 이름이나 사용법은 표준이 없어 DBMS 별로 거의 호환되지 않는다.

MySQL에서는 어떤 함수를 제공하는지 한번 알아보자.

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

IFNULL()은 컬럼이나 표현식의 값이 NULL인지 비교하고, NULL이면 다른 값으로 대체하는 용도로 사용할 수 있다.

첫 번째 인자는 NULL 비교 여부를 확인할 컬럼이나 표현식, 두 번째 인자는 NULL일 경우 대체할 값이나 컬럼을 설정한다.

SELECT IFNULL(NULL, 1) -- 결과 : 1

 

ISNULL()은 인자러 전달한 표현식이나 컬럼의 값이 NULL인지 여부를 확인하는 함수이다.

인자가 NULL이면 1(TRUE) 아니면 0(FALSE)를 반환한다.

SELECT ISNULL(0) -- 결과 : 0

현재 시각 조회 (NOW, SYSDATE)

NOW()와 SYSDATE() 모두 현재의 시간을 반환하는 함수지만 작동 방식에서 큰 차이가 있다.

NOW()는 하나의 SQL에서 항상 같은 값을 반환하지만

SYSDATE()는 하나의 SQL 내에서도 호출되는 시점에 따라 결과값이 달라진다.

 

아래의 쿼리를 통해 차이를 비교해보자.

(중간에 SLEEP으로 2초 동안 대기하고 다음 함수를 호출하도록 했다.)

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

 

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

 

결과를 통해 2초대기 후에 실행된 SYSDATE()는 처음 호출한 SYSDATE()의 2초 후 시간이 찍힌것을 볼 수 있다.

SYSDATE()는 이러한 특성으로 SYSDATE() 함수가 사용된 SQL은 Source 서버와 Replica 서버에서 실행되는 시점이 달라

안정적으로 복제되지 못한다는 문제점이 있음으로 주의하자.

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

날짜 타입의 컬럼의 값을 원하는 형태의 문자열로 변환하기 위해서는 다음과 같이 DATE_FORMAT() 함수를 사용할 수 있다.

SELECT DATE_FORMAT(NOW() , '%Y-%m-%d %H:%i:%s');

 

사용된 문자들은 다음과 같은 의미를 갖는다.

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

 

기본적으로 표준 형태 (년-월-일 시:분:초)로 입력된 문자열은 필요한 경우 자동으로 DATETIME 타입으로 변환되지만

아닌 경우에는 MySQL 서버가 문자열에 사용된 날짜 타입 포맷을 알 수 없다.

이 경우에는 STR_TO_DATE() 함수를 이용해 DATETIME 타입으로 변환할 수 있다.

-- 결과 : 2024-12-29 18:00:00
SELECT DATE_FORMAT ('2024/12/29 18/00/00', '%Y-%m-%d %H:%i:%s');

 

정리하면 DATE_FORMAT은 날짜 타입 -> 문자열로 변환

STR_TO_DATE는 문자열 -> DATETIME 타입으로 변환.

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

특정 날짜에서 연도나 월일 또는 시간을 더하거나 뺄 때는 DATE_ADD(), DATE_SUB() 함수를 사용할 수 있다.

사실 DATE_ADD()는 더하기 빼기 모두 처리할 수 있어 DATE_SUB()는 크게 필요 없다.

 

위 함수들은 2개의 인자가 필요한데, 첫 번재 인자는 연산을 수행할 날짜

두 번째 인자는 더하거나 빼고자 하는 월의 수나 일자의 수 등을 다음과 같은 형식으로 입력해야한다.

INTERVAL n [YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, ...]

여기서 n은 더하거나 빼고자 하는 차이 값이고 그 뒤에 명시되는 키워드에 따라 년,월,일 등으로 결정된다.

 

아래는 실제 사용 예시이다.

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’으로 부터 경과된 초의 수인 타임스탬프 값을 반환한다.

인자가 없으면 현재 날짜와 시간의 타임스탬프 값을, 인자에 특정 날짜를 전달하면 그 날짜와 시간의 타임스탬프를 반환한다.

SELECT UNIX_TIMESTAMP() -- 결과 : 1735468774
SELECT UNIX_TIMESTAMP(DATE_ADD('1970-01-01 00:00:00', INTERVAL 1 DAY)); -- 결과 : 86400

 

FROM_UNIXTIME() 함수는 반대로 인자로 전달한 타임스탬프 값을 DATETIME으로 변환하는 함수다.

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('1970-01-01 00:00:00'))

 

MySQL의 TIMESTAMP 타입은 4바이트 숫자 타입으로 저장되기 때문에 위 두 함수는

1970-01-01 00:00:01 ~ 2038-01-19 03:14:07 (UTC) 범위의 날짜 값만 가능하다.

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

RPAD()는 문자열의 우측 LPAD()는 좌측에 문자를 덧 붙여서 지정된 길이의 문자열로 만드는 함수이다.

총 개의 인자가 필요하며, 첫 번째 인자는 패딩 처리할 문자열, 두 번째 인자는 몇 글자로 맞춰 패딩할 것인지

세 번째 인자는 패딩할 문자를 의미한다.

SELECT RPAD('tistory', 9, '_'); -- 결과 : tistory__
SELECT LPAD('tistory', 9, '_'); -- 결과 : __tistory

 

RTRIM()은 문자열의 우측 LTRIM()은 좌측에 연속된 공백 문자를 제거하는 함수이고,

TRIM()은 RTRIM() + LTRIN()을 동시에 수행하는 함수다.

SELECT RTRIM('tistory  '); -- 결과 : tistory
SELECT LTRIM('  tistory'); -- 결과 : tistory
SELECT TRIM('  tistory  '); -- 결과 : tistory

문자열 결합 (CONCAT, CONCAT_WS)

CONCAT()은 여래 개의 문자열을 연결해서 하나의 문자열로 반환하는 함수이다.

인자의 개수는 제한이 없으며 숫자 값을 전달하면 문자열 타입으로 자동 변환해 합친다.

보통은 MySQL이 자동 형변환을 해줘 문제가 되지 않지만 형변환이 되지 않아

의도된 결과가 아닌 경우 CAST()함수를 이용해 타입을 문자열로 변환할 수 있다.

SELECT CONCAT('hyun', ' ', 'log'); -- 결과 : hyun log
SELECT CONCAT('주문번호', ' : ', CAST(order_code AS CHAR)) FROM orders;

 

비슷한 함수로 CONCAT_WS()가 있는데 이는 각 문자열을 연결할 구분자를 지정한다는 점이 특징이다.

SELECT CONCAT_ws('_', 'hyun', 'log', 'tistory'); -- 결과 : hyun_log_tistory

GROUP BY 문자열 결합 (GROUP_CONCAT)

그룹화된 행들의 값을 하나의 문자열로 연결하는 MySQL 집계 함수이다.

GROUP_CONCAT() 함수는 값들을 먼저 정렬한 후 연결하거나 각 값의 구분자 설정도 가능하며

여러 값 중에서 중복을 제거하고 연결하는 것도 가능하다.

 

옵션이 다양한데 공식 문서에 나와있는 사용법은 다음과 같다.

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

 

실제 쿼리 사용 예시는 다음과 같다.

SELECT GROUP_CONCAT(id) FROM users; -- 결과 : 1,2,3
SELECT GROUP_CONCAT(id SEPARATOR ' | ') FROM users; -- 결과 : 1 | 2 | 3
SELECT GROUP_CONCAT(id ORDER BY id DESC) FROM users WHERE age >= 20; -- 결과 : 3,2
SELECT GROUP_CONCAT(DISTINCT age ORDER BY id DESC) FROM users WHERE age >= 20; -- 결과 : 30,20

 

GROUP_CONCAT() 함수는 지정한 컬럼의 값들을 연결하기 위해 제한적인 메모리 버퍼 공간을 사용한다.

따라서 결과가 시스템 변수에 지정된 크기를 초과하면 쿼리에서 경고 메시지가 발생한다.

하지만 JDBC로 실행될 때는 경고가 아닌 에러로 취급되어 쿼리가 실패하므로 지정된 버퍼 크기를 초과하지 않도록 주의해야한다.

 

만약 기본값인 1024 BYTE 버퍼 크기를 초과하는 쿼리를 자주 수행하게 된다면 아래 시스템 변수로 사이즈를 조절할 수 있다.

SELECT @@group_concat_max_len; -- 기본값 : 1024 BYTE

버퍼 크기를 높이는 것은 곧 메모리를 더 사용하는 것이기 때문에 신중히 조절할 필요가 있어보인다.

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

CASE WHEN은 SWITCH 구문과 같은 역할을 한다.

CASE로 시작하고 END로 끝나야하며 WHEN .. THEN .. 은 필요한 만큼 반복해서 사용할 수 있다.

(THEN 절은 CASE WHEN 절이 일치하는 경우에만 실행된다)

SELECT 
    id,
    age,
    CASE 
        WHEN age >= 20 AND age < 30 THEN '20대'
        WHEN age >= 30 AND age < 40 THEN '30대'
        ELSE '기타 연령대'
    END AS age_group
FROM ody.users;

타입 변환 (CAST, CONVERT)

매개변수화된 쿼리인 Prepared Statement의 경우를 제외하고

일반 SQL 쿼리인 Statement는 모든 입력값을 문자열로 취급한다.

만약 명시적으로 타입의 변환이 필요하다면 CAST(), CONVERT() 함수를 이용할 수 있다.

 

CAST() 함수는 다음과 같은 타입으로 변환할 수 있다.

BINARY, CHAR, DATE, DATETIME, DECIMAL, SIGNED INTEGER, TIME, UNSIGNED INTEGER

 

사용법은 다음과 같다.

SELECT CAST(-1234 AS SIGNED INTEGER); -- 부호가 있는 정수로 변환
SELECT CAST(-1234 AS UNSIGNED INTEGER); -- 부호가 없는 정수로 변환

 

CONVERT() 함수는 좀 더 넓은 범위의 타입 변환으로

타입을 변환하는 용도와 문자열의 charactor set을 변환하는 2가지 용도로 사용할 수 있다.

SELECT CONVERT(-1234, SIGNED INTEGER);
SELECT CONVERT('안녕하세요🌟' USING 'utf8');
SELECT CONVERT('안녕하세요🌟' USING 'utf8mb4');

암호화 및 해시 함수 (MD5, SHA, SHA2)

이 함수들 모두 사용자의 비밀번호와 같은 암호화가 필요한 정보를 인코딩하는데 사용된다.

MD5와 SHA와 같은 해시 함수가 생성하는 값은 이진 데이터로 각각 16 BYTE, 20 BYTE가 필요하며

16진수의 문자열로 저장할 경우엔 이의 두배인 CHAR(32), CHAR(40) 타입을 필요로 한다.

만약 이진 데이터 그대로 저장해 저장 공간을 줄이고 싶다면 BINARY 타입 또는 VARBINARY 타입으로 저장하면 된다.

해시 함수 중 특히 MD5() 함수는 입력된 문자열의 길이를 줄이는 용도로 사용된다.

 

하지만 해시 함수는 충돌 위험이 있고 MD5()는 더 작은 바이트를 가져 더욱 충돌 위험이 많다.

따라서 컬럼에 해시값으로 저장하기 위한 타입을 사용하는 것은 적절하지 않을 수 있다.

 

만약 URL 같이 긴 크기의 데이터를 자주 조회하게 되어 인덱스가 필요한 상황이 있다고 가정해보자.

인덱스 키의 크기는 커질 수록 인덱스 탐색 깊이가 깊어지기 때문에 키 크기를 적절히 조절할 필요가 있다.

 

최대 길이가 1000인 url에 대해 해싱을 적용하지 않고 인덱스를 생성하려한다면

CREATE TABLE article (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    url VARCHAR(1000) NOT NULL,
    INDEX ix_accessurl (url)
) ENGINE=InnoDB;

인덱스 키의 최대 길이 제한을 초과해 다음과 같은 에러가 발생한다.

Error Code: 1071. Specified key was too long; max key length is 3072 bytes

1000자로 설정을 했지만 charactor set이 UTF8MB4로 되어 있어 한글자당 4 바이트를 잡아먹어

4000 바이트로 최대 길이인 3072 바이트를 초과한 것이다.

 

MySQL 8.0 버전부터 지원되는 ‘함수 기반 인덱스’를 적용한다면 인덱스 크기를 상당히 절약할 수 있다.

CREATE TABLE article (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    url VARCHAR(1000) NOT NULL,
    INDEX ix_accessurl ((UNHEX(MD5(url))))
) ENGINE=InnoDB;

INSERT INTO article (url) VALUES ('https://hstory0208.tistory.com');

SELECT * FROM article WHERE UNHEX(MD5(url)) = UNHEX(MD5('https://hstory0208.tistory.com'));

처리 대기 (SLEEP)

Java의 Thread.sleep()과 같이 처리를 대기 시키는 것이다.

다른 점은 밀리세컨드가 아닌 초 단위로 인자를 받는다는 것이다.

디버깅 용도로 유용하게 사용할 수 있다.

SELECT SLEEP(1) FROM users WHERE age >= 10;

SLEEP() 함수는 레코드의 건수만큼 대기를 적용하기 때문에 만약 위 결과 레코드가 3건이라면

총 3초 동안 대기한다.

밴치마크 (BENCHMARK)

디버깅이나 간단한 함수의 성능 테스트용으로 유용한 함수이다.

2개의 인자를 필요로 하며, 첫 번째 인자는 반복해서 수행할 횟수, 두 번재 인자는 반복해서 실행할 표현식이다.

이 함수의 반환값은 주용하지 않고 지정한 횟수만큼 반복 실행하는데 총 얼마나 시간이 걸렸는지를 확인하는 것이 중요하다.

SELECT BENCHMARK (1000000, (SELECT CONCAT('hyun', ' ', 'log'))); -- 0.054 sec 소요

BENCHMARK() 함수를 사용해 성능을 테스트할 때 주의할점이 있는데

위와 같이 select 쿼리를 직접 백만번 수행하는 것과 차이가 있다는 점이다.

실제 클라이언트 요청으로 서버에서 DB에 쿼리를 보낼 때 마다 쿼리 파싱, 잠금, 네트워크 통신 비용 등이 추가로 든다.

하지만 위 함수는 몇 번을 반복하든 단 1번의 쿼리 파싱, 잠금, 네트워크 통신 비용 등이 소요된다는 점이다.

 

따라서 BENCHMARK()로 쿼리의 성능을 테스트하는 것은 크게 의미가 없고

2개의 동일 기능을 상대적으로 비교 분석할 경우 유용하게 사용할 수 있다.

IP 주소 변환 (INET_ATON, INET_NTOA)

대부분의 DBMSD는 IP 주소를 저장하는 타입을 별도로 제공하지 안흔다.

MySQL에서는 INET_ATON() 함수와 INET_NTOA() 함수를 이용해 IPv4 주소를 문자열이 아닌 부호 없는 정수 타입(Unsigned Integer)으로 저장할 수 있게 제공한다.

INET_ATON() 함수는 문자열로 구성된 IPv4 주소를 정수형으로 변환하는 함수이며

INET_NTOA() 함수는 정수형의 IPv4 주소를 사람이 읽을 수 있는 형태의 ‘.’으로 구분된 문자열로 변환하는 함수다.

SELECT INET_ATON('127.0.0.1'); -- 결과 : 2130706433 
SELECT INET_NTOA('2130706433'); -- 결과 : 127.0.0.1

이런식으로 정수형 (INT) 타입으로 저장하게 된다면 32 비트로 이뤄진 IP가 10진 수로 표현했을 때

옥텟(’.’)을 표현한 길이가 최대 15자리(ex: 192.168.001.001) 임으로 VARCHAR(15)로 저장하는 것 보다

훨씩 작은 크기인 4 BYTE 크기로 저장할 수 있고 숫자로 저장해 문자열보다 범위 탐색이 더욱 빠르다.

 

추가로 IPv6 주소를 변환할 수 있는 INET6_ATON(), INET6_NTOA() 함수도 제공된다.

살짝 다른점은 INET6_ATON() 함수는 IPv6, IPv4 주소를 모두 BINARY 타입으로 변환할 수 있고

INET6_NTOA() 함수는 BINARY 타입의 IPv6, IPv4 주소를 모두 문자열로 변환할 수 있다.

SELECT HEX(INET6_ATON('fdfe::5a55:caff:fefa:9089')); -- 결과 : FDFE0000000000005A55CAFFFEFA9089
SELECT INET6_NTOA(UNHEX('FDFE0000000000005A55CAFFFEFA9089')); -- 결과 : fdfe::5a55:caff:fefa:9089

HEX() / UNHEX() 함수로 2진수 데이터를 16진수로 변환 / 16진수 데이터를 2진수 데이터로 변환하지 않는다면

128비트로 이뤄진 IPv6의 주소를 2진수 데이터로 변환할 때 너무 큰 바이너리 값으로 변환해 BLOB 객체가 반환된다.

따라서 HEX() / UNHEX() 도 함께 사용해주었다.

 

참고로 INET_ATON(), INET_NTOA() 함수로 변환된 IPv4, IPv6 주소를 저장하기 위해선

BINARY 타입으로 변환되기 때문에 BINARY 또는 VARBINARY 타입을 사용해야 한다.

(IPv4는 BINARY(4), IPv6는 BINARY(16), 둘다 저장한다면 VARBINARY(16) 타입 사용)

CREATE TABLE ipv6_addresses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip BINARY(16)
);

INSERT INTO ipv6_addresses (ip) VALUES (INET6_ATON('fdfe::5a55:caff:fefa:9089'));

SELECT id, INET6_NTOA(ip) FROM ipv6_addresses;
참고자료
  • Real MySQL 8.0 2권
저작자표시

'◼ DB' 카테고리의 다른 글

MySQL의 최적화 조인들에 대해 알아보자  (0) 2025.01.28
MySQL의 JOIN 형태 별로 인덱스를 어떻게 사용하는지 알아보자  (0) 2025.01.28
MySQL의 쿼리 작성 기초 및 연산자, 타입별 쿼리 작성법 정리  (2) 2025.01.23
MySQL의 인덱스 종류에 대해 알아보자.  (1) 2025.01.23
MySQL의 인덱스 스캔에 대해 알아보자 (feat. 커버링 인덱스)  (0) 2024.12.31
◼ DB 관련 글
더 보기

티스토리툴바