[MySQL] SQL 모드 (sql_mode)와 클라이언트별 설정 방법

MySQL에서 SQL 모드 (sql_mode)는 서버가 SQL 쿼리를 처리하는 방식을 제어하는 중요한 시스템 변수입니다. 이를 통해 데이터 입력의 유효성 검사, 그룹화 규칙, 날짜 형식 처리 등 다양한 SQL 동작을 세부적으로 설정할 수 있습니다.

 

SQL 모드란?

SQL 모드는 MySQL 서버가 SQL 명령을 해석하고 실행하는 방식을 정의합니다. 이를 통해 데이터의 유효성 검사와 처리 방식을 세부적으로 조정할 수 있습니다. 예를 들어, 잘못된 데이터 입력을 엄격하게 막거나, 특정 형식의 데이터를 허용하지 않도록 설정할 수 있습니다.

 

주요 SQL 모드 종류

• STRICT_TRANS_TABLES: 잘못된 데이터 입력 시 오류를 발생시켜, 트랜잭션이 롤백되도록 합니다.
• NO_ZERO_IN_DATE: '0000-00-00'과 같은 날짜 값을 허용하지 않습니다.
• ONLY_FULL_GROUP_BY: GROUP BY 문에 포함되지 않은 컬럼을 SELECT 할 수 없도록 합니다.
• ANSI_QUOTES: 쌍따옴표를 식별자(identifier)로 사용하도록 합니다.

 

서버 전체 SQL 모드 설정

1. 서버에 설정된 모드 확인 

show variables like 'sql_mode'

또는 

select @@sql_mode;

 

2. 모드 변경 

1) 현재 연결된 세션에서만 모드 변경 

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

 

2) 영구적으로 모드 변경 

MySQL을 사용하는 시스템에서 MySQL을 설정 파일을 변경해야 합니다. 

Windows	설정 파일 이름	my.ini
	기본 위치	C:\Program Files\MySQL\MySQL Server X.Y\my.ini C:\ProgramData\MySQL\MySQL Server X.Y\my.ini C:\Windows\my.ini C:\my.ini
Mac	설정 파일 이름	my.cnf
	기본 위치	/etc/my.cnf /usr/local/mysql/my.cnf /usr/local/etc/my.cnf
Linux	설정 파일 이름	my.cnf
	기본 위치	/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf /usr/local/etc/my.cnf ~/.my.cnf (사용자 홈 디렉토리에 있는 파일)

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY"

 

 

클라이언트별 SQL 모드 설정

MySQL에서 "클라이언트"는 데이터베이스 서버에 연결하여 쿼리를 실행하는 프로그램 또는 사용자를 의미합니다.
(Ex : 데이터베이스 관리 도구 (DBeaver, HeidiSQl 등),  응용프로그램(웹 애플리케이션,  모바일 등))
클라이언트는 MySQL 서버에 요청을 보내고, 서버는 그 요청을 처리하여 결과를 반환합니다. 

1. 서버의 기본 SQL 모드 확인

SELECT @@GLOBAL.sql_mode;

2. 서버의 기본 SQL 모드 설정 (관리자 권한 필요)

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY';

 

3. 클라이언트 세션의 SQL 모드 확인 

SELECT @@SESSION.sql_mode;

4. 클라이언트 세션의 SQL 모드 설정 (현재 세션에 대한 설정) 

SET SESSION sql_mode = 'ANSI_QUOTES,PAD_CHAR_TO_FULL_LENGTH';

 

 

 

 

---

https://velog.io/@posasac/Etc.sqlmodeonlyfullgroupby-%EC%98%A4%EB%A5%98-%ED%95%B4%EA%B2%B0%ED%95%98%EA%B8%B0MacmySQL

[Etc.]sql_mode=only_full_group_by 에러 해결하기(Mac, mySQL)

 

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

MySQL의 sql_mode와 기본 sql_mode들이 하는 역할

 

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-setting

MySQL :: MySQL 8.0 Reference Manual :: 7.1.11 Server SQL Modes