MySQL 내장 함수 99개 총정리

『 '데이널' 컨텐츠에 포함된 정보는? 』

 

MySQL에서 SQL을 만들다 보면, 간단한 작업부터 복잡한 계산까지 다양한 함수가 필요합니다. MySQL은 이러한 작업을 쉽게 처리할 수 있도록 여러 내장 함수를 지원하고 있습니다. 이글을 이용해 MySQL에서 사용되는 내장 함수들을 모두 정리해 보겠습니다.

 

MySQL 함수는 문자열 함수, 수학 함수, 날짜와 시간 함수, 논리 함수, 그리고 집계 함수로 구분할 수 있습니다. 각 영역별로 사용되는 대부분의 함수를 정리했으니 참고하시기 바랍니다. 

 

MySQL 함수 종류

 

1. 문자열 함수

문자열 함수는 텍스트를 처리할 때 주로 사용되는데요. 예를 들어, 이름이나 주소와 같은 데이터를 다룰 때 많이 사용됩니다.

함수명	설명	예시	결과
CONCAT()	여러 문자열을 하나로 합침	CONCAT('Hello','','World!')	Hello World!
SUBSTRING()	문자열의 일부를 추출	SUBSTRING('HelloWorld!',7,5)	World
LENGTH()	문자열의 길이를 반환	LENGTH('Hello')	5
UPPER()	문자열을 대문자로 변환합니다.	UPPER('hello')	HELLO
LOWER()	문자열을 소문자로 변환합니다.	LOWER('HELLO')	hello
TRIM()	문자열의 앞뒤 공백을 제거합니다.	TRIM(' Hello ')	Hello
LTRIM()	문자열의 앞쪽 공백을 제거합니다.	LTRIM(' Hello')	Hello
RTRIM()	문자열의 뒤쪽 공백을 제거합니다.	RTRIM('Hello ')	Hello
REPLACE()	문자열에서 특정 부분을 다른 문자열로 대체합니다.	REPLACE('Hello World', 'World' 'MySQL')	Hello MySQL
INSTR()	문자열 내에서 특정 문자열의 위치를 반환합니다.	INSTR('Hello World' 'World')	7
LEFT()	문자열의 왼쪽에서 지정한 길이만큼의 부분을 반환합니다.	LEFT('Hello World', 5)	Hello
RIGHT()	문자열의 오른쪽에서 지정한 길이만큼의 부분을 반환합니다.	RIGHT('Hello World', 5)	World
REPEAT()	문자열을 지정한 횟수만큼 반복합니다.	REPEAT('MySQL', 3)	MySQLMySQLMySQL
REVERSE()	문자열을 뒤집습니다.	REVERSE('MySQL')	LQSyM
LPAD()	문자열의 왼쪽에 지정한 문자로 채워서 지정한 길이로 만듭니다.	LPAD('123', 5, '0')	00123
RPAD()	문자열의 오른쪽에 지정한 문자로 채워서 지정한 길이로 만듭니다.	RPAD('123', 5, '0')	12300
CHAR_LENGTH()	문자열의 길이를 반환합니다. (문자의 개수 바이트 단위가 아님)	CHAR_LENGTH('Hello')	5
SUBSTRING_INDEX()	문자열에서 지정한 구분자를 기준으로 부분 문자열을 반환합니다.	SUBSTRING_INDEX('a,b,c,d', ',',2)	a, b
FIND_IN_SET()	문자열이 리스트 내에서 몇 번째 위치에 있는지 반환합니다. (쉼표로 구분된 리스트)	FIND_IN_SET('b', 'a,b,c')	2
FORMAT()	숫자를 문자열로 변환하고 지정한 형식으로 포맷팅합니다.	FORMAT(1234567.89, 2)	1234567.89

 

2. 수학 함수

수학 함수는 숫자 계산을 할 때 주로 사용됩니다. 합계를 구하거나 평균을 내는 등 다양한 계산에 유용합니다.

함수명	설명	예시	결과
ROUND()	숫자를 반올림	ROUND(3.14159,2)	3.14
FLOOR()	주어진 숫자보다 작거나 같은 가장 큰 정수를 반환	FLOOR(2.9)	2
ABS()	숫자의 절대값을 반환	ABS(-5)	5
CEIL()	주어진 숫자보다 크거나 같은 가장 작은 정수를 반환합니다.	CEIL(2.3)	3
CEILING()	`CEIL()`의 동의어로, 동일한 기능을 수행합니다.	CEILING(2.3)	3
FLOOR()	주어진 숫자보다 작거나 같은 가장 큰 정수를 반환합니다.	FLOOR(2.9)	2
ROUND()	숫자를 반올림합니다.	ROUND(3.14159, 2)	3.14
TRUNCATE()	숫자를 소수점 아래에서 지정한 자리까지 잘라냅니다.	TRUNCATE(3.14159, 2)	3.14
MOD()	나머지 연산을 수행하여 나머지를 반환합니다.	MOD(10, 3)	1
POW()	주어진 숫자를 거듭제곱합니다. (동의어: `POWER()`)	POW(2, 3)	8
POWER()	주어진 숫자를 거듭제곱합니다.	POWER(2, 3)	8
SQRT()	주어진 숫자의 제곱근을 반환합니다.	SQRT(16)	4
EXP()	주어진 숫자의 지수승(자연로그의 역수)을 계산합니다.	EXP(1)	2.718281828
LOG()	주어진 숫자의 자연 로그를 계산합니다.	LOG(10)	2.302585093
LOG10()	주어진 숫자의 10 로그를 계산합니다.	LOG10(1000)	3
LOG2()	주어진 숫자의 2 로그를 계산합니다.	LOG2(8)	3
PI()	원주율 π (3.141592653589793)을 반환합니다.	PI()	3.141592654
RADIANS()	각도를 라디안으로 변환합니다.	RADIANS(180)	3.141592654
DEGREES()	라디안을 각도로 변환합니다.	DEGREES(PI())	180
SIN()	주어진 각도의 사인 값을 반환합니다.	SIN(PI()/2)	1
COS()	주어진 각도의 코사인 값을 반환합니다.	COS(PI())	-1
TAN()	주어진 각도의 탄젠트 값을 반환합니다.	TAN(PI()/4)	1
SIGN()	숫자의 부호를 반환합니다. (양수는 1, 음수는 -1, 0은 0)	SIGN(-10)	-1
RAND()	0과 1 사이의 랜덤 값을 반환합니다.	RAND()	예:0.673847238402
GREATEST()	주어진 인수들 중에서 가장 큰 값을 반환합니다.	GREATEST(1, 5, 3, 9)	9
LEAST()	주어진 인수들 중에서 가장 작은 값을 반환합니다.	LEAST(1, 5, 3, 9)	1

 

3. 날짜와 시간 함수

날짜와 시간 함수는 시간과 관련된 데이터를 처리할 때 사용됩니다. 예를 들어, 날짜를 다른 형식으로 변환한다거나 할 때 많이 사용됩니다. 

함수명	설명	예시	결과
NOW()	현재 날짜와 시간을 반환	NOW()	현재 날짜와 시간
DATE_ADD()	날짜에 특정 기간을 더함	DATE_ADD('2023-08-27',INTERVAL10DAY)	`2023-09-06`
DATEDIFF()	두 날짜 사이의 차이를 계산	DATEDIFF('2023-09-06','2023-08-27')	`10`
CURDATE()	현재 날짜를 `YYYY-MM-DD` 형식으로 반환합니다.	CURDATE()	예: `2024-08-27`
CURTIME()	현재 시간을 `HH:MM:SS` 형식으로 반환합니다.	CURTIME()	예: `14:30:00`
DATE()	날짜 부분만 추출합니다.	DATE('2024-08-27 14:30:00')	`2024-08-27`
YEAR()	주어진 날짜의 연도를 반환합니다.	YEAR('2024-08-27')	`2024`
MONTH()	주어진 날짜의 월을 반환합니다.	MONTH('2024-08-27')	`8`
DAY()	주어진 날짜의 일을 반환합니다.	DAY('2024-08-27')	`27`
HOUR()	주어진 시간의 시간을 반환합니다.	HOUR('14:30:00')	`14`
MINUTE()	주어진 시간의 분을 반환합니다.	MINUTE('14:30:00')	`30`
SECOND()	주어진 시간의 초를 반환합니다.	SECOND('14:30:45')	`45`
WEEK()	주어진 날짜가 연중 몇 번째 주인지 반환합니다.	WEEK('2024-08-27')	`35`
DAYOFWEEK()	주어진 날짜가 주의 몇 번째 요일인지 반환합니다. (일요일이 1)	DAYOFWEEK('2024-08-27')	`3`
DAYOFMONTH()	주어진 날짜가 월의 몇 번째 날인지 반환합니다.	DAYOFMONTH('2024-08-27')	`27`
DAYOFYEAR()	주어진 날짜가 연중 몇 번째 날인지 반환합니다.	DAYOFYEAR('2024-08-27')	`240`
LAST_DAY()	주어진 날짜의 해당 월의 마지막 날짜를 반환합니다.	LAST_DAY('2024-08-15')	`2024-08-31`
ADDDATE()	날짜에 지정된 기간을 더합니다.	ADDDATE('2024-08-27', INTERVAL 10 DAY)	`2024-09-06`
SUBDATE()	날짜에서 지정된 기간을 뺍니다.	SUBDATE('2024-08-27', INTERVAL 10 DAY)	`2024-08-17`
TIMESTAMP()	날짜와 시간을 하나의 타임스탬프로 결합합니다.	TIMESTAMP('2024-08-27', '14:30:00')	`2024-08-27 14:30:00`
TIMEDIFF()	두 타임스탬프 간의 차이를 지정된 단위로 반환합니다.	TIMESTAMPDIFF(DAY, '2024-08-01', '2024-08-27')	`26`
TO_DAYS()	주어진 날짜를 연속된 일수로 변환합니다.	TO_DAYS('2024-08-27')	예: `750000`
FROM_DAYS()	연속된 일수를 날짜로 변환합니다.	FROM_DAYS(750000)	`2024-08-27`
STR_TO_DATE()	지정된 형식의 문자열을 날짜로 변환합니다.	STR_TO_DATE('27-08-2024', '%d-%m-%Y')	`2024-08-27`
DATE_FORMAT()	날짜를 지정된 형식으로 변환하여 반환합니다.	DATE_FORMAT('2024-08-27', '%Y/%m/%d')	`2024/08/27`
MAKEDATE()	주어진 연도와 연중 몇 번째 날인지로 날짜를 생성합니다.	MAKEDATE(2024, 240)	`2024-08-27`
MAKETIME()	주어진 시, 분, 초로 시간을 생성합니다.	MAKETIME(14, 30, 0)	`14:30:00`
SEC_TO_TIME()	초를 시간으로 변환하여 반환합니다.	SEC_TO_TIME(3661)	`01:01:01`
TIME_TO_SEC()	시간을 초로 변환하여 반환합니다.	TIME_TO_SEC('01:01:01')	`3661`

 

 

 

4. 논리 함수

논리 함수는 조건을 이용할 때 사용됩니다. 아래 함수들을 이용해서 특정 조건이 참인지 거짓인지에 따라 결과를 다르게 처리할 수 있습니다.

함수명	설명	예시	결과
IF()	조건에 따라 다른 값을 반환합니다.	IF(1<2,'True','False')	`True`
CASE WHEN	여러 조건을 평가하여 맞는 값을 반환	CASE WHEN age >= 18 THEN '성인' ELSE '미성년자' END	나이에 따라 '성인' 또는 '미성년자'
IFNULL()	첫 번째 인수가 NULL이면 두 번째 인수를 반환하고, 그렇지 않으면 첫 번째 인수를 반환합니다.	SELECT IFNULL(NULL, 'Default');	`'Default'`
NULLIF()	두 인수가 같으면 NULL을 반환하고, 그렇지 않으면 첫 번째 인수를 반환합니다.	SELECT NULLIF(1, 1);	`NULL`
COALESCE()	인수 목록에서 첫 번째 NULL이 아닌 값을 반환합니다.	SELECT COALESCE(NULL, NULL, 'MySQL');	`'MySQL'`
GREATEST()	주어진 인수들 중에서 가장 큰 값을 반환합니다.	SELECT GREATEST(1, 3, 7, 2);	`7`
LEAST()	주어진 인수들 중에서 가장 작은 값을 반환합니다.	SELECT LEAST(1, 3, 7, 2);	`1`
ELT()	주어진 인덱스에 해당하는 문자열을 반환합니다.	SELECT ELT(2, 'red', 'green', 'blue');	`'green'`
INTERVAL()	주어진 값이 지정된 값 사이에 어디에 위치하는지 반환합니다.	SELECT INTERVAL(5, 1, 4, 6, 8);	`2`
STRCMP()	두 문자열을 비교하여 같으면 0, 첫 번째가 크면 1, 두 번째가 크면 -1을 반환합니다.	SELECT STRCMP('abc', 'abd');	`-1`

 

5. 집계 함수

집계 함수는 여러 행의 데이터를 Grouping해서 결과를 산출합니다. 예를 들어, 직원들의 평균 연봉을 계산할 때 집계 함수를 사용합니다.

함수명	설명	예시	결과
SUM()	숫자 열의 합계를 반환합니다.	SELECT SUM(price) FROM products;	전체 `price`의 합계
AVG()	숫자 열의 평균값을 반환합니다.	SELECT AVG(score) FROM students;	전체 `score`의 평균
COUNT()	행의 개수를 셉니다.	SELECT COUNT(*) FROM orders;	전체 행의 수
MAX()	주어진 열에서 최대값을 반환합니다.	SELECT MAX(salary) FROM employees;	최대 `salary` 값
MIN()	주어진 열에서 최소값을 반환합니다.	SELECT MIN(age) FROM customers;	최소 `age` 값
GROUP_CONCAT()	그룹화된 결과를 문자열로 연결하여 반환합니다.	SELECT GROUP_CONCAT(name) FROM students;	`name`들을 쉼표로 연결
COUNT(DISTINCT)	고유한 값의 개수를 셉니다.	SELECT COUNT(DISTINCT department)  FROM employees;	고유한 `department`의 수
STDDEV()	집합의 표준편차를 반환합니다.	SELECT STDDEV(salary) FROM employees;	`salary`의 표준편차
VARIANCE()	집합의 분산을 반환합니다.	SELECT VARIANCE(salary) FROM employees;	`salary`의 분산
BIT_AND()	모든 행의 비트 AND 연산 결과를 반환합니다.	SELECT BIT_AND(flags) FROM settings;	모든 `flags`의 비트 AND
BIT_OR()	모든 행의 비트 OR 연산 결과를 반환합니다.	SELECT BIT_OR(flags) FROM settings;	모든 `flags`의 비트 OR
BIT_XOR()	모든 행의 비트 XOR 연산 결과를 반환합니다.	SELECT BIT_XOR(flags) FROM settings;	모든 `flags`의 비트 XOR
JSON_ARRAYAGG()	JSON 배열 형식으로 그룹화된 결과를 반환합니다.	SELECT JSON_ARRAYAGG(name) FROM students;	이름들의 JSON 배열
JSON_OBJECTAGG()	JSON 객체 형식으로 키-값 쌍을 그룹화하여 반환합니다.	SELECT JSON_OBJECTAGG(id, name) FROM students;	ID와 이름의 JSON 객체

 

마치며

MySQL의 내장 함수도 오라클만큼이나 많이 지원합니다. 자주 사용하는 함수는 이미 외우고 있지만 가끔씩 필요한 함수들은 참고하기 위해서 정리해 보았습니다. 위 정리된 함수들만 알아도 대부분의 데이터 작업을 처리할 수 있을 거예요.

 

오라클 함수의 개념 및 활용 방법은 무엇일까?

[SQLD] 함수의 종류 및 활용(문자함수, 숫자함수, 날짜함수, 분석함수, ALL, ANY)