Redis 정의

Redis(Remote Dictionary Server)는 Key-Value 구조의 비정형 데이터를 저장하고 관리하기 위한 오픈소스 기반의 비관계형 데이터베이스 관리 시스템(DBMS)이지만 DBMS보다는 빠른 캐시의 성격으로 대표되며, NoSQL 중 하나에 속한다.

데이터베이스, 캐시, 메시지 브로커로 사용되며 인메모리 데이터 구조를 가진 저장소이다.

Redis는 기본적으로 영속성을 위한 데이터베이스는 아니지만, 백업을 통한 영속성을 지원한다.

Redis 활용처로는 Session Store, Cache, 분당 호출 수를 제한하는 등의 Limit Rater, Job Queue 등이 있다.

 

Key-Value Store

Redis는 Key-value 구조로 자바의 Map과 같은 구조이다.

Key-Value Store의 장단점은 아래와 같다.

장점 단점
- 단순성에서 오는 쉬운 구현과 사용성
- Hash를 이용해 값을 바로 읽으므로 빠른 속도를 보장한다.
- Hash를 이용하기 때문에 추가 연산이 필요없다.
- 분산 환경에서의 수평적 확장성이 보장된다.
- Key를 통해서만 값을 읽을 수 있다.
- 범위 검색등의 복잡한 질의가 불가능하다.

 

 

NoSQL의 종류

NoSQL의 데이터 모델은 Key-Value, Document, Wide-column, Graph로 분류할 수 있다.

 

  • Key-Value
    • Redis, Memcached, Riak, DynamoDB
  • Document
    • MongoDB, CouchDB
  • Wide-column
    • Cassandra, HBase, Google Big Table
  • Graph
    • Neo4j, OrientDB, AgensGraph

 

Data Type

Redis의 value는 여러 데이터 타입이 존재한다.

  1. Strings
  2. Lists
  3. Sets
  4. Hashes
  5. SortedSets
  6. Bitmaps
  7. HyperLogLog

 

Strings

가장 기본적인 데이터 타입이며 바이트 배열을 저장하는데 binary-safe로 처리된다.

binary-safe는 모든 문자를 표현할 수 있다는 의미로 제외되는 문자 없이 모든 문자를 저장할 수 있다.

C 언어의 경우 변수에 문자열을 넣을 때 마지막에 null 문자를 넣는다. null 문자열 자체는 문자 코드가 존재하는데 이 문자코드는 문자열의 끝을 의미하기 때문에 문자열로 표현하지 않는다.

그러나 binary-safe는 이런 제외되는 문자열 없이 모든 문자를 저장할 수 있는 것이다.

 

주요 명령어로는 set, get, incr, decr, mset, mget이 있다.

 

 

 

set은 특정 키에 문자열 값을 저장하는 명령어다. set [keyName] [value] 구조로 저장할 수 있다.

get은 특정 키에 저장된 문자열 값을 가져오는 명령어다. get [keyName] 구조로 되어있다.

 

Redis에는 MySQL의 auto_increment나 Oracle의 Sequence같이 자동 증감을 처리할 수 있다.

위 기능들은 간단한 이해를 위한 예시일 뿐이고 차이가 있는데 MySQL과 Oracle은 데이터가 삽입되는 시점에 특정 컬럼 값을 증가시켜주기 위함이지만 Redis에서는 특정 Key의 value를 Integer로 취급하고 1씩 증감한다는 점이다.

INCR [keyName] 혹은 DECR [keyName] 구조로 사용한다.

INCR은 1 증가, DECR은 1 감소시킨다.

이 두 명령어는 원자성이 보장되기 때문에 여러 요청이 몰리더라도 중복되거나 누락되는일 없이 정상 처리된다.

 

mset, mget은 한번에 여러 키에 대한 값을 저장하거나 가져오는 명령어다.

mset keyName1 value1 keyName2 value2 ... 와 같은 구조다.

mget keyName1 keyName2 ... 와 같은 구조로 여러 데이터를 가져올 수 있다.

mset firstKey firstValue secondKey secondValue 이 명령어를 수행한 뒤

keys * 명령어를 통해 모든 key를 출력해보면 firstKey, secondKey가 출력되는 것을 확인할 수 있으며

mget firstKey secondKey 이 명령어를 실행하게 되면 firstValue, secondValue가 출력되는 것을 볼 수 있다.

한번에 여러 Key와 그 값을 저장할 수 있다라는 개념이지 배열 형태로 저장하는 것이 아니다.

 

 

Lists

Linked-List 형태의 자료구조로 인덱스 접근은 느리지만, 데이터의 추가 및 삭제가 빠르다는 장점이 있다.

Lists는 Queue와 Stack의 역할로도 사용할 수 있다.

 

주요 명령어로는 lpush, rpush, llen, lrange, lpop, rpop이 있다.

 

lpush와 rpush는 리스트의 왼쪽(head) 혹은 오른쪽(tail)에 새로운 값을 추가하는 명령어다.

lpush listName value

rpush listName value

 

llen은 리스트에 들어있는 아이템 개수를 반환한다.

llen listName

 

lrange는 리스트의 특정 범위를 반환한다.

lrange listName 시작 인덱스 끝 인덱스

인덱스 범위를 0 -1로 설정하게 되면 0번 인덱스부터 가장 끝 요소까지 모두를 출력한다.

이때 인덱스의 경우 zero index 방식이다.

 

lpop은 리스트의 왼쪽(head)에서 값을 삭제하고 반환한다.

lpop listName

즉, Queue와 같이 가장 먼저 들어간 head 값을 추출하고 반환하게 된다.

 

rpop은 리스트의 오른쪽(tail)에서 값을 삭제하고 반환한다.

rpop listName

lpop과 반대로 Stack처럼 가장 나중에 들어간 값을 추출하고 반환한다.

 

 

Sets

순서가 없는 유니크한 값의 집합이다. 유니크한 집합인 만큼 중복이 존재할 수 없다.

검색이 빠르고 개별 접근을 위한 인덱스는 존재하지 않는다.

sets 타입을 통한 교집합, 합집합 등의 집합 연산이 가능하다.

 

주요 명령어로는 sadd, srem, scard, smembers, sismember가 있다.

 

sadd는 Set에 데이터를 추가하는 명령어다.

sadd setName value

 

srem은 set에서 데이터를 삭제하는 명령어다.

srem setName value

 

scard는 set에 저장된 아이템의 개수를 반환한다.

scard setName

 

smembers는 set에 저장된 아이템들을 반환한다.

smembers setName

 

sismember는 특정 값이 set에 들어있는지를 반환한다.

sismember setName value

 

위 명령어들 중 scard, smembers를 제외한 나머지 명령어들은 결과가 0, 1로 반환된다.

0은 실패, 1은 성공이다.

이미 들어가있는 데이터를 또 넣으려고 한다거나, 존재하지 않는 데이터를 삭제하고자 하는 경우에도 오류가 발생하는 것이 아닌 0이 출력된다.

 

 

Hashes

Hashes는 하나의 Key 하위에 여러개의 field-value 쌍을 저장하는 구조다.

여러필드를 가진 객체를 저장하는 것으로 HINCRBY 명령어를 통해 카운터로 활용이 가능하다.

 

하나의 Key 하위에 여러개의 field-value 쌍을 가지는 구조라는 것은 마치 아래 JSON과 같은 구조라고 볼 수 있다.

{
    "user1": {
        "name": "coco",
        "age": 9
    }
}

 

Redis는 JSON을 그대로 value에 넣을 수 있는데 Hashes와 JSON Value의 차이점은 단일 데이터 조회에서 발생한다.

위와 같은 구조의 데이터를 저장한뒤 name만 조회해야 하는 상황이라고 가정했을 때, JSON이 그대로 value에 담기는 경우 user1의 모든 데이터를 조회해 가져온 뒤 name만 따로 뺄 수 있도록 파싱해야 한다.

하지만, Hashes로 저장하게 되면 조회 시점에서부터 name만 조회해 파싱하는 과정이 사라지게 된다.

 

항상 해당 데이터가 전부 필요하다고 보장된다면 JSON을 그대로 담는 방법도 좋을 수 있겠으나, 그렇지 않은 경우에는 Hashes가 더 유용할 수 있다.

또한, 하나의 value에 대해 increase, decrease도 사용할 수 있기 때문에 이런 기능이 필요하다면 Hashes를 선택하는 것이 유리하다.

 

주요 명령어로는 hset, hget, hmget, hincrby, hdel이 있다.

 

hset은 한개 또는 다수의 필드에 값을 저장하는 명령어다.

hset key field value field value
위 코드를 예시로 user1은 key가 된다. 그리고 name은 field 또는 sub-key라고 불리며, 그 값은 value이다.

그럼 예시대로 저장하기 위한 명령어는 hset user1 name bear age 10  이런 명령어가 된다.

 

hget은 특정 필드의 값을 반환하는 명령어다.

hget key field

hget user1 name 명령어를 입력하면 user1의 name 필드 값인 coco가 반환된다.

 

hmget은 한개 이상의 필드값을 반환하는 명령어다.

hmget key field field

# 명령어 결과
hmget user1 name age

1) "coco"
2) "9"

 

 

hincrby는 특정 필드의 값을 Integer로 취급하고 지정한 수 만큼 증가시킨다.

hincrby key field 증가값

# 명령어 결과
hincrby user1 name 1

(error) ERR hash value is not an integer


hincrby user1 age 1

(integer) 10


hmget user1 name age

1) "coco"
2) "10"

위와 같이 Integer로 변환이 불가능한 경우에는 오류가 발생하며, 변환할 수 있는 값인 경우에만 증가된다.

Hashes에서는 hincrby를 통해 값을 증가시키는 것은 가능하지만 감소시키는 decrease는 수행할 수 없다.

 

 

hdel은 한개 이상의 필드를 삭제하는 명령어다.

hdel key field field

삭제된 필드를 조회하면 (nil)이 출력된다. 애초에 저장하지 않았던 필드를 조회하는 경우에도 (nil)이 출력되기 때문에 존재하지 않는 필드를 조회하는 경우에는 (nil)이 출력된다고 보면 된다.

그렇기 때문에 name, age를 모두 제거하고 hmget으로 조회하더라도 둘다 (nil)이 나오게 된다.

그럼 user1이라는 key에 존재하는 모든 필드가 제거되었는데 이 key는 남는건가? 싶을 수 있는데 keys * 명령어를 통해 조회해보면 해당 key 역시 삭제되어 존재하지 않는 것을 확인할 수 있다.

 

그리고 hdel user1 name을 통해 name 필드만 제거한 뒤 다시 hset user1 name coco 명령어를 통해 필드를 저장하면 동일하게 user1 key안에 name 필드가 저장된다.

 

 

SortedSet

Set과 유사한 유니크한 값의 집합이지만, 각 값은 연관된 정수형의 score를 갖고 정렬되어 있다는 점이 차이점이다.

SortedSet은 정렬된 상태이기 때문에 빠르게 최소, 최대값을 구할 수 있어 순위계산이나 리더보드 구현등에 활용이 된다.

 

주요 명령어는 zadd, zrange, zrank, zrevrank, zrem, zincrby가 있다.

 

 

zadd는 한개 또는 다수의 값을 추가 또는 업데이트 하는 명령어다.

zadd key score value score value ... 와 같은 구조다.

 

zrange는 특정 범위의 값을 오름차순 정렬로 반환한다.

zrange key 시작인덱스 끝인덱스 [withscores]

Lists와 마찬가지로 zero index 방식이다.

withscores 옵션을 같이 입력하게 되면 score와 함께 출력한다.

# 값 추가
zadd rank 10 coco 20 mozzi

# 조회
zrange rank 0 -1

1) "coco"
2) "mozzi"


zrange rank 0 -1 withscores

1) "coco"
2) "10"
3) "mozzi"
4) "20"

 

그럼 여기까지 잠깐 정리하면 score는 정렬되는 기준이 된다.

그렇기 떄문에 삭제 명령어를 통해 coco를 제거한 뒤 다시 10의 score로 추가하게 되면 동일하게 coco가 먼저 출력되게 된다.

 

주의사항으로는 score는 중복될 수 있으며, 같은 값인 경우에는 value 기준 사전순으로 정렬된다.

zadd rank 10 coco
zadd rank 10 mozzi
zadd rank 10 youn
zadd rank 10 jung

위와 같이 값을 추가하는 경우 coco -> jung -> mozzi -> youn 순서로 정렬된다.

 

 

zrank는 특정 값의 위치(순위)를 반환하는데 이때 오름차순 기준으로 위치를 반환한다.

zrank key value

이때도 zero index로 가장 첫 값의 위치는 0이다.

 

zrevrank는 zrank와 마찬가지로 특정 값의 위치를 반환하는데 내림차순 기준으로 위치를 반환한다.

zrevrank key value

그래서 가장 첫 값을 조회하게 되면 0이 아닌 총 개수 - 1개의 결과를 반환한다.

 

 

zrem은 한개 이상의 값을 삭제하는 명령어다.

zrem key value

 

 

zincrby는 특정 값의 순위를 지정한 값만큼 증가시키는 명령어다.

zincrby key increase-score value

위 명령어 예시에서 increase-score에 작성한 값만큼 score 값이 증가하게 되는데 이때도 증가한 값이 중복되는 값이라면 value 기준 사전순으로 정렬된다.

zadd rank 10 coco 20 mozzi 30 jung 30 youn

zrange rank 0 -1

1) "coco"
2) "mozzi"
3) "jung"
4) "youn"


zincrby rank 10 mozzi

1) "coco"
2) "jung"
3) "mozzi"
4) "youn"

 

 

 

Bitmaps

비트 벡터를 사용해 N개의 Set을 공간 효율적으로 저장한다.

하나의 비트맵이 가지는 공간은 4,294,967,295(2^32 - 1)인데 0과 1로만 이루어진 비트 벡터를 사용함으로써 간결하게 표현할 수 있는 데이터들의 저장 공간을 효율적으로 사용하는 것이다.

예시로, 방문 여부를 확인해야 할 때, visit이라는 key값의 bitmap을 활용하고 비트맵의 인덱스를 사용자 번호로 사용한다면 0은 미방문, 1은 방문으로 체크할 수 있게 된다.

그럼 하나의 비트맵만으로 42억명의 방문 이력을 처리할 수 있기 때문에 효율적이다.

 

주요 명령어로는 setbit, getbit, bitcount, bitop가 있다.

 

 

setbit는 비트맵의 특정 offset 값을 변경한다.

setbit key offset 변경값(0, 1)

 

getbit는 비트맵의 특정 offset 값을 반환한다.

getbit key offset

 

bitcount는 비트맵에서 set(1) 상태인 비트의 개수를 반환한다.

bitcount key

 

bitop는 비트맵들간의 비트 연산을 수행하고 결과를 비트맵에 저장한다.

bitop 수행할연산 결과비트맵key key1 key2

여기서 연산에는 and, or, xor, not을 사용할 수 있다.

 

# visit 이라는 key 값의 bitmap 생성 및 10번 인덱스의 값을 1로 변환
setbit visit 10 1
(integer) 0

setbit visit 9 1
(integer) 0


getbit visit 10
(integer) 1

getbit visit 11
(integer) 0


# 범위를 벗어난(43억번째 인덱스) 값 변경. getbit도 동일한 오류 발생
setbit 4300000000 1
(error) ERR bit offset is not an integer or out of range


# 새로운 visit2 bitmap 생성
setbit visit2 10 1
(integer) 0


# visit과 visit2를 비트연산하고 결과는 visitResult 라는 비트맵을 생성해 저장
bitop and visitResult visit visit2
(integer) 2


keys *
1) "visitResult"
2) "visit2"
3) "visit"


getbit visitResult 10
(integer) 1

bitcount visitResult
(integer) 1

 

결과에 대해 정리.

setbit에 대한 결과는 수정되기 전의 값이다.

즉, setbit visit 10 1을 최초 수행했을 때는 최초 설정값이 0이기 때문에 0이 출력되는거고 이후 setbit visit 10 1 또는 setbit visit 10 0을 수행하는 경우 이전 값인 1이 출력된다.

 

범위를 벗어난 값을 조회 또는 수정하려고 한다면 out of range 오류가 발생한다.

 

bitop 명령어의 결과는 생성된 비트맵의 크기를 나타내는데 byte 단위 결과다.

8bit = 1byte이기 때문에 10(index 11)까지만 값을 변경했으므로 2byte의 크기를 갖게 되므로 2를 반환하는 것이다.

그럼 여기서 알 수 있는것은 bitmaps는 크기가 최초 생성시부터 2^32 - 1인 512MB로 생성되는 것이 아니라는 것을 알 수 있다.

 

최대 크기가 512MB인거지 최초 생성시부터 512MB갖고 생성되지 않는다.

위 코드처럼 setbit visit 10 1을 통해 visit이라는 bitmap을 생성하게 되면 zero index 구조이기 때문에 11의 크기가 필요하다.

하지만 byte 단위로 생성되기 때문에 16bit은 2byte의 크기로 최초 생성되게 된다.

이후 setbit 명령어를 통해 더 큰 수의 index를 수정하는 경우 그때마다 byte 단위로 크기가 커지게 되는 것이다.

그리고 그 최대치는 512MB인 2^31 - 1까지 허용이 된다.

 

 

HyperLogLog

HyperLogLog는 유니크한 값의 개수를 효율적으로 얻을 수 있다.

확률적 자료구조이기 때문에 오차가 있으며, 매우 큰 데이터를 다룰 때 사용이 된다.

18,446,744,073,709,551,616(2^64, 1844경)개의 유니크 값을 계산하는 것이 가능하며 12KB의 메모리를 사용해 0.81%의 오차율을 허용한다.

보통 값들을 넣은 뒤 그 값들이 유니크한 값으로 나눴을 때 총 몇개인지를 알아내는 용도로 사용되고, bitmap의 bitcount와 비슷한 용도이지만 효율성이 더 높다.

 

만약 HyperLogLog가 아닌 Set을 사용한다면 데이터가 수백만개만 되더라도 몇 MB의 메모리를 사용하게 된다.

하지만 HyperLogLog를 사용하면 약 1800경 개의 데이터를 담더라도 고작 12KB만 사용하게 된다.

너무 대용량의 데이터이기 때문에 확률적으로 어느정도 오차율을 허용하고 성능을 높인다.

너무 많은 양의 데이터를 사용하는 경우는 99% 이상의 정확도라면 로직을 처리하기에 충분한 경우가 많다고 한다.

HyperLogLog는 0.81%의 오차율을 허용하기 때문에 이에 적합하고 많이 사용된다고 한다.

 

용도는 아무래도 count에 관련되다보니 주로 add, count 두가지 명령어를 사용한다.

특징으로는 값을 넣을 때 내부에 데이터를 저장하지 않는다는 점이 있다.

HyperLogLog라는 나름대로의 확률적 자료구조를 내부구현 형태로 갖고 있기 때문에 add로 저장한다고 해서 실제로 내부에 저장되지는 않는다.

 

 

주요 명령어로는 pfadd, pfcount, pfmerge가 있다.

 

pfadd는 HyperLogLog에 값을 추가하는 명령어다.

pfadd key value value value ...

 

pfcount는 HyperLogLog에 입력된 값들의 cardinality를 반환한다.

pfcount key

 

pfmerge는 다수의 HyperLogLog를 반환한다.

pfmerge 결과key key1 key2

 

# visit이라는 HyperLogLog 생성
pfadd visit coco mozzi youn jung
(integer) 1

pfcount visit
(integer) 4

# visit2라는 HyperLogLog 생성. youn만 visit과 동일하게.
pfadd visit2 coco2 mozzi2 youn jung2
(integer) 1

pfcount visit2
(integer) 4


# visit과 visit2를 합집합으로 합쳐서 visitResult라는 HyperLogLog 생성
pfmerge visitResult visit visit2
OK

# 중복은 제거되기 때문에 중복된 youn 하나가 제거되고 7개만 남는다.
pfcount visitResult
(integer) 7

 

HyperLogLog의 설명처럼 대용량의 데이터를 넣어서 테스트 해볼수가 없어서 간단하게 명령어 테스트만 수행해봤다.

유니크한 값의 개수를 효율적으로 얻기 위한 방법이기 때문에 set과 마찬가지로 중복은 허용하지 않으며, 중복되는 값을 의도적으로 넣더라도 오류가 발생하지 않는다.

 

 

 

Reference

 

[Redis] 레디스 데이터 타입 정리

이 포스팅은 레디스 공식 문서를 보고 지식을 정리하기 위해 쓴 글입니다. Strings Redis String 유형은 Redis 키와 연결할 수 있는 가장 간단한 유형의 값이다. Memcached에서의 유일한 데이터 타입이자, R

yeongunheo.tistory.com

 

프로젝트를 진행하면서 더미데이터를 몇백만건씩 넣어본적도 없고 넣기도 빡세고 해서 count 쿼리에 대한 고민은 사실 해본적이 없었다.

 

매번 그냥 count(*) 로 처리했었는데 이번에 한번 대량의 데이터를 넣어서 해보고자 200만건의 데이터를 넣어봤다.

그리고 서버를 돌리고 해당 페이지에 접근했더니 한~참있다가 페이지가 뜬다..

그래서 테스트를 좀 해봤다.

 

테스트 환경

  • MySQL
  • 데이터 200만건.
  • MySQL Workbench에서 테스트

결과는 count(*)로 조회 했을 때 200만건을 처리하는데 16초가 걸렸다.

 

이래서 느렸구나 싶어 방법을 찾아봤고, 마땅한 해결책을 찾지 못해 처음에는 테이블을 새로 만들었다.

테이블에 각 게시판명을 기본키로 잡고 다른 컬럼에는 데이터의 총 개수를 넣어주었다.

그리고 trigger를 통해 insert, delete 발생시에 수정하도록 했다.

 

당연히 속도는 엄청 빨랐다.

하지만 이 문제를 좀 해결했으면 했다.

 

그래서 프로젝트가 다 마무리되고 나서 다시 방법을 좀 찾아봤다.

찾은 방법으로는 count(*)로 처리할것이 아니라 count(distinct(pk)) 형태로 처리하면 더 빠르게 조회할 수 있다는 것이었다.

그래서 테스트를 바로 해봤다.

 

count(distinct(boardNo))

결과는 0.6초가 걸렸다.

그냥 count(*) 쿼리가 16초가 걸린 반면 엄청난 시간차이였다.

 

여기서 만족할 수 없었는데, 이유는 게시판 특성상 검색 기능이 존재해야 하고 그럼 검색한 게시글의 개수가 많다면?

또 같은 문제가 발생할 수 있기 때문에 테스트가 필요했다.

 

제목으로 검색했고, 조회된 데이터 수는 130만건이었다.

count(*) = 1.093

count(distinct(boardNo)) = 1.297

WHERE 조건이 걸리지 않았을 때는 count(distinct(pk))가 훨씬 더 빠른 처리를 보여줬던 반면

조건이 걸려있을때는 count(*)가 더 빨랐다.

 

테스트를 하나 더 해보고 싶었다.

인덱스를 통해 처리하면 좀 더 빠르게 처리할 수 있다는 말은 데이터베이스에 대해 처음 공부할 때부터 계속 듣던 말이다.

그럼 여기에 인덱스까지 설정해주면????

그래서 boardNo, title 두가지를 묶어서 인덱스를 생성하고 다시 테스트를 해봤다.

count(*) = 0.641

count(distinct(boardNo)) = 0.953

결과는 count(*)가 더 빨랐다.

 

 

이렇게 몇가지 테스트를 해본 결과 count 쿼리를 처리하는데 있어서 전체 데이터를 조회해야 한다면 count(*)보다는 count(distinct(pk))가 훨씬 빠른 처리시간을 보여주지만,

WHERE 조건이 붙는 경우에는 count(*)가 더 빠르며, 인덱스까지 생성해준다면 더 빠르게 처리할 수 있다는 결과를 볼 수 있었다.

 

더 많은 양의 데이터를 처리하면 또 다른 결과를 볼 수 있을지도 모르겠지만...

아직 쿼리 효율에 대해 고민하고 공부해야 할 점이 너무 많다는 생각이 들었다.

 

프로젝트 리펙토링 중에 게시판 댓글 처리에 대해 좀 고민을 하게 되었다.

계층형 구조인데 댓글을 삭제했을 때 어떻게 처리할것인가에 대한 고민이었다.

 

처음에는 그냥 해당 댓글을 삭제하도록 구현했었고, 고민하다 보니 그것도 구조가 좀 이상하지 않나 싶어 하위 모든 댓글을 삭제 하는것으로 할까 하다가 게시판은 그렇게 처리했으니 다르게 처리하고 싶었다.

 

그래서 결정한 방법이 많은 사이트에서 볼 수 있는 '삭제된 댓글입니다'를 출력하는 것이었다.

 

이렇게 처리하기 위해 댓글 상태를 표현하는 필드가 필요했고 그거 추가하는거야 어렵지 않으니 추가해줬다.

default '0', 삭제 요청이 들어오면 데이터를 delete처리하는것이 아닌 1로 update 처리를 하도록 했다.

 

그럼 이제 여기서 고민이 있었다.

그냥 select * from comment 이렇게 조회해온다면 삭제된 댓글임에도 내용이 그대로 조회될 것이다.

 

여기서 고민끝에 생각난 방법이 세가지.

 

1. 프론트로 응답하기 전 서버에서 데이터 리스트를 하나하나 검증해 status가 1이라면 내용을 수정한다.

   이 방법은 떠오르자마자 아니다 싶었다.

   리스트를 하나하나 보면서 체크하고 해당되는 데이터를 수정한다? 그건 좀 아니다 싶었다.

 

2. 데이터를 응답받은 프론트에서 파싱하는 과정 중 status가 1인 데이터는 내용을 수정하도록 한다.

    괜찮은 방법이라고 생각했다.

    어차피 파싱 과정에서 데이터를 차례대로 파싱해 나가야 하고, 그 과정에서 status값 하나를 체크하는 것 뿐이니

    처리 시간에 지장을 주지 않고 되지 않겠나 싶었다.

    하지만 문득 이 방법으로 처리하면 내용이 노출될 가능성이 있다는 생각이 들었다.

    그래서 이 방법은 보류.

 

3. 쿼리로 조회할 때 부터 수정된 상태로 받으면 이 문제가 다 해결되지 않을까?

    그래서 쿼리문에서 조건문을 사용할 수 있는지를 찾아봤다.

    그리고 동적쿼리로 if문을 사용할 수 있다는 것을 알아냈다.

 

 

문제해결

if 조건문을 통해 처리하는 방법(MySQL)

SELECT commentNo
    , IF(commentStatus > 0, '삭제된 댓글입니다', commentContent) AS commentContent
    , ...
FROM comment

IF( 조건, true, false) AS 컬럼 표시명

이런 구조로 작성해주면 된다.

그럼 commentStatus는 0, 1두가지로만 구성되어있기 때문에 > 0 을 만족한다는 것은 삭제된 댓글이라는 의미이므로 

'삭제된 댓글입니다'를 content로 가져오게 되고 false의 경우 0이므로 삭제된 댓글이 아니기 때문에 원래 내용을 가져오게 된다.

 

하지만 여기서 문제가 있었다.

JPQL에서는 nativeQuery로 처리하지 않으면 Syntax 오류가 발생한다.

물론 nativeQuery로 처리하면 된다.

하지만 그렇게 되면 클래스 타입의 DTO로 받을 수 없었고, 프로젝트에서는 이 데이터를 또 파싱해야 했어서 무조건 List 타입의 엔티티로 받아야 했기 때문에 다른 방법을 찾아야 했다.

 

 

CASE WHEN

SELECT commentNo
    , CASE
        WHEN (commentStatus > 0) THEN '삭제된 댓글입니다'
        ELSE commentContent
      END AS commentContent
    , ...
FROM comment

다르게 조건문을 사용할 수 있는 CASE WHEN이다.

IF의 경우 단일 조건에 대해서만 처리가 가능하지만 CASE WHEN은 자바에서 switch case 처럼 다양한 조건에 대해 처리가 가능하다.

예를들어 

SELECT commentNo
    , CASE
        WHEN (commentStatus == 0) THEN null
        WHEN (commentStatus == 1) THEN '삭제된 댓글입니다'
        ELSE commentContent
      END AS commentContent
    , ...
FROM comment

이런식으로도 사용이 가능하다.

JPQL에 이걸 그대로 적용하게 되면 END뒤의 AS와 매핑할 DTO의 괄호 부분에 빨간 밑줄이 생기긴 한데 처리하는데 문제가 발생하지는 않았다.

아직까지는 JPQL에서 이 동적쿼리를 사용하는데 있어서 문제가 발생하지는 않았지만 빨간 밑줄이 생기는건 좀 찝찝하긴 해서 원인을 좀 찾아보긴 해야할듯 하다.

 

그리고 이 CASE WHEN의 경우는 Oracle에서도 사용이 가능하다!

'DB' 카테고리의 다른 글

Redis 정리  (1) 2024.12.07
다량의 데이터 count 처리  (0) 2023.09.28
Oracle Scheduler, MySQL Event Scheduler  (0) 2022.04.21
mysql 계층형 쿼리(함수, 재귀)  (0) 2022.04.04
EC2에 설치한 MySQL 데스크탑 workbench에서 접근  (0) 2021.11.09

데이터베이스에서 일정 주기마다 데이터 처리를 해줘야 하는 상황이 발생할 수 있다.

이걸 찾아보게 된 계기 역시 일정 시간이 지난 데이터들을 주기적으로 삭제해주기 위해서였다.

이렇게 주기적으로 처리하도록 스케줄러를 활용해 처리할 수 있다.

 

Oracle Scheduler

오라클에서는 10g 이전에는 job 으로 사용했었고 그 이후에는 Scheduler를 지원한다고 한다.

물론 10g 이상이라고 해서 job을 사용할 수 없는것은 아니지만 job의 문제점을 보완해 나온 패키지이기 때문에

더 많이 사용한다고 한다.

 

Oracle job의 경우는 PLSQL_BLOCK과 STORED_PRECEDURE만 구동할 수 있으며

Scheduler에서는 OS 상의 crontab에 등재되는 쉘 프로그램도 사용할 수 있다는 차이가 있다.

 

Oracle Scheduler의 생성 문법은 아래와 같다.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => '스케줄러 이름'
    , job_type => '스케줄러 타입'
    , job_action => '스케줄러 동작시 실행할 프로그램'
    , start_date => 첫 동작 시점
    , repeat_interval => '반복 시점'
    , end_date => 종료 시점
    , enabled => 활성화 여부
    , auto_drop => 스케줄러 종료시 drop 여부
    , comment => 'discription'
  );
END;

Oracle Scheduler 생성은 BEGIN과 END 사이에 DBMS_SCHEDULER.CREATE_JOB( )으로 생성하게 된다.

 

job_name

job_name은 적혀있는대로 스케줄러명을 의미한다.

 

 

job_type

job_type은 스케줄러의 타입인데 아래와 같은 타입이 존재한다.

  • PLSQL_BLOCK : PL/SQL 블럭 형태
  • STORED_PROCEDURE : 내장 프로시저
  • EXECUTABLE : 실행 파일

이 스케줄러가 동작하면서 실행할 프로그램이 어떤것이냐에 따라 job_type 설정을 해주면 된다.

보통 예제들로 나오는것들은 프로시저로 처리하는 예제가 많았다.

간단한 쿼리문만 실행하도록 하게 한다면 PLSQL_BLOCK을 사용하면 되고

프로시저를 생성해 그 프로시저를 호출하도록 할것이면 STORED_PROCEDURE를 job_type으로 지정하는 형태로 사용하면 된다.

 

 

job_action

job_action은 스케줄러가 동작할 때 실행할 프로그램이다.

위에서 언급한것처럼 job_type에 맞게 작성하면된다.

PLSQL_BLOCK이라면 여기에 쿼리문을 작성해주면 되고 STORED_PROCEDURE라면 프로시저명을 작성해주면 된다.

 

 

start_date, repeat_interval

start_date는 스케줄러가 동작할 시점이고 repeat_interval은 그 다음으로 반복할 시간대를 의미한다.

이 두가지를 묶은데에는 이유가 있다.

둘다 시간대에 대한 설정이기 때문이다.

 

start_date와 repeat_interval에서 지금 눈에 보이기만 하는 차이는 ' ' 가 있느냐 없느냐 차이정도다.

왠지 모르겠지만 start_date에서는 그냥 사용하면 되고 repeat_interval은 ' '안에 작성해야 한다.

그럼 스케줄러 생성과 동시에 바로 동작하도록 하기 위해서는 start_date에

sysdate    이렇게 작성해주면 된다.

 

이제 여기서부터가 생각보다 언급되지 않아서 여기저기 좀 찾아봤었다.

내가 작성하게 될 스케줄러는 일정기간이 지난 데이터 삭제만 처리하는 내용이기 때문에 자정에 처리하는것이 목적이었다.

그래서 찾아보니 방법은 TRUNC(sysdate+1) + 0/24  이렇게 사용하는것이었다.

처음 딱 보자마자 sysdate+1은 다음날을 의미하는건 알겠고 24시간중 0시를 의미하나?? 이생각이 들었다.

다행히 내 생각이 맞았지만 문제는 이 다음이었다...

 

시작시간을 어떠한 시간으로 고정하는것이 아닌 1분뒤, 1시간뒤 이렇게 설정해야 한다면?

보통 포스팅들은 1분 뒤 시작하도록 하는 예제가 많았다.

이때 작성은 sysdate + 1/24/60 이렇게 작성한다.

그럼 1분뒤 실행이 된다.

그럼 1초단위는?

sysdate + 1/24/60/60 이렇게 작성한다.

즉, 1/24는 한시간단위, 1/24/60은 1분단위, 1/24/60/60은 1초단위다.

포스팅을 보면서 알게된건 1/24/60/10 이렇게 작성하게 되면 6초가 된다.

그럼 30초 뒤에 작동하도록 작성한다면

sysdate + 1/24/60/2 이렇게 작성하는 방법과 sysdate + 30/24/60/60 이 방법이 있다.

개인적으로는 후자가 더 좋은듯.............

 

그리고 또 한가지 방법으로는 to_date를 사용하는 방법이 있다.

to_date('21/04/2022 18:00:00', 'dd/mm/yyyy hh24:mi:ss')

이렇게 날짜와 시간을 명시해 처리하는 방법도 있다.

 

start_date 작성을 정리하자면 아래와 같다.

  1. TRUNC(sysdate+1) + 0/24 : 다음날 몇시에 동작할지 명시할 수 있다.
  2. sysdate + 1/24 : 한시간 뒤 동작. 현재 시간으로부터 시간, 분, 초 단위 이후에 동작하도록 할 수 있다. 단, 이떄는 TRUNC 함수를 사용할 수 없다. 사용하게 되면 생성 직후 동작한다.
  3. to_date('21/04/2022 18:00:00', 'dd/mm/yyyy hh24:mi:ss') : 날짜와 시간을 명시해 동작하도록 할 수 있다.

 

repeat_interval에서도 마찬가지로 사용이 가능하지만 더 편리한 방법이 존재한다.

'FREQ=DAILY;INTERVAL=1' 이렇게 작성하게 되면 다음날 같은 시간에 반복하게 된다.

FREQ의 경우는 SECONDLY, MINUTELY, HOURLY, DAILY, WEEKLY, MONTHLY, YEARLY 이렇게 존재한다.

앞에서부터 초, 분, 시, 일, 주, 월, 연 단위다.

INTERVAL은 주기를 의미한다.

위 예시 그대로 보면 1일뒤 반복을 의미하고 FREQ=DAILY;INTERVAL=2 이렇게 하면 2일에 한번 반복하게 된다.

일단위, 주단위 형태로 사용하게 된다면 이렇게 사용하는게 더 편한것 같다.

 

end_date

end_date는 스케줄러의 종료 시점을 의미한다.

start_date와 같은 형태로 작성하면 된다.

 

 

enabled

enabled는 활성화 여부를 의미한다.

생략해도 문제가 발생하지 않지만 default가 false로 되어있기 때문에 따로 활성화를 시켜줘야 한다.

생성하자마자 바로 활성화를 원한다면 true로 설정해주면 된다.

 

만약 생략한 경우는 아래와 같이 활성화를 한다.

-- 활성화
BEGIN
  DBMS_SCHEDULER.ENABLE(name=>'스케줄러명');
END;

-- 비활성화
BEGIN
  DBMS_SCHEDULER.DISABLE(name=>'스케줄러명', force => true);
END;

활성화가 있으면 당연히 비활성화도 있기 때문에 위와 같이 사용하면 된다.

 

 

auto_drop

auto_drop의 경우는 포스팅에서 따로 설명을 찾지 못했다.

Oracle docs에서는 간단하게 

If TRUE (the default), indicates that the job should be dropped once completed

라고만 나온다.

작업 완료 후 자동으로 삭제된다는 것.근데 oraexcel 이라는 사이트에서는 좀 더 상세한 설명을 볼 수 있었다.추가된 내용으로는 작업이 완료된 시점으로 간주하는 경우가 있었다.그 시점으로는 종료날짜가 지난경우, max_runs에 도달한 경우, max_failures 횟수에 도달하는 경우이다.max_runs는 최대 실행횟수, max_failures는 최대 실패 횟수인데 설정하는 방법은 아래와 같다.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => '스케줄러 이름'
    , job_type => '스케줄러 타입'
    , job_action => '스케줄러 동작시 실행할 프로그램'
    , start_date => 첫 동작 시점
    , repeat_interval => '반복 시점'
    , end_date => 종료 시점
    , enabled => 활성화 여부
    , auto_drop => 스케줄러 종료시 drop 여부
    , comment => 'discription'
  );
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => '스케줄러명'
    , attribute => 'MAX_RUNS'
    , value => 3
  );
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => '스케줄러명'
    , attribute => 'MAX_FAILURES'
    , value => 5
  );
END;

이렇게 설정하게 되면 max_runs는 3, max_failures는 5로 설정이 된다.

이걸 확인해보는 방법은 

SELECT * from user_scheduler_jobs;

이렇게 확인할 수 있다.

 

 

comments

comments는 그냥 이 스케줄러에 대한 코멘트를 달아놓는 것이다.

discription과 동일하다고 볼 수 있다.

 

 

그 외

만약 잡을 생성할 때 권한 오류가 발생한다면 해당 사용자에게 권한을 부여해줘야 한다.

권한은 sys 계정으로 부여하면 되고 아래 쿼리를 실행한다.

GRANT CREATE ANY JOB TO 계정명;

이렇게 부여할 수 있다.

 

잡 생성 내역에 대한 확인 및 실행내역은 아래 쿼리로 확인할 수 있다.

-- 잡 생성내역
SELECT * FROM user_scheduler_jobs;

-- 잡 실행내역
SELECT * FROM user_scheduler_job_log;

-- 잡 실행내역 좀 더 상세하게
SELECT * FROM user_scheduler_job_run_details;

생성내역에서는 설정 내역들을 볼 수 있고

실행내역에서는 간단한 실행 내역들을 볼 수 있다.

그리고 좀 더 상세하게 run_details로 실행하게 되면 오류가 발생했을 때 로그까지 확인이 가능하다.

 

마지막으로 삭제 쿼리는 아래와 같이 작성한다.

BEGIN
  DBMS_SCHEDULER.DROP_JOB(job_name => '스케줄러명', defer => false, force => false);
END;

 

 

 

MySQL Event Scheduler

MySQL의 Event Scheduler는 Oracle의 Job Scheduler보다 훨씬 간단하게 사용할 수 있다.

CREATE EVENT test_event
ON SCHEDULER EVERY 1 MINUTE
STARTS '2022-04-21 18:00:00'
COMMENT '테스트 이벤트'
DO
CALL procedure_test();

 

CREATE EVENT

이벤트 스케줄러 생성 구문이고 뒤에는 스케줄러명을 작성해주면 된다.

 

 

ON SCHEDULER EVERY 1 MINUTE

스케줄러의 반복 주기를 의미한다.

EVERY 1 MINUTE라는 것은 1분에 한번씩 반복한다는 의미이다.

오라클에서와 마찬가지로 SECOND, MINUTE, HOUR, DAY, WEEK, MONTH로 사용이 가능하다.

찾아보다가 stackoverflow에서 알게된건데

1분 30초 간격으로 반복하고 싶다면?

ON SCHEDULER EVERY '1:30' MINUTE_SECOND

이렇게 쓰면 1분 30초 간격으로 설정할 수 있다.

 

 

STARTS

시작 시점을 의미한다.

예제처럼 시간대를 명시해도 되지만 curdate()로 생성 즉시 실행하도록 할 수 있다.

 

 

COMMENT

오라클에서의 comment와 마찬가지로 설명을 적는 부분이다.

 

 

DO

실행 구문을 의미한다.

프로시저를 실행할거면 CALL 프로시저명(); 형태로 작성하면 되고

쿼리문을 실행할거면

쿼리문을 아래에 작성해주면 된다.

 

 

그 외

이벤트 스케줄러를 조회하는 쿼리는 아래와 같다.

SHOW EVENTS;

SELECT * FROM information_schema.events;

그리고 이벤트 스케줄러 삭제 쿼리는 아래와 같다.

DROP EVENT 스케줄러명;

 

 

 

 

oracle과 mysql 두개만 써봤다 보니 두가지에 한정에서 스케줄러 사용법을 좀 공부했는데

아무래도 mysql이 더 편해보이긴 한다...

근데 또 세세하게 설정하자면 그게 그거일 것 같기도 하고..

 

 

 

레퍼런스

● Oracle Scheduler

 

[Oracle] 스케줄러 사용하기

저번에 프로젝트를 하다가 스케줄러의 필요성을 느껴서 스케줄러에 대해 알아보고 사용해보기로 했다.데이터베이스를 운영하다보면 하루에 한번씩 운영서버의 데이터를 개발서버에 데이터를

velog.io

 

[오라클] 잡 생성

잡 생성을 해보자. 잡 생성 문을 그냥 모르는 입장에서 보기만 해봐도 대충 뭐가 뭘 할 것이라는 것이 눈에 들어온다. job은 무시하고, 잡 번호가 자동생성 되서 들어오는 듯 하다. what 무엇이냔데

seodaeya.tistory.com

 

[Database] Oracle 스케쥴, 잡

-- ********** 1ST. DBMS_JOB************* 주기적으로 수행되어지는 JOB을 JOB QUEUE를 사용하여 스케줄링 할 수 있다. (데이터베이스 내에 생성한 프로시저 함수들에 대해 데이터베이스 내의 스케줄러에게 지

goddaehee.tistory.com

 

 

AUTO_DROP Parameter DBMS_SCHEDULER.CREATE_JOB Oracle 12cr1

auto_drop This flag, if TRUE, causes a job to be automatically dropped after it has completed or has been automatically disabled. A job is considered completed if: Its end date (or the end date of the job schedule) has passed. It has run max_runs number of

www.oraexcel.com

 

 

Creating and Managing Schedules to Define Jobs

You control when and how often a job repeats by setting the repeat_interval attribute of the job itself or of the named schedule that the job references. You can set repeat_interval with DBMS_SCHEDULER package procedures or with Enterprise Manager. The res

docs.oracle.com

 

● MySQL Event Scheduler

 

[MySQL]개발자라면 반드시 알아야하는 EVENT SCHEDULE

시작하면서... (삽질마니해따...)Event_Scheduler와 MySQL 프로시저를 사용한 이유는 회사제품 수집기에서 수집해온 데이터가 쌓이는 테이블에서 다른 테이블로 데이터를 정제해서 이관하기 위해서 사

pinokio0702.tistory.com

 

Mysql 이벤트 스케쥴러 (Event Scheduler)

Mysql에서 주기적으로 DB에 어떤 작업을 해주어야 할 때가 있다. 예를 들어 지속적으로 쌓이는 temporary 데이터가 있는데 해당 테이블을 자주 비워줌으로써 용량 차지가 되지 않게끔 하고자 한다.

jungeunpyun.tistory.com

 

How to schedule a MySQL Event on every XX:50 Seconds?

I Have a MySQL table with field name result. I want to use a event to execute on every XX:50 seconds and update the value of field by a random number. Need help regarding the syntax for every XX:50

stackoverflow.com

 

프로젝트 리펙토링을 하면서 oracle로 구현했던 계층형 게시판을 mysql로 다시 구현해보고자 시도했다가 문제가 발생.

 

기존 Oracle에서 구현할때는 start with ~ connect by 를 활용해 아주 간단하게 처리했다.

이때 완전 착각했던게 '원글 글번호를 토대로 아래에 UpperNo가 동일한 데이터를 찾아 하위에 넣어주면 되겠네. MySQL에서도 그냥 이렇게 하면 되겠다' 라고 생각한점이다.

 

그래서 솔직히 mysql에서 다시 구현 해봐야지 생각만 했지 미루고 있다가 막상 하려 했더니 MySQL에는 start with ~ connect by를 사용을 못하네...?

 

그래서 이것저것 조인도 해보고 컬럼 추가도 해보고 삭제도 해보고 별걸 다해보고 고민해봤지만 방법이 딱 하나 떠올랐다.

step이라는 컬럼을 생성해 소수점 형태로 처리하는 방법.

원글은 0, 첫 답글은 0.1, 두번째 답글은 0.2, 첫답글의 답글은 0.11, 두번째글의 답글은 0.21 이런형태로.

 

이전에도 비슷한 방법으로 구현한적이 있었는데 그때는 아예 step 컬럼이 하위 계층의 순서를 정의하는 역할을 했었다.

그러다보니 중간에 넣어줘야 하는 경우는 뒷번호들을 모두 하나씩 밀어야 하는 경우가 생겨 해놓고도 비효율적이라는 생각을 했었던 방법이다.

 

요즘 사이트들을 보면 계층형태더라도 한 계층 정도만 나오거나 몇계층 안나오는걸 많이 보긴 했는데 그런 경우에는 사용해도 괜찮겠지만 이 경우는 계층에 제한이 없다는 전제하에 만들었던 거라...........

 

이렇게 소수점 형태로 처리하면 그냥 GroupNo만들어서 역순으로 정렬하고 step 순서로 출력하게 하면 잘 나오긴한다.

근데 문제는 글 등록할때마다 그럼 step값을 제대로 넣어줄 수 있어야 하고 그럼 자기 자신의 위치를 찾기 위해 조회를 한번 더 해야하는 경우가 발생할것 이라고 생각했다.

 

한 이틀 내리 그냥 이것저것 쿼리문 계속 만들어보고 돌려보고 하다가 도저히 안되겠어서 검색해봤더니 함수를 이용한 처리와 재귀를 이용한 처리가 있다는 것을 알게 되었다.

 

전에는 못보던 글들인데 아무래도 그때는 'mysql 계층형 게시판' 이렇게만 검색해서 안나왔었던것 같다.

 

그래서 '이방법은 알아두면 좋겠다.' , '써먹을 수 있는 방법이겠다' 라는 생각이 드는 방법이 함수랑 재귀 쿼리다.

 

 

함수를 이용한 처리의 단점으로는 구현이 복잡하다는 것이 단점이고 재귀 쿼리를 사용하는 방법은 mysql 5.7 이하 버전에서는 사용할 수 없다는 것과 테이블의 모든 행 개수만큼 반복하기 때문에 데이터가 많을수록 효율이 떨어진다.

 

나중에 다 까먹었을때 다시 보더라도 빠르게 이해할 수 있도록 최대한 자세하게 작성했으니

혹시나 보시는 분들 있으시면 알고계신 부분들은 넘기시면서 보시는거 추천..

 


데이터는 이렇게 존재한다. 물론 뭐 이 위로도 1~73까지 있긴하지만 계층형 구현을 하는 부분은 이부분 밖에 없기 때문에...

그럼 여기서 순서는 75 -> 76 -> 77 -> 78 -> 79 -> 81 -> 82 -> 84 -> 80 이순서로 계층이 완성되어야 한다.

 

방법 1. 함수

-- 함수

DROP FUNCTION IF EXISTS fnc_hierarchi; -- fnc_hierarchi라는 함수가 존재하면 drop

DELIMITER $$
CREATE FUNCTION fnc_hierarchi() RETURN INT -- fnc_hierarchi() 함수 생성 및 이 함수의 리턴 타입은 int
NOT DETERMINISTIC -- Stored routine을 매번 새로 호출해서 비교. 비교되는 레코드 수 만큼 호출 발생.
READS SQL DATA -- 함수가 데이터를 변경하지 않도록 한다.
BEGIN
  DECLARE v_id int;     -- 글번호(boardNo) 변수
  DECLARE v_parent int; -- 상위글 번호(upperNo) 변수
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL; -- 마지막 레코드에 도달하면 @id를 null로 변환
  
  SET v_parent = @id; -- v_parent값을 @id 값으로 변환
  SET v_id = -1; -- v_id를 -1로 초기화
  
  IF @id IS NULL THEN -- 일반적인 if문과 동일. id가 null일 경우 아래 return을 수행.
    RETURN NULL;
  END IF;
  
  LOOP -- 반복문
  
  SELECT MIN(boardNo)
    INTO @id
    FROM board
    WHERE boardUpperNo = v_parent
      AND boardNo > v_id;         -- 조회 결과를 @id에 복사
  
  IF(@id IS NOT NULL) OR (v_parent = @start_with) THEN
    SET @level = @level + 1;
    RETURN @id;
  END IF;
  
  SET @level := @level - 1;
  
  SELECT boardNo, boardUpperNo
    INTO v_id, v_parent
  FROM board
  WHERE boardNo = v_parent;    -- 조회된 결과를 v_id = boardNo, v_parent = boardUpperNo로 넣어줌.
  
  END LOOP; -- 반복문 끝
  
END $$

DELIMITER;

-- Query

SELECT b.boardNo
  , CASE WHEN LEVEL-1 > 0 THEN CONCAT(CONCAT(REPEAT('   ', level-1),'ㄴ'), b.boardTitle)
      ELSE b.boardTitle
    END AS boardTitle
  , b.boardUpperNo
  , b.boardGroupNo
FROM(
  SELECT fnc_hierarchi() AS id
    , @level AS level
  FROM(
    SELECT @start_with:=0
      , @id=@start_with
      , @level:=0
  ) vars
    JOIN board
  WHERE @id IS NOT NULL
) fnc
  JOIN board b ON fnc.id = b.boardNo
  ;

 

이렇게 작성했다.

mysql에서 함수 작성이 가능한지도 몰랐기 때문에 처음보는 부분이 많았다.

 

일단 문법을 먼저 뜯어봤다.

 

DROP FUNCTION IF EXISTS fnc_hierarchi;

이 부분의 경우는 그냥 보자마자 이해할 수 있는 부분이었는데 fnc_hierarchi라는 함수가 존재한다면 DROP 해준다.

 

DELIMITER $$

보통 쿼리에서 ; 로 쿼리문을 마무리하게 되는데 이것을 $$로 바꾼다는 것이다.

이걸 설정하지 않으면 문장을 구분하기 어렵기 때문에 세미콜론이 아닌 $$로 변경하는 것이고

그래서 함수 마지막에 DELIMITER ; 로 다시 세미콜론을 사용하도록 되돌리는 것이다.

 

CREATE FUNCTION fnc_hierarchi() RETURNS INT

이 부분 역시 그냥 보이는 그대로 fnc_hierarchi라는 함수를 생성할것이고 이 함수의 반환타입은 INT라고 정의하는 부분이다.

이때 ( ) 안에는 파라미터가 들어갈 수도 있다.

여기서는 굳이 필요가 없어서 안넣었는데 fnc_hierarchi(boardNo INT) 이런식으로 사용할 수 있다.

 

NOT DETERMINISTIC

이 옵션의 경우는 Stored routine의 결과값이 계속 달라진다고 가정하고, 비교가 실행되는 레코드마다 이 Stored routine을 매번 새로 호출해 비교를 실행하도록 하는것이다.

기본 default가 NOT DETERMINISTIC이고 DETERMINISTIC 옵션은 동일한 입력 매개변수에 대해 항상 동일한 결과를 생성한다고 가정하고 1번만 함수를 호출하도록 한다.

한 포스팅에서 본것을 그대로 예를 들자면 데이터가 2,702,270개가 존재한다고 했을 때

함수에서는 2,700,000을 리턴하도록 작성했다면

1~ 2,702,700의 값을 갖고 있는 a 컬럼이 이 함수의 리턴값 보다 큰 경우를 count(*)하도록 하는 쿼리를 작성한다고 하자.

 

그럼 NOT DETERMINISTIC은 a가 1일때 함수를 호출해 2,700,000을 리턴받아 a와 비교를 하고,

a가 2일때 함수를 또 호출해 리턴받은 값과 비교하는 방법으로 처리하기 때문에

함수를 레코드 수만큼 호출하게 된다.

 

하지만 DETERMINISTIC 옵션으로 설정한다면 a가 1일때 함수를 호출해 2,700,000을 리턴받아 비교했으면

a가 2일때는 함수를 호출하지 않고 이전 리턴값인 2,700,000과 그대로 비교하게 된다.

그럼 함수는 레코드 수만큼 호출되는 것이 아닌 처음 한번만 호출되고 그 뒤로는 비교처리만 하게 되는것이다.

 

잘 설명해주신 분이 있어서 그건 Reference에서 확인할것.

이 함수에서는 하위 글에 대한 조회를 계속하고 리턴값이 계속 변하기 때문에 NOT DETERMINISTIC으로 모든 레코드수 만큼 호출되도록 해야 한다.

 

READS SQL DATA

함수가 데이터를 변경하지 않는다는 것을 나타내는 것이다.

이 옵션은 CONTAINS SQL, NO SQL, READS SQL DATA, MODIFIES SQL DATA 이렇게 네가지 종류가 존재한다.

함수가 데이터를 읽거나 또는 쓰는 정보를 제공하는 옵션이고 default는 CONTAIN SQL이기 때문에 하나를 명확히 지정해서 사용하는 것이 좋다.

 

이 함수에서는 함수가 데이터를 변경해야 할 이유가 없으므로 READS SQL DATA로 설정한다.

 

그리고 CREATE FUNCION 명령문이 default로 수용되도록 하기 위해서는 DETERMINISTIC 옵션이나 NO SQL 및 READS SQL DATA 중에 한개는 반드시 확실하게 명시해야 한다.

그렇지 않으면 

ERROR 1418 : This function has none of DETERMINISTIC, NO SQL or READS SQL DATA in its declaration and binary logging is enabled

이런 오류가 발생한다.

 

READS SQL DATA가 뭔지 알아보기 위해 검색했을때 다 이 오류에 대한 포스팅 밖에 안나왔다...

 

그나마 포스팅 한개를 찾았으니 자세한건 아래 Reference에서 확인.

 

BEGIN, END

구현부의 시작과 끝을 명시하는것이다.

 

DECLARE

변수를 의미한다.

그래서 이 함수에서는 v_id라는 int형 변수와 v_parent라는 int형 변수를 생성한 것이다.

 

마지막 CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

이 부분의 경우는 좀 다르다.

 

handler를 명시한건데 CONTINUE는 핸들러의 begin ~ end 부분을 실행하고 남은 본문을 이어서 수행하도록 하는 것이다.

그리고 not found는 커서가 마지막 레코드에 도달해 다음 레코드를 fetch하지 못했을때의 상황을 의미한다.

 

마지막 레코드에 도달했다면 SET @id = NULL; 로 @id 값으로 NULL을 넣어주라는 것이다.

 

여기서 핸들러는 try catch로 exception 처리를 하는것과 동일하다고 볼 수 있다.

 

IF

보이는 그대로 if문과 동일한 조건문이다.

IF로 시작하고 조건이 끝나 처리부분에 들어가려면 THEN으로 맺음을 해준다.

if(@id == null)

  return null;

이 상태와 같다고 보면 되고 if문이 끝나는 부분에서는 END IF로 끝났음을 알려야 한다.

 

LOOP

반복문이다.

LOOP역시 끝나는 부분에서 END LOOP로 끝났음을 알려야 한다.

 

SELECT ~ INTO ~

조회된 결과를 복사한다.

함수 내용으로 보면 MIN(boardNo)로 가장 작은 boardNo를 가져와 @id에 복사해주는 것이다.

함수 마지막 조회쿼리도 보면

SELECT boardNo, boardUpperNo INTO v_id, v_parent

이렇게 되어있는데

그럼 보이는 그대로 v_id에는 boardNo가 v_parent에는 boardUpperNo가 들어가게 된다.

 

사용자 정의 변수

함수 말고 쿼리문에서 확인해보면 @id, @start_with, @level 이런것들을 볼 수 있다.

이게 사용자 정의 변수다.

말그대로 내가 변수를 생성해 사용하는 방법이 되는것이고

여기에 값을 넣어주는 방법으로는 SET과 := 가 있다.

SET은 함수에서처럼 SET @id = 0 이런식으로 사용할 수 있다.

하지만 SET을 제외한 명령문에서는 = 가 비교연산자로 취급을 받기 때문에 :=로 값을 대입해야 한다.

@id:=0  이런식으로.

물론 SET에서도 SET @id:=0 이렇게 사용하는것도 가능하다.

 

여기서 저장하는 값에 의해 자료형이 정해지며 Integer, Decimal, Float, Binary, 문자열타입만 취급할 수 있다.

또한 변수를 초기화 하지 않은 경우의 값은 NULL, 자료형은 String 타입이 된다.

 

 

처리과정

 

SELECT fnc_hierarchi() AS id, @level AS level
FROM(
  SELECT @start_with:=0, @id:=@start_with, @level:=0
) vars
  JOIN board
WHERE @id IS NOT NULL

쿼리문에서 일단 이 부분을 먼저 보자면 제일 안쪽 select 에서 start_with를 0으로 잡고 @id는 @start_with를 그대로 받았으니 동일하게 0, @level 역시 0이다.

그럼 함수에서 보면  SET v_parent = @id; 이런 부분이 있다.

이때 @id가 저기 쿼리문에 있는 @id이다.

 

그래서 처음 함수를 호출했을 때 @id는 0이 되어 들어가게 된다.

중간중간에 있는 @level역시 쿼리문에 존재하는 level의 값이 들어간다.

 

함수 위에서부터 쭉 내려가보면

v_parent과 v_id에 값을 SET 해주고

IF문에서는 @id가 null이 아니기 때문에 넘어가게 된다.

 

그럼 이제 LOOP를 타게 되는데

테이블에서 상위 글번호를 의미하는 boardUpperNo와 v_parent가 동일하고 boardNo가 v_id인 -1보다 큰 데이터를 조건으로 찾고 있으므로 제일 작은 1번 글의 boardNo 가 @id로 복사된다.

 

그럼 여기까지 각 변수들 값을 확인해보면

@id = 1,  v_parent = 0, v_id = -1, level = 0이다.

 

LOOP 안에 있는 IF문을 보면 id가 null이 아니거나 v_parent = @start_with 인 조건인데

id는 null이 아니고 v_parent는 0이기 때문에 조건에 만족한다.

 

그래서 level + 1로 level이 1이 되고 그대로 @id를 리턴한다.

 

여기까지의 값들은 

@id = 1, level = 1 이다.

 

 

2번째 호출

 

다시 함수가 호출이 될때 새로 @id가 0이 들어오는 것이 아닌 1이 들어오게 되고

그럼 v_parent = 1, v_id = -1이 된다.

 

첫 IF문은 역시 뛰어넘게 되고 LOOP에 들어가 select에서 조회를 하는데

boardUpperNo = 1인것이 없기 때문에 null이 되고 @id는 null을 갖게 된다.

 

그럼 다음 if문에서 @id는 null이고 v_parent = 1이니까 0인 @start_with와 다르기 때문에 false로 빠지게 된다.

 

여기서 level을 감소시킨 뒤에 v_id = boardNo, v_parent = boardUpperNo

이렇게 값을 넣어주게 되는데

WHERE boardNo = v_parent이기 때문에 v_id가 v_parent가 되고 해당 데이터의 upperNo가 v_parent가 된다.

그래서 v_id = 1, v_parent는 0이 된다.

 

여기서 LOOP를 끝내는 조건이 없기 때문에 함수 제일 위로 올라가는 것이 아닌 LOOP문 제일 위로 올라가게 되고

select에서 이 값을 그대로 갖고 처리한다.

그럼 UpperNo = v_parent는 0이니 만족하고 boardNo > 1 이기 때문에 바로 다음 데이터인 2번 글의 boardNo가 @id에 복사되게 되고 다음 if문도 만족해 @id = 2, @level = 1이 된다.

 

이렇게 설명한 이유는 처리과정때문인데

처음 받아서 첫 글을 확인해 @id에 복사한 뒤에 다시 함수가 호출되었을때 하위글을 찾는 과정이기 때문이다.

 

만약 이 두번째 호출에서 1번글의 하위글이 있었다면?

LOOP에 들어와 첫 조회쿼리에서 boardUpperNo = 1 and boardNo > -1이기 때문에

하위 글이 @id에 복사되게 되었을 것이다.

 

근데 존재하지 않았기 때문에 null이 되었고 조건문 또한 통과하지 못해 LOOP에 갇히게 되며

그럼 하위 글이 없다고 봐야하므로 level을 다시 감소시키는 것이다.

그리고 마지막에 현재 @id보다 큰 수를 갖는 boardNo를 찾아야 하기 때문에 v_id에 boardNo를 넣어주고

@id가 boardNo인 데이터는 UpperNo가 설계한 제일 default 값일 것이므로 v_parent에 넣어 다음 원글을 찾을 수 있도록 하는것이다.


다시 처리 순서를 정리.

1~84번글까지 존재하고 답글이 달려있는 글은 위 이미지의 데이터만 존재한다고 가정.

그럼 처음 1번글에 대해 조회하고 @id에 boardNo를 넣어준다.

 

boardNo = 1,  @id = 1

 

그리고 하위데이터인 답글이 존재하는지 확인

 

where boardUpperNo = 1       == false

 

존재하지 않기 때문에 다음글을 조회

 

boardNo = 1, @id = 1

boardNo = 2, @id = 2

 

이 데이터 역시 하위데이터가 존재하는지 확인

 

where boardUpperNo = 2        == false

 

다음데이터 조회

 

boardNo = 1, @id = 1

boardNo = 2, @id = 2

boardNo = 3, @id = 3

......

boardNo = 75, @id = 75

 

이렇게 75번글까지 계속 반복.

 

하위데이터 확인

 

where boardUpperNo = 75     == true

이때 조회된 데이터는 75보다 큰 데이터중 가장 작은 데이터이기 때문에 76번글을 조회해 @id에 복사.

 

boardNo = 75, @id = 75

boardNo = 76, @id = 76

 

동일하게 하위 데이터 조회

 

where boardUpperNo = 77     == true

 

 

boardNo = 75, @id = 75

boardNo = 76, @id = 76

boardNo = 77, @id = 77

boardNo = 78, @id = 78

 

78번까지 이런식으로 조회하고 이 하위데이터는 없기 때문에 다시 상위로 이동.

 

where boardUpperNo = 77     == false

 

false이기 때문에 다시 한번 더 상위로 이동

 

where boardUpperNo = 76     == true

 

boardNo = 79, @id = 79

....

 

이런 순서로 계속 쌓아나간다.

 

그래서 @id가

1, 2, 3 ..... 75, 76, 77, 78, 79, 80, 81, 82, 84, 83

이렇게 쌓여나가게 되고 이걸 기준으로 정렬되어 출력되기 때문에 계층형이 완성된다.

 

 

여기까지는 잘 해결했으나 보통의 게시판처럼 마지막에 작성한 글이 제일 위에 있도록 내림차순 정렬을 하게 되면

이 계층형이 틀어지는 문제가 발생했다.

 

추가한건 쿼리문에서 ORDER BY boardGroupNo desc 한줄.

그래서 id도 다시 정렬하게 해줘야 하나? 라는 생각에

ORDER BY boardGroupNo desc, id asc

이렇게 바꿔봤더니

이번에는 80번 글이 중간에 들어가버린다..

너무 자연스러운 위치에 들어가서 속을뻔..........

 

그래서 생각을 조금 바꿔봤다.

아예 순서를 정해놓고 정렬하면 될거같은데??

 

그래서 함수랑 쿼리를 좀 수정했다.

-- 함수

DROP FUNCTION IF EXISTS fnc_hierarchi;

DELIMITER $$
CREATE FUNCTION fnc_hierarchi() RETURN INT 
NOT DETERMINISTIC 
READS SQL DATA 
BEGIN
  DECLARE v_id int;
  DECLARE v_parent int;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;
  
  SET v_parent = @id;
  SET v_id = -1;
  
  IF @id IS NULL THEN
    RETURN NULL;
  END IF;
  
  LOOP
  
  SELECT MIN(boardNo)
    INTO @id
    FROM board
    WHERE boardUpperNo = v_parent
      AND boardNo > v_id;
  
  IF(@id IS NOT NULL) OR (v_parent = @start_with) THEN
    SET @level = @level + 1;
    SET @step = @step + 1;
    RETURN @id;
  END IF;
  
  SET @level := @level - 1;
  
  SELECT boardNo, boardUpperNo
    INTO v_id, v_parent
  FROM board
  WHERE boardNo = v_parent;
  
  END LOOP;
  
END $$

DELIMITER;

-- Query

SELECT b.boardNo
  , CASE WHEN LEVEL-1 > 0 THEN CONCAT(CONCAT(REPEAT('   ', level-1),'ㄴ'), b.boardTitle)
      ELSE b.boardTitle
    END AS boardTitle
  , b.boardUpperNo
  , b.boardGroupNo
FROM(
  SELECT fnc_hierarchi() AS id
    , @level AS level, @step AS step
  FROM(
    SELECT @start_with:=0
      , @id=@start_with
      , @level:=0
      , @step:=0
  ) vars
    JOIN board
  WHERE @id IS NOT NULL
) fnc
  JOIN board b ON fnc.id = b.boardNo
  ORDER BY boardGroupNo desc, step asc;

 

@id가 들어갈때마다 step역시 하나씩 증가해 들어가게 되기 때문에

출력 순서를 그대로 잡아주게 된다.

만약 함수를 사용하지 않고 step 컬럼을 만들어 처리한다면 데이터를 넣어줄때마다 수정을 해야하는 상황이 발생하지만 함수는 쿼리문에서 호출하면서 처리해주니까 그런 문제가 없어서 좀 더 낫다고 생각한다.

내림차순으로 조회하지 않는 경우는 step을 굳이 사용할 필요가 없겠지만

내림차순으로 조회해야 한다면 step을 사용하는것이 좋은것 같다.

물론 다른방법도 있겠지만 아직까지는 이 방법 말고는 딱히 생각이 안난다..................................

 

 

방법 2. 재귀

함수 이외의 방법으로 제일 많이 언급된 방법이 아마 재귀쿼리인것 같다.

WITH RECURSIVE board_CTE AS (
  SELECT boardNo
      , boardTitle
      , boardUpperNo
      , boardIndent
      , CAST(boardNo AS CHAR(100)) lvl
      , boardGroupNo
  FROM board
  WHERE boardUpperNo = 0
  UNION ALL
  SELECT b.boardNo
      , b.boardTitle
      , b.boardUpperNo
      , b.boardIndent
      , CONCAT(c.lvl, ',', b.boardNo) lvl
      , b.boardGroupNo
  FROM board b
    INNER JOIN board_CTE c
    ON b.boardUpperNo = c.boardNo
)
SELECT boardNo
    , CONCAT(REPEAT('    ', boardIndent), '', boardTitle) AS boardTitle
    , boardUpperNo
    , boardIndent
    , lvl
    , boardGroupNo
FROM board_CTE
ORDER BY boardGroupNo desc, lvl;

 

이 쿼리문의 경우 시작이 recursive 바깥 select가 시작지점이다.

from board_CTE로 recursive에 들어가게 된다.

 

CTE(common table expression)은 해당 SQL문 내에서만 존재하는 일시적인 테이블(결과의 집합)을 말한다.

WITH는 CTE를 생성하는 문법이고 RECURSIVE CTE는 서브쿼리에서 스스로를 참조하는 CTE이다.

 

이 안에서는 UNION으로 구분된 2파트로 나누어 진다.

첫 SELECT는 최초 행을 반환하고 두번째 SELECT는 추가행을 반환한다.

그리고 두번째 SELECT문이 더이상 행을 생성하지 않을 때 재귀가 끝나게 된다.

 

그럼 여기서 첫 SELECT문은 답글을 제외한 모든 원글의 데이터가 된다.

두번째 SELECT문은 board와 CTE를 조인해 UpperNo = boardNo를 만족하는 데이터를 찾아옴으로써

답글의 원글을 찾게 된다.

 

참고 데이터로 보자면 76번글은 UpperNo로 75를 갖고 있다.

그럼 조인에서 b.boardUpperNo(75) = c.boardNo(75) 이렇게 되고

조회되는 데이터는

 

b.boardNo(76)

, b.boardTitle(RE: 제목 75)

, b.boardUpperNo(75)

, b.boardIndent(1)

, lvl(75,76)

, b.boardGroupNo(75)

 

이렇게 된다.

기존 데이터와 다른부분이 lvl뿐이다.

 

그럼 76의 하위글인 77번글은?

76의 lvl을 그대로 가져와 lvl(75,76,77)

이렇게 된다.

 

이렇게 모든 데이터에 대한 조회를 하게 되는데

원글의 경우 upperNo가 0으로 되어있기 때문에 따로 조인이 일어나지 않아 가져오는 데이터가 없게 되고

답글들만 조회해 참조하고 있는 데이터의 No와 합쳐 lvl 을 생성하게 되는 구조다.

 

그럼 데이터는 아래와 같이 나오게 된다.

lvl에서는 최상위인 원글부터 그 아래 답글까지 다 갖게 해서 이걸로 정렬하게 된다.

그래서 내림차순으로 정렬하는 경우는 ORDER BY boardGroupNo desc, lvl asc로 처리해주면 되고

오름차순의 경우 ORDER BY boardGroupNo, lvl로 처리해주면 된다.

 

 

 

Oracle에서 start with ~ connect by 를 사용해보고 난 뒤여서 그런지

이 두방법 다 복잡해보이고 효율도 좋아보이지 않는다..

그래도 이전에 했던 방법처럼 step 컬럼으로 순서 조정하면서 찾아다가 수정하고 등록하고 하는 방법보다는 나은것 같아서 이렇게 계층에 제한이 없는 조건이라면 이 방법이 더 나은것 같긴 하다.

하지만 요즘 사이트 댓글들 보면 그냥 한계층만 가능하도록 하는 경우도 많아서 그럴때는 또 굳이 이렇게까지는 쓰지 않아도 될것같다.

그때그때 알아서 잘 사용할 수 있게 미리 배워둔다는 느낌으로 알아두면 좋을듯!!!!

 

 

 

Reference

  • stored routine(READS SQL DATA)

 

 

MySQL Functions 생성, Stored 루틴 및 트리거 바이너리 로깅

먼저 function 을 만들려는 데 다음과 같은 에러가 발생했다. ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you..

blog.pages.kr

 

  • 함수 문법
 

[Mysql]Function 과 Procedure(함수와 프로시저) -1

안녕하세요. 오늘은 Function과 Procedure을 공부하겠습니다. 1편에서는 함수에 대해 잘 알아볼께요. Fu...

blog.naver.com

 

 

DETERMINISTIC , NOT DETERMINISTIC

함수나 프로시저 같은 Object 생성시 사용할 수 있는 옵션입니다. default값은 NOT DETERMINISTIC입니다. 아무것도 입력하지 않으면 자동으로 NOT DETERMINISTIC이 설정됩니다. 동일한 입력 매개 변수에 대해

bae9086.tistory.com

 

  • WITH RECURSIVE

 

 

MySQL WITH RECURSIVE

CTE와 재귀적 CTE

velog.io

 

 

  • 계층형 쿼리 참고
 

GNUJAVA

MySQL 에는 안타깝게도 Oracle 의 start with, connect by 를 지원하는 함수가 없다...  때문에 아래와 같이 function 을 만들어서 사용한다. 예제 테이블) test.servers_group create table test.servers_group  (  group_idx in

www.gnujava.com

 

EC2에 MySQL을 설치해뒀는데 데스크탑에서 볼 수 있게 셋팅하고 싶었다.

 

일단 EC2 접속 후

  sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

이렇게 mysqld.cnf 파일을 열어준다.

sudo를 사용하지 않았더니 권한때문에 저장이 안된다.

처음부터 이렇게 접근하는게 맞는것 같다.

 

조금만 아래로 내려보면 이렇게 bind-address 와 mysqlx-bind-address 를 볼 수 있다.

보통 다른글 포스팅 보면 bind-address 밖에 없는데 나는 두개..

이 두개를 0.0.0.0으로 바꿔주거나 앞에 # 을 붙여 주석처리를 해준다.

저장하고 종료.

 

그리고 mysql 에 접속한다.

 

접속해서 외부접속 허용 sql을 입력한다.

mysql> create user 'con'@'%' identified by '1111';
mysql> grant all privileges on *.* to 'con'@'%' with grant option;

con부분은 유저이름을 넣어주면 되고 identified by 뒤는 비밀번호다.

생성해주고 접속 권한을 변경해주는 것이다.

 

많은 포스팅을 본것은 아니지만 보통 이렇게 생성하는거에 대해 적어주신 분은 없다.

보통은

mysql> grant all privilage on *.* to 'root'@'%' identified by '1111';
mysql> flush privileges;

이렇게 많이 알려주셨다.

근데 접속 권한 쿼리부터 잘못된 쿼리라는 오류가 발생해 다른 방법을 찾게된 것이다.

보통 이렇게 써주신 분들 포스팅은 ubuntu 16.04에 mysql 5.7이던데 아마 버전차이가 아닐까 싶다.

 

현재 ubuntu 20.04에 mysql 8.0.27이기 때문에 차이가 있을 것 같다.

 

그래서 첫번째 방법으로 시도해서 해결되었다.

 

 

그래서 저 방법을 통해 접속 권한을 변경했다면 mysql을 재시작하고 포트를 설정해준다.

sudo service mysql restart
sudo ufw allow out 3306/tcp
sudo ufw allow in 3306/tcp
sudo service mysql restart

ufw allow 는 포트를 설정해주는 것이다. Rules updated 가 나와야 정상적으로 처리가 된것이다.

그리고 EC2 인스턴스에도 보안그룹 인바운드 규칙에 3306을 추가해줘야 한다 꼭!

 

 

여기까지 했다면 workbench를 실행해서 연결을 진행하면 된다.

Hostname에는 EC2 퍼블릭 IP 주소 적으면 된다.

username이랑 Password는 아까 설정하면서 생성한 그 계정으로 하면 끝!

'DB' 카테고리의 다른 글

Oracle Scheduler, MySQL Event Scheduler  (0) 2022.04.21
mysql 계층형 쿼리(함수, 재귀)  (0) 2022.04.04
Oracle과 Tomcat충돌문제 해결  (0) 2020.10.18
Oracle에서 auto_increment  (0) 2020.10.17
Oracle DataType  (0) 2020.10.17

포트번호가 8080으로 겹치기 때문에 포트번호를 변경해줘야 한다.

cmd로 들어가서 

 

sqlplus sys as sysdba

 

비밀번호 입력.

 

EXEC DBMS_XDB.SETHTTPPORT(9090);

포트번호 9090으로 변경

 

SELECT DBMS_XDB.GETHTTPPORT() FROM DUAL;

포트번호 확인.

 

충돌은 나지 않는데 localhost:8080/

이렇게만 접속하면 localhost:8080/apex/f?p=4950이 계속 뜬다?

 

쿠키삭제.........................삭제하니까 정상적으로 서버 잘 돌아간다.

 

그리고 getStarter 실행했을때 %HTTPPORT%를 찾을 수 없다고 하면 오른쪽 클릭해서 속성 들어간다음 대상에 있는 주소로 이동하는데 제일 뒤에 Get_Started.url은 지우고 이동해서 Get_Started 오른쪽 클릭 속성으로 이동.

 

거기서 URL에 있는 %HTTPPORT%지우고 그자리에 9090입력하면 끝!!!!

 

환경변수로 %HTTPPORT% 만들면 된다는 분들도 있는데 일단 난 안됨..

 

 

'DB' 카테고리의 다른 글

Oracle Scheduler, MySQL Event Scheduler  (0) 2022.04.21
mysql 계층형 쿼리(함수, 재귀)  (0) 2022.04.04
EC2에 설치한 MySQL 데스크탑 workbench에서 접근  (0) 2021.11.09
Oracle에서 auto_increment  (0) 2020.10.17
Oracle DataType  (0) 2020.10.17

오라클에는 mysql처럼 auto_increment 기능이 없기 때문에 시퀀스를 생성해서 자동으로 인덱스 값을 증가시키도록 사용해야 한다.

 

예제 테이블로

create table tmp(

idx_tmp number(10),

name varchar(1000)

);

이렇게 만들어주고 idx_tmp에 auto_increment를 사용하기 위해 시퀀스를 생성한다.

 

CREATE SEQUENCE tmp_seq START WITH 1 INCREMENT BY 1 MAXVALUE 100 CYCLE NOCACHE;

 

tmp_seq라는 시퀀스를 생성한다.

 

여기서 사용할 수 있는 키워드.

MINVALUE는 시퀀스가 시작되는 최초의 숫자.

MAXVALUE는 시퀀스가 끝나는 최대 숫자.

INCREMENT BY는 시퀀스가 증가되는 단위

START WITH는 시퀀스 생성이 시작되는 값

NOCACHE는 캐시를 사용하지 않음 사용할 때는 CACHE n 이렇게 사용.

NOORDER는 요청되는 순서대로 값을 생성하지 않음. 병렬서버를 사용할 경우 요청 순서에 따라 정확하게 시퀀스를 생성하기를 원할 때 ORDER로 지정한다. 단일 서버일 경우 이 옵션과 관계 없이 정확히 요청 순서에 따라 시퀀스가 생성된다.

NOCYCLE은 초기값부터 다시 시작하지 않음. CYCLE로 지정하며 MAXVALUE에 도달 했을 때 다시 MINVALUE부터 시작하게 된다.

 

여기서 만든 tmp_seq는 1부터 시작해서 1씩 증가하며 100까지 증가하는 시퀀스이다.

100이 넘어가면 1로 다시 돌아가서 다시 시작한다.

 

INSERT INTO tmp values(tmp_seq.NEXTVAL, 'test1');

시퀀스를 사용한 값을 삽입한다. 이렇게 여러번 반복해서 insert해주면 계속해서 증가하는 값을 확인할 수 있다.

 

무한대값으로 설정하고 싶다면 NOMAXVALUE를 사용하면 된다. 이럴 경우 cycle은 NOCYCLE로 작성하거나 아예 빼면 된다.

 

출처: luji.tistory.com/71

 

[Oracle DB] 오라클 auto_increment (시퀀스)

Oracle 에서는 MySql에 있는 Auto_Increment 기능이 없다. 그렇기에 자동으로 인덱스값을 증가시켜주기 위해서는 시퀀스를 생성해서 사용해야한다. 1) 예제 테이블 생성 create table tmp( idx_tmp number(10), na..

luji.tistory.com

offbyone.tistory.com/239

 

Oracle Sequence 만들기 (일련번호 생성하기)

데이터베이스에서 일련번호를 생성하는 것은 동시성 문제가 발생할 수 있어 간단한 작업이 아닙니다. 그래서 대부분의 데이터베이스에서는 일련번호 생성을 간단히 처리할 수 있는 기능들을 ��

offbyone.tistory.com

 

'DB' 카테고리의 다른 글

Oracle Scheduler, MySQL Event Scheduler  (0) 2022.04.21
mysql 계층형 쿼리(함수, 재귀)  (0) 2022.04.04
EC2에 설치한 MySQL 데스크탑 workbench에서 접근  (0) 2021.11.09
Oracle과 Tomcat충돌문제 해결  (0) 2020.10.18
Oracle DataType  (0) 2020.10.17

+ Recent posts