1. mysql-server 설치

>sudo apt-get update  //apt-get 업데이트

>sudo apt-get install mysql-server  //mysql-server 설치

 

2. /etc/mysql/ 밑의 conf 에서

binding ip 0.0.0.1 로 수정하여 외부에서도 접속 가능토록 수정

 

3. 실행 및 종료

>sudo /etc/init.d/mysql restart //재시작

>sudo systemctl start mysql //시작

>sudo systemctl enable mysql //서버 재시작시 mysql 자동 시작되도록 등록)

>sudo systemctl stop mysql

 

4. 유저 관리 및 DB 권한 주기

3-1) 데이터베이스 생성

>mysql -u root -p //root 로 접속

>CREATE DATABASE bootmybatis; //bootmybatis 이름으로 database 생성

>SHOW DATABASES; //databases 목록 조회

※ 유저 삭제

>DROP DATABASE dbname;

 

3-2) 유저 생성 및 접근 허용 IP 지정

>CREATE USER 'developyo'@'%' IDENTIFIED BY 'password';

//암호를 'password'로 하는 developyo 계정을 생성, 해당 사용자는 어떤 ip 에서도('%') 접속이 가능

>ALTER USER ''root'@'localhost' IDENTIFIED BY 'password';

//암호를 'password'로 하는 root 계정을 수정, 해당 사용자는 localhost에서만 접속이 가능

>FLUSH PRIVILEGES;  //commit

>SELECT user, host, password FROM mysql.user; //유저, 접근허용ip, 암호 확인

※ 유저 삭제

>DROP USER 'id'@'host';

 

3-3) 데이터베이스에 권한 부여

>GRANT ALL PRIVILEGES ON bootmybatis.* to developyo@'%';

>FLUSH PRIVILEGES;  //commit

>SHOW GRANTS FOR 'developyo'@'%'; //developyo 갖고있는 권한 확인

 

 

[호스트(window)에서 vm 게스트(우분투) mysql 에 접속하기]

1) NAT 설정 브릿지로 바꾸기

설정(settings) > 네트워크(network) > 다음에 연결됨 : 어댑터에 브리지 (attached to: Bridged Adapter) 로 변경

2) 터미널에서 ip 확인

>ifconfig 

3) mysql client 에서 위의 ip로 연결

 

참고 :

https://dejavuqa.tistory.com/317

https://bugwhale.com/virtualbox-bridged-adapter-error/

 

반응형

본 포스팅은 '기억보단 기록'을 이라는 타이틀로 유명한 'jojoldu' 님의 인덱스관련 포스팅을 공부차원에서 단순 요약한 포스팅에 불과하므로,

보다 정확한 정보 습득을 원하시는 분들은 필히 아래 링크로 이동 하시길 바랍니다.

https://jojoldu.tistory.com/243

 

[인덱스 기준]

1. 카디널리티가 높은 --> 낮은 칼럼 순서로 인덱스를 잡아야 좋다.

카디널리티가 높다 : 중복수치가 낮다 ex) 주민등록번호

카디널리티가 낮다 : 중복수치가 높다 ex) 이름

 

2. 인덱스를 잡을 때 키의 길이는 짧을 수록 좋다.

InnoDB(MySQL)에서 디스크에 데이터를 저장하는 가장 기본단위를 페이지라 하며, 인덱스 역시 페이지 단위로 관리된다.

인덱스가 저장되는 페이지는 16KB 크기로 고정되어 있다.

키의 크기가 길 수록 페이지에 저장되는 인덱스의 양은 줄어들고,

페이지에 저장된 인덱스의 양이 줄어들 수록, 조회시 더 많은 페이지를 조회해야 한다.

 

 

[인덱스 사용한 조회]

1. 복수개의 칼럼으로 인덱스를 구성했을 때, 조건절 주의사항

인덱스를 회원그룹(group), 나이(age), 성별(gender)  과 같이 잡았을 때,

아래의 쿼리는 인덱스(age)를 타지 않음

select *
from user
where age = 18

반면, 아래의 쿼리는 인덱스(group)를 탄다.

select *
from user
where group='premium'

: 첫번째 인덱스 칼럼은 조회조건에 포함되어야만 한다.

 

2. 그 외의 주의사항

1) between, like, <, > 등의 범위 조건에 사용된 칼럼은 인덱스를 타지만, 그 뒤의 인덱스 칼럼들은 인덱스로 사용되지 않음.

아래 쿼리에서 gender는 인덱스로 사용되지 않는다.

* LIKE 의 경우 'str%' 좌측 일치 패턴만 인덱스가 사용된다.

select *
from user
where group = 'premium'
and age > 18
and gender = 'M'

2) =, IN 조건으로 사용된 칼럼 뒤의 인덱스 칼럼들은 인덱스로 사용된다.

(IN은 =을 여러번 실행 시킨 것이므로)

단, IN 조건 안에 서브쿼리를 넣게 되면 성능상 이슈가 발생. (서브쿼리 외부 먼저 실행 후 IN 구절은 체크조건으로 실행되므로)

 

3) AND 연산자는 ROW 수를 줄이는 역할을 하지만 OR 연산자는 비교해야할 ROW가 더 늘어나므로 풀 테이블 스캔이 발생할 확률이 높음. 

 

4) 인덱스로 사용된 컬럼은 컬럼값 그대로 사용해야 인덱스가 사용된다.

select *
from user
where group = 'premium'
and age+1 = 18

위 쿼리는 인덱스를 타지 못한다.

위 경우, age = 18-1 과 같이 수정해야 한다.

칼럼이 문자열인데 숫자로 조회시 타입이 달라 인덱스가 사용되지 않는다.

 

5) null의 경우 where ? is null 로 인덱스 사용이 가능

일반적인 DBMS 와 달리 Mysql 에선 null 값도 인덱스로 관리

 

 

[인덱스 사용한 조회시 조회 칼럼 순서]

조회조건에 포함되어 있는지가 중요할 뿐, 반드시 인덱스 순서와 조회 칼럼 순서를 지킬 필요는 없다.

(옵티마이저가 조회 칼럼 순서를 인덱스 칼럼 순서에 맞춰 재배열 하는 과정이 추가되긴한다)

 

반응형

[ON vs WHERE]

ON : JOIN 을 하기 전 필터링을 한다 (=ON 조건으로 필터링이 된 레코들간 JOIN이 이뤄진다)

WHERE : JOIN 을 한 후 필터링을 한다 (=JOIN을 한 결과에서 WHERE 조건절로 필터링이 이뤄진다)

 

[INNER JOIN 에서의 ON vs WHERE]

1. INNER JOIN + ON 조건절 + ON 조건절

1
2
3
4
5
SELECT *
FROM a
INNER JOIN b
ON a.key = b.key
AND a.key2 = b.key2
cs

2. EQUI JOIN + WHERE 조건절

1
2
3
4
SELECT *
FROM a AS a , b AS b
WHERE a.key = b.key
AND a.key2 = b.key2
cs

3. INNER JOIN + ON 조건절 + WHERE 조건절

1
2
3
4
5
SELECT *
FROM a
INNER JOIN b
ON a.key = b.key
WHERE a.key2 = b.key2
cs

위 세 쿼리는 모두 같은 결과, 같은 퍼포먼스가 나온다.

즉, inner join 에서의 on 조건과 where 조건은 같다.

https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause

 

[그래도 EQUI JOIN 보단 INNER JOIN 을 써야 하는 이유]

1. 가독성

2. OUTER JOIN 으로 수정이 용이

 

[OUTER JOIN 에서의 ON vs WHERE]

1. OUTER JOIN 정리 (출처)

 

2. OUTER JOIN 에서의 ON vs WHERE

1) OUTER JOIN test 를 위한 샘플 준비.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 샘플 테이블 생성
create table table1 (
  col1 INT,
  col2 VARCHAR(10)
);
create table table2 (
  col1 INT,
  col2 VARCHAR(10)
);
commit;
 
-- 샘플 데이터 입력
insert into table1 (col1, col2) values (1'하나');
insert into table1 (col1, col2) values (2'둘');
insert into table1 (col1, col2) values (3'셋');
 
insert into table2 (col1, col2) values (1'일');
insert into table2 (col1, col2) values (2'이');
commit;
cs

2) 데이터 확인

table1 :

col1 col2
1 하나
2
3

table2 :

col1 col2
1
2

 

3) OUTER JOIN 에서의 ON

1
2
3
4
5
SELECT t1.col1, t1.col2, t2.col1, t2.col2
FROM   table1 t1
LEFT OUTER JOIN table2 t2
ON t1.col1 = t2.col1
AND t2.col2 = '일';
cs

위 쿼리의 결과 :

t1.col1 t1.col2 t2.col1 t2.col2
1 하나 1
2 null null
3 null null

>> table1의 전체 row와 

     table2에서 col2 칼럼 값이 '일'인 로우만 뽑은 row 들을 OUTER JOIN.

>> on 조건은 join 을 하기 전에 필터링 되는 기준

 

4) OUTER JOIN 에서의 WHERE (위 쿼리의 ON 조건을 WHERE 에 그대로 추가)

1
2
3
4
5
select t1.col1, t1.col2, t2.col1, t2.col2
from  table1 t1
LEFT OUTER JOIN table2 t2
ON t1.col1 = t2.col1
where t2.col2 = '일';
cs

위 쿼리의 결과

t1.col1 t1.col2 t2.col1 t2.col2
1 하나 1

>> table1의 전체 row와

     table2 전체 row 를 OUTER JOIN 한 후,

     col2 칼럼 값이 '일'인 로우만 뽑는다.

>> WHERE 조건은 JOIN을 한 이후에 필터링하는 기준

 

반응형

SYSDATE() : SYSDATE() 함수가 호출된 시각

NOW() : NOW() 가 포함된 쿼리(구문(statement))가 호출된 시각

 

[Ex]

1
2
3
4
5
6
7
SELECT
        SYSDATE(), 
        SLEEP(3) AS 'sleep'
        SYSDATE(),
        NOW(), 
        SLEEP(3) AS 'sleep'
        NOW();
cs

 

[결과]

 

[쿼리 한 줄씩 해석]

SELECT

SYSDATE() 함수 호출될 때의 시각 (6:52:28)

SLEEP(3)    3초간 Thread sleep (6:52:31)

SYSDATE() 함수 호출될 때의 시각 (6:52:31)

NOW()       쿼리(구문)가 실행될 때의 시각(6:52:28)

SLEEP(3)    3초간 Thread sleep (6:52:34)

NOW()      쿼리(구문)가 실행될 때의 시각(6:52:28)

 

※ SYSDATE와 NOW 는 각각 언제 사용해야 하는가?

- 실행결과에 따른 시각이 중요, 이를 저장해야하는 경우 NOW 사용

- 호출된 시각이 중요한 경우 SYSDATE 사용

ex) Spring Batch/Spring Quartz의 경우 Schedule, Job의 실행시각들이 메타테이블에 의해 관리가 되는데(Schedule, Job 관리가 inMemory 방식이 아닌 jdbc 방식인 경우), 쿼리가 끝난 시각이 아닌 호출시점의 시각이 중요하므로 SYSDATE를 사용하여 관리되고 있다.

 

참고 : https://database.guide/sysdate-vs-now-in-mysql-whats-the-difference/

반응형

Mysql hierarchy sql 계층구조 쿼리 : 설계 및 쿼리작성(Multi selectbox)

 

고객들이 열람할 가이드 정보가 있고,

가이드 정보의 카테고리를 N depth 로 분류(멀티 셀렉트박스 사용)하여 조회할 수 있게 하여 다음과 같은 화면을 만들어 보자.

<1: 테이블 설계>

1. '전체' 옵션의 유무

case 1 멀티 셀렉트박스에 '전체'라는 옵션이 없는 경우 :

마지막 셀렉트박스의 value(node_id)를 기준으로 가이드를 조회

case 2 멀티 셀렉트박스에 '전체'라는 옵션이 존재하는 경우 :

'전체'가 선택된 마지막 셀렉트박스 직전의 value(node_id)를 기준으로 모든 자손 node_id를 찾아 조회해야한다.

 

예를 들어,

case 1 ex) 고객지원 > 고객지원sub > 고객지원sub의sub 와 같이 마지막 select box에 특정 값이 선택된 경우 :

고객지원sub의sub 의 value(node_id) 를 기준으로 가이드 조회

case 2 ex) 고객지원 > 고객지원sub > 전체 와 같이 마지막 select box가 '전체'로 선택된 경우 :

고객지원sub을 부모 노드로 삼는 모든 자손 노드를 기준으로 가이드 조회

 

2. 동적 depth 여부

case 1 : depth 가 특정 값으로 고정인 경우, 셀렉트박스용 분류 코드테이블을 따로 두고 join 을 몇번 걸면 해결이 된다

case 2 : depth 가 동적(N)인 경우, join 이 N 번 되어야 하므로 단순한 join으로 해결 할 수 없다..

 

전체 옵션이 존재하고 depth 가 동적(N)인 경우를 전재하고 설계 및 개발해보자.

 

 

<2: 테이블 생성>

1. 메인테이블 생성 : 가이드 정보 테이블 생성

가이드 정보 테이블은 제목, 가이드의 노드 아이디, 상세 내용만 지니고 있다.

1
2
3
4
5
6
7
8
CREATE TABLE `guide_info` (
  `SEQ` int(11NOT NULL AUTO_INCREMENT COMMENT '일련번호',
  `TITLE` varchar(100NOT NULL COMMENT '제목',
  `GUIDE_ID` int(11NOT NULL COMMENT '가이드 노드 아이디',
  `TEXT_ITEM_1` text COMMENT '가이드 내용',
  PRIMARY KEY (`SEQ`)
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='고객지원 가이드 게시판';
 
cs

 

2. 서브테이블 생성 : 가이드 분류 정보를 따로 지니고 있는 노드 정보 테이블 생성

1
2
3
4
5
6
7
8
9
CREATE TABLE `node_info` ( 
  `NODE_ID` int(11NOT NULL AUTO_INCREMENT COMMENT '노드아이디'
  `PNODE_ID` int(11NOT NULL COMMENT '부모노드아이디'
  `NODE_PATH` varchar(200DEFAULT NULL COMMENT '노드경로'
  `NODE_NM` varchar(20DEFAULT NULL COMMENT '노드명'
  `DEPTH` int(11DEFAULT '1' COMMENT '노드DEPTH'
  `LEAF_YN` varchar(1DEFAULT 'N' COMMENT '자식노드존재유무'
  PRIMARY KEY (`NODE_ID`
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='노드 정보'
cs

 

생성된 테이블 :

NODE_ID(노드아이디)와 PNODE_ID(부모 노드 아이디)가 서로 참조하는 관계의 테이블 구조.

 

샘플 데이터 :

 

<3: 쿼리 작성>

필요한 쿼리 종류는 크게 두가지로 나눠볼 수 있다.

1. 셀렉트박스의 구성요소인 노드정보(node_info TABLE)를 조회하는 쿼리 : 멀티셀렉트박스를 그리기 위한 데이터를 뽑는 쿼리

2. 선택된 셀렉트박스 값들을 기준으로 가이드 목록(guide_info TABLE)을 조회한 결과 : 조회 결과를 출력하기 위한 데이터를 뽑는 쿼리

 

3-1. 셀렉트 박스의 구성요소인 노드정보를 조회하는 쿼리 :

N번째(Ndepth) 셀렉트박스가 특정값으로 선택된 경우 N+1번째(N+1depth) 셀렉트박스 조회

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT  node_id
      , pnode_id
      , node_nm
      , depth
      , leaf_yn
  FROM  node_info 
 WHERE  1=1
<choose>
    <when test='node_id neq null and node_id neq ""'> 
       AND  pnode_id = #{node_id}
    </when>
    <otherwise>  
       AND  pnode_id = 1
    </otherwise>
</choose>
 
 
cs

* 8~15line : node_id 가 없는 경우, 아무것도 선택되지 않은 경우(화면에 최초 진입한 상태)이며 최고 조상이라 할 수 있는 node_id 1 을 기준으로 자식 노드를 구한다

 

3-2. 선택된 셀렉트박스 값들을 기준으로 가이드 목록을 조회한 결과

1) 마지막 셀렉트박스가 특정 값으로 선택된 경우 : 단순 join 으로 해결

2) 마지막 셀렉트박스가 '전체'로 선택된 경우 : 아래와 같이 해결

 

ORACLE 은 CONNECT BY 함수를 사용하여 트리구조의 데이터를 출력 할 수 있겠지만,

MYSQL 은 CONNECT BY 함수를 지원하지 않는다.

 

다음과 같이 특정 노드 밑에 있는 모든 자손, 특정 노드 상위에 있는 모든 조상 노드를 구할 수 있다.

 

1. 특정 노드의 자손 구하기

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT node_id
     , pnode_id
     , depth
     , leaf_yn
     , node_path
 FROM  (
        SELECT * 
          FROM node_info
      ORDER BY pnode_id, node_id
        ) node_sorted,
       (SELECT @pv := #{node_id}) initial
WHERE   FIND_IN_SET(pnode_id, @pv)
  AND   LENGTH(@pv := concat(@pv, ',', node_id))
cs

 

2. 특정 노드의 조상 구하기

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT   nid AS node_id
       , pnode_id
       , leaf_yn
       , depth
       , node_path
  FROM (
        SELECT  @nid AS nid,
              (
               SELECT  @nid := pnode_id
                 FROM  node_info
                WHERE  node_id = nid
               ) AS pnode_id
               , leaf_yn
               , depth
               , node_path
          FROM  (
                 SELECT  @nid := #{node_id}
                ) vars
                , node_info
         WHERE  @nid <> 0
        ) as tmp
cs

 

3. 특정 분류 밑의 모든 가이드 목록 조회 (특정 노드아이디 기준 모든 자손 노드를 구하는 1번 쿼리를 sub query로 사용)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT  seq, 
        title, 
        (SELECT node_path FROM node_info where node_id = gi.guide_id) as guide_path
  FROM  guide_info gi
 WHERE  1=1
   AND  gi.guide_id IN  (SELECT * FROM (
                                             SELECT   node_id
                                               FROM  (SELECT node_id, pnode_id
                                                        FROM node_info
                                                    ORDER BY pnode_id, node_id
                                                     ) node_sorted,
                                                     (select @pv := #{node_id}) initial
                                              WHERE   FIND_IN_SET(pnode_id, @pv)
                                                AND   LENGTH(@pv := concat(@pv, ',', node_id))
                            ) as temp
                            )
ORDER BY seq
cs

 

※ 위와 같이 subquery 를 사용하여 sql 에서 처리하지 않고, 소스레벨에서 처리하고 싶은 경우 foreach 를 사용하여 다음과 같이 처리할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT   gi.title, gi.guide_id, gi.text_item_1, ni.node_path AS rs
  FROM   guide_info gi
       , node_info ni
 WHERE   1=1
   AND   ni.node_id = gi.guide_id
<if test = 'sch_word neq null and sch_word neq ""'>
   AND   title LIKE CONCAT('%',#{sch_word},'%')
</if>
<if test = 'totalSearchFlag neq null and totalSearchFlag neq ""'>
  <choose>
     <when test = 'totalSearchFlag eq "Y"'>
      AND   ni.node_id IN 
       <foreach item="node_id_grp" index="i" collection="node_id_grp" separator="," open="(" close=")">
        #{node_id_grp}
       </foreach>
      </when>
      <otherwise>
        AND   ni.node_id = #{node_id}
      </otherwise>
  </choose>
</if>
 
cs

* totalSearchFlag 는 selectBox 에서 '전체'가 선택되었음을 판별하기 위해 사용되는 값

* 1번 쿼리(특정노드의 모든 자손 구하기)로 뽑아온 모든 자손 노드를 필드명이 node_id_grp 인 List 자료형 에 담아 sql mapper 로 넘겨주어 사용

* open 은 for 문이 맨처음 돌기 전에, close 는 for문이 끝까지 돌고나서, separator 는 for 문이 한 번 씩 돌 때마다 적용된다.

* foreach(for문)로 작성된 쿼리 결과물은 다음과 같다

  (생략) ~ AND ni.node_id IN (2, 6)

 

 

 

위 쿼리를 사용해 가져온 데이터를 화면에 그려보자.

https://developyo.tistory.com/55?category=689339

반응형

1. 더하기

DATE_ADD(date, INTERVAL expr type)

DATE_ADD(날짜, INTERVAL 시간 시간타입)

 

ex) 

DATE_ADD(now(), INTERVAL 1 HOUR) : 현재 시각에 1시간 더하기

DATE_ADD(now(), INTERVAL -10 MINUTE) : 현재 시각에 10분 빼기

DATE_ADD(now(), INTERVAL 100 SECOND) : 현재 시각에 100초 더하기

1
2
3
4
SELECT  seq 
  FROM  hst_info
 WHERE  1=1
   AND  JOIN_DATE < DATE_ADD(now(), INTERVAL -1 HOUR)
cs

: 현재시각(now()) 에 -1 시간(HOUR)을 더한(DATE_ADD) 값이 JOIN_DATE 보다 더 큰 hst_info 테이블의 seq 값을 조회

* expr 값으로 음수를 넣을 경우 음수를 더하면 뺄셈이 되므로 DATE_SUB 함수와 같이 동작한다.

 

 

2. 빼기

DATE_SUB(date, INTERVAL expr type)

DATE_SUB(날짜, INTERVAL 시간 시간타입)

 

ex) 

DATE_SUB(now(), INTERVAL 1 HOUR) : 현재 시각에 1시간 빼기

DATE_SUB(now(), INTERVAL -10 MINUTE) : 현재 시각에 10분 더하기

DATE_SUB(now(), INTERVAL 100 SECOND) : 현재 시각에 100초 빼기

1
2
3
4
SELECT  seq 
  FROM  hst_info
 WHERE  1=1
   AND  JOIN_DATE < DATE_SUB(now(), INTERVAL 1 HOUR)
cs

: 현재시각(now()) 에 1 시간(HOUR)을 뺀(DATE_SUB) 값이 JOIN_DATE 보다 더 큰 hst_info 테이블의 seq 값을 조회

* expr 값으로 음수를 넣을 경우 음수를 빼는건 덧셈이 되므로( -(-1) = +1) DATE_ADD 함수와 같이 동작한다.

 

 

3. 기간 구하기

TIMESTAMPDIFF(interval, datetime_expr1, datetime_expr2)

TIMESTAMPDIFF(시간타입, 시작시각, 끝시각)

 

ex)

TIMESTAMPDIFF(DAY, '20190820', now()) : 현재년월일 - 20190820 (%Y%m%d) 을 DAY 로 환산

TIMESTAMPDIFF(SECOND, '20190820140000', now()) : 현재년월일 - 20190820140000 (%Y%m%d%H%i%s) 을 SECOND 로 환산

1
2
3
SELECT seq
  FROM hst_info
 WHERE TIMESTAMPDIFF(SECOND, JOIN_DATE, now()) > 3600
cs

: JOIN_DATE 시각 부터 현재시각(now()) 까지의 시간차이를 초(SECOND)로 환산한 값이 1시간(3600초) 보다 큰 hst_info 테이블의 seq 값을 조회

 

 

위와 같은 함수들을 사용하여

이력 및 통계를 뽑아내거나, 배치를 돌릴 때 시간을 기준으로 데이터를 조작할 수 있다.

 

참고 : https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

 

 

* ORACLE 시간계산

https://oracle.tistory.com/216

 

 

 

반응형

SELECT 한 결과를 INSERT/UPDATE 하기

 

별도의 sql id 로 select, insert 쿼리를 각각 분리하여 선언하고

select 한 값을 서버에서 다시 insert sql 의 매개변수로 던져주어 DB에 insert 할 경우

WAS서버와 DB 서버간 DB connection 이 두 번 발생 하므로, 

다음과 같은 방법을 이용하자.

 

1. mysql 의 select insert 문법 사용

ex1)

1
2
INSERT INTO sampleTable (col1, col2, col3)
SELECT #{val1}, #{val2}, #{val3}
cs

ex2)

1
2
INSERT INTO sampleTable(col1, col2, col3)    
     SELECT #{val1}, #{val2}, MAX(col3) FROM sampleTable WHERE pkKey = #{pkKey}
cs

 

2. mybatis 의 selectKey 사용

1
2
3
4
5
6
7
8
9
<insert id="sampleSelectInsert" parameterType="java.util.HashMap">
<selectKey keyProperty="selectRs" resultType="integer" order="before">
  SELECT MAX(val)
    FROM sampleTable
   WHERE key = #{value}
</selectKey>
INSERT INTO targetTable (col1, col2, col3)    
                SELECT #{val1}, #{val2}, #{selectRs}
</insert>
cs

* order 옵션을 before 로 사용시 메인 쿼리 (위 예제에서 insert sql) 실행 전에 selectKey sql 실행.

  위의 경우 insert sql 실행 전에 select sql가 실행되어야 selectRs 라는 값을 가져올 수 있으므로 order 를 before로 지정

* order 옵션을 after 로 사용시 메인 쿼리 실행 후에 selectKey sql 이 실행되므로, insert 이후의 seq(시퀀스) max 값을

  return해야 할 때 유용하게 사용 가능

 

※ update 에서 selectkey 를 사용할 경우 mybatis 버전이 낮을 때 에러가 발생하는 경우가 있는 것 같다.

   select + update 를 사용해야 하는 경우 서브쿼리를 사용하자.

 

반응형

n개의 row 로 결과가 출력되는 쿼리를 한 줄의 row에 표현하고 싶은 경우, GROUP_CONCAT을 사용한다.

* SELECT GROUP_CONCAT('칼럼명' SEPARATOR '구분문자')

   FROM 테이블명

 

예를 들어,

쿼리 :

1
2
SELECT user_id AS result 
  FROM user_info;
cs

결과 :

result
user1
user2
user3

 

n개의 로우가 출력되는 쿼리에 GROUP_CONCAT 사용시,

쿼리:

1
2
SELECT GROUP_CONCAT(user_id SEPARATOR '^') AS result 
  FROM user_info;
cs

결과 :

result
user1^user2^user3

위처럼 하나의 row에 구분자를 기준으로 모든 row가 붙어서 출력된다.

* GROUP_CONCAT('데이터' ORDER BY '정렬기준' desc SEPARATOR '구분자') 와 같이 order by 사용도 가능.

 

* 응용

1
2
3
4
5
6
7
SELECT COUNT(*)
  FROM sample_table
 WHERE json_data REGEXP (
                         SELECT GROUP_CONCAT(user_id SEPARATOR '|') AS result 
                           FROM user_info
 );
 
cs

적절한 예인지 모르겠으나, 위와 같이 복수개의 문자열에 대한 LIKE 조건을 건 것 처럼 사용 할 수도 있지 않을까..

* jstl 을 사용한 클라이언트단에서의 출력

 

1
2
3
4
5
6
<td>
    <c:set var="user_id" value="${fn:split(list.user_id, '^')}" />
    <c:forEach var="obj" items="${user_id}" varStatus="idx">
    ${obj}<c:if test="${!idx.last}"><br></c:if>
    </c:forEach>
</td>
cs

 

※ ORACLE

LISTAGG(column, 'separator') WITHIN GROUP(ORDER BY column)

1
2
SELECT LISTAGG(user_id, ',') WITHIN GROUP(ORDER BY user_id) AS result
FROM user_info
cs

 

반응형

 MYSQL GROUP BY(incompatible with sql_mode=only full group by)에 대해 알아보자

 

로컬 및 개발 환경에서 멀쩡히 돌아가던 쿼리가

테스트 서버에 올리고 나니 다음과 같은 Exception을 뱉었다.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column '칼럼명' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

: group by 절에 포함되지 않은 칼럼(집계되지 않은 칼럼(nonaggregated column))을 select 절에서 뽑아올 경우, 어느 칼럼을 표시해야 할 지 몰라 exception 이 발생하는 현상이다. 

only_full_group_by 란?

mysql 5.7버전부터 sql_mode로 추가된 설정으로

집계되지 않은 칼럼을 select 절에서 뽑아오는 경우 exception을 낼지 exception 을 내지 않고 동작할지 결정하는 옵션이다.

 

간단히 예를 들면.

아래와 같이 사람 별로 영어 점수 총 합계를 조회하는 쿼리가 있다.

1
2
3
4
5
SELECT id as '아이디'
       ,  phone_num as '전화번호'
       ,  SUM(eng_point) as '영어점수합계'
FROM  sampletable
GROUP BY id
cs

phone_num (전화번호) 은 유니크한 값으로 다른 레코드와 중복될 수 없는 값이란 걸 개발자는 알겠지만,

DB입장에선 phone_num의 경우 어떤 값을 표시해야 할지 알 수 없다. (이를 집계되지 않은 칼럼 nonaggregated column)이라 칭함)

 

* 동일한 쿼리를 ORACLE 에서 실행시 적합하지 않은 group by 사용으로 exception 이 발생한다.

* mysql 에서 실행시 only_full_group_by 옵션의 활성화/비활성화 상태에 따라 exception이 발생/발생하지 않는다.

 

[해결 방법]

1. 쿼리를 수정한다.

1) 쿼리 자체를 수정. 

1-1) group by 절에 nonaggregated column 추가

1
2
3
4
5
6
SELECT  id as '아이디'
       ,  phone_num as '전화번호'
       ,  SUM(eng_point) as '영어점수합계'
FROM  sampletable
GROUP BY id, phone_num
 
cs

 

1-2) 서브쿼리 사용(경우에 따라 left outer join 사용)

1
2
3
4
5
6
7
  SELECT  id as '아이디'
       ,  phone_num as '전화번호'
       ,  ( SELECT SUM(eng_point) 
            FROM  sampletable
            WHERE st.id = id 
            GROUP BY id) as '영어점수합계'            
FROM  sampletable st
cs

 

2) select 절의 집계되지 않은 칼럼에 ANY_VALUE(칼럼) 함수 추가

1
2
3
4
5
SELECT  id as '아이디'
       ,  ANY_VALUE(phone_num) as '전화번호'
       ,  SUM(eng_point) as '영어점수합계'
FROM  sampletable
GROUP BY id
cs

 

2. mysql 설정파일을 수정한다. 

2-1) 현재 옵션 상태를 확인한다.

 

sql_mode 조회.

> select @@sql_mode;

 

sql_mode 조회 결과.

| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,                                                                                        DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

ONLY_FULL_GROUP_BY 이 포함되어 있다면 해당 설정이 활성화 되어 있음을 의미.

 

2-2) my.cnf 설정파일을 열어 해당 부분을 찾아 제거

2-3) mysql 재기동.

 

※ 머릿속에서 나온 예제이므로 적합하지 않을 수 있으며, 실제 동작하지 않는 쿼리일 수도 있다.

 애초에 쿼리를 잘 짜면 이 같은 문제가 나올 수 없다...

 

반응형

MYSQL에서 데이터 형식 바꾸기(DATE_FORMAT)

 

oracle , sybase , mysql 전부 데이터 형식 바꾸는 표현식이 전부 다르다..

 

1. mysql (출처 : https://www.w3schools.com/sql/func_mysql_date_format.asp)

SELECT DATE_FORMAT(NOW(), '%y/%m/%d')

return : 18/12/10

 

SELECT DATE_FORMAT(NOW(), '%Y/%m/%d')

return : 2018/12/10

 

%Y : 4자리 년도

%y : 2자리 년도

%H : 00~23

%h : 00~12

%Y%m%d%H%i%s : yyyyMMddHH24miss

%y%m%d%h%i%s : yyMMddHHmiss

 

2. ORACLE

SELECT TO_DATE(SYSDATE, 'YY/MM/DD') 

return : 18/12/10

 

3. SYBASE ( http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1520/html/iqrefbb/Convert.htm 참고 )

SELECT CONVERT(varchar, GETDATE(), 111)

return : 2018/12/10

 

뭐 sybase야 다시 쓸 일이 없을 것 같긴 하지만..

거지같다복잡하다

 

 

반응형

+ Recent posts