http://www.mysqlkorea.co.kr
한글매뉴얼 5.0 , 한글매뉴얼 5.1 , MySQL 5.1 HA , 사용자매뉴얼
Advanced Knowle...  
엔지니어 노트  
블로그존  
글로벌 MySQL  
MySQL 5.5 GA  
MySQL 5.6 Developer  
최신글
인텍스 추가 등에…
mysql master - s…
다대다 관계에서 …
mysql my파일과 …
mysql server 설…
 
한글매뉴얼 5.0 > 매뉴얼존 > 한글매뉴얼 5.0
 

5.2.5 서버 SQL 모드

MySQL 서버는 서로 다른 SQL 모드 상태에서 동작할 수 있으며, 서로 다른 클라이언트에 대해 이러한 모드들을 서로 다르게 적용할 수가 있다. 이를 통해 각 어플리케이션들은 서버 동작 모드를 따라서 자신의 요구 사항을 처리할 수 있게 된다.

모드란 MySQL이 어떤 SQL 신텍스를 지원하고 있는지 그리고 데이터의 유효성 (Validation)을 검사하는 방법은 무엇이 있는지를 가리키는 것이다. 이것은 MySQL을 서로 다른 환경에서 사용하는 것을 손쉽게 만들어 주며 다른 데이터 베이스 서버와 함께 MySQL을 사용하는 것이 가능하도록 만들어 준다.

디폴트 SQL 모드 설정은 mysqld--sql-mode="modes" 옵션과 함께 구동 시켜서 할 수가 있다. modes 는 콤마 (,)로 구분되는 서로 다른 모드 리스트이다. 디폴트 값은 비어 있다 (empty) (아무런 모드고 설정되어 있지 않다). 만약에 확실하게 비어둘 생각이라면, modes 값도 역시 비울 수가 있다. (--sql-mode="").

sql_mode 시스템 값을 설정을 하기 위한 명령문인 SET [GLOBAL|SESSION] sql_mode='modes'를 사용하면 서버 런 타임 시점에 SQL 모드 값을 변경 시킬 수가 있다. GLOBAL 변수 설정은 SUPER 권한이 필요하며 그 시간 이후에 연결되는 모든 클라이언트의 동작에 영향을 주게 된다. 모든 클라이언트는 자신의 세션 sql_mode 값을 언제든지 변경할 수가 있다.

현재의 글로벌 또는 세션 sql_mode 값은 아래의 명령문을 사용해서 설정할 수가 있다:

SELECT @@global.sql_mode;
SELECT @@session.sql_mode;

가장 중요한 sql_mode 값은 다음과 같다:

  • ANSI

이 모드는 표준 SQL에 보다 근접하도록 신텍스와 실행 동작을 변경한다.

  • STRICT_TRANS_TABLES

만약에 값을 지정한 형태로 트랜젝션 테이블에 삽입할 수가 없다면, 명령문을 중단한다. -트랜젝션 (non-transactional) 테이블에 대해서는, 그 값이 단일-열 명령문 또는 다중-열 명령문의 맨 처음 열에 나오게 되면 명령문을 중단한다. 보다 자세한 설명은 이 섹션 후반부에 하기로 한다. (MySQL 5.0.2에서 구현됨.)

  • TRADITIONAL

MySQL 동작을 마치전통적인 (traditional)” SQL 데이터 베이스 시스템처럼 구동되도록 만든다. 올바르지 않은 값을 컬럼에 삽입할 때 이 모드에 대한 간단한 설명이 경고문 대신에 에러로 나온다”.

 

Note:  INSERT/UPDATE는 에러가 발생되는 즉시 중단된다. 이것은 여러분이 논-트랜젝션(non-transaction) 스토리지 엔진을 사용하면서 원하는 결과가 아닐 것인데, 그 이유는 에러 이전에 수행된 데이터 변경은 롤백 (rolled-back)되지 않기 때문에 그 결과로 인해 부분적으로 수행된 (partially done)” 업데이트만 되기 때문이다. (MySQL 5.0.2에 추가 됨)

이 매뉴얼이 스트릭트 모드 (strict mode)를 언급할 때에는, 적어도 STRICT_TRANS_TABLES 또는 STRICT_ALL_TABLES 중의 하나가 활성화 되어 있는 곳의 모드를 의미하는 것이다.

아래의 리스트는 모든 지원 모드에 대한 설명이다:

  • ALLOW_INVALID_DATES

전체 날짜를 검사하지 않는다. (month) 1에서 12 사이와 날짜가 1에서 31일 사이인 것만 검사한다. 이것은 여러분이 년, , 그리고 날짜 (day)를 세 개의 필드에서 얻고 사용자가 삽입하는 (날짜 검증 없이) 것을 정확하게 저장하고자 하는 웹 어플리케이션에서 유용하게 사용할 수 있다. 이 모드는 DATE DATETIME 컬럼에 적용된다. 이것은 TIMESTAMP 컬럼에는 적용되지 않는데, 이 컬럼은 유효한 날짜를 필요로 한다.

 

이 모드는 MySQL 5.0.2에서 추가 되었다. 5.0.2 이전에는, 이것이 디폴트 MySQL 날짜-핸들링 모드였다. 5.0.2 이후에는, 서버는 월 (month)와 날짜 (day) 값이, 단순히 1에서 12 사이와 1에서 31 사이의 값이 아닌, 규칙에 맞아야 하는 것을 요구한다. 스트릭트 모드를 비활성화 시키면, '2004-04-31' 과 같은 유효하지 않은 값은 '0000-00-00'로 변환 되고 경고문을 내보내게 된다. 스트릭트 모드를 활성화 시키면, 유효하지 않은 날짜 값은 에러를 발생 시킨다. 이러한 날짜 값을 허용하기 위해서는, ALLOW_INVALID_DATES를 활성화 시킨다.

  • ANSI_QUOTES

"를 아이덴티파이어 인용 부호 문자로 다루고 (‘`인용 부호 문자 처럼) 스트링 인용 문자로 다루지 않는다. 여러분은 이 모드에서도 여전히 `를 인용 아이덴티파이어로 사용할 수가 있다. ANSI_QUOTES를 활성화 시키면, 이중 인용 부호를 사용해서 문자 스트링을 인용할 수는 없게 되는데, 그 이유는 그것이 아이덴티파이어로 해석되기 때문이다.

  • ERROR_FOR_DIVISION_BY_ZERO

INSERT 또는 UPDATE를 실행하는 동안 0 (또는 MOD(X,0))으로 나눗셈을 하는 것을 만나게 되면 스트릭트 모드에서는 에러가 나오게 된다 (다른 모드에서는 경고가 나옴). 만약에 이 모드가 활성화 되지 않았다면, MySQL 0으로 나누는 것에 대해 NULL 값을 대신 리턴한다. INSERT IGNORE 또는 UPDATE IGNORE에서 사용되면, MySQL 0으로 나누는 것에 대해 경고문을 만들어 내지만, 동작의 결과는 NULL이 된다. (MySQL 5.0.2에서 구현됨)

  • HIGH_NOT_PRECEDENCE

MySQL 5.0.2 이후에, NOT 연산자의 우선권은 NOT a BETWEEN b AND c 와 같은 수식에서는 NOT (a BETWEEN b AND c)와 같이 분석 (parse)된다. MySQL 5.0.2 이전에는, 수식을 (NOT a) BETWEEN b AND c 과 같이 분석하였다. 이전 버전과 같은 우선권을 얻기 위해서는 HIGH_NOT_PRECEDENCE SQL 모드를 활성화 시키면 된다. (MySQL 5.0.2에서 추가됨)

mysql> SET sql_mode = '';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
        -> 0
mysql> SET sql_mode = 'broken_not';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
        -> 1
  • IGNORE_SPACE

함수 이름과 (문자 사이에 스페이스를 허용한다. 이것은 모든 함수 이름을 사용 지정된(reserved) 단어로 취급하도록 한다. 그 결과, 만약에 어떤 데이터 베이스, 테이블, 또는 사용 지정된 컬럼 이름에 여러분이 접근 하고자 한다면, 반드시 그것을 인용 부호화 해야 한다. 예를 들면, USER() 라는 함수는 이미 존재하기 때문에, mysql 데이터 베이스에 있는 user 테이블과 그 테이블 안에 있는 User 컬럼은 사용이 지정되며, 따라서 그것들은 인용 부호로 둘러 쌓여야 한다:

SELECT "User" FROM mysql."user";

IGNORE_SPACE SQL 모드는 빌트인 (built-in) 함수에 적용 되며, 스토어드 루틴에는 적용되지 않는다. 루틴 이름 다음에 스페이스를 가지는 것은 항상 가능한데, IGNORE_SPACE가 활성화 되어 있는지 와는 상관이 없다.

  • NO_AUTO_CREATE_USER

비어 있지 않는 (non-empty) 패스워드가 함께 지정되지 않는다면, GRANT가 자동으로 새로운 사용자를 생성하지 못하도록 만든다. (MySQL 5.0.2에 추가 됨)

  • NO_AUTO_VALUE_ON_ZERO

NO_AUTO_VALUE_ON_ZEROAUTO_INCREMENT 컬럼을 처리 하는데 영향을 준다. 일반적으로, NULL 또는 0 중에 하나를 컬럼에 삽입하면 컬럼에 대한 그 다음 시퀀스의 숫자를 만들어낼 수 있다. NO_AUTO_VALUE_ON_ZERO 0 에 대한 이러한 행동을 종식 시켜서 NULL 만이 다음 시퀀스 숫자를 만들어 내도록 한다.

이 모드는 0 이 테이블의 AUTO_INCREMENT 컬럼에 있을 경우에 유용하게 사용될 수 있다. (0을 저장하는 것은 실제로는 권장할 사항이 아니다.) 예를 들면, 만약에 여러분이 mysqldump를 가지고 테이블을 덤프하고 그 다음에 그것을 다시 로드 한다면, MySQL0 값을 만날 때 새로운 시퀀스 번호를 만들어 내고, 그 결과 덤프된 내용과는 다른 내용물을 가진 테이블이 생성되게 된다. 덤프 파일을 다시 로드 하기 전에 NO_AUTO_VALUE_ON_ZERO를 활성화 시키는 것이 이 문제를 해결하는 방법이다. mysqldump는 이 문제를 해결하기 위해서, 자신의 결과에 자동으로 NO_AUTO_VALUE_ON_ZERO를 활성화 시키는 명령문을 포함 시키고 있다.

l        NO_BACKSLASH_ESCAPES

백슬레시 문자(‘\’)를 스트링 안에서 이스케이프 (escape) 문자로 사용하지 않도록 한다. 이 모드가 활성화가 되면, 백슬레시는 다른 문자와 같이 일상적인 문자로 인식된다. (MySQL 5.0.1에서 구현됨)

  • NO_DIR_IN_CREATE

테이블을 생성할 때, 모든 INDEX DIRECTORY DATA DIRECTORY 디렉토리를 무시한다. 이 옵션은 슬레이브 리플리케이션 서버에서 유용하다.

  • NO_ENGINE_SUBSTITUTION

CREATE TABLE 같은 명령문이 비활성화 되었거나 또는 컴파일 되지 않은 스토리지 엔진을 지정할 때 디폴트 스토리지 엔진으로 자동 대체 되는 것을 막는다. (MySQL 5.0.8에서 구현됨)

  • NO_FIELD_OPTIONS

SHOW CREATE TABLE 결과에 MySQL-특성 컬럼 옵션을 기록 하지 않는다. 이 모드는 포터빌러티 (portability) 모드에서 mysqldump에 의해 사용된다.

  • NO_KEY_OPTIONS

SHOW CREATE TABLE 결과에 MySQL-특성 인덱스 옵션을 기록 하지 않는다. 이 모드는 포터빌러티 (portability) 모드에서 mysqldump에 의해 사용된다.

  • NO_TABLE_OPTIONS

SHOW CREATE TABLE 결과에 MySQL-특성 테이블 옵션 (ENGINE과 같은)을 기록 하지 않는다. 이 모드는 포터빌러티 (portability) 모드에서 mysqldump에 의해 사용된다.

  • NO_UNSIGNED_SUBTRACTION

뺄셈 연산에서, 오퍼랜드 (operand) 중의 하나가 부호화 되지 않으면 (unsigned) 그 결과를 UNSIGNED로 표시하지 않는다. 이것은 BIGINT UNSIGNED를 모든 문장에서 100% 사용할 수 있다는 의미하는 것이 아니라는 것을 알아 두도록 하자.

  • NO_ZERO_DATE

스트릭트 모드에서, '0000-00-00'을 유효한 날짜 표시 방법으로 사용하지 않도록 한다. 여러분은 여전히 IGNORE 옵션을 사용해서 제로 날짜를 입력할 수 있게 된다. 스트릭트 모드가 아닐 때에는, 이러한 날짜는 사용할 수는 있지만 경고가 발생한다. (MySQL 5.0.2에서 추가됨)

  • NO_ZERO_IN_DATE

스트릭트 모드에서, 월 또는 날짜 부분이 0 인 날짜는 받아들이지 않는다. 만약에 IGNORE 옵션이 사용된다면, MySQL'0000-00-00' 날짜를 이러한 날짜에 삽입한다. 스트릭트 모드가 아닌 경우에는, 이러한 날짜는 경고를 만들기는 하지만 사용 가능하다. (MySQL 5.0.2에 추가됨)

  • ONLY_FULL_GROUP_BY

GROUP BY 구문이 결과 컬럼 리스트에 존재 하지 않는 컬럼을 참조하기 위한 쿼리를 허용하지 않는다.

  • PIPES_AS_CONCAT

Treat || OR 에 대한 동의어가 아닌 스트링 연쇄 (concatenation) 연산자로 다룬다.

  • REAL_AS_FLOAT

REALFLOAT에 대한 동의어로 다룬다. 디폴트로는, MySQLREALDOUBLE의 동의어로 처리한다.

  • STRICT_ALL_TABLES

모든 스토리지 엔진에 대해 스트릭트 모드를 활성화 시킨다. 유효하지 않은 데이터는 거절 된다. 추가적인 설명은 나중에 한다 (MySQL 5.0.2에 추가됨)

  • STRICT_TRANS_TABLES

-트랜젝션 스토리지 엔진을 사용 가능할 때, 트랜젝션 스토리지 엔진에 대한 스트릭트 모드를 활성화 시킨다. 부가적인 설명은 나중에 한다. (MySQL 5.0.2에서 구현됨)

스트릭트 모드는 MySQL가 유효하지 않거나 누락된 (missing) 데이터를 처리하는 방법을 제어한다. 하나의 값은 여러 가지 이유로 유효하지 않게 된다. 예를 들면, 컬럼에 대해 틀린 데이터 타입을 가질 수가 있거나, 또는 범위를 벗어날 수도 있다. 삽입될 새로운 열이 자신의 정의문 안에서 명확한 DEFAULT 구문이 없는 -NULL 컬럼 값을 가지고 있지 않을 때 데이터는 누락 된다.

트랜젝션 테이블에 대해서는, STRICT_ALL_TABLES 또는 STRICT_TRANS_TABLES 중 하나의 모드가 활성화 될 때 명령문 안에 데이터가 유효하지 않거나 누락이 되면 에러가 발생하게 된다. 명령문은 중단되고 롤백 (roll-back) 된다.

-트랜젝션 (non-transactional) 테이블에 대해서는, 만약에 삽입되어야 하거나 업데이트 되어야 하는 처음 열에 유효하지 않은 값이 나오게 되면, 양쪽 모드에 대해서 동일한 결과를 만들게 된다. 명령문은 중단되고 테이블은 변경되지 않는 상태를 유지한다. 명령문이 여러 개의 열을 삽입 또는 수정하고 유효하지 않은 값이 두 번째 또는 그 이후의 열에 나타나게 되면, 그 결과는 어떤 스트릭트 옵션이 활성화 되어 있는지에 따라 다르게 나온다:

  • STRICT_ALL_TABLES에 대해서는, MySQL은 에러를 리턴 하고 나머지 열들을 무시한다. 하지만, 이와 같은 경우에, 가장 앞서 있는 열들은 여전히 삽입 또는 업데이트가 된다. 이것은 여러분이 부분적인 업데이트를 한다는 것을 의미하며, 이런 상태를 원하지는 않았을 것이다. 이런 상황을 피하기 위해서는, 단일 열 명령문을 사용하는 것이 최선인데, 그 이유는 이러한 명령문은 테이블 변경 없이 중단될 수 있기 때문이다.
  • STRICT_TRANS_TABLES에 대해서는, MySQL은 유효하지 않은 값을 컬럼에 대해 가장 근접한 유효 값으로 변환 시킨 다음에 이 값을 삽입한다. 데이터 값이 누락될 경우에는, MySQL은 컬럼 데이터 타입에 대한 암시적인 (implicit) 디폴트 값을 삽입하게 된다. 둘 중의 어느 경우에도, MySQL은 에러 메시지가 아닌 경고 메시지를 만들게 되며 명령문을 계속 처리하게 된다. 암시적인 (Implicit) 디폴트 값에 대해서는 Section 11.1.4, “Data Type Default Values”에서 자세히 설명한다.

스트릭트 모드는 '2004-04-31'과 같은 유효하지 않은 날짜 값은 허용하지 않는다. 이 모드는 '2004-04-00' 또는제로(zero)날짜와 같은 제로 부분을 가진 날짜를 허용하지 않는다. 이러한 값들을 허용하지 않기 위해서는, 스트릭트 모드와 함께 NO_ZERO_IN_DATE NO_ZERO_DATE SQL 모드를 활성화 시킨다.

스트릭트 모드를 사용하지 않는다면 (, STRICT_TRANS_TABLES 또는 STRICT_ALL_TABLES 중에 어느 것도 활성화 시키지 않는다면), MySQL은 유효하지 않거나 누락된 데이터 값을 적당히 유효하게 변환 시킨 값으로 삽입하고 경고문을 발생 시킨다. 스트릭트 모드에서는, INSERT IGNORE 또는 UPDATE IGNORE를 사용해서 이러한 동작을 구현할 수가 있다. Section 13.5.4.25, “SHOW WARNINGS 신텍스를 참조할 것.

다음에 나오는 특별 모드들은 위에서 언급한 리스트에서 모드 값을 조합하기 위한 숏핸드 (shorthand) 형태로 제공된다. 이러한 모드들 전부 5.0.0부터 추가되었으나, TRADITIONAL은 예외적인 것으로서, 이것은 MySQL 5.0.2에서 추가가 되었다.

아래의 설명은 최신의 MySQL 버전에서 가장 많이 사용되고 있는 모든 모드 값들에 대한 것이다. 이전 버전의 경우에는, 조합 (combination) 모드는 사용할 수 없는 개별 모드 값을 포함하지 않는다.

  • ANSI

REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE와 동일하다. MySQL 5.0.3 이전에는, ANSI에는 ONLY_FULL_GROUP_BY 도 포함되어 있었다. Section 1.9.3, “Running MySQL in ANSI Mode”를 참조할 것.

  • DB2

PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS과 동일 하다.

  • MAXDB

PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER와 동일 하다.

  • MSSQL

PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS과 동일하다.

  • MYSQL323

NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE와 동일하다.

  • MYSQL40

NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE와 동일하다.

  • ORACLE

PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER와 동일하다.

  • POSTGRESQL

PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS과 동일하다.

  • TRADITIONAL

STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER와 동일하다.

상위
5.2.5. 서버 SQL 모드
MySQL Korea 사이트의 컨텐츠 소유권은 (주)상상이비즈에 있으므로 무단전재를 금합니다.
Copyright ⓒ ssebiz All Rights Reserved.