검색 자동완성 with mysql function

 

블로그 다른 글에서 검색창에서 자동완성을 위한 자료구조를 쓴 적이 있다.

trie라는 자료구조를 이용해 단어를 쪼개고(자음까지) tree로 연결연결 시켜서 자동완성을 구현했었더랬다.

브랜드 이름 검색어 자동완성 with Trie

브랜드 이름 검색어 자동완성 with Trie

 

그치만 이번에 프로젝트하면서 또 다른 방법으로 구현하는 선임을 보면서 해당 방법을 한번 정리해보고자 한다.

 

 

네이버 주식을 보면 검색할 수 있는 부분이 있고, 종목코드, 종목명, 종목명의초성, 종목명+초성으로 검색할 수 있게 되어 있다.

종목명, 코드는 DB에 저장되어 있다고 했을때...

이전 블로그 포스팅 방법으로는 종목명을 전부 들고와 trie 자료구조에 맞게 변형시켜서 구현했다면,

선임의 경우는 종목명의 초성만 추출하는 “mysql function” sql을 구성한 뒤,

(코드, 종목명, 종목명의 초성) 만 존재하는 view를 만들고 해당 view에 쿼리를 날리는 방식으로 구성했다.

 

 

한번 구현해보자.

데이터는 한국거래소 정보데이터시스템 홈페이지에서 구했다.

http://data.krx.co.kr/contents/MDC/MAIN/main/index.cmd

KRX 정보데이터시스템

 

 

한국 주식 종목의 정보를 다운로드 받아 DB에 저장해 준다.

(M1 mac으로 워크벤치를 사용하는 경우, (내경우엔 8.0.30CE 버전)

xlsx를 다운받아 pages로 csv 변환, CotEditor로 csv 인코딩형식을 utf8로 변환, 터미널에서

open /Applications/MySQLWorkbench.app 로 워크벤치 실행 후 import 해야 제대로 들어간다…)

 

 

테이블의 정보는 아래와 같다. (내 경우엔 종목코드, 종목명, 시장 정보를 뺀 나머지는 제외했다.)

table의 이름은 stockInfo, 데이터는 아래와 같이 나온다.

 

이제 초성만 추출하는 function을 만들어 보자. 이 글을 쓰게 된 이유가 mysql로 데이터를 조회만 해봤지

function을 만들어본 적이 없기에 실제로 구현하는데 의의를 두었다.

mysql function의 기초 골자는 이렇다.

DELIMITER $$
CREATE FUNCTION 함수이름 ( 인자이름 인자타입 ) RETURNS 반환타입
BEGIN
	-- 변수 선언
	declare 변수이름 변수타입 default 초기값;
	
	-- 로직
	
END $$
DELIMITER ;

 

이제 내가 원하는 종목명이 들어오면, 종목명의 초성을 반환하는 함수를 구성해 보자.

drop function syl;
DELIMITER $$
CREATE FUNCTION syl(name text) returns text
BEGIN
	declare i INT default 0;
    declare charLen INT default 0;
    declare result varchar(50) default '';
    declare temp varchar(10) default '';
    
    set charLen = char_length(name);
    while (i <= charLen) do
        set temp = right(left(name, i),1);
        if temp >= '가' and temp <= '깋' then
            set result = concat(result, 'ㄱ');
        elseif temp >= '까' and temp <= '낗' then
            set result = concat(result, 'ㄲ');
        elseif temp >= '나' and temp <= '닣' then
            set result = concat(result, 'ㄴ');
        elseif temp >= '다' and temp <= '딯' then
            set result = concat(result, 'ㄷ');
        elseif temp >= '따' and temp <='띻' then
            set result = concat(result, 'ㄸ');
        elseif temp >= '라' and temp <= '맇' then
            set result = concat(result, 'ㄹ');
        elseif temp >='마' and temp <='밓' then
            set result = concat(result, 'ㅁ');
        elseif temp >='바' and temp <='빟' then
            set result = concat(result, 'ㅂ');
        elseif temp >='빠' and temp <='삫' then
            set result = concat(result, 'ㅃ');
        elseif temp >='사' and temp <='싷' then
            set result = concat(result, 'ㅅ');
        elseif temp >='싸' and temp <='앃' then
            set result = concat(result, 'ㅆ');
        elseif temp >='아' and temp <= '잏' then
            set result = concat(result, 'ㅇ');
        elseif temp >= '자' and temp <='짛' then
            set result = concat(result, 'ㅈ');
        elseif temp >='짜' and temp <='찧' then
            set result = concat(result, 'ㅉ');
        elseif temp >= '차' and temp <='칳' then
            set result = concat(result, 'ㅊ');
        elseif temp >= '카' and temp <='킿' then
            set result = concat(result, 'ㅋ');
        elseif temp >= '타' and temp <='팋' then
            set result = concat(result,'ㅌ');
        elseif temp >='하' and temp <='힣' then
            set result = concat(result, 'ㅎ');
        else
            set result = concat(result,temp);
        end if;

        set i = i+1;
    end while;
    
    return result;
END $$
DElIMITER ;

 

 

단어가 들어오면 단어 길이만큼 while 루프를 돌면서, 한 글자씩 초성을 변수에 concat 하는 간단한 함수다.

중요한 점은 한번 함수로 만들어 놓으면 다음에 다시 쓸 수 있다는 점이다.

view를 구성해 보자.

create view vw_stockInfo
as
select Code, Name, syl(Name) as sylName
from stockInfo;

 

 

원하는 대로 한글 초성이 추출된 것을 확인할 수 있었다.

다시 한번 검색창에 들어오는 경우를 확인해 보자면

1. 종목코드의 일부
2. 종목명의 일부
3. 종목명의 초성일부
4. 종목명 일부 + 종목명 나머지의 초성

으로 총 4가지다.

종목코드의 일부가 들어오는 첫 번째의 경우,

select * from vw_stockInfo where Code like {검색된코드}%

 

종목명의 일부가 들어오는 두 번째의 경우

select * from vw_stockInfo where Name like {검색된종목명}%

 

종목명의 초성일부가 들어오는 세 번째의 경우

select * from vw_stockInfo where sylName like {검색된종목초성}%

 

종목명 일부 + 종목명 나머지의 초성 가 들어오는 마지막의 경우

select * from vw_stockInfo where Name like {종목명일부}% and sylName like {종목초성일부}%

 

위와 같이 쿼리를 날려서 데이터를 조회할 수 있게 된다.

 

간단하게 springboot로 웹으로 띄워서 확인해 보자면 (mysql, mybatis 이용!)

<!-- sqlMapper-stockInfo.xml -->

<select id="selectByCode" parameterType="map" resultType="com.example.demosearch.entity.StockInfo">
      SELECT Code, Name, sylName
      FROM vw_stockInfo
      WHERE Code like #{Code}
      order by Name
      ;
  </select>

  <select id="selectByName" parameterType="map" resultType="com.example.demosearch.entity.StockInfo">
      SELECT Code, Name, sylName
      FROM vw_stockInfo
      WHERE Name like #{Name}
      and sylName like #{sylName}
      order by Name
      ;
  </select>

조회 sql은 종목 코드로 검색하는 경우와 종목명(종목명 혹은 초성)으로 검색하는 경우 두 가지로 구성했다.

 

조회해 온 mysql row는 entity로 구성했다.

//stockInfo.java

@Data
@Getter
@ToString
public class StockInfo {
    private String Code;
    private String Name;
    private String sylName;

    public JSONObject toJSON() {
        JSONObject json = new JSONObject();
        json.put("Code", Code);
        json.put("Name", Name);
        json.put("sylName", sylName);
        return json;
    }
}

중요한 건 위에서 얘기한 사용자 검색 경우의 수다. 다시 보자면

1. 종목코드의 일부
2. 종목명의 일부
3. 종목명의 초성일부
4. 종목명 일부 + 종목명 나머지의 초성

이렇게 총 4가지 일 텐데 해당 경우의 수를 구분할 수 있는 service 코드가 필요해 보인다.

우선 코드와 종목명의 검색을 구분해 보자.

//StockInfoService.java

public JSONObject getStockNames(String searchText){

	...
	boolean check_isCode = false;
        for(char ch : searchText.toCharArray()){
          if(ch >= '0' && ch <= '9') check_isCode = true;
          else {
              check_isCode = false;
              break;
          }
        }
	...
}

검색단어의 모든 단어가 0~9 숫자라면 코드라고 볼 수 있고, 해당 조건을 체크할 수 있게끔 boolean 값을 둔다.

코드라면 코드로 검색 (selectByCode), 일부분이 검색되더라도 검색이 되게끔 ‘%’를 붙여준다.

//StockInfoService.java

public JSONObject getStockNames(String searchText){

	...
	List<StockInfo> stockInfoList = new ArrayList<>();
          Map<String, Object> requestMap = new HashMap<String, Object>();
          if(check_isCode) {
              requestMap.put("Code", searchText+"%");
              stockInfoList = stockInfoMapper.selectByCode(requestMap);
          }
	...
}

코드가 아니라면?

1. 종목명 일부
2. 종목명 초성 일부
3. 종목명 일부 + 종목명 초성 일부

이렇게 3가지 경우로 들어올 수 있다.

검색단어를 순회하면서 초성이 등장한 부분이 있는지, 있다면 종목명일부는 몇 글자 인지 체크해 쿼리를 구성한다.

삼성전자를 예를 들어보면

사용자는 ‘삼성ㅈ’까지 입력했을 때 service코드는 ‘삼성%’ + ‘_ _ ㅈ%’로 쪼개주는 거다!

//StockInfoService.java

public JSONObject getStockNames(String searchText){

	...
    List<StockInfo> stockInfoList = new ArrayList<>();
      Map<String, Object> requestMap = new HashMap<String, Object>();
      if(check_isCode) {
          ...
      }else{
          String Name = searchText;
          String sylName = "";

          char[] syls = searchText.toCharArray();

          for(int i = 0; i< syls.length; i++) {
              if(syls[i] >='ㄱ' && syls[i] <= 'ㅎ'){ // 초성이 등장한다면
                  Name = searchText.substring(0, i);
                  for(int j = 0; j < i; j++) sylName += "_";
                  sylName += searchText.substring(i, searchText.length());
                  break;
              }
          }

          requestMap.put("Name", Name+"%");
          requestMap.put("sylName", sylName+"%");
          stockInfoList = stockInfoMapper.selectByName(requestMap);
      }
	...
}

 

 

결과를 확인해 보자.

웹 화면상에 내가 원하는 대로 검색 후보가 등장했고!

 

서버 console 상에도 쿼리가 제대로 돌아간 걸 확인할 수 있었다.

 

 

끝!

 

ps. 단순히 search input의 변할 때마다 쿼리를 날리게 하면 서버에 큰 부담을 줄 수 있으니… debounce나 throttle을 이용해야 한다..

검색창 debounce 처리

검색창 debounce 처리