[Mysql,Mariadb] sql_mode 구성 항목 및 설정

sql_mode: MySQL 및 MariaDB에서의 중요한 설정

 

sql_mode란?

---

• MySQL 또는 MariaDB에서 데이터베이스 서버에 의해 수행되는 SQL 작업의 동작 및 유효성(엄격성)을 정의하는 설정입니다.
  이는 SQL 구문, 데이터 유효성 검사, 오류 처리 등을 다루는 방법을 결정합니다.
  이는 데이터베이스의 호환성 및 동작에 영향을 미칠 수 있는 중요한 설정입니다.

구성 항목(요소)

---

• ANSI_QUOTES
  활성화되면 이중 인용부호 (")는 열 이름 및 테이블과 같은 식별자만 처리하도록 허용합니다.

    SELECT "COLUMN_NAME" FROM "TABLE_NAME"

• STRICT_TRANS_TABLE
  컬럼 유형과 다를 경우 최대한 가깝게 값을 입력할지 여부를 결정합니다. OFF 상태일 때 유형이 다르면 오류가 발생합니다.

• STRICT_ALL_TABLE
STRICT_TRANS_TABLE과 유사하지만 전체 테이블에 엄격성을 적용하며 트랜잭션만이 아닙니다.

• ERROR_FOR_DIVISION_BY_ZERO
  0으로 나누기에 대한 유효성 처리 (MODE(N,0). ON 상태일 때 0으로 나누기에 대한 INSERT 수행 시 NULL이 입력되면 오류가 발생합니다.

• NO_ZERO_TIME
  OFF 상태에서는 datetime 필드에 공백이 입력되면 날짜 형식 (0000-00-00) 또는 시간 형식 (0000-00-00 00:00:00) 형식으로 자동 등록됩니다.

• NO_ZERO_IN_TIME
  ON 상태일 때 연도 및 월에 대한 0 입력이 허용되지 않습니다. 2023-00-12 08:57:11 과 같은 데이터를 삽입하려고하면 오류가 발생합니다.

• ALLOW_INVALID_DATES
  ON 상태에서는 잘못된 날짜의 삽입이 허용됩니다. 예를 들어 _2023-02-31_과 같은 잘못된 날짜도 등록할 수 있습니다.

• ONLY_FULL_GROUP_BY
  GROUP BY 시 SELECT 절의 모든 필드를 포함해야 합니다. (v5.7에서 추가됨)

• NO_ENGINE_SUBSTITUTION
  ON 상태일 때 CREATE 또는 ALTER 중에 엔진을 사용할 수 없으면 오류가 발생합니다.

• NO_AUTO_CREATE_USER
  사용자 계정 생성을 처리하는 MySQL의 방법 중 하나입니다. 클라이언트가 존재하지 않는 사용자 이름으로 데이터베이스에 연결하려고 할 때 기본 작업은
  해당 이름과 공백 비밀번호를 사용하여 새로운 사용자 계정을 만드는 것이지만 해당 옵션이 ON인 경우 연결이 오류 메시지와 함께 거부됩니다. 다음은 불가능한 예입니다.

    mysql -u root -p < dump.sql

적용 방법

---

쿼리를 사용하여 직접 적용

• 현재 세션에만 설정

    SET SESSION 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

• 전역적으로 설정

    SET GLOBAL 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

• 쿼리로 설정하는 경우 현재 세션 또는 현재 실행 중인 서버 상태에만 적용되므로 서버 재시작이 발생하면 옵션이 적용되지 않습니다.
  따라서 위와 같이 별도로 설정 파일에 선언하여 설정을 일관되게 유지하려면 권장됩니다.

구성 파일에서 적용

• my.cnf(또는 my.ini)에 다음과 같이 설정합니다.

  ...
  
  [mysqld]
  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
  
  ...

  위와 같이 설정 파일에 선언하면 업데이트 등으로 서버가 다시 시작되더라도 기존 환경과 동일하게 적용되므로 관련 오류 상황에 대비할 수 있습니다.