mysql에서 int 자료형에 string을 넣으면 들어갈까?

MySQL에서 int자료형인 칼럼에 string 자료형인 값을 넣으면 과연 들어갈까?

답은 들어갈때도 있고, 아닐때도있다.

그 의미인 즉, MYsql sql_mode에 따라 다르다.

sql_mode = 'ANSI' 인 경우 들어가고,

sql_mode = 'STRICT_ALL_TABLES' 이거나,

sql_mode = 'TRADITIONAL' 이면 안들어간다.

아래참조.

( https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

The Most Important SQL Modes

The most important sql_mode values are probably these:

• ANSI

  This mode changes syntax and behavior to conform more closely to standard SQL. It is one of the special combination modes listed at the end of this section.

• 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 details are given later in this section.

  As of MySQL 5.7.5, the default SQL mode includes STRICT_TRANS_TABLES.

• TRADITIONAL

  Make MySQL behave like a “traditional” SQL database system. A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column. It is one of the special combination modes listed at the end of this section.

)

create table test_strict_mode(

id int,

string_data varchar(10)

);

insert into test_strict_mode values ('test', 'test');

select * from test_strict_mode;

위 쿼리문 실행시, 

1, set sql_mode = 'ANSI'; 인 경우

결과값은 1 row(s) returned.

id = 0,

string_data = 'test'

로 결과값 나옴

2. set sql_mode = 'TRADITIONAL' 이거나, set sql_mode = 'STRICT_ALL_TABLES'인 경우에는 

애초에 값자체가 안들어감.

에러메시지: Error Code: 1366. Incorrect integer value: 'test' for column 'id' at row 1 0.011 sec

https://stackoverflow.com/questions/2262196/why-can-you-insert-characters-into-a-mysql-int-field

[ The default SQL mode in MySQL 5.7 includes these modes: 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.]

 위와같은 모드들은 5.7버전 이상에는 기본버전인듯하다..ㅠ