본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
MySQL 내장함수
DBMS 종류에 관계없이 기본적인 기능의 SQL 함수는 대부분 동일하게 제공되지만 함수 이름이나 사용법은 표준이 없어 DBMS별로 거의 호환되지 않는다. 포스팅에 작성된 내장함수는 사용자 정의 함수와 혼동하지 않도록 주의하자.
NULL 값 비교 및 대체 (IFNULL, ISNULL)
IFNULL()은 칼럼이나 표현식의 값이 NUL인지 비교하고 NULL 이면 다른 값으로 대체하는 용도로 사용할 수 있는 함수다. IFNULL() 함수에는 두 개의 인자를 전달하는데, 첫 번째 인자는 NULL 인지 아닌지 비교하는 칼럼이나 표현식을, 두 번째 인자로는 첫 번째 인자의 값이 NULL 일 경우 대체할 값이나 칼럼을 설정한다. IFNULL() 함수의 반환값은 첫 번째 인자가 NULL이 아니면 첫 번째 인자의 값을, 첫 번째 인자의 값이 NULL이면 두 번째 인자의 값을 반환한다.
ISNULL() 함수는 이름 그대로, 인자로 전달한 표현식이나 칼럼의 값이 NULL인지 아닌지 비교하는 함수다. 반환되는 값은 인자의 표현식이 NULL이면 TRUE(1), NULL이 아니면 FALSE(0)를 반환한다. 두 함수의 사용법을 예제로 살펴보자.
mysql> SELECT IFNULL(NULL, 1); // 1
mysql> SELECT IFNULL(0, 1); // 0
mysql> SELECT ISNULL(0); // 0
mysql> SELECT ISNULL(1/0); // 1
현재 시각 조회(NOW, SYSDATE)
두 함수 모두 현재의 시간을 반환하는 함수로서 같은 기능을 수행한다. 하지만 NOW()와 SYSDATE() 함수는 작동 방식에 큰 차이가 있다. 하나의 SQL에서 모든 NOW() 함수는 값을 가지지만 SYSDATE()는 호출되는 시점에 따라 결과 값이 달라진다.
NOW() 함수를 사용한 첫 번째 예제에서는 두 번의 NOW() 함수 결과가 같은 값을 반환했다. 하지만 두 번째 예제에서 사용된 SYSDATE() 함수는 SLEEP() 함수의 대기 시간인 2초 동안의 차이가 있음을 알 수 있다.
SYSDATE() 함수는 이러한 특성 탓에 두 가지 큰 잠재적인 문제가 있다.
- 첫 번째로 SYSDATE() 함수가 사용된 SQL은 복제가 구축된 MySQL의 슬레이브에서 안정적으로 복제되지 못한다.
- 두 번째로 SYSDATE() 함수와 비교되는 칼럼은 인덱스를 효율적으로 사용하지 못한다.
두 번째 부분에 대해서는 다음 예제를 좀 더 자세히 알아보자
EXPLAIN SELECT emp_no, salary, from_date, to_date
FROM salaries WHERE emp_no=10001 AND from_date>NOW();
EXPLAIN SELECT emp_no, salary, from_date, to_date
FROM salaries WHERE emp_no=10001 AND from_date>SYSDATE();
위의 예제를 살펴보면 첫 번째 쿼리는 emp_no와 from_date 칼럼 모두 적절히 인덱스를 사용해 인덱스 전체길이인 7바이트 모두를 사용했다. 하지만 두번째 쿼리는 emp_no 칼럼만 인덱스를 사용했기 때문에 인덱스 중에서 emp_no에 속하는 4바이트만 레인지 스캔에 이용했다.
SYSDATE() 함수는 호출될 때마다 다른 값을 반환하므로 상수가 아니다. 그래서 인덱스를 스캔할 때도 매번 비교되는 레코드마다 함수를 실행해야 한다. 반면에 NOW()는 쿼리 실행시점에 실행되고 값을 할당받아 그 값을 SQL 문장의 모든 부분에서 사용하게 되므로 쿼리가 1시간동안 실행되더라도 시점에 관계없이 항상 같은 값을 보장한다.
꼭 필요한 때가 아니라면 SYSDATE() 함수를 사용하지 않는 편이 좋다. 사실 일반적으로 웹 서비스에서는 특별히 SYSDATE() 함수를 사용해야 할 만한 이유가 없다. 시스템 설정 파일에서 sysdate-is-now 설정을 추가해 SYSDATE() 함수가 NOW() 함수와 동일하게 작동하도록 설정할 것을 권장한다.
날짜와 시간의 포맷(DATE_FORMAT, STR_TO_DATE)
DATETIME 타입의 칼럼이나 값을 원하는 형태의 문자열로 변환해야 할때는 DATE_FORMAT() 함수를 이용한다. 날짜의 각 부분을 의미하는 지정자는 다음과 같다.
- %Y : 4자리 년도
- %m : 2자리 숫자 표시의 월 (00 ~ 12)
- %d : 2자리 숫자 표시의 일자(00 ~ 31)
- %H : 2자리 숫자 표시의 시(00 ~ 23)
- %i : 2자리 숫자 표시의 분(00 ~ 59)
- %s : 2자리 숫자 표시의 초(00 ~ 59)
위의 지정자를 이용해 필요한 포맷 또는 필요한 부분만의 문자열로 변환할 수 있다.
mysql> SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS current_dt;
// 2017-01-03
mysql> SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS current_dttm;
// 2017-01-03 11:31:16
SQL에서 표준 형태(년-월-일 시:분:초)로 입력된 문자열은 필요한 경우 자동적으로 DATETIME 타입으로 변환되어 처리된다. 물론 이 밖에도 자동으로 DATETIME 으로 자동 변환이 가능한 형태가 있다. 하지만 그렇지 않은 형태는 MySQL 서버가 문자열에 사용된 날짜 타입의 포맷을 알 수 없으므로 명시적으로 날짜 타입으로 변환해 주어야 한다. 이때 STR_TO_DATE() 함수를 이용해 문자열을 DATETIME 타입으로 변환할 수 있다.
mysql > SELECT STR_TO_DATE('2017-01-03',. '%Y-%m-%d') AS current_dt;
// 2017-01-03
mysql > SELECT STR_TO_DATE('2017-01-03 11:40:23',. '%Y-%m-%d %H:%i:%s') AS current_dt;
// 2017-01-03 11:40:23
날짜와 시간의 연산(DATE_ADD, DATE_SUB)
특정 날짜에서 년도나 월일 또는 시간 등을 더하거나 뺄때는 DATE_ADD(), DATE_SUB() 함수를 사용한다. 사실 DATE_ADD()함수로 더하거나 빼는 처리를 모두 할 수 있다. 두 함수 모두 두 개의 인자를 필요로 한다. 첫 번째 인자는 연산을 수행할 날짜이며, 두 번째 인자는 더하거나 빼고자 하는 월의 수나 일자의 수 등을 입력하면 된다. 두 번째 인자는 INTERVAL n [YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, ...] 형태로 입력해야 한다.
mysql> SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS tomorrow;
// 2017-01-03 15:21:33
mysql> SELECT DATE_ADD(NOW(), INTERVAL -1 DAY) AS yesterday;
// 2016-01-02 15:21:33
대표적인 것들만 명시했으며 상세한 내용은 아래의 표를 참조하시길...
타임 스탬프 연산 (UNIX_TIMESTAMP, FROM_UNIXTIME)
UNIX_TIMESTAMP() 함수는 '1970-01-01 00:00:00'로부터 경과된 초의 수를 반환하는 함수다. 다른 운영체제나 프로그래밍 언어에서도 같은 방식으로 타임스탬프를 산출해 상호 호환을 유지한다. UNIX_TIMESTAMP() 함수는 인자가 없으면 현재 날짜와 시간의 타임스탬프 값을 반환하고, 인자로 특정 날짜를 전달하면 그 날짜와 시간의 타임스탬프를 반환한다. FROM_UNIXTIME() 함수는 UNIX_TIMESTAMP() 함수와 반대로, 인자로 전달된 타임스탬프 값을 DATETIME 타입으로 변환하는 함수다.
mysql> SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');
// 1111860000
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2005-03-27 03:00:00'));
// 2005-03-27 03:00:00
MySQL의 TIMESTAMP 타입은 4바이트 숫자 타입으로 저장되기 때문에 실제로 가질 수 있는 값의 범위는 '1970-01-01 00:00:01' ~ '2038-01-09 03:14:07' 까지의 날짜 값만 가능하다. FROM_UNIXTIME() 함수나 UNIX_TIMESTAMP() 함수도 이 범위의 날짜에서만 사용할 수 있다.
