2020. 3. 16. 09:54ㆍDB/MySQL
MySQL 서버에서는 운영에 필요한 구성설정에 이용되는 다양한 시스템 변수가 존재합니다.
MySQL 서버의 버전에 따라 각 시스템 변수는 기본값이 존재하고, 별도의 설정을 통해 값을 변경할 수 있습니다.
이번 시간에는 MySQL 시스템 변수중 sql_mode를 이용하여 시스템 변수를 설정하는 방법을 알아보도록 할 것입니다.
- sql_mode 란?
- sql_mode 설정 - 1. permanent
2-1) my.cnf 파일 찾기
2-2) my.cnf 파일에 sql_mode 설정 추가하기
2-3) mysql client로 접속하여 현재 세션에 적용된 sql_mode 조회하기
2-4) runtime 환경에서 바로 적용
2-5) MySQL서버 재시작하기 - sql_mode 설정 - 2. runtime
3-1) runtime 전역 설정
3-2) runtime 세션 설정 - 정리
- 사용 예
5-1) session에 sql_mode를 설정하기 전
5-2) session에 sql_mode를 설정한 후
5-3) session 종료 후 다시 접속한다면?
1. sql_mode 란?
sql_mode 는 MySQL에 저장될 데이터에 대한 유효성 검사(validation check) 범위를 설정하는 시스템 변수 입니다.
sql_mode에 활성화 하고자 하는 모드를 , 구분자로 구분하여 한줄로 추가합니다.
MySQL 버전별로 default sql_mode값이 다르며, sql_mode를 설정하여 데이터 유효성 검사 범위를 커스터마이징 할 수 있습니다.
현재 접속중인 세션에 적용되어있는 sql_mode를 조회하는 방법은 아래와 같습니다.
SELECT @@sql_mode;
sql_mode 설정 - 1. permanent
MySQL 8.0 에서는 기본적으로 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION 모드가 활성화 되어있습니다.
만일 sql_mode를 영구적으로 변경하고 싶다면
my.cnf(Windows OS에서는 my.ini) 파일에 sql_mode 속성을 추가하면 됩니다.
여기서 말하는 영구적 변경은 MySQL 서버의 재시작 또는 OS의 재시작 후에도 설정한 속성이 유지되는 것을 의미합니다.
2-1) my.cnf 파일 찾기
[jini@apple ~]$ sudo find / -name my.cnf [sudo] password for jini: /etc/my.cnf
find 명령어로 my.cnf 파일을 찾습니다.
C:\Users\jini>where /r C:\ my.ini
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
windows OS에서는 where 명령어로 my.ini를 찾습니다.
※ windows 용 MySQL에서는 버전에 따라 my.ini 파일이 없을수도 있습니다. 그럴 경우에는 MySQL이 설치된 디렉토리에 my.ini파일을 만들면 됩니다.
2-2) my.cnf 파일에 sql_mode 설정 추가하기
sudo vim /etc/my.cnf
[mysqld] ... sql_mode="ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
[mysqld] 구간에 sql_mode 속성을 추가합니다.
활성화하고자 하는 모드는 ,로 구분하여 나열하여 쓰면 됩니다.
2-3) mysql client로 접속하여 현재 세션에 적용된 sql_mode 조회하기
mysql> select @@sql_mode; +-----------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-----------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------------------------------------------------+
mysql client로 접속하여 @@sql_mode를 조회했더니 my.cnf 파일에 설정했던 값이 아닌 default 설정값이 그대로 나오네요?
my.cnf로 설정한 정보는 MySQL 서버가 재시작된 후에 반영되는 정보이기 때문입니다.
2-4) runtime 환경에서 바로 적용
runtime 환경에서도 변경한 sql_mode를 적용하는 방법은 아래 2가지가 있다.
- MySQL 서버를 재시작
- @@global.sql_mode, @@session.sql_mode 설정
2-5) MySQL서버 재시작하기
[jini@apple ~]$ sudo systemctl restart mysqld
[jini@apple ~]$ mysql -u root -p mysql> select @@sql_mode; +---------------------------------------------------+ | @@sql_mode | +---------------------------------------------------+ | ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +---------------------------------------------------+
mysqld 서비스를 재시작 한 후 다시 mysql client 접속을 합니다.
sql_mode를 조회했더니 이제는 my.cnf 에 설정한 값이 적용된것을 확인할 수 있었습니다.
(위의 sql_mode 설정값은 테스트를 위해 임의로 설정한 값이므로 my.cnf에서 sql_mode 설정정보를 제거하고 mysqld 서비스를 재시작 합시다.)
MySQL 서버를 재시작하면 my.cnf에 설정한 사항을 런타임 세션 및 글로벌 영영에서 적용할 수 있습니다. 그러나, MySQL 서버를 종료하면 안되는 상황이라면 어떻게 적용할 수 있을까요?
바로, 영구설정과 런타임 설정을 함께하는 것입니다!
두번째 방법인 @@global.sql_mode, @@session.sql_mode 설정 부분은 아래에서 알아보도록 하겠습니다.
3. sql_mode 설정 - 2. runtime
3-1) runtime 전역 설정
@@global은 MySQL 서버의 runtime 환경 전역을 의미합니다.
SET @@global.sql_mode="NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
SET @@global.sql_mode=로 MySQL 서버의 runtime 환경 전역에 설정합니다.
runtime이 끝나기 전까지(= MySQL 서버가 재시작 되기 전까지) 설정정보가 유효합니다.
runtime 환경에 설정하는 것이기 때문에 MySQL 서버가 재시작할 경우
my.cnf에 설정된 값(my.cnf에 별도의 설정이 없을 경우에는 MySQL Server 버전별 default값)으로 변경됩니다.
3-2) runtime 세션 설정
@@session은 client가 접속한 세션 영역입니다.
SET @@session.sql_mode="NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
SET @@sql_mode="NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
SET @@session.sql_mode=으로 세션영역에 sql_mode를 설정할 수 있으며, session은 생략 가능합니다.
@@session.sql_mode의 default 값은 @@global.sql_mode에 설정된 값입니다.(my.cnf에 설정된 값이 아닌 runtime-global에 설정된 값)
세션 시작 시 global영역에 설정된 sql_mode를 가져와 세션영역에 적재하며,
세션 중에 변경된 global설정은 자동으로 변경되지는 않습니다(※ 주의)
sql_mode 설정을 현재 접속중인 세션중에만 유효하도록 설정하고 싶다면 @@session.sql_mode에 설정하면 됩니다.
4. 정리
Q : MySQL 서버를 재시작하지 않으면서 영구적으로 설정하고 싶다면?
my.cnf 설정, @@global.sql_mode, @@sql_mode 설정
Q : MySQL 서버 재시작 전까지만 설정하고 싶다면? (runtime)
@@global.sql_mode, @@sql_mode
Q : 현재 접속중인 session에서만 이용할거라면?
@@sql_mode
5. 사용 예시
5-1) session에 sql_mode를 설정하기 전
mysql> select @@global.sql_mode; +---------------------------------------------------------------------------------------------+ | @@global.sql_mode | +---------------------------------------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select @@session.sql_mode; +---------------------------------------------------------------------------------------------+ | @@session.sql_mode | +---------------------------------------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> INSERT INTO test(type, name, create_dt) VALUES('0', 'sol', '0000-00-00'); ERROR 1292 (22007): Incorrect datetime value: '0000-00-00' for column 'create_dt' at row 1
MySQL 서버의 @@global.sql_mode가 STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION로 설정되어 있습니다.(ln 1~7)
MySQL client가 접속하여 얻은 session의 sql_mode은 아직 별도의 설정을 하지 않았기 때문에 global.sql_mode와 같은 결과를 출력합니다.(ln 9~15)
세션의 sql_mode에 STRICT_TRANS_TABLES와 NO_ZERO_DATE 모드가 활성화 되어있기 때문에 날짜 컬럼에 0이 들어갈 수 없습니다.(ln 17-18)
5-2) session에 sql_mode를 설정한 후
mysql> set @@session.sql_mode='NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select @@global.sql_mode; +---------------------------------------------------------------------------------------------+ | @@global.sql_mode | +---------------------------------------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select @@session.sql_mode; +------------------------------------------------------------+ | @@session.sql_mode | +------------------------------------------------------------+ | NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> INSERT INTO test(type, name, create_dt) VALUES('0', 'sol', '0000-00-00'); Query OK, 1 row affected (0.00 sec)
세션의 sql_mode에서 NO_ZERO_DATE와 STRICT_TRANS_TABLES를 비활성화 합니다.(ln 1~2)
session 영역에서는 sql_mode가 설정되어있으며, global영역에서는 변동사항이 없는 것을 확인할 수 있습니다.(ln 4~18)
datetime에 0을 넣어도 에러 출력없이 insert됩니다.(ln 20~21)
mysql접속을 종료한 후 다시 로그인하여 @@session.sql_mode값을 조회할 경우 @@global.sql_mode로 설정된 값이 다시 출력됩니다.
5-3) session 종료 후 다시 접속한다면?
세션이 종료된다는 말은 현재 접속중인 MySQL Command line Client를 종료하거나, MySQL Workbench CE와 같은 툴을 끄면서 접속을 끄는것을 의미한다.
2)번 과정에서 변경했던 sql_mode가 그대로 나올까?
mysql> exit Bye [jini@apple ~]$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement mysql> select @@session.sql_mode; +---------------------------------------------------------------------------------------------+ | @@session.sql_mode | +---------------------------------------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
session을 종료하고 다시 접속할 경우 MySQL 서버에서는 @@global.sql_mode에 설정된 값을 다시 @@session.sql_mode에 설정합니다.
+++
- set MySQL Server System Variables
'DB > MySQL' 카테고리의 다른 글
| [MySQL Function] 숫자 함수 (0) | 2020.07.21 |
|---|---|
| [MySQL Function] 날짜, 시간 함수 (0) | 2020.04.28 |
| [MySQL] 3. user(계정) 생성, 로그인, 변경, 삭제 (6) | 2020.04.23 |
| [MySQL] 2. database schema 조회, 생성, 변경, 삭제, 이름변경 (0) | 2020.02.06 |
| [MySQL] 1. CentOS7에 MySQL8.0 Community Server 설치하기 (0) | 2020.01.28 |