[MySQL] sql_mode 설정

MySQL 은 테이블 생성시 또는 테이블 컬럼의 제약 사항에 따라

ERROR 를 내어야 하는 statement (syntax error는 아니며 제약 사항에 위배되는 statement 등 ) 에 대해서 sql_mode 가 어떻게 설정되느냐에 따라서 그냥 warning 만 내고

Statement 를 완료 시킵니다.

 

예를 들어 default 가 설정되지 않은 not null 컬럼에 아무 값도 넣지 않으면… 빈 문자열을 넣고 ( mysql 에서 빈문자열 ‘’ 이 null 이 아니네요… 오라클에선 그런 것  같은데 기억이 가물 )

Warning 만 하고 statement를 완료 시킵니다.                                                                                                                           

 

컬럼이 int 형이면 0 을 넣어버리는 등  default 값을 임의대로 넣어버리고 statement를 완료 시킵니다.

 

대부분의 개발이 이런 특성을 고려해서 개발 되는 것이 아니기 때문에 statement에 실수가 발생해도 그대로 진행되어서

나중에 의도 하지 않은 결과들을 볼 수 있는 위험이 있습니다.

 

관련 sql_mode 옵션 : STRICT_TRANS_TABLES

 

또 한 가지 테이블 생성시 엔진명이 틀리거나 명시하지 않으면 그냥 default storage engine으로 설정해서 테이블 생성해 버립니다.

 

Warning 제대로 안 보다간 Error도 안나니 전혀 의도 하지 않은 엔진으로 테이블 생성하고 나중에 문제가 될 수 있습니다.

 

 

그 외에도 여러 가지가 있는데 위 두 가지가 중요한 것 같습니다.

 

그래서 5.6 부터는 default my.cnf my.ini 파일에 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 로 기본 설정하고 있습니다.  

 

 

 

그리고 MySQL 에서 warning 확인은 절대적이네요..

Warning 발생시 꼭 확인 필요 ^^;;

 

https://blogs.oracle.com/supportingmysql/entry/mysql_server_5_6_default

 

http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html

 

• STRICT_TRANS_TABLES

If a value could not be inserted as given into a transactional table, abort the statement. For a nontransactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement. More detail is given later in this section.

 

• NO_ENGINE_SUBSTITUTION

Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLEspecifies a storage engine that is disabled or not compiled in.

Because storage engines can be pluggable at runtime, unavailable engines are treated the same way:

With NO_ENGINE_SUBSTITUTION disabled, for CREATE TABLE the default engine is used and a warning occurs if the desired engine is unavailable. For ALTER TABLE, a warning occurs and the table is not altered.

With NO_ENGINE_SUBSTITUTION enabled, an error occurs and the table is not created or altered if the desired engine is unavailable.

테스트 )

1. Insert 될 수 없는 statement를 임의 처리 하는 CASE

 

create table test_strict(

id bigint(20) not null auto_increment,

a varchar(50) not null,

b varchar(50) not null,

primary key (id)

)engine=innodb default charset=utf8;

 

 

insert into test_strict(id,a)values(1,'a');

 

 

insert into test_strict(id,a)values(2,'c');

 

 

mysql> select @@sql_mode;

+------------+

| @@sql_mode |

+------------+

|            |

+------------+

1 row in set (0.00 sec)

 

mysql> create table test_strict(

    -> id bigint(20) not null auto_increment,

    -> a varchar(50) not null,

    -> b varchar(50) not null,

    -> primary key (id)

    -> )engine=innodb default charset=utf8;

Query OK, 0 rows affected (0.05 sec)

 

mysql> insert into test_strict(id,a)values(1,'a');   ß b가 not null 인데도 error 없이 statement 완료 됨. Warning 만

Query OK, 1 row affected, 1 warning (0.01 sec)

 

mysql> select * from test_strict;

+----+---+---+

| id | a | b |

+----+---+---+

|  1 | a |   |

+----+---+---+

1 row in set (0.00 sec)

 

mysql> select b from test_strict;

+---+

| b |

+---+

|   |

+---+

1 row in set (0.00 sec)

 

mysql> set @@sql_mode = 'strict_trans_tables';

Query OK, 0 rows affected (0.00 sec)

 

mysql>

mysql>

mysql> insert into test_strict(id,a)values(2,'c');

ERROR 1364 (HY000): Field 'b' doesn't have a default value  ß sql_mode = strict_trans_tables 설정시 error return

 

 

 

create table test_strict(

id bigint(20) not null auto_increment,

a varchar(50) not null,

b varchar(50) not null,

c int not null,

primary key (id)

)engine=innodb default charset=utf8;

 

mysql> set @@sql_mode='';

Query OK, 0 rows affected (0.00 sec)

 

 

 

 

mysql> drop table test_strict;

Query OK, 0 rows affected (0.00 sec)

 

mysql> create table test_strict(

    -> id bigint(20) not null auto_increment,

    -> a varchar(50) not null,

    -> b varchar(50) not null,

    -> c int not null,

    -> primary key (id)

    -> )engine=innodb default charset=utf8;

Query OK, 0 rows affected (0.03 sec)

 

mysql> insert into test_strict(id,a)values(1,'a');

Query OK, 1 row affected, 2 warnings (0.00 sec)

 

mysql> select * from test_strict;

+----+---+---+---+

| id | a | b | c |

+----+---+---+---+

|  1 | a |   | 0 | ß--- int의 경우 0으로 설정

+----+---+---+---+

1 row in set (0.00 sec)

 

mysql> insert into test_strict(id,a)values(2,'c');

Query OK, 1 row affected, 2 warnings (0.01 sec)

 

mysql> show warnings\G

*************************** 1. row ***************************

  Level: Warning

   Code: 1364

Message: Field 'b' doesn't have a default value

*************************** 2. row ***************************

  Level: Warning

   Code: 1364

Message: Field 'c' doesn't have a default value

2 rows in set (0.00 sec)

 

 

 

2. Create table storage engine 지정이 잘못 되었을시 default engine으로 대체

 

create table test_engine(f1 int) engine = kpt;

 

 

mysql> create table test_engine(f1 int) engine = kpt;

Query OK, 0 rows affected, 2 warnings (0.00 sec)

 

mysql> show warnings\G

*************************** 1. row ***************************

  Level: Warning

   Code: 1286

Message: Unknown storage engine 'kpt'

*************************** 2. row ***************************

  Level: Warning

   Code: 1266

Message: Using storage engine InnoDB for table 'test_engine'

2 rows in set (0.00 sec)

 

mysql> show create table test_engine;

+-------------+-----------------------------------------------------------------------------------------------+

| Table       | Create Table                                                                                  |

+-------------+-----------------------------------------------------------------------------------------------+

| test_engine | CREATE TABLE `test_engine` (

  `f1` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------------+-----------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

 

mysql> show global variables like '%default%';

+------------------------+--------+

| Variable_name          | Value  |

+------------------------+--------+

| default_storage_engine | InnoDB |

| default_week_format    | 0      |

+------------------------+--------+

2 rows in set (0.00 sec)