MySQL 내장 함수 정리

MySQL 내장 함수

MySQL은 다양한 내장 함수를 포함하고 있습니다. 종류는 제어 흐름 함수, 문자열 함수, 수학 함수, 날짜/시간 함수, 전체 테스트 검색 함수, 형 변환 함수, XML 함수, 비트 함수, 보안/압축 함수 , 정보 함수, 공간 분석 함수, 기타 함수 등이 있습니다. 

 

* 특정한 함수의 사용법, 개념을 알고 싶어 들어오셨다면 Ctrl + F로 함수명을 검색하시면 편리하실 것 같습니다.

 

제어 흐름 함수

제어 흐름 함수는 프로그램의 흐름을 제어하는 함수입니다.

SELECT IF(1>3, 'true', 'false'); -- IF(수식, 참, 거짓)

IF는 수식과 참일 때 반환할 값과 거짓일 때 반환할 값을 입력하면, 수식의 진리값에 따른 값이 반환됩니다. 위에서는 false가 반환됩니다.

SELECT IFNULL(200,100) -- IFNULL(수식1,수식2)

IFNULL은 수식1이 NULL이면 수식2를 반환하고, 그렇지 않으면 수식1을 리턴합니다. 위에서는 200이 반환됩니다.

SELECT NULLIF(1,1); -- NULLIF(수식1, 수식2)

NULLIF는 수식1과 수식2가 같다면 NULL을, 아니면 수식1을 반환합니다. 위에서는 NULL이 반환됩니다.

SELECT CASE 10 -- CASE ... WHEN ... ELSE ... END
    WHEN 1 THEN 'a'
    WHEN 5 THEN 'e'
    WHEN 10 THEN 'j'
    ELSE '?'
END;

CASE는 내장함수는 아니고 다중 분기에 사용하는 Operator(연산자)입니다. switch문과 유사하며 위에서는 'j'가 반환됩니다. WHEN에서 찾을 수 없다면 ELSE의 값이 반환됩니다.

 

문자열 함수

문자열 함수는 문자열을 조작하는 함수입니다.

SELECT ASCII('A'); -- ASCII(아스키 코드)

ASCII는 문자의 아스키 코드값을 반환합니다. 위에서는 65가 반환됩니다.

SELECT CHAR(65); -- CHAR(숫자)

CHAR은 아스키 코드값에 해당하는 문자를 반환합니다. 위에서는 'A'가 반환됩니다.

SELECT BIT_LENGTH('abc'), CHAR_LENGTH('abc'), LENGTH('abc');
SELECT BIT_LENGTH('가나다'), CHAR_LENGTH('가나다'), LENGTH('가나다');
-- BIT_LENGTH(문자열), CHAR_LENGTH(문자열), LENGTH(문자열)

BIT_LENGTH는 할당된 Bit 크기, CHAR_LENGTH는 문자의 개수, LENGTH()는 할당된 Byte 수를 반환합니다. 위에서는 첫번줄에서 24, 3, 3과 둘째 줄에서 72, 3, 9가 반환됩니다. (UTP-8 코드에서는 한글은 문자당 3바이트이기 때문.)

SELECT CONCAT('2020', '01', '01'); -- CONCAT(문자열1, 문자열2, ...)
SELECT CONCAT_WS('/','2020', '01', '01'); -- CONCAT_WS(구분자, 문자열1, 문자열2, ...)

CONCAT은 문자열을 이을 때 사용합니다. 위에서는 '20200101'이 반환됩니다.

CONCAT_WS는 구분자와 함께 문자열을 이을 때 사용합니다. 위에서는 '2020/01/01'이 반환됩니다.

SELECT ELT(2, 'a', 'b', 'c'), -- ELT(위치, 문자열1, 문자열2, ...)
       FIELD('b', 'a', 'b', 'c'), -- FIELD(찾을 문자열, 문자열1, 문자열2, ...)
       FIND_IN_SET('b', 'a,b,c'), -- FIND_IN_SET(찾을 문자열, 문자열 리스트)
       INSTR('abcd', 'b'), -- INSTR(기준 문자열, 부분 문자열)
       LOCATE('b', 'abcd'); -- LOCATE(부분 문자열, 기준 문자열);

ELT는 위치 번째의 문자를 반환합니다. 위에서는 'b'을 반환합니다.

FIELD는 찾을 문자열의 위치를 찾아서 있으면 위치를, 없으면 0을 반환합니다. 위에서는 '2'를 반환합니다.

FIND_IN_SET은 찾을 문자열을 문자열 리스트에서 찾아서 위치를 반환합니다. 문자열 리스트는 콤마(,)로 구분되어 있어야 하며 공백이 없어야 합니다. 위에서는 '2'를 반환합니다.

INSTR은 기준 문자열에서 부분 문자열을 찾아서 그 시작 위치를 반환합니다. 위에서는 2를 반환합니다.

LOCATE는 INSTR와 동일하지만 파라미터의 순서가 반대로 되어있습니다. POSITION()과 동일한 함수입니다.

SELECT FORMAT(123.1234, 2); -- FORMAT(숫자, 소숫점 자릿수)

숫자를 소숫점 아래 자릿수까지만 표현합니다. 그리고 1000 단위마다 콤마를 표시합니다. 위에서는 123.12를 반환합니다.

SELECT BIN(31), -- BIN(숫자)
       HEX(31), -- HEX(숫자)
       OCT(31); -- OCT(숫자)

BIN은 2진수, HEX는 16진수, OCT는 8진수 값을 반환합니다. 위에서는 11111, 1F, 37을 반환됩니다.

SELECT INSERT('가나다라마', 2, 3, '@@@'); -- INSERT(기준 문자열, 위치, 길이, 삽입할 문자열)

기준 문자열의 위치부터 길이만큼을 삽입할 문자열로 변경합니다.. 위에서는 '가@@@마'가 반환됩니다.

SELECT LEFT('가나다라마바', 3), -- LEFT(문자열,길이)
       RIGHT('가나다라마바', 3); -- RIGHT(문자열,길이)

LEFT는 문자열의 왼쪽부터 길이만큼 반환합니다. 위에서는 '가나다'가 반환됩니다.

RIGHT는 문자열의 오른쪽부터 길이만큼 반환합니다. 위에서는 '라마바'가 반환됩니다.

SELECT LCASE(aBcDe), -- LCASE(문자열)
       UCASE(aBcDe); -- UCASE(문자열)

LCASE는 대문자를 소문자로 변경한 후 반환합니다. 위에서는 'abcde'가 반환됩니다. LOWER함수와 동일합니다.

UCASE는 소문자를 대문자로 변경한 후 반환합니다. 위에서는 'ABCDE'가 반환됩니다. UPPER함수와 동일합니다.

SELECT LPAD('가나다', 5, '@@'), -- LPAD(문자열, 길이, 채울 문자열)
       RPAD('가나다', 5, '@@'); -- RPAD(문자열, 길이, 채울 문자열)

LPAD/RPAD는 문자열을 길이만큼 왼쪽/오른쪽에서 늘린 후에 빈 곳을 채울 문자열로 채웁니다. 위에서는 '@@가나다', '가나다@@'을 반환합니다.

SELECT LTRIM('   abc'), -- LTRIM(문자열)
       RTRIM('abc   '); -- RTRIM(문자열)
       TRIM('  abc  '); -- TRIM(문자열)

LTRIM/RTRIM은 문자열의 왼쪽/오른쪽 공백을 제거합니다. 

TRIM은 양쪽의 공백을 모두 제거 합니다.

중간의 공백은 제거하지 않습니다. 위에서는 모두 'abc'를 반환합니다.

SELECT TRIM(BOTH 'a' FROM 'aababaa'); -- TRIM(방향 자를문자열 FROM 문자열)

TRIM FROM은 앞(LEADING)또는 뒤(TRAILING)또는 양쪽(BOTH)에서 원하는 문자열을 자를 수 있습니다. 위는 'bab'를 반환합니다.

SELECT REPEAT('abc', 3) -- REPEAT(문자열, 횟수)

REPEAT은 문자열을 횟수만큼 반복합니다. 위에서는 'abcabcabc'를 반환합니다.

SELECT REPLACE ('It is banana', 'banana', 'apple'); -- REPLACE(문자열, 원래 문자열, 바꿀 문자열)

REPLACE는 문자열에서 원래 문자열을 찾아서 바꿀 문자열로 바꿔줍니다. 위에서는 'It is apple'을 반환합니다.

SELECT REVERSE ('가나다'); -- REVERSE(문자열)

REVERSE는 문자열을 거꾸로 만듭니다. 위에서는 '다나가'를 반환합니다.

SELECT CONCAT('ab', SPACE(5), 'cd'); -- SPACE(길이)

SPACE는 길이만큼의 공백을 반환합니다. 위에서는 'ab     cd'를 반환합니다.

SELECT SUBSTRING('abcdef', 3, 2); -- SUBSTRING(문자열, 시작위치, 길이)

SUBSTRING은 시작 위치부터 길이만큼 문자를 반환합니다. 위에서는 'cd'를 반환합니다. SUBSTRING(문자열 FROM 시작위치 FOR 길이)문법으로 작성하기도 합니다. SUBSTR, MID 함수와 동일합니다.

SELECT SUBSTRING_INDEX('jaehoney.tistory.com', '.', 2);
SELECT SUBSTRING_INDEX('jaehoney.tistory.com', '.', -2);
-- SUBSTRING_INDEX (문자열, 구분자, 횟수)

SUBSTRING_INDEX는 문자열에서 시작부터 구분자가 횟수번나오면 그 이후는 버립니다. 횟수가 음수면 오른쪽부터 시작합니다. 위에서는 'jaehoney.tistory', 'tistory.com'이 반환됩니다.

 

수학 함수

SELECT ABS(-100); -- ABS(숫자)

ABS는 숫자의 절댓값을 반환합니다. 위에서는 100을 반환합니다.

SELECT CEILING(3.7), FLOOR(3.7), ROUND(3.7); -- CEILING(숫자), FLOOR(숫자), ROUND(숫자)

CEILING은 올림, FLOOR는 내림, ROUND는 반올림한 숫자를 반환합니다. 위에서는 4, 3, 4를 반환합니다.

SELECT MOD(14, 3), 14 % 3, 14 MOD 3 -- MOD(숫자1, 숫자2), 숫자1 % 숫자2, 숫자1 MOD 숫자2

MOD는 숫자1을 숫자2로 나눈 나머지 값을 구합니다. 위에서는 모두 2를 반환합니다. 

SELECT POW(3,2), SQRT(16) -- POW(숫자1, 숫자2), SQRT(숫자)

POW는 숫자1의 숫자2제곱 값을 구합니다. 위에서는 9가 반환됩니다.

SQRT는 숫자의 제곱근 값을 구합니다. 위에서는 4가 반환됩니다.

SELECT RAND(), -- RAND()
       FLOOR(1 + (RAND() * 6));

RAND는 0 이상 1 미만의 무작위 실수를 구합니다. 위에서는 (0~1사이의 실수)와 (1,2,3,4,5,6 중 랜덤으로 하나)를 반환합니다.

SELECT SIGN(-100) -- SIGN(숫자)

SIGN은 숫자가 양수면 1 음수면 -1을 반환합니다. 0이면 0을 반환합니다. 위에서는 -1을 반환합니다.

SELECT TRUNCATE(1234.1234, 2), -- TRUNCATE(숫자, 정수)
       TRUNCATE(1234.1234, -2);

TRUNCATE는 소숫점을 기준으로 정수 위치까지 구하고 나머지를 버립니다. 위에서는 1234.12, 1200을 반환합니다.

SELECT CONV(100, 10, 2) -- CONV(숫자, 기존 진수, 변환할 진수)

CONV는 기존 진수의 숫자를 변환할 지수로 계산 후 반환합니다. 위에서는 10진수 100을 2진수로 변환한 144가 반환됩니다.

SELECT DEGREES(PI()), RADIANS(180); -- DEGREES(숫자), RADIANS(숫자), PI()

DEGREES는 라디안 값을 각도값으로, RADIANS는 각도값을 라디안 값으로 변환합니다. PI()는 3.14152를 반환합니다.

위에서는 180, 원주율값이 반환됩니다.

 

날짜 및 시간 함수

SELECT ADDDATE('2020-01-01', INTERVAL 31 DAY), -- ADDDATE(날짜, 차이)
       SUBDATE('2020-01-01', INTERVAL 31 DAY); -- SUBDATE(날짜, 차이)

ADDDATE는 날짜를 기준으로 차이를 더한 날짜를 구합니다. 위에서는 '2020-02-01'이 반환됩니다.

SUBDATE는 날짜를 기준으로 차이를 뺀 날짜를 구합니다. 위에서는 '2019-12-01'이 반환됩니다.

DATE_ADD, DATE_SUB와 동일한 함수입니다.

SELECT ADDTIME('2020-01-01 21:20:00', '1:0:0'), -- ADDTIME(날짜/시간, 시간)
       SUBTIME('2020-01-01 21:20:00', '1:0:0'); -- SUBTIME(날짜/시간, 시간)

ADDTIME은 날짜/시간을 기준으로 시간을 더한 결과를 반환합니다. 위에서는 '2020-01-01 22:20:00)이 반환됩니다.

SUBTIME은 날짜/시간을 기준으로 시간을 뺀 결과를 반환합니다. 위에서는 '2020-01-01 20:20:00)이 반환됩니다.

SELECT CURDATE(), CURTIME(), NOW(), SYSDATE();

CURDATE는 현재 '연-월-일', CURTIME은 '시:분:초', NOW와 SYSDATE는 '연-월-일 시:분:초'를 반환합니다.

SELECT YEAR(NOW()), MONTH(NOW()), DAYOFMONTH(NOW()),
       HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()), MICROSECOND(NOW));

YEAR, MONTH, DAYOFMONTH, HOUR, MINUTE, SECOND, MICROSECOND는 특정 날짜나 시간에 대한 연, 월, 일, 시, 분, 초, 밀리초를 반환합니다.

SELECT DATE(NOW()), TIME(NOW());

DATE는 '연-월-일', TIME은 '시:분:초'를 반환합니다.

SELECT DATEDIFF('2020-1-5', '2020-1-1'), -- DATEDIFF(날짜1, 날짜2)
       TIMEDIFF('14:30:00', '06:30:00'); -- TIMEDIFF(날짜1 OR 시간1, 날짜2 OR 시간2)

DATEDIFF는 날짜2에서 날짜1까지 몇 일 남았는지를 반환합니다. 위에서는 5가 반환됩니다.

TIMEDIFF는 시간이 얼마나 남았는지를 반환합니다. 위에서는 08:00:00이 반환됩니다.

SELECT DAYOFWEEK(NOW()), MONTHNAME(NOW()), DAYOFYEAR(NOW());

DAYOFWEEK는 요일을(월:2 화:3), MONTHNAME은 해당 월의 영어이름, DAYOFYEAR는 1년 중 몇일이 지났는지를 반환합니다.

SELECT LAST_DAY('2020-02-04');

LAST_DAY는 주어진 월의 마지막날을 반환합니다. 위에서는 '2020-02-29'가 반환됩니다.

SELECT TIME_TO_SEC('10:53:10');

TIME_TO_SEC은 시간을 초 단위로 구합니다. 위에서는 '39190'이 출력됩니다.

 

시스템 정보 함수

시스템의 정보를 출력하는 함수입니다.

SELECT USER(), CURRENT_USER(), SESSION_USER();

USER()와 CURRENT_USER()와 SESSION_USER()는 모두 현재 사용자를 'root@localhost' 형식으로 반환합니다.

SELECT DATABASE(), SCHEMA();

DATABASE()와 SCHEMA()는 모두 현재 선택(USE)된 데이터베이스를 반환합니다.

SELECT FOUND_ROWS();

SELECT FOUND_ROWS()는 제일 최근에 했던 SELECT문의 조회 행 개수를 구합니다.

SELECT ROW_COUNT();

ROW_COUNT()는 제일 최근에 했던 INSERT, UPDATE, DELETE문에서 실행했던 행의 개수를 구합니다. SELECT문은 -1을 반환하고 CREATE, DROP문은 0을 반환합니다.

SELECT VERSION();

VERSION()는 현재 MySQL의 버전을 구합니다. 저는 '8.0.21'이 반환되었습니다.

SELECT SLEEP(5); -- SLEEP(초)

쿼리의 실행을 초동안 멈춥니다. 위의 경우는 5초동안 쿼리를 멈추고 다음 쿼리문은 5초가 지나야 실행됩니다.

 

그 외 함수

이외에도 비트 함수, 전체 텍스트 검색 함수, 보안 함수, 압축 함수, Enterprise 암호화 함수, XML 함수, 공간 분석 함수, JSON 함수 등이 있습니다.

 

나머지 내장 함수 들은

dev.mysql.com/doc/refman/8.0/en/functions.html

MySQL :: MySQL 8.0 Reference Manual :: 12 Functions and Operators

여기서 확인하실 수 있습니다.