![leejinwoo1126](https://t1.daumcdn.net/tistory_admin/static/manage/images/r3/default_L.png)
개요
- 파일명 내에 숫자 범위 검색 가능하도록 검색 필터 기능 추가 요청받음
- Mysql v8.0 부터 regexp_replace() function 이 지원되는데, 테스트 DB 버전 확인 결과 v5.7 이었음😅
- 인터넷에 있는 소스는 동작하지 않아, 직접 mysql function 선언 복습/정리함
Mysql 함수 선언 형식
CREATE FUNCTION '함수명'(파라미터명 as 타입, ...) ----- ①
RETURNS 반환할 데이터 타입 ----- ②
BEGIN
선언문, 수행할 쿼리 .. ----- ③
RETURN 반환할 값 ----- ④
END
설명
① Function Arguments/parameters
함수 매개변수/파라미터 명칭 및 타입 선언 // ex. name VARCHAR(20), age INTEGER
② Return Date Type
최종적으로 함수에서 RETURN 할 데이터의 타입 // ex. VARCHAR(20)
③ BUSINESS LOGIC
# 변수 선언
DECLEARE 변수명 데이터타입(크기); // DEFAULT 처리 가능
# 변수 초기화
SET 변수명 = 값;
SELECT 컬럼 INTO 변수명; // 함수에서 조회한 특정 컬럼 내용을 변수명에 모두 적용됨 ex. CONCAT() 처리 후 변수에 적용하는 용도
# 조건문
1) If Statement
IF 조건문 THEN 실행/처리;
ELSEIF 조건문 THEN 실행/처리;
ELSE 실행/처리;
END IF;
2) Case Statement -- 아직 사용 안 해봄
CASE
WHEN 조건 THEN 처리
WHEN 조건 TEHN 처리
ELSE 처리
END
# 반복문
1) Loop Statement
[begin_label:] LOOP
statement_list
END LOOP [end_label]
------------------------
label1: LOOP //label1은 LOOP명칭
SET p1 = p1 + 1;
IF p1 < 10 THEN
ITERATE label1; // continue 와 같은 역할
END IF; // IF문 끝 부분 표시
LEAVE label1; // break 와 같은 역할 Loop명을 적어줘야 함.
END LOOP label1; // LOOP문 끝 부분 표시
2) 그외 While Statement , Repeat ~ Until Statement 등이 있음(생략)
④ BUSINESS LOGIC 에서 DECLEAR(선언)한 변수 중 최종적으로 RETURN 할 변수명을 적음
참고. Mysql 5.7 공식 Reference
https://dev.mysql.com/doc/refman/5.7/en
MySQL :: MySQL 5.7 Reference Manual
MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6 Abstract This is the MySQL Reference Manual. It documents MySQL 5.7 through 5.7.44, as well as NDB Cluster releases based on version 7.5 of NDB through 5.7.44-ndb-7.5.32, respec
dev.mysql.com
regexp_replace() 함수 선언
- 파일명 내에 있는 숫자가 필요하므로 정규식 통해 뽑아서 형변환(CAST) 후 결과값 리턴한다
- 파일명에 숫자가 없으면 '0'을 반환
SELECT regexp_replace(대상 컬럼명|텍스트,'정규 표현식','교체 값');
DELIMITER $$
CREATE FUNCTION `regexp_replace`(original VARCHAR(100), pattern VARCHAR(100), replacement VARCHAR(100))
RETURNS BIGINT -- RETURN TYPE → BIGINT
DETERMINISTIC
BEGIN
DECLARE result VARCHAR(100); -- DECLARE : 선언하다
DECLARE ch VARCHAR(1);
DECLARE i INT;
SET i = 1; -- 초기화
SET result = '';
IF original REGEXP pattern THEN -- REGEXP 정규 표현식 만족하면 1 ( 틀리면 0 )
loop_label: LOOP
IF i > CHAR_LENGTH(original) THEN
LEAVE loop_label;
END IF;
SET ch = SUBSTRING(original,i,1); -- SUBSTRING(매개변수,시작위치(1부터),짜를 문자수)
IF NOT ch REGEXP pattern THEN
SET result = CONCAT(result, ch); -- CONCAT() 연결함수
ELSE
SET result = CONCAT(result, replacement);
END IF;
SET i = i+1;
END LOOP;
ELSE
SET result = original;
END IF;
RETURN CAST(result as unsigned); -- 최종적으로 형변환 , 숫자가 없으면 0
END$$
DELIMITER ;
function 생성 후 테스트 해본 결과 파일명내 번호로 범위 검색이 가능했다
SELECT *
FROM TABLE_NAME
WHERE file_name regexp '^test[0-9]+.xml$' = 1 -- test123.xml 형태의 파일명 찾음
AND regexp_replace(file_name, '[.|a-z|A-Z]', '') between 15 and 20; -- 문자열내 숫자 검출 후 15~20 사이 값인지 filtering
참고. Hibernate(ORM) custom function 등록 포스팅
https://dev-ljw1126.tistory.com/124
[Hibernate] DBMS custom function 을 ORM에서 호출하는 방법
개요 ORM 으로 Hibernate를 맨땅에서 시작한지 3개월 차, 요구사항 기능 구현하는데 custom function이 필요하게 되었다. 상세한 내용은 생략하고 설정 방법과 Criteriabuilder로 어떻게 function을 사용해서 호
dev-ljw1126.tistory.com
'공부 > DB' 카테고리의 다른 글
[MySQL] transaction isolation 이상현상 3가지(Dirty Read, Non-Repeatable Read, Phantom Read) (1) | 2024.04.30 |
---|---|
H2 데이터 베이스 연결(*.yml) (0) | 2023.06.30 |
[DA# Modeler] Mysql 연결해서 논리/물리 ERD 생성하기 (0) | 2022.03.12 |
[MySql Workbench] SSH 터널링 통해 DB 연결하기 (0) | 2022.02.25 |
[DBeaver] SQL자동완성풀기, 행번호표시, 컬럼설명표시, 단축키 확인 및 설정 (0) | 2021.10.12 |
![leejinwoo1126](https://t1.daumcdn.net/tistory_admin/static/manage/images/r3/default_L.png)
포스팅이 좋았다면 "좋아요❤️" 또는 "구독👍🏻" 해주세요!