오라클에선 DB의 PK로 시퀀스 객체를 만들어 간편하게 사용했는데,
MySQL에선 시퀀스 객체를 지원하지 않는다고 한다.
그래서 구현방법을 찾아보니 크게 2가지가 있다.
AUTO_INCREMENT
하나는 테이블 생성시, PK에 AUTO_INCREMENT 옵션을 넣는것이다.
CREATE TABLE '테이블명'(
'컬럼명' INT AUTO_INCREMENT PRIMARY KEY
)구현이 매우 간단하다.
현재 AUTO_INCREMENT 값을 확인하려면 다음과 같이 질의한다.
SHOW TABLE STATUS WHERE name = '테이블명';AUTO_INCREMENT 값을 초기화하려면, 다음과 같이 입력해준다.
ALTER TABLE 테이블명 AUTO_INCREMENT = 1;시퀀스 테이블 만들기
내가 시도해볼 방법은 시퀀스 자체를 테이블에 데이터로 저장하는 방법이다.
MySQL 문법에 익숙해지기 위해 조금 더 복잡해보이는 방법을 선택했다.
테이블 생성
CREATE TABLE SEQUENCES(
SEQ_NAME VARCHAR(32) PRIMARY KEY,
CURRVAL BIGINT UNSIGNED
);SEQ_NAME : 시퀀스 이름
CURRVAL : 시퀀스의 현재 값
CREATE_SEQUENCE 프로시저 생성
Oracle에서 사용하던 시퀀스 생성문을 프로시저로 구현해준다.
CREATE PROCEDURE `CREATE_SEQUENCE` ( IN NAME VARCHAR(32) )
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
DELETE FROM SEQUENCES WHERE SEQ_NAME = NAME;
INSERT INTO SEQUENCES VALUES(NAME, 0);
END
NEXTVAL 함수 생성
Oracle에서 사용하던 시퀀스 값을 증가시키고, 현재 시퀀스값을 가져오는 함수를 만들어준다.
CREATE FUNCTION `NEXTVAL` ( NAME VARCHAR(32) )
RETURNS BIGINT UNSIGNED
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
DECLARE CVAL BIGINT UNSIGNED;
UPDATE SEQUENCES
SET CURRVAL = CURRVAL + 1
WHERE SEQ_NAME = NAME;
SELECT CURRVAL INTO CVAL
FROM SEQUENCES
WHERE SEQ_NAME = NAME;
RETURN CVAL;
END시퀀스 사용하기
시퀀스 생성은 다음과 같이 프로시저를 호출하면 된다.
CALL CREATE_SEQUENCE('시퀀스명'); 시퀀스 사용은 다음과 같이 하면 된다.
INSERT INTO '테이블명'
(PK, COL1, ...)
VALUES
( NEXTVAL('시퀀스명'), 값 , ...)시행착오
굳이 ORACLE SQL DEVELOPER말고 MYSQL WORKBENCH 쓰면 이고생 안해도 된다. 그냥 맞는 툴 쓰자.
프로시저, 함수가 생성이 안돼네?
CREATE PROCEDURE `CREATE_SEQUENCE` ( IN NAME VARCHAR(32) )
BEGIN
DELETE FROM SEQUENCES WHERE SEQ_NAME = NAME;
INSERT INTO SEQUENCES VALUES(NAME, 0);
END;
CREATE FUNCTION `NEXTVAL` ( NAME VARCHAR(32) )
RETURNS BIGINT UNSIGNED
BEGIN
DECLARE CVAL BIGINT UNSIGNED;
UPDATE SEQUENCES
SET CURRVAL = CURRVAL + 1
WHERE SEQ_NAME = NAME;
SELECT CURRVAL INTO CVAL
FROM SEQUENCES
WHERE SEQ_NAME = NAME;
RETURN CVAL;
END;맨 처음, 이렇게 입력해두고 SQL Developer에서 Ctrl+Enter을 눌러서 SQL문을 실행시키니 다음과 같이 에러가 났다.
오류 보고 -
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE FUNCTION `NEXTVAL` ( NAME VARCHAR(32) )
RETURNS BIGINT UNSIGNED
BEGIN' at line 7아니 문법상 에러가 날 부분이 없는데 어째서..?
DELIMITER를 사용하지 않아, 세미콜론 인식을 잘못해 SQL문이 끊어져서 그런건가 싶어서 DELIMITER $$ 를 추가해 봤는데도, 에러가 났다.
이것때문에 계속 이것저것 수정해보면서 한 2시간 삽질 했는데 결론은 어이없었다.
저 SQL 두개를 하나의 묶음으로 실행시켜서 그런거였다.
SQL 하나하나 드래그 해서 선택 한 뒤, 따로따로 실행시키면 아주 잘 된다...
혹시라도 나처럼 습관적으로 ctrl + enter 쓰시는 분들 주의...
그래도 함수가 생성이 안돼???
오류 보고 - log_bin_trust_function_creators
오류 보고 -
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)함수 생성시에 함수의 특성을 명시해야 하는데( ex) DETERMINISTIC, MODIFIES SQL DATA 등등... ) 하지 않아서 발생한 에러였다.
함수가 어떤 동작을 할 지 모르니, 보안상의 이유로 해당 함수의 실행 자체를 막는것이였다.
그럼 저걸 추가하면 잘 되겠지? ㅎㅎ
어림도 없지!!
오류 보고 -
You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
인터넷에 오류 해결법을 쳐보니, root 유저로 다음 명령어를 실행하면 된다.
SET GLOBAL log_bin_trust_function_creators=ON; -- 기본값은 OFF다만 이 명령어를 실행하면 다음과 같은 경고문이 뜬다.
'@@log_bin_trust_function_creators' is deprecated and will be removed in a future release.그래서 아무래도 권장되지 않는 방법이다보니, 슈퍼 권한을 부여하려 했다.
GRANT SUPER ON *.* TO 'SPRINGBOOT'@'LOCALHOST';
FLUSH PRIVILEGES;아니 근데 얘도
The SUPER privilege identifier is deprecated이런 경고가 발생한다;;
그리고 슈퍼 권한도 부여되지 않는다. ( 어째서?? )
여기도 이것저것 찾아보며 한 1시간을 더 삽질했다...
어느쪽이든 엄청 찝찝했지만. 결국 당장 문제 해결을 위해선 log_bin_trust_function_creators=ON 으로 하는게 맞는 것 같다.
추후 공부를 더 해 볼 부분인 것 같다.
근데 시퀀스 생성이 안돼??
이제 다 만들었으니 프로시저를 호출하려 했는데
CALL CREATE_SEQUENCE('BOARD_SEQ');하니까 갑자기 TO_CHAR(BOARD_SEQ) 뭐시기 에러가 뜨면서 안된다...
아니 난 TO_CHAR이라는 오라클 문법 자체를 쓴 적도 없는데 왜 이럴까...
결국 문제 해결은 못했고, 원인으로 제일 강하게 추측되는 부분은
이게 ORACLE SQL DEVELOPER라 SQL문 실행할 때 자동으로 변환되는 부분이 있을 것이다!
라는 추측이였다.
그냥 MYSQL WORKBENCH 깔아서 SQL 실행해보니 정말 잘되더라...
절대 MYSQL을 ORACLE SQL DEVELOPER에서 굳이 실행하지 말자
