sql_mode > 기술자료

• 목록

sql_mode

Description: Sets the SQL Mode. Multiple modes can be set, separated by a comma.
Commandline: --sql-mode=value[,value[,value...]]
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value:
     STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (>= MariaDB 10.2.4)
     NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (>= MariaDB 10.1.7)
     (empty string) (<= MariaDB 10.1.6)
Valid Values: See SQL Mode for the full list.



1. MariaDB 버전 확인

[root@localhost ~]# mysql -uroot -p -e 'show global variables like "version";'
Enter password: 
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| version       | 10.1.28-MariaDB |
+---------------+-----------------+



2. sql_mode 확인하기

[root@localhost ~]# mysql -uroot -p -e "select @@sql_mode;"
Enter password: 
+--------------------------------------------+
| @@global.sql_mode                          |
+--------------------------------------------+
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+



3. sql_mode 변경
[root@localhost ~]# vi /etc/my.cnf.d/server.cnf
[mysqld]
# MariaDB 버전에 맞게 sql_mode 를 추가합니다.

# MariaDB 10.2.4 이후  Since MariaDB 10.2.4, SQL_MODE is by default set to
sql_mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION  

# MariaDB 10.1.7 ~ 10.2.3
sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# MariaDB 10.1.6 까지
sql_mode=


4. MySQL 5.x / MariaDB 10.x 재시작
[root@localhost ~]# systemctl restart mysql.service



5. sql_mode 확인
[root@localhost ~]# mysql -uroot -p -e "select @@sql_mode;"
Enter password: 
+--------------------------------------------+
| @@global.sql_mode                          |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+

 

Option	Description
ALLOW_INVALID_DATES	Allow any day between 1-31 in the day part. This is convenient when you want to read in all (including wrong data) into the database and then manipulate it there. 1 일부터 31 일 사이의 하루 중 하루를 허용합니다. 이것은 데이터베이스에 잘못된 데이터를 포함하여 모두 읽은 다음 데이터베이스에서 조작하려는 경우에 편리합니다.
ANSI	Changes the SQL syntax to be closer to ANSI SQL. Sets: SQL 구문을 ANSI SQL에 더 가깝게 변경합니다. REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE It also adds a restriction: an error will be returned if a subquery uses an aggregating function with a reference to a column from an outer query in a way that cannot be resolved.  하위 쿼리가 해결할 수없는 방식으로 외부 쿼리의 열에 대한 참조와 함께 집계 함수를 사용하면 오류가 반환됩니다.
ANSI_QUOTES	Changes " to be treated as `, the identifier quote character. This may break old MariaDB applications which assume that " is used as a string quote character. 식별자의 인용 문자로 취급되는 변경 "이것은 문자열 인용 문자로 사용되는 오래된 MariaDB 응용 프로그램을 손상시킬 수 있습니다.
DB2	Same as: PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, DB2, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS
EMPTY_STRING_IS_NULL	Oracle-compatibility option that translates Item_string created in the parser to Item_null, and translates binding an empty string as prepared statement parameters to binding NULL. For example, SELECT '' IS NULL returns TRUE, INSERT INTO t1 VALUES ('') inserts NULL. Since MariaDB 10.3.3 파서에서 작성된 Item_string을 Item_null로 변환하고 준비된 명령문 매개 변수로 빈 문자열을 바인드 NULL로 바인딩하는 Oracle 호환성 옵션. 예를 들어 SELECT ''IS NULL은 TRUE를 반환하고 INSERT INTO t1 VALUES ( '')는 NULL을 삽입합니다. MariaDB 10.3.3 이후
ERROR_FOR_DIVISION_BY_ZERO	If not set, division by zero returns NULL. If set, returns a warning as well. Also see MDEV-8319. Default since MariaDB 10.2.4. 설정하지 않으면 0으로 나누기가 NULL을 반환합니다. 설정된 경우 경고도 반환합니다. MDEV-8319를 참조하십시오. MariaDB 10.2.4 이후의 기본값.
HIGH_NOT_PRECEDENCE	Compatibility option for MySQL 5.0.1 and before; This changes NOT a BETWEEN b AND c to be parsed as (NOT a) BETWEEN a AND c MySQL 5.0.1 및 이전 버전의 호환성 옵션. 이것은 b와 b와 c 사이의 변경 사항이 아니라 a와 c의 (아닌) 파싱으로 변경됩니다.
IGNORE_BAD_TABLE_OPTIONS	If this is set generate a warning (not an error) for wrong table option in CREATE TABLE. Also, since 10.0.13, do not comment out these wrong table options in SHOW CREATE TABLE. 이것이 설정되면 CREATE TABLE의 잘못된 테이블 옵션에 대한 경고 (오류 아님)를 생성하십시오. 또한 10.0.13부터 SHOW CREATE TABLE에서 잘못된 테이블 옵션을 주석 처리하지 마십시오.
IGNORE_SPACE	Allow one to have spaces (including tab characters and new line characters) between function name and '('. The drawback is that this causes built in functions to become reserved words. 함수 이름과 '(') 사이에 공백 (탭 문자와 줄 바꿈 문자 포함)을 허용하십시오. 이로 인해 내장 함수가 예약어가됩니다.
MAXDB	Same as: PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, MAXDB, NO_KEY_OPTIONS, NO_TABLE_OPTIONS,NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER
MSSQL	Same as: PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, MSSQL, NO_KEY_OPTIONS, NO_TABLE_OPTIONS,NO_FIELD_OPTIONS
MYSQL323	Same as: NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE
MYSQL40	Same as: NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE
NO_AUTO_CREATE_USER	Don't automatically create users with GRANT. Produce a 1133 error: "Can't find any matching row in the user table". Default since MariaDB 10.1.7. GRANT를 사용하여 자동으로 사용자를 만들지 마십시오. 1133 오류 : "사용자 테이블에서 일치하는 행을 찾을 수 없습니다." MariaDB 10.1.7 이후 기본값.
NO_AUTO_VALUE_ON_ZERO	If set don't generate an AUTO_INCREMENT on INSERT of zero in an AUTO_INCREMENT column. Normally both zero and NULL generate new AUTO_INCREMENT values. 설정된 경우 AUTO_INCREMENT 열에 INSERT가 0 인 AUTO_INCREMENT가 생성되지 않습니다. 일반적으로 0과 NULL은 새로운 AUTO_INCREMENT 값을 생성합니다.
NO_BACKSLASH_ESCAPES	Disables using the backslash character \ as an escape character within strings, making it equivalent to an ordinary character. 문자열 내에서 백 슬래시 문자 \를 이스케이프 문자로 사용하지 않도록 설정하여 일반 문자와 동일하게 만듭니다.
NO_DIR_IN_CREATE	Ignore all INDEX DIRECTORY and DATA DIRECTORY directives when creating a table. Can be useful on slave replication servers. 테이블을 만들 때 모든 INDEX DIRECTORY 및 DATA DIRECTORY 지시문을 무시하십시오. 슬레이브 복제 서버에서 유용 할 수 있습니다.
NO_ENGINE_SUBSTITUTION	If not set, if the available storage engine specified by a CREATE TABLE is not available, a warning is given and the default storage engine is used instead. If set, generate a 1286 error when creating a table if the specified storage engine is not available. See also enforce_storage_engine. Default since MariaDB 10.1.7. 설정되지 않은 경우 CREATE TABLE으로 지정된 사용 가능한 스토리지 엔진을 사용할 수 없으면 경고가 제공되고 대신 기본 스토리지 엔진이 사용됩니다. 설정 한 경우 지정된 스토리지 엔진을 사용할 수없는 경우 테이블을 만들 때 1286 오류가 발생합니다. enforce_storage_engine을 참조하십시오. MariaDB 10.1.7 이후 기본값.
NO_FIELD_OPTIONS	Remove MariaDB-specific column options from the output of SHOW CREATE TABLE. This is also used by the portability mode of mysqldump. SHOW CREATE TABLE의 결과에서 MariaDB 관련 컬럼 옵션을 제거하십시오. 이것은 mysqldump의 이식성 모드에서도 사용된다.
NO_KEY_OPTIONS	Remove MariaDB-specific index options from the output of SHOW CREATE TABLE. This is also used by the portability mode of mysqldump. SHOW CREATE TABLE의 결과에서 MariaDB 관련 색인 옵션을 제거하십시오. 이것은 mysqldump의 이식성 모드에서도 사용된다.
NO_TABLE_OPTIONS	Remove MariaDB-specific table options from the output of SHOW CREATE TABLE. This is also used by the portability mode of mysqldump. SHOW CREATE TABLE 결과에서 MariaDB 관련 테이블 옵션을 제거하십시오. 이것은 mysqldump의 이식성 모드에서도 사용된다.
NO_UNSIGNED_SUBTRACTION	When enabled, subtraction results are signed even if the operands are unsigned. 활성화되면 피연산자가 부호가없는 경우에도 빼기 결과에 서명됩니다.
NO_ZERO_DATE	Don't allow '000-00-00' as a valid date in strict mode (produce a 1525 error). Zero dates can be inserted with IGNORE. If not in strict mode, a warning is generated. 엄격 모드에서 '000-00-00'을 유효한 날짜로 허용하지 마십시오 (1525 오류 생성). IGNORE로 0의 날짜를 삽입 할 수 있습니다. 엄격 모드가 아니면 경고가 생성됩니다.
NO_ZERO_IN_DATE	Don't allow dates where the year is not zero but the month or day parts of the date are zero (produce a 1525 error). For example, with this set, '0000-00-00' is allowed, but '1970-00-10' or '1929-01-00' are not. If the ignore option is used, MariaDB will insert '0000-00-00' for those types of dates. If not in strict mode, a warning is generated instead. 연도가 0이 아니지만 날짜의 월 또는 일 부분이 0 인 날짜는 허용하지 마십시오 (1525 오류 생성). 예를 들어이 집합을 사용하면 '0000-00-00'이 허용되지만 '1970-00-10'또는 '1929-01-00'은 허용되지 않습니다. ignore 옵션을 사용하면 MariaDB는 이러한 유형의 날짜에 '0000-00-00'을 삽입합니다. 엄격 모드가 아니면 경고가 대신 생성됩니다.
ONLY_FULL_GROUP_BY	For SELECT ... GROUP BY queries, disallow SELECTing columns which are not referred to in the GROUP BY clause, unless they are passed to an aggregate function like COUNT() or MAX(). Produce a 1055 error. SELECT ... GROUP BY 쿼리의 경우 COUNT () 또는 MAX ()와 같은 집계 함수에 전달되지 않는 한 GROUP BY 절에서 참조되지 않은 열을 SELECT 할 수 없습니다. 1055 오류가 발생합니다.
ORACLE	Sets sql_mode to: PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ORACLE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER. From MariaDB 10.3, the server understands a large subset of Oracle's PL/SQL language instead of MariaDB's traditional syntax for stored routines. See SQL_MODE=ORACLE From MariaDB 10.3. MariaDB 10.3부터, 서버는 MariaDB의 저장 루틴에 대한 전통적인 구문 대신 오라클의 PL / SQL 언어의 상당 부분을 이해합니다. SQL_MODE = 오라클 참조 MariaDB에서 10.3.
PAD_CHAR_TO_FULL_LENGTH	Trailing spaces in CHAR columns are by default trimmed upon retrieval. With PAD_CHAR_TO_FULL_LENGTH enabled, no trimming occurs. Does not apply to VARCHARs. CHAR 열의 후행 공백은 기본적으로 검색시 잘립니다. PAD_CHAR_TO_FULL_LENGTH를 사용하면 트리밍이 발생하지 않습니다. VARCHAR에는 적용되지 않습니다.
PIPES_AS_CONCAT	Allows using the pipe character (ASCII 124) as string concatenation operator. This means that "A" || "B" can be used in place of CONCAT("A", "B"). 파이프 문자 (ASCII 124)를 문자열 연결 연산자로 사용할 수 있습니다. 이것은 "A"|| CONCAT ( "A", "B") 대신 "B"를 사용할 수 있습니다.
POSTGRESQL	Same as: PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, POSTGRESQL, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS
REAL_AS_FLOAT	REAL is a synonym for FLOAT rather than DOUBLE. REAL은 DOUBLE이 아닌 FLOAT의 동의어입니다.
STRICT_ALL_TABLES	Strict mode. Statements with invalid or missing data are aborted and rolled back. For a non-transactional storage engine with a statement affecting multiple rows, this may mean a partial insert or update if the error is found in a row beyond the first. 엄격한 모드. 유효하지 않거나 누락 된 데이터가있는 명령문은 중단되고 롤백됩니다. 여러 행에 영향을주는 문이있는 트랜잭션이 아닌 저장소 엔진의 경우 오류가 첫 행을 초과하는 행에서 발견되면 부분 삽입 또는 업데이트를 의미 할 수 있습니다.
STRICT_TRANS_TABLES	Strict mode. Statements with invalid or missing data are aborted and rolled back, except that for non-transactional storage engines and statements affecting multiple rows where the invalid or missing data is not the first row, MariaDB will convert the invalid value to the closest valid value, or, if a value is missing, insert the column default value. Default since MariaDB 10.2.4. 엄격한 모드. 유효하지 않거나 누락 된 데이터가 첫 번째 행이 아닌 여러 행에 영향을주는 비 트랜잭션 저장 엔진 및 명령문을 제외하고 MariaDB가 유효하지 않은 값을 가장 가까운 유효한 값으로 변환한다는 점을 제외하고 유효하지 않거나 누락 된 데이터가있는 명령문은 중단되고 롤백됩니다. 값이 누락 된 경우 열 기본값을 삽입하십시오. MariaDB 10.2.4 이후의 기본값.
TRADITIONAL	Makes MariaDB work like a traditional SQL server. Same as: STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, TRADITIONAL, NO_AUTO_CREATE_USER

참고자료
http://idchowto.com/?p=21574
http://www.tocker.ca/2014/01/14/making-strict-sql_mode-the-default.html
https://www.liquidweb.com/kb/how-to-disable-mysql-strict-mode/
https://stackoverflow.com/questions/28849293/how-to-make-sql-mode-no-engine-substitution-permanent-in-mysql-my-cnf
https://mariadb.com/kb/en/library/server-system-variables/#sql_mode
https://mariadb.com/kb/en/library/sql-mode/
https://www.lesstif.com/pages/viewpage.action?pageId=24445406