[MySQL] sql_mode 확인 및 옵션

 

---

 

sql_mode 란?

MySQL에 저장될 데이터에 대한 유효성 검사 범위를 설정하는 시스템 변수이다.

버전별로 sql_mode 기본값이 다르며, 원하는 sql_mode 를 설정하여 원하는 유효성 검사 범위를 설정 할 수 있다.

 

sql_mode 조회 쿼리

아래 쿼리를 통해 현재 sql_mode 를 조회 할 수 있다.

SELECT @@sql_mode;

SHOW VARIABLES LIKE 'sql_mode';

MySQL 버전별 기본 sql_mode

MySQL 5.7 sql_mode 

ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION.

 

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_SUBSTITUTION

 

 

sql_mode 옵션

ONLY_FULL_GROUP_BY
 - HAVING 이나 ORDER BY 절이 GROUP BY 절에서 명명 되지 않았거나, 집계되지 않은 열을 참조하는 쿼리를 허용하지 않는다.
 - 잘못된 GROUP BY 사용 방지를 위한 목적이다.
 - MySQL 5.7.5 버전부터 기본 SQL 모드에 적용된다.
 

아래와 같은 쿼리를 조회할 경우 오류가 떨어진다.

GROUP BY 절에 선언되지 않은 c.charname 을 가져오려고 하기때문에 문제가 발생한다.

SELECT g.guildid, c.charname, MAX(c.level)
FROM guild AS g
INNER JOIN character AS c ON g.guildid = c.guildid
GROUP BY g.guildname;

 

아래와 같이 GROUP BY 절에 c.charname 을 선언해주면 된다.

SELECT g.guildid, c.charname, MAX(c.level)
FROM guild AS g
INNER JOIN character AS c ON g.guildid = c.guildid
GROUP BY g.guildname, c.charname;

  
STRICT_TRANS_TABLES
 - 비활성화 시, char 나 varchar 컬럼에 데이터를 INSERT 할 때 해당 컬럼 크기보다 큰 데이터를 넣으면 데이터가 잘려 들어간다.

 - 활성화 시, 컬럼 크기보다 큰 데이터를 넣으면 잘려 들어가지 않고 에러가 발생한다.

 - 데이터의 무결성을 체크하는 목적이다.

 - MySQL 5.7 버전부터 기본 SQL 모드에 적용된다.

  
NO_ZERO_IN_DATE
 - '2023-00-10' 혹은 '2023-10-00' 와 같이 월 또는 일 부분이 0 인 날짜를 허용하지 않는다.
 - MySQL 5.7 버전부터 기본 SQL 모드에 적용된다.

  
NO_ZERO_DATE
 - '0000-00-00' 의 날짜를 허용하지 않는다.
 - MySQL 5.7 버전부터 기본 SQL 모드에 적용된다.
 
  
ERROR_FOR_DIVISION_BY_ZERO
 - MOD (N, 0) 혹은 0 으로 나누기 작업(ex. 1/0) 임을 경고한다.
 - 활성화 시, 0 으로 나누면 NULL 이 삽입되며 경고가 노출된다.

 - 비활성화 시, NULL 이 삽입되나 경고가 노출되지 않는다.

 - MySQL 5.7 버전부터 기본 SQL 모드에 적용된다.
 
 
NO_AUTO_CREATE_USER
 - 계정이 존재하지 않는 경우, GRANT 문으로 USER 생성을 허용하지 않으며, CREATE USER 을 사용하여 계정을 생성해야 한다.
 - MySQL 8.0 의 경우, 기본 SQL 모드에 적용된다.
 - GRANT SELECT ON gms.* TO 'test'@'%' IDENTIFIED BY 'PassWord!@34'; 안 됨.

 

아래와 같은 계정 생성문을 허용하지 않는다.

GRANT SELECT ON *.* TO 'TestUser'@'%' IDENTIFIED BY 'PassWord!@34';

 

아래와 같이 CREATE USER 문을 사용하여 계정을 생성할 수 있다.

-- 계정 생성
CREATE USER TestUser'@'%' IDENTIFIED BY 'PassWord!@34';

-- 권한 부여
GRANT SELECT ON *.* TO 'TestUser'@'%';

 
NO_ENGINE_SUBSTITUTION
 - CREATE TABLE 또는 ALTER TABLE 시, 지정한 스토리지 엔진을 사용할 수 없을 경우 경고가 표시되며, 기본 스토리지 엔진이 대신 사용된다.
 - 활성화 시, 원하는 스토리지 엔진을 사용할 수 없는 경우 오류가 발생하며, 테이블 생성이나 수정되지 않는다.

 - 비활성화 시, CREATE TABLE 문은 원하는 엔진을 사용할 수 없을 경우 경고가 표시되며, 기본 엔진으로 생성되며, ALTER TABLE 은 경고가 표시되며, 테이블이 변경되지 않는다.