개발 흔적 남기기

특정 문자열에 데이터가 ','를 기준으로 들어가고 개수는 3개까지 제한되어 들어가지만 1개가 들어갈지 2개가 들어갈지 비어있을지 모르는 상황에서 해당 데이터를 쪼개고 JOIN을 하던지 SubQuery를 날리던지 해야하는 상황이였다.

쉽게 설명하자면 위 그림과 같은 상황이였다.

각각 컬럼별로 쪼개어 넣으면 좋았겠지만, 기존 프로젝트 구조 설계도 이런 기능이 지원되지 않는 상황이였고 어거지로 집어넣었기때문에 개수는 3개제한인점을 활용하여 각각 특수문자를 비교하고 서브쿼리를 통해 처리하기로 하였다.

처리한 방법

1. 먼저 구분자값인 ','의 개수가 몇개인지 파악할 필요가 있었다.

SELECT 
    SUM((CHAR_LENGTH(column)-CHAR_LENGTH(REPLACE(column,',',''))+1))
FROM TABLENAME

해당 쿼리를 사용하면 특정 데이터의 특수문자나 문자열등의 개수를 체크할수 있다.

사용예시)

2. 다음 해당 개수에 따른 CASE WHEN문을 통해 비교후 SPLIT 하였다.

각각 SPLIT된 데이터의 이름값을 concat_ws메소드를 통해 문자열을 다시 합쳐주었다.

SELECT
  CASE 
      SUM((CHAR_LENGTH(column)-CHAR_LENGTH(REPLACE(column,',',''))+1))

  #1개라면 특정테이블에서 바로 조회해온다.
  WHEN 1 THEN (SELECT columnName FROM R_FILEUPLOAD    WHERE no = column)

  #2개라면 ,기준으로 1개만 자른값을 비교하여 넣고
  #        2번째까지 가져오고 문자열을 합치다.
  WHEN 2 THEN (SELECT concat_ws(
      ','
      ,(select columnName from R_FILEUPLOAD 
      where no = SUBSTRING_INDEX(column, ',', 1))
      ,(select columnName from R_FILEUPLOAD
      where no = SUBSTRING_INDEX(SUBSTRING_INDEX(column, ',', 2), ',', -1))
  ))

  #3개라면 ,기준으로 1개만 자른값을 비교하여 넣고
  #        2번째까지 가져오고
  #        3번째까지 가져와서 문자열을 합친다.
  WHEN 3 THEN (SELECT concat_ws(
      ','
      ,(select columnName from R_FILEUPLOAD 
      where no = SUBSTRING_INDEX(column, ',', 1))
      ,(select columnName from R_FILEUPLOAD 
      where no = SUBSTRING_INDEX(SUBSTRING_INDEX(column, ',', 2), ',', -1))
      ,(select columnName from R_FILEUPLOAD 
      where no = SUBSTRING_INDEX(SUBSTRING_INDEX(column, ',', 3), ',', -1))
  ))
  END as REAL_NAME;

결과 데이터는 '테스트1,테스트2,테스트3' 의 형태로 처리되었다.

어쩔수 없는 상황에 임시대처로 해당방법을 사용하였고... 어지간하면 컬럼별로 쪼개어서 조인을 하는게 맞는것 같다...

저런형태로되면 나중에 볼때 기억도 안날것같고... 유지보수도 어려울것 같... ㅜㅠ

Mysql를 사용하다 보면 단일행에 대하여 참고하는 테이블의 데이터가 다중행일 경우가 있습니다.

이러한 경우 join문을 통해 데이터를 조회하게 되면 아래와 같이 다중행으로 출력됩니다.

다중 데이터를 group_concat 메소드를 활용하여 조회 결과를 단일행으로 변경하는 방법을 알아보겠습니다.

GROUP_CONCAT

select
    group_concat(column)
from table;

단순하게 다중에서 단일로 묶고자 하는 컬럼을 group_concat 메소드 파라미터로 처리하면 됩니다.

아래는 사용 예제입니다.

group_concat 사용 예제

아래와 같은 2개의 테이블 있다고 가정하겠습니다.

board테이블은 게시판을 나타내는 테이블이며, file_info는 board테이블에 대한 파일 정보를 담고 있는 테이블입니다.

file_info테이블의 board_idx는 board테이블의 idx값을 참조합니다.

select
    a.*,
    group_concat(b.file_idx, b.file_name SEPARATOR '|')    
from(
    select
        *
    from board
    limit 0, 10 #paging처리
) a
left outer join file_info b
on a.idx = b.board_idx
group by a.idx;

board 테이블에서 10개의 데이터를 먼저 가져오고, 해당 데이터를 a라고 별칭을 처리하고 file_info테이블의 참조컬럼을 join합니다.

group_concat을 통해 각각 파일에 대한 idx값인 file_idx컬럼과 file_name컬럼을 붙여서 출력합니다.

SEPARATOR 키워드는 이름 그대로 구분자를 지정할 수 있습니다. 부득이한 상황으로 쉼표 구분자를 사용할 수 없다면 해당 키워드를 추가하여 구분값을 변경해주세요. SEPARATOR 키워드가 없으면 기본값인 쉼표로 구분처리 됩니다.

조회 결과

프로젝트 진행도중 특정 테이블에 특정 구분자값으로 구분된 문자열을 통으로 넣어줄테니 알아서 파싱하여 쓰세요같은 불친절한 일이 발생했습니다.

처음에 처리방법으로 생각한건 java에서 일정시간마다 스케줄러가 데이터를 수집하고 해당데이터들을 구문별로 나누고 테이블에 insert해주는 방법을 생각했지만, 데이터가 꼬일 우려가 있어 Flag처리를 통한 방법... 또는 스케줄러 동작이 끝나기전에 또 스케줄러가 돌아야 하는 경우가 발생하거나 등등... 여러 문제가 예상되었습니다....

아이디어 회의를 통해 해결 방법으로 제시된게 문자열을 넣어주면 바로 mysql trigger를 사용해보자였고, 예전에 pg에서 사용해본 기억이 있어서 mysql에서 진행해보았습니다.

총에서 방아쇠를 당기면 총알이 날라가듯 여기서 trigger란 특정 테이블에 어떤 행동 이벤트가 발생하면 발동하는 것을 말합니다.

여기서 이벤트로는 특정 테이블에 INSERT, UPDATE, DELETE가 일어나면 발동 시킬 수 있고, 실행되는 순서도 지정할 수 있습니다. INSERT행위가 일어나기 전에 처리하려면 BEFORE INSERT행위가 일어나고 처리하려면 AFTER 키워드를 사용합니다.

바로 생성방법 사용법을 알아보겠습니다.  급하신분들은 맨 아래로 내려가면 최종 트리거를 볼 수 있습니다.

Trigger 생성하기

DROP TRIGGER IF EXISTS parserTrigger;
delimiter $$
	CREATE TRIGGER parserTrigger
	AFTER INSERT ON input_table
	FOR EACH ROW
	BEGIN
		INSERT INTO output_table
	SET
		sender = NEW.data,
		timestamp = now();
	END
$$delimiter;

이미 같은 이름의 트리거가 존재할경우 지우기 위해 DROP TRIGGER를 사용하였고

CREATE TRIGGER를 통해 트리거의 이름을 parserTrigger라고 만들었습니다.

delimiter라는 키워드도 보이실텐데 생소한분들도 많을 것 같습니다. 제가 지금 생소하거든요.

delimiter는 트리거의 구문을 시작하고 끝내는 키워드라고 합니다. 즉 트리거들의 구분을 위한 키워드로 생각하시면 됩니다.

input_table 테이블에 INSERT동작이 발생하면 data컬럼의 값을 가져와  output_table에 넣어주는 INSERT -> INSERT 트리거를 생성했습니다.

실제 동작 테스트

정상적으로 데이터가 인입된 것을 볼 수 있습니다.

응용편 - 특정 문자열을 구분값으로 나누어 집어넣기

이번에는 기본 생성방식에서 응용하여 어떤 문자열이 들어오고 ';'구분값으로 나누어 들어온 데이터를 행으로 입력하는 트리거를 작성해보겠습니다.

작업 준비를 위해 테이블을 생성합니다.

input_table

먼저 insert가 들어올 테이블입니다

CREATE TABLE IF NOT EXISTS `input_table` (
  `idx` int(11) NOT NULL AUTO_INCREMENT,
  `data` varchar(6500) NOT NULL DEFAULT '0',
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

output_table

input_table에 insert가 일어나면 trigger를 통해 받을 테이블입니다.

CREATE TABLE IF NOT EXISTS `output_table` (
  `idx` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(50) NOT NULL,
  `value` varchar(50) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

idx: pk용 sequence값 입니다.

type : key값이 들어갈 컬럼입니다. ex) type1, type2, type3...

value : key에 해당하는 값이 들어갈 컬럼입니다. ex) 5, 7, 29

timestamp: trigger 동작시간인 now()를 넣을 컬럼입니다.

이제 트리거를 만듭니다.

문자열를 잘라서 넣어주는 Trigger

DROP TRIGGER IF EXISTS parserTrigger;
delimiter $$
	CREATE TRIGGER parserTrigger
	AFTER INSERT ON input_table
	FOR EACH ROW		
	BEGIN
		DECLARE type1 varchar(100);
		DECLARE type2 varchar(100);
		DECLARE type3 varchar(100);

		SET type1 = substring_index(substring_index(NEW.data, ';', 1), ':', -1);
		SET type2 = substring_index(substring_index(NEW.data, ';', 2), ':', -1);
		SET type3 = substring_index(substring_index(NEW.data, ';', 3), ':', -1);
			
		INSERT INTO output_table (type, value, timestamp) values ('type1', type1, now());
		INSERT INTO output_table (type, value, timestamp) values ('type2', type2, now());
		INSERT INTO output_table (type, value, timestamp) values ('type3', type3, now());
	END;
$$delimiter;

이번엔 DECLARE라는 키워드가 추가되었습니다.

DECLARE는 트리거의 변수를 생성할때 사용하는 키워드로 변수명 자료형(사이즈) 형태로 입력해주면 됩니다.

예시로 들어올 데이터는 'type1:20;type2:5;type3:15' 형태라고 가정하겠습니다.

;으로 구분되어 있고 ':'의 앞글자는 key, 뒷글자는 value값입니다.

input_table의 data컬럼에 예시데이터('type1:20;type2:5;type3:15' )가 들어오면 substring_indxe를 통해 생성한 변수 type1, 2, 3에 각각 잘라서 값을 넣어주고 값이 비어있지 않다면 각각 INSERT를 처리하는 트리거입니다.

동작을 확인해보겠습니다.

type1에 20

type2에 5

type3에 15

한줄로 들어가 있던 데이터가 정상적으로 들어간 모습을 볼 수 있습니다.

위에서 한 트리거도 동작은 하지만 문제가 있습니다 3개의 데이터가 무조건 있어야 정상동작을 한다는 점입니다.

'type2:5;type3:15' 이것처럼 type1이 안들어오면 버그가 발생합니다.

key값도 받고 길이도 체크할 필요가 있습니다.

최종 완성 - 값이 없으면 넣지 않도록 조건문 추가하기

입력값을 확인하여 동적으로 INSERT하는 TRIGGER

DROP TRIGGER IF EXISTS parserTrigger;
delimiter $$
	CREATE TRIGGER parserTrigger
	AFTER INSERT ON input_table
	FOR EACH ROW		
	BEGIN
		DECLARE dataStr varchar(1000);
		DECLARE splitLen INT default 0;
		DECLARE type1 varchar(100);
		DECLARE type2 varchar(100);
		DECLARE type3 varchar(100);
		DECLARE typeValue1 varchar(100);
		DECLARE typeValue2 varchar(100);
		DECLARE typeValue3 varchar(100);
		
		SET dataStr = NEW.data;
		SET splitLen = ROUND((CHAR_LENGTH(dataStr) - CHAR_LENGTH(REPLACE(dataStr, ';', '')) / CHAR_LENGTH(';')));
		SET type1 = substring_index(substring_index(substring_index(dataStr, ';', 1), ';', -1), ':', 1);
		SET type2 = substring_index(substring_index(substring_index(dataStr, ';', 2), ';', -1), ':', 1);
		SET type3 = substring_index(substring_index(substring_index(dataStr, ';', 3), ';', -1), ':', 1);
		SET typeValue1 = substring_index(substring_index(dataStr, ';', 1), ':', -1);
		SET typeValue2 = substring_index(substring_index(dataStr, ';', 2), ':', -1);
		SET typeValue3 = substring_index(substring_index(dataStr, ';', 3), ':', -1);
		
			
		IF type1 is NOT NULL
			THEN BEGIN
				INSERT INTO output_table (type, value, timestamp) values (type1, typeValue1, now());
			END; END IF;
			
		IF splitLen > 0 and type2 is NOT NULL
			THEN BEGIN
				INSERT INTO output_table (type, value, timestamp) values (type2, typeValue2, now());
			END; END IF;
			
		IF splitLen > 1 and type3 is NOT NULL
			THEN BEGIN
				INSERT INTO output_table (type, value, timestamp) values (type3, typeValue3, now());
			END; END IF;
	END;
$$delimiter;

추가된 것으로는 아래와 같습니다.

dataStr은 입력이 들어온 data컬럼의 값

splitLen은 ';' 특수문자의 개수

type의 키

typeValue는 값

별개로 받는 변수를 처리하여 IF문에 따라 NULL체크와 길이를 체크하여 INSERT를 제어합니다.

동작 확인!

최근 특정 코드별로 쌓이는 테이블을 일정시간마다 호출해서 마지막값을 가져와야 할 일이 생겼다.

여러가지 찾아본 결과는 다음과 같다.

1. order by desc를 통해 select를 해온다.

2. select된 데이터를 한번 더 group by를 한다.

막상 적용해보면 내가 쌓고 있던 테이블의 데이터에서는 group by 후 마지막 날짜의 데이터가 나오지 않았다.

아래와 같은 데이터가 있다고 가정하겠다.

항상 code컬럼의 AAA, BBB, CCC, DDD의 데이터들의 마지막 날짜 데이터들을 가져오는 쿼리를 작성해보겠다.

code컬럼에는 n개가 존재하며 언제나 증가할 수 있다고 가정하고 쿼리를 작성하였다.

아래는 조회에 성공한 쿼리이다.

select
	*
from(
	select
		*
	from tb_test
	where (code, date_time) in (
		select code, max(date_time) as date_time
		from tb_test group by code
	)
	order by date_time desc
) t
group by t.code

먼저 뽑아낼 테이블을 max함수를 통해 마지막 날짜 조회 후 group by를 하고 해당하는 데이터를 조건문에 처리(where 조건문 in절부분)하여 최신 데이터 순으로 order by를 통해 다시 한번 정렬을 한다.

사실 여기까지만 처리해도 정상적으로 볼 수 있지만, 예시데이터처럼 중복된 시:분:초로 들어온 경우 마지막 한개의 데이터가 보장이 되질 않아 마지막에 group by를 한번 더 처리하였다.

아래는 마지막 group by를 안할 경우 발생하는 중복 데이터 노출이다.

처음에 사실 쉽게 처리가 되질 않아 n개의 code값을 group by하여 가져온 후 그 코드값을 where절에 넣고 select를 n번 요청하였는데, 그 방법보단 위 방법이 깔끔한 것 같다.

mysql에서 시간 날짜등을 표현할때 원하는 형태로 표기하기 위해 date_format 함수를 많이 사용하는데,

매번 포맷형식이 자바와 다르고 대소문자에 따라 다른 결과가 나오고 헷갈려 포스팅을 진행합니다.

바로 사용법에 대해 알아보겠습니다.

- 함수 사용법

DATE_FORMAT(시간값, 원하는 포맷);

첫번째 파라미터에는 원하는 컬럼, 데이터를 넣고 두번째 파라미터값에는 원하는 출력 형태의 포맷 문자열을 넣습니다.

사용하는 포맷의 대,소문자를 유의하면서 사용해야 합니다. 표기되는 결과가 달라질 수 있습니다.

바로 사용예시를 보겠습니다.

now()

select now()

바로 now() 함수를 사용하면 현재 시스템의 시간을 출력해줍니다.

그럼 이걸 원하는 형태로 바꿔서 날짜만 표기해보겠습니다.

날짜만 표기하기

select date_format(now(), '%Y-%m-%d')

날짜 + 시간 표기하기

select date_format(now(), '%Y.%m.%d %H:%i:%s')

기본형태와 차이를 주기 위해 날짜 구분자를 .으로 바꿔봤습니다.

대소문자를 항상 주의하여 사용해야 합니다. 잘못된 표기법으로 나올 수 있습니다.

문자열 날짜 변경

select date_format('2020/02/06 15:16:50', '%Y.%m.%d %H:%i:%s')

'2020/02/06 15:16:50'으로 입력된 문자열이 '2020.02.06 03:16:50'으로 정상적으로 변경되어 파싱된 걸 볼 수 있습니다.

좀 더 자세하게 변경 양식을 보고 싶다면 아래 링크를 참고해주세요.

https://www.w3schools.com/sql/func_mysql_date_format.asp

mysql 사용 도중 code의 값이 꼬여서 다른 테이블에 존재하는 code값을 읽어와

pk값이 같은 데이터끼리 update를 해줄 일이 생겼다.

일반적인 조건문에 따른 update처리는 해봤지만 join문이 들어간 update를 찾아보니

set전에 일반적인 select의 join문을 사용하면 되는 것을 확인하였다.

Join문을 활용한 Update

update 
	table1 t1
	[INNER JOIN | LEFT OUTER JOIN] table2 t2
	on t1.joinColumn = t2.joinColumn
set
	t1.changeColumn = t2.changeColumn
where
	t1.의 조건문
	and t2.의 조건문

위 와 같은 문법을 활용하여 update를 처리하면 된다.

사용예시)

r_board와 r_board_detail 테이블이 있다고 가정하겠다.

r_board

r_board_detail

r_board code값을 일치시키는 update문을 구성하겠다.

update 
	r_board b1
    	INNER JOIN r_board_detail b2
	on b1.d_no = b2.d_no
set
	b1.d_code = b2.d_code

처리 후에는 r_board의 code값이 r_board_detail 테이블과 동일하게 바뀐 모습을 볼 수 있다.

Mysql DB를 사용하면서 스케줄러를 통해 특정 테이블에서 데이터를 긁어와 insert를 해주는 로직을 작성하였다.

문제는 insert 해주었던 바라보는 기존 테이블이 수정이 일어나면 수정날짜부분을 확인하여 똑같이 수정을 해줘야 하는데, 그때마다 PK값을 가져와서 전부 DELETE하고 다시 INSERT하고 이렇게 자바단에서 처리를 해야하나 골치아픈 상황을 경험하였는데 검색을 통해 INSERT를하면서 PK값이 존재하면 UPDATE를 해주는 ON DUPLICATE KEY UPDATE를 찾았다.

해당 방식은 INSERT를 하다가 PK값이 존재하면 UPDATE를 한다는 조건이 있는데 이 조건을 만족하기 위해 테이블에 무조건 PK값이 지정이 되어있어야 정상적으로 동작한다.

Mysql 쿼리 사용법

INSERT INTO TABLENAME
(
	PKColumn,
	Column1,					
	Column2,
	Column3,
	Column4,
	Column5,
	...
)
VALUES
(
	pkData,
	Data1,
	Data2,
	Data3,
	Data4,
	Data5,
	...
)
ON DUPLICATE KEY UPDATE
	Column1 = Data1,
	Column2 = Dat2,
	Column3 = Data3,
	Column5 = Data5

특정 테이블에 Insert하는부분은 동일하다

values 처리하는부분이 끝나고 ON DUPLICATE KEY UPDATE

처리후 넣고자 하는 컬럼에 데이터값을 입력해주면 된다.

해당 예시 쿼리는 Column1~5까지 insert를 하지만 pk값이 겹칠경우 column4는 제외하고 업데이트가 일어난다.

Mybatis에서 forEach내 사용법

INSERT INTO TABLENAME
(
	PKColumn,
	Column1,					
	Column2,
	Column3,
	Column4,
	Column5,
	...
)
VALUES
<if test="list != null and list.size > 0">
	<foreach collection="list" item="item" index="index" separator=" , ">
	(
		#{item.pkData},
		#{item.Data1},
		#{item.Data2},
		#{item.Data3},
		#{item.Data4},
		#{item.Data5},
		...
	)
	</foreach>
	ON DUPLICATE KEY UPDATE
		Column1 = values(Column1),
		Column2 = values(Column2),
		Column3 = values(Column3),
		Column5 = values(Column5)
</if>

mybatis에서 insert처리시 여러개를 처리할때 파라미터로 LIST 데이터를 담아서 넘기고 해당 값을 쪼개서 동시에 처리하는 경우가 있는데 이때도 동일하게 사용할 수 있다.

UPDATE구문에선 values(컬럼명)으로 변경해줘야 정상적으로 UPDATE가 동작한다.

종종 데이터베이스 서버를 확인하다보면 어디서 접근중인지 확인하거나

정상적으로 접근이 종료되었는지 등등 확인해야하는 경우가 있는데 이럴때는 아래의 쿼리를 날리면

접근중인 세션의 정보들이 나온다.

접근중인 database명, IP정보 상태 등등

SHOW PROCESSLIST;