컴퓨터 활용/노년에 즐기는 코딩

MySQL 8.x 기준 내장 함수 핵심 정리

easyfly 2025. 11. 6. 11:22
반응형

다음은 MySQL 8.x 기준 내장 함수 핵심 정리와 실무 예시입니다. 범주별로 대표 문법, 사용 팁, 주의점을 함께 묶었습니다. 

 

1) 문자열 함수 (String)

  • 길이/자르기
  • SELECT LENGTH('한글'), CHAR_LENGTH('한글'); -- 바이트/문자 수(UTF-8에서 다름) SELECT LEFT('abcdef',3), RIGHT('abcdef',2); SELECT SUBSTRING('가나다라마바사',3,2); -- '다라'
  • 검색/치환/결합
  • SELECT INSTR('bananA','na'); -- 3 (1부터 시작) SELECT LOCATE('na','banana',4); -- 5 (시작 위치 지정) SELECT REPLACE('a,b,c', ',', '|'); -- 'a|b|c' SELECT CONCAT('Hello ', UPPER('mysql')); -- 'Hello MYSQL' SELECT CONCAT_WS('-', '2025','07','20'); -- '2025-07-20'
  • 공백/패딩/정렬
  • SELECT TRIM(' x '), LTRIM(' x'), RTRIM('x '); SELECT LPAD('7',3,'0'), RPAD('A',5,'*'); -- '007', 'A****'
  • 비교/정규표현식
  • SELECT 'abc' REGEXP '^[a-z]+$'; -- 1(일치), 0(불일치), NULL SELECT REGEXP_REPLACE('010-1234-5678','-',''); -- '01012345678'

팁: 한글 길이는 CHAR_LENGTH()로, 바이트 계산은 LENGTH()로 확인하십시오. 정규식은 8.0에서 개선되었으며 ICU 엔진을 사용합니다.

2) 수치 함수 (Numeric)

  • 절대값/반올림
  • SELECT ABS(-3.14), ROUND(3.14159,2), CEIL(2.1), FLOOR(2.9);
  • 나머지/난수
  • SELECT MOD(10,3); -- 1 SELECT RAND(), RAND(42); -- 시드 고정 가능(재현성)
  • 합성/로그/지수
  • SELECT POW(2,10), EXP(1), LN(10), LOG10(1000);

팁: ROUND(x,0)은 정수 반올림, TRUNCATE(x,d)는 절삭입니다.

3) 날짜·시간 함수 (Date/Time)

  • 현재 시각
  • SELECT NOW(), CURRENT_DATE(), CURRENT_TIME();
  • 구성/파싱
  • SELECT MAKEDATE(2025, 200); -- 2025-07-19 SELECT MAKETIME(14,30,0); -- 14:30:00 SELECT STR_TO_DATE('2025/07/20','%Y/%m/%d');
  • 연산/차이
  • SELECT DATE_ADD('2025-07-20', INTERVAL 7 DAY); SELECT TIMESTAMPDIFF(DAY, '2025-07-01', '2025-07-20'); -- 19 SELECT DATEDIFF('2025-07-20','2025-07-01'); -- 19
  • 부분 추출/형식화
  • SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()); SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');

주의: 타임존을 사용한다면 세션 변수 time_zone을 확인하십시오. 애플리케이션은 UTC 저장, 현지 시간 변환을 권장합니다.

4) 집계 함수 (Aggregate)

  • 기본
  • SELECT COUNT(*), COUNT(col), SUM(col), AVG(col), MIN(col), MAX(col) FROM t; SELECT GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') FROM users;

NULL 규칙: COUNT(col)은 NULL을 제외하고 세며, SUM/AVG는 모든 값이 NULL이면 NULL을 반환합니다(필요 시 COALESCE로 대체값 지정).

5) 윈도 함수 (Window / OLAP, 8.0+)

  • 누계/순위
  • SELECT emp, dept, salary, SUM(salary) OVER (PARTITION BY dept ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_sum, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk FROM employees;
  • 집계 유지한 채 상세 행까지 함께 조회할 때 유용합니다.
    성능 팁: PARTITION BY/ORDER BY에 맞는 인덱스를 고려하십시오.

6) 조건·흐름 제어 (Conditional)

SELECT IF(1<2, 'Y','N');                   -- 스칼라 if
SELECT IFNULL(col, 'N/A'), COALESCE(a,b,0);
SELECT CASE
         WHEN score >= 90 THEN 'A'
         WHEN score >= 80 THEN 'B'
         ELSE 'C'
       END AS grade;

SARGability: WHERE 절에서 컬럼에 함수를 감싸면 인덱스를 못 탈 수 있습니다. 가능하면 상수를 변환하는 방식으로 작성하십시오.
예) WHERE date_col >= CURDATE()(좋음) vs WHERE DATE(date_col) = CURDATE()(인덱스 사용 불리).

7) 변환·캐스팅 (Type/Collation)

SELECT CAST('123' AS UNSIGNED), CONVERT('2025-07-20', DATETIME);
SELECT CONVERT('abc' USING utf8mb4);        -- 문자셋 변환

한글 정렬: COLLATE utf8mb4_0900_ai_ci 등 정렬규칙에 따라 대/소문자·악센트·자모 분해 처리 등이 달라집니다.

8) JSON 함수 (8.0 필수 기능)

  • 생성/접근
  • SELECT JSON_OBJECT('name','홍길동','age',70); -- {"name":"홍길동","age":70} SELECT JSON_EXTRACT('{"a":1,"b":[10,20]}', '$.b[1]'); -- 20 SELECT JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name')) FROM t;
  • 수정/머지/검색
  • SELECT JSON_SET('{"a":1}', '$.b', 2); -- {"a":1,"b":2} SELECT JSON_MERGE_PATCH('{"a":1}', '{"a":9,"c":3}');-- {"a":9,"c":3} SELECT JSON_SEARCH('["사과","배","포도"]','one','배'); -- 경로 반환
  • 인덱싱(생성 컬럼 + 함수 인덱스)
  • ALTER TABLE orders ADD COLUMN cust_id INT GENERATED ALWAYS AS (JSON_EXTRACT(doc,'$.custId')) STORED, ADD INDEX idx_cust_id (cust_id);

팁: 쿼리에서 자주 꺼내는 JSON 키는 생성(가상/저장) 컬럼 + 인덱스로 최적화하십시오.

9) 보안·해시·암호화

SELECT SHA2('text',256);
SELECT MD5('text');                    -- 충돌 위험, 무결성 확인 외엔 비권장
SELECT AES_ENCRYPT('secret','key'), AES_DECRYPT(aes_col,'key');

주의: 사용자 비밀번호는 애플리케이션 층에서 강력한 해시(예: bcrypt/Argon2)를 사용하십시오. DB 내 단순 해시는 지양합니다.

10) 정보 함수 (메타/상태)

SELECT VERSION(), CONNECTION_ID(), DATABASE(), USER();
SELECT ROW_COUNT();      -- 직전 DML 영향 행수
SELECT LAST_INSERT_ID(); -- AUTO_INCREMENT 마지막 값(세션별)

11) 공간(GIS)·텍스트

  • 공간(옵션): ST_Distance, ST_Contains, ST_AsText, ST_GeomFromText 등.
  • 전문검색: MATCH(col) AGAINST ('+나주 +문학' IN BOOLEAN MODE)
    주의: InnoDB FULLTEXT는 한국어 형태소 한계가 있습니다. 필요한 경우 외부 검색엔진(Elasticsearch 등) 고려.

12) 실전 예시: 하루 주간 매출 요약

-- 스키마: sales(id, sold_at DATETIME, amount DECIMAL(10,2))
-- 1) 오늘 vs 어제 매출
SELECT
  DATE(sold_at) AS d,
  ROUND(SUM(amount),0) AS total
FROM sales
WHERE sold_at >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY d;

-- 2) 최근 7일 이동평균(윈도 함수)
SELECT
  DATE(sold_at) AS d,
  SUM(amount) AS day_sum,
  ROUND(AVG(SUM(amount)) OVER (ORDER BY DATE(sold_at)
       ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 0) AS ma7
FROM sales
GROUP BY DATE(sold_at)
ORDER BY d;

-- 3) 시간대별 피크(00~23시)
SELECT
  HOUR(sold_at) AS hh,
  SUM(amount) AS sum_amt
FROM sales
WHERE sold_at >= DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY hh
ORDER BY sum_amt DESC
LIMIT 5;

13) NULL·정밀도·성능 체크리스트

  • NULL: = NULL은 항상 FALSE → IS NULL/IS NOT NULL 사용.
  • 정밀도: 금액은 DECIMAL(p,s) 권장(부동소수 오차 회피).
  • 함수 + 인덱스: 8.0의 함수 기반 인덱스를 활용하면 계산식에서도 인덱스 사용 가능.
  • CREATE INDEX idx_date_only ON sales ((DATE(sold_at)));
  • 결정성(Deterministic): 가상 컬럼/함수 인덱스는 결정적 표현식이 유리합니다.
  • 로케일: 정렬/대소문자 비교는 COLLATE에 좌우. 한글 검색 품질은 적절한 Collation 선택.

14) 자주 쓰는 미니 레시피

-- 1) 안전 나누기(0 회피)
SELECT amount / NULLIF(cnt,0) FROM stats;

-- 2) 쉼표금액 표시
SELECT FORMAT(SUM(amount), 0) FROM sales;

-- 3) 전화번호 하이픈 제거/표준화
SELECT REGEXP_REPLACE(tel, '[^0-9]', '');

-- 4) 다중 문자열 포함 필터(INSTR/REGEXP)
SELECT * FROM posts
WHERE title REGEXP '(낭독극|오유권|영산포)';

-- 5) 문자열 → 날짜 캐스팅 실패 처리
SELECT STR_TO_DATE(dt_str, '%Y-%m-%d') AS d
FROM raw
WHERE STR_TO_DATE(dt_str, '%Y-%m-%d') IS NOT NULL;

15) 학습·강의용 실습 테이블

CREATE TABLE demo(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  score INT,
  jdoc JSON,
  sold_at DATETIME,
  amount DECIMAL(10,2)
);

INSERT INTO demo(name,score,jdoc,sold_at,amount) VALUES
('홍길동',95, JSON_OBJECT('city','나주','age',70), NOW(), 12000.00),
('심청',  82, JSON_OBJECT('city','영산포','age',68), NOW(),  8000.00),
('춘향', NULL, JSON_OBJECT('city','광주','age',72), NOW(),  9000.00);

-- 실습 포인트: COALESCE/CASE/JSON_EXTRACT/DATE_FORMAT/윈도함수
SELECT
  name,
  COALESCE(score,0) AS score0,
  JSON_UNQUOTE(JSON_EXTRACT(jdoc,'$.city')) AS city,
  DATE_FORMAT(sold_at, '%Y-%m-%d %H:%i') AS ts,
  SUM(amount) OVER (ORDER BY id) AS run_sum
FROM demo;

마무리 요약

  • 문자열/수치/날짜 핵심 함수로 **전처리·집계의 80%**를 해결할 수 있습니다.
  • 8.0의 윈도 함수·JSON 함수·함수 기반 인덱스는 보고서·API 성능을 크게 끌어올립니다.
  • 인덱스 활용을 위해 WHERE 절에서 컬럼을 함수로 감싸지 않기, 필요한 경우 생성 컬럼 + 인덱스를 고려하십시오.

 

반응형
저작자표시 (새창열림)