MySQL/MariaDB sql_mode 설정 변경으로 NULL 데이터 처리하기

MySQL/MariaDB sql_mode 설정 변경으로 NULL 데이터 처리하기

 

·       Version : MySQL 5.7

 

MS SQL Server에서 BCP를 이용하여 데이터를 탭 구분형식의 텍스트 파일로 내려받고 MySQL에서는 Bulk load(LOAD DATA INFILE)를 사용하여 데이터를 복원할때 아래와 같은 에러가 지속적으로 발생하였다.

Error Code: 1366. Incorrect decimal value …

 

이 문제는 Decimal 컬럼에 NULL값을 입력할때 MySQL의 유효성 문제로 에러가 발생한 것이다. 해당 컬럼은 NULL 허용 컬럼임에도 불구하고 지속적으로 동일한 문제가 발생하여, 해결 방법을 찾아본 결과 아래와 같은 sql_mode 변경으로 해결할 수 있었다.

SET SESSION sql_mode = ''

 

Server SQL Mode 는 MySQL이 지원해야 하는 SQL 구문과 수행해야하는 데이터의 유효성 검사의 종류를 정의한다. MySQL 서버는 이러한 모드를 클라이언트에 개별적으로 적용할 수 있다. 운영중에 모드를 변경하려는 경우, SET 명령을 사용할 수있으며, GLOBAL 또는 SESSION  변수를 사용할 수 있다. GLOBAL 변수의 경우 SUPER 권한이 필요하며, 설정 이후 모든 클라이언트의 작동에 영향을 준다. SESSION은 현재 클라이언트에만 영항이 있다. 현재 설정된 sql_mode 값을 확인하려면 아래 스크립트를 실행한다.

SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode;

 

 

. MySQL 5.7 경우 기본SQL 모드는 아래와 같다.

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

 

이번 해결은 유효성 검사를 비활성화 하여 해결하였지만 Sql_mode 변경은 시스템 환경을 변경하는것으로 반드시 잘 알고 사용해야 하며 다른 서비스에 영향이 없는지 반드시 검토가 필요하다.  자세한 내용은 아래 링크를 참고 한다.

·       Server SQL Mode : https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

 

 

 [참고자료]

·       Server SQL Mode : https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

·       FAQ : Server SQL Mode : https://dev.mysql.com/doc/refman/5.7/en/faqs-sql-modes.html

 

 

2019-07-05 / 강성욱 / http://sungwookkang.com

 

MySQL, MySQL sql_mode, 유효성 검사, 벌크로드, NULL Exception, NULL 값처리, 데이터 마이그레이션, LOAD DATA INFILE