[mysql] 마스킹처리하는 함수 만들고 실행하기

문자열, 마스킹처리 시작위치, 마스킹처리 끝위치, 마스킹 문자를 입력하면

문자열을 마스킹처리 해주는 함수를 만들어보겠습니다.

MASK("잇서니티스토리블로그", 3, 7, "*" ) 함수를 수행하면 잇서니****블로그로 출력될 것입니다.

1. 함수 생성

DELIMITER //

DROP FUNCTION IF EXISTS mwav.MASK//

/**
 * Returns a new string that is masked by given character.
 * The masked portion begins at the specified P_BEGIN_INDEX and extends to the character at index P_END_INDEX - 1.
 * Thus, the lenght of the masked portion is (P_END_INDEX - P_BEGIN_INDEX).
 * BEWARE that the index starts with zero and P_END_INDEX is equals or greater than P_BEGIN_INDEX.
 * 
 * Examples :
 * MASK('ABCDEFG', 2, 5, '*') -> 'AB***FG'
 * MASK('123456-0000000', 7, 14, '*') -> '123456-*******'
 * MASK('', 0, 4, '*') -> '****'
 * 
 * @param P_INPUT         : Input string
 * @param P_BEGIN_INDEX    : The beginning index, inclusive.
 * @param P_END_INDEX    : The ending index, exclusive.
 * @param P_CHARACTER     : Mask character
 */


CREATE FUNCTION MASK
(
      P_INPUT VARCHAR(100)
    , P_BEGIN_INDEX INT
    , P_END_INDEX INT
    , P_CHARACTER VARCHAR(1)
)
RETURNS VARCHAR(100) DETERMINISTIC

BEGIN
    RETURN CONCAT(
                      LEFT(P_INPUT, P_BEGIN_INDEX)
                    , LPAD('', P_END_INDEX - P_BEGIN_INDEX, P_CHARACTER)
                    , RIGHT(P_INPUT, CHAR_LENGTH(P_INPUT) - P_END_INDEX)
                 );
END

//
DELIMITER ;

• DELIMITER
  • 함수를 만들 때 DELIMITER 구문으로 시작한다.
  • 함수 구현이 끝나면 DELIMITER 구문으로 마무리한다.
• CREATE FUNCTION
  • 함수를 정의하는 부분이다.
  • 함수이름과 입력 받을 파라미터를 정의한다.
• RETURNS
  • 함수는 반드시 리턴값이 있어야 한다. 즉 출력되는 값이다.
  • RETURNS 다음에 리턴값의 형식을 적어준다. (ex. VARTCHAR, INT 등)
• DETERMINISTIC 또는 NOT DETERMINISTIC
  • DETERMINISTIC은 확정적이라는 뜻이다. 함수에 같은 입력값을 넣으면 항상 같은 값을 출력하는 경우이다.
  • NOT DETERMINISTIC은 확정적이지 않다는 뜻이다. 함수에 같은 입력값을 넣어도 다른 값을 출력할 수 있는 경우이다. 예를 들어, 함수 안에 랜덤 숫자를 만들어내는 RAND() 함수를 쓰는 경우이다.
• BEGIN ~ END
  • 함수 로직이 구현되는 부분이다.
  • LEFT(), LPAD(), RIGHT() 함수를 활용하여 문자열을 자르고 마스킹 처리를 한 후
  • CONCAT()으로 문자열을 합쳐서 최종 결과값을 RETURN하는 로직이다.

-- 문자에 왼쪽을 기준으로 일정 갯수를 가져오는 함수
-- LEFT(문자, 가져올 개수)
SELECT LEFT("잇서니티스토리블로그",3);
-- '잇서니' 출력됨

-- 왼쪽부터 특정문자를 원하는 자리수만큼 채우는 함수
-- LPAD(문자, 원하는 자리수, 채울 문자)
SELECT LPAD("잇서니티스토리블로그", 7-3, "*")
-- '****' 출력됨

//문자에 오른쪽을 기준으로 일정 갯수를 가져오는 함수.
-- RIGHT(문자, 가져올 개수)
SELECT RIGHT("잇서니티스토리블로그",CHAR_LENGTH("잇서니티스토리블로그")-7);
-- '블로그' 출력됨

//위 결과들을 CONCAT()으로 합치면 '잇서니****블로그'가 출력됨
SELECT CONCAT(
    LEFT("잇서니티스토리블로그",3),
    LPAD("잇서니티스토리블로그", 7-3, "*"),
    RIGHT("잇서니티스토리블로그",CHAR_LENGTH("잇서니티스토리블로그")-7)
);

　

2. 함수 실행권한 설정

// GRANT <부여할 권한> ON FUNCTION <스키마이름>.<함수이름> TO '<user ID>'@'<host>';
grant execute on function mwav.MASK to 'xtrabackup'@'%';

flush privileges;

　

3. 함수 실행

SELECT mwav.MASK(mbrLoginId,5,LENGTH(mbrLoginId),'*')
FROM mwav.Member_tbl;

　

참고) 프로시저와 함수 차이

	함수	프로시저
실행되는 곳	클라이언트	서버
리턴값	있어야 함	있어도 되고 없어도 됨
속도	다소 느림	서버에서 실행되므로 빠른 편
호출방법	SELECT 함수명() FROM 데이터베이스명 ;	CALL 프로시저명() ; 또는 EXECUTE 프로시저명() ;