MySQL 서버에는 sql_mode 라는 시스템 변수가 있습니다. 이 변수를 이용하면 각기 다른 클라이언트에 다른 sql 모드를 적용하는 것이 가능합니다.
sql_mode 설정은 서버 운영 요구사항에 맞게 global 하게 설정할 수도 있고 각각의 애플리케이션 요구사항에 맞도록 session에 설정하는 것도 가능합니다.
Mode는 MySQL이 지원하는 SQL 문법과 데이터 유효성 검사에 영향을 주게 됩니다. 예를 들어 GROPU BY를 할 때 SELECT문에 unique한 column이 있더라도 SELECT가 가능하게 되서 데이터 조회가 가능하게 만드는 것도 가능합니다.
sql_mode 설정 방법
서버가 시작할 때 sql_mode를 설정하고 싶다면 command line에서
--sql-mode=“modes”설정을 사용하거나,
my.cnf(UNIX 운영체제) 또는 my.ini(Windows 운영체제).modes 와 같은 설정 파일에 콤마로 모드들을 구별해서
sql-mode=“modes”를 사용하면 됩니다.
sql_mode를 명시적으로 지우고 싶을 땐 빈 문자열을 설정해주면 됩니다. runtime 에서 sql_mode를 변경하고 싶다면 SET 문법을 사용하면 됩니다.
SET GLOBAL sql_mode = ‘modes’;
SET SESSION sql_mode = ‘modes’;기본 MySQL Mode
MySQL 8.0에서 기본 sql_mode는 다음의 모드들이 포함되어 있습니다.
ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE,
NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUITIONONLY_FULL_GROUP_BY
select 목록, HAVING 조건 또는 ORDER BY 목록이 GROUP BY 절에 이름이 지정되지 않았거나 GROPU BY column에 primary key나 unique key 처럼 고유하게 존재하여 집계되지 않는 column을 참조하면 쿼리를 거부합니다.
STRICT_TRANS_TABLES
트랜잭션 스토리지 엔진에 대해 엄격한 SQL Mode를 활성화하고 가능한 경우 비트랜잭션 스토리지 엔진을 활성화합니다.
Strict(엄격) SQL Mode
엄격 모드는 INSERT나 UPDATE 처럼 데이터 변경 문법에서 유효하지 않거나 없는 값을 어떻게 MySQL에서 처리할지 제어합니다.
값은 여러 이유로 인해 유효하지 않도록 하는 것이 가능합니다.
예를 들어, column에 대해 틀린 데이터 타입을 가질 경우나 범위를 벗어난 경우가 해당됩니다.
명시적으로 DEFAULT가 정의되어 있지 않은 non-NULL column에 새로운 row를 insert 하는데 값이 없을 때 값이 누락됩니다(NULL column인 경우 값이 누락되면 NULL이 insert 됩니다).
엄격 모드는 또한 CREATE TABLE과 같은 DDL 문법에 영향을 줍니다.
NO_ZERO_IN_DATE
NO_ZERO_IN_DATE 모드는 서버에서 year 부분이 0이 아닌지 또는 day 부분이 0인 날짜를 허용하는지 아닌지에 영향을 줍니다
(이 모드는 ‘’0000–00–00’이 아닌 2010–00–01’ 또는 ‘2010–01–00’과 같은 날짜에 영향을 줍니다. 서버에서 ‘0000–00–00’ 을 허용할지 말지 제어하기 위해선 NO_ZERO_DATE 모드를 사용해야 합니다).
NO_ZERO_IN_DATE의 효과는 strict SQL mode가 활성화 되었는지에 따릅니다.
- 만약 이 모드가 비활성화일 경우, 0이 포함된 날짜가 허용되고 insert시 경고를 내보내지 않습니다.
- 만약 이 모드가 횔성화일 경우, 0이 포함된 날짜는 ‘0000–00–00’으로 insert 되고 경고를 내보냅니다.
- 만약 이 모드와 엄격 모드 둘 다 활성화일 경우, 0이 포함된 날짜는 허용되지 않으며 insert할 때 IGNORE을 주지 않는 한 error를 내보냅니다. INSERT IGNORE가 UPDATE IGNORE을 할 경우, 0이 포함된 날짜는 ‘0000–00–00’으로 insert 되고 경고를 내보냅니다.
MySQL 8.0에서 NO_ZERO_IN_DATE는 deprecated 되었습니다.
NO_ZERO_IN_DATE는 엄격모드에 속하지 않으며, 엄격 모드와 함께 사용해야 하고 기본적으로 활성화되어 있습니다.
엄격 모드를 활성화 하지 않고 NO_ZERO_IN_DATE를 활성화하거나 그 반대의 경우에도 경고를 내보냅니다.
NO_ZERO_DATE
NO_ZERO_DATE 모드는 서버에서 ‘0000–00–00’을 유효한 날짜로 허가할지말지에 영향을 줍니다. 이 모드는 엄격 SQL 모드가 활성화 되었느냐에 따라 효과가 달라집니다.
- 만약 이 모드가 비활성화일 경우, ‘0000–00–00’은 허용되고 insert시 경고를 내보내지 않습니다.
- 만약 이 모드가 활성화일 경우, ‘0000–00–00’은 허용되고 insert시 경고를 내보냅니다.
- 만약 이 모드와 엄격 모드 둘 다 활성화일 경우, ‘0000–00–00’은 허용되지 않고 insert시 IGNORE을 주지 않는 한 error를 내보냅니다.
INSERT IGNORE과 UPDATE IGNORE을 할 경우, ‘0000-00–00’은 허용되고 insert시 경고를 내보냅니다.
MySQL 8.0에서 NO_ZERO_DATE는 deprecated 되었습니다. NO_ZERO_DATE는 엄격 모드에 속하지 않으며, 엄격 모드와 함께 사용해야 하고 기본적으로 활성화되어 있습니다.
엄격 모드를 활성화 하지 않고 NO_ZERO_DATE를 활성화하거나 그 반대의 경우에도 경고를 내보냅니다.
ERROR_FOR_DIVISION_BY_ZERO
MOD(N,0)을 포함하여 0으로 나누기를 할 때의 처리에 영향을 줍니다. 데이터 변경(INSERT, UPDATE)의 경우 엄격 SQL 모드가 활성화 되었는지에 따라 효과가 달라집니다.
- 만약 이 모드가 비활성화일 경우, 0으로 나누기 insert시 NULL 값이 들어가고 경고를 하지 않습니다.
- 만약 이 모드가 활성화일 경우, 0으로 나누기 insert시 NULL 값이 들어가고 경고를 내보냅니다.
- 만약 이 모드가 엄격모드와 함께 활성화일 경우, 0으로 나누기는 IGNORE을 주지 않는 한 error을 내보냅니다.
INSERT IGNORE과 UPDATE IGNORE을 할 경우, 0으로 나누기를 insert하는 경우 NULL 값이 들어가고 경고를 내보냅니다.
SELECT의 경우, 0으로 나누기시 NULL을 반환합니다.
ERROR_FOR_DIVISION_BY_ZERO를 활성화하면 엄격 모드 활성화 여부에 관계 없이 경고를 내보냅니다.
MySQL 8.0에서 ERROR_FOR_DIVISION_BY_ZERO는 deprecated 되었습니다 .
ERROR_FOR_DIVISION_BY_ZERO는 엄격 모드에 속하지 않으며, 엄격 모드와 함께 사용해야 하고 기본적으로 활성화되어 있습니다.
엄격 모드를 활성화 하지 않고 ERROR_FOR_DIVISION_BY_ZERO를 활성화하거나 그 반대의 경우에도 경고를 내보냅니다.
NO_ENGINE_SUBSTITUITION
CREATE TABLE 또는 ALTER TABLE과 같은 명령문을 비활성화하거나 컴파일되지 않은 storage engine을 지정할시 기본 storage engine으로 자동으로 대체할지를 제어합니다.
기본적으로 NO_ENGINE_SUBSTITUTION은 활성화되어 있습니다.
왜냐하면 storage engine은 runtime일 때 연결될 수 있으므로 사용할 수 없는 engine은 다음과 같은 방식으로 처리됩니다.
- NO_ENGINE_SUBSTITUTION이 비활성화일 경우, CREATE TABLE에 대해 기본 engine이 사용되며 원하는 engine은 사용할 수 없다면 경고를 생성합니다. ALTER TABLE의 경우, 경고를 내보내고 테이블은 변경되지 않습니다.
- NO_ENGINE_SUBSTITUTION이 활성화일 경우, 만약 원하는 engine을 사용할 수 없다면 error를 발생시키고 테이블은 생성되거나 수정되지 않습니다.
