[MySQL] sql_mode 각 옵션에 대한 설명 및 엄격모드
주로 데이터를 입력할때의 특징에 대한것으로 엄격하게 관리를 할거냐 아니면 일부 문제가 있어도 문제가 될것이 크지 않기 때문에 생산성 위주로 할것이냐에 대한것으로 보시면 됩니다. 데이터의 문제는 입력하는 프로그램 단계에서 검증을 하는 방법이 있겠지만 최종적으로 입력되는 데이터베이스에서 관리를 하게 되면 더 완벽하게 됩니다. 문제는 어떤것이든 쉽게 얻어 지는 것은 없으니 그에 대한 댓가가 있습니다. 이 댓가가 어느쪽이 더 이익이 되느냐의 문제 이겠습니다. 흔히 이야기하는 삽으로 할일을 포크레인으로 하는 것은 미련한 짓입니다.
sql_mode 영구적으로 설정
[mysqld]
sql_mode="설정값,설정값";
아래는 실행중에 설정하는 값으로 영구적이지 않다.
set sql_mode = '설정값';
옵션값의 의미
sql_mode는 엄격모드와 결합시 더 강하게 반응을 하게 되어 있기 때문에 엄격모드도 같이 확인하는 것이 좋습니다.
우선 활성화하고 경고정도만 받으려면 엄격모드는 비활성화 하면 됩니다.
- 문제 없이 사용하려면 sql_mode 값 전체를 빈값으로 두면 됩니다.
- sql을 엄격하게 사용하려면 엄격모드를 true 하고 sql_mode도 true 하면 sql 실행 되지 않습니다.
엄격모드
아래 문제가 있다면 엄격모드를 사용하는 것이 좋다. 반대로 왠만하면 데이터는 입력이 되게 하고 싶으면 엄격모드를 사용하는것이 좋다. 데이터의 무결성에 관심이 많다면 켜는게 좋은데 안되는것이 많을 수 있다는 것은 개발 시간은 오래 걸릴 수 있다는 의미도 된다.
- CHAR, VARCHAR 컬럼 사이즈보다 큰 데이타가 입력됐을때 안된다
- unsigned 컬럼에 -1 이 입력되면 0으로 저장된다 ``` 아래가 들어가면 strict mode 가 되는 것이다. 그렇기 때문에 해제하고 싶으면 /etc/my.cnf 에서 삭제 하면 된다. STRICT_ALL_TABLES STRICT_TRANS_TABLES
확인은 show variables like 'sql_mode'; 명령으로 가능하다.
MySQL8에서 기본 모드는 아래와 같습니다.
ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE,
NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUITION
```
ERROR_FOR_DIVISION_BY_ZERO
비활성: 0으로 나누기 insert시 NULL 값이 들어가고 경고를 하지 않습니다.
활 성: 0으로 나누기 insert시 NULL 값이 들어가고 경고를 내보냅니다.
만약 이 모드가 엄격모드와 함께 활성화일 경우, 0으로 나누기는 IGNORE을 주지 않는 한 error을 내보냅니다.
NO_ENGINE_SUBSTITUTION
NO_ENGINE_SUBSTITUTION은 활성화가 기본설정 입니다.
비활성: CREATE TABLE에 대해 기본 engine이 사용되며 원하는 engine은 사용할 수 없다면 경고를 생성합니다. ALTER TABLE의 경우, 경고를 내보내고 테이블은 변경되지 않습니다.
활 성: 원하는 engine을 사용할 수 없다면 error를 발생시키고 테이블은 생성되거나 수정되지 않습니다.
PIPES_AS_CONCAT
문자열 연결 연산자를 || 오라클과 동일하게 사용하기 위한 편의성
REAL_AS_FLOAT
REAL 을 FLOAT 의 동의어로 취급합니다. 기본적으로 MySQL은 REAL 을 DOUBLE 의 동의어로 취급합니다.
https://mariadb.com/docs/skysql-dbaas/ref/xpand/sql-modes/REAL_AS_FLOAT/
NO_ZERO_IN_DATE
false이면 2023?01?00 같은것이 가능 합니다. 이것은 year, day 부분에 0으로 들어가는 것을 허용 할것이냐에 대한 것입니다.
NO_ZERO_DATE
true이면 0000?00?00를 허용하고 경고가 발생 합니다. 엄격모드 일때는 오류발생 합니다. 즉, false 이면 사용가능 합니다.
STRICT_ALL_TABLES
일반적으로 MySQL에서는 저장하려는 값의 길이가 칼럼의 길이보다 더 긴 경우라 하더라도 에러가 발생하지 않고,
칼럼의 길이를 초과하는 부분은 버리고 남은 부분만 저장한다. (경고 메시지 발생)
이 옵션을 주면, 칼럼의 정해진 길이보다 큰 값을 저장할때 경고가 아닌 에러를 발생시킨다.
STRICT_TRANS_TABLES
MySQL 서버는 칼럼의 타입과 호환되지 않는 값을 저장할때 최대한 바꿔서 저장한다.
이 옵션을 주면, 원하지 않는 데이터 타입의 변환이 필요할때 에러를 발생시킨다.
ANSI_QUOTES
홀따옴표만 문자열 값 표기로 사용할 수 있고, 쌍따옴표는 칼럼명이나 테이블명과 같은 식별자를 표기하는 데만 사용할 수 있다.
ONLY_FULL_GROUP_BY
GROUP BY 절에 포함되지 않은 칼럼을 집합함수로 감싸야만 사용할 수 있게 해준다.
PAD_CHAR_TO_FULL_LENGTH
CHAR 타입의 칼럼값을 가져오는 경우, 뒤쪽의 공백이 제거되지 않고 반환되도록 한다.
IGNORE_SPACE
프로시저나 함수명과 괄호 사이의 공백을 무시한다.
ANSI
위에서 언급된 여러 옵션을 조합해 MySQL 서버가 최대한 SQL 표준에 맞게 동작하게 만들어준다.
엄격모드 해제
https://www.7-star.net/bbs/board.php?bo_table=talk_computer&wr_id=723
참고사이트
http://www.innodbcluster.com/?depth=050107
https://medium.com/@su_bak/mysql%EC%9D%98-sql-mode%EC%99%80-%EA%B8%B0%EB%B3%B8-sql-mode%EB%93%A4%EC%9D%B4-%ED%95%98%EB%8A%94-%EC%97%AD%ED%95%A0-b01c09d84cdc