[MySQL] int(n)와 zerofill, varchar(n)와 sql_mode

MySQL에서 VARCHAR는 문자열 타입 중 하나로 가변 길이의 문자열을 저장합니다. 필요한 만큼만 공간을 사용하기에 고정 길이 타입보다 저장 공간을 적게 사용하기도 합니다. VARCHAR의 default 크기는 255입니다. VARCHAR(255)

예를 들어 데이터 타입이 VARCHAR(4)인 컬럼에는 4byte까지만 데이터를 저장할 수 있습니다.

 

정수 타입 중 INT의 범위는 ~2147483648 ~ 2147483647 (signed), 0 ~ 4294967295 (unsigned) 인데, 처음에는 INT(N)에서 N에 들어가는 값이 VARCHAR처럼 길이인 줄 알았습니다. 그래서 두 자리 숫자를 저장하기 위해 INT(2)로 타입을 지정했는데, INT 범위의 숫자는 다 저장이 되었습니다.

mysql> CREATE TABLE test(a varchar(4), b int(2));
mysql> INSERT INTO test(a, b) VALUES ('test', '12345');
mysql> SELECT * FROM test;
# 기대값 : ('test', '12')
# 실제 조회값 : ('test', '12345')
+----------+----------+
| a        | b        |
+----------+----------+
| test     | 12345    |
+----------+----------+

 

확인해보니 N은 zerofill 옵션 설정 시 N 자릿수를 기준으로 값의 공백을 0으로 바꾸어 주는 역할을 합니다.

즉, N으로 INT 값의 범위를 정하는 것이 아닙니다.

mysql> CREATE TABLE test(a int(10), b int(10) zerofill);
mysql> INSERT INRO test(a, b) VALUES ('test', '123', '123');
mysql> SELECT * FROM test;
+---------+--------------+
| a       | b            |
+---------+--------------+
| 123     | 0000000123   |
+---------+--------------+

 

 

그리고 VARCHAR의 지정 byte가 넘어갔을 경우 당연히 오류가 발생하는 줄 알았습니다.

mysql> CREATE TABLE test(a varchar(2), b varchar(4));
mysql> INSERT INTO test(a, b) VALUES ('test', 'testtest');
# 기대 실행값 : ERROR: 1406, Data too long for column 'a' at row 1
# 실제 실행값 : Query OK, 1 row afttected, 2 warning
mysql> SELECT * FROM test;
+-----+------+
| a   | b    |
+-----+------+
| te  | test |
+-----+------+

그런데 생각과 다르게 값이 사이즈만큼 truncate 되어 insert 되는 것을 보고 당황했습니다. 확인해보니 sql_mode 라는 strict mode에 따라 데이터 검증을 수행한다고 합니다. (https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_sql-mode)

 

DB에 sql_mode를 조회해보니 값이 비워져 있었습니다. sql_mode를 값을 지정한 형태로 테이블에 삽입할 수가 없으면 명령문을 중단하는 STRICT_TRANS_TABLES로 설정하고 다시 값을 insert 해보겠습니다.

mysql> SET @@sql_mode="STRICT_TRANS_TABLES";
mysql> SELECT @@sql_mode;
+---------------------+
| @@sql_mode          |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+

mysql> CREATE TABLE test(a varchar(2), b varchar(4));
mysql> INSERT INTO test(a, b) VALUES ('test', 'testtest');
ERROR: 1406, Data too long for column 'a' at row 1
mysql> SELECT * FROM test;
+-----+------+
| a   | b    |
+-----+------+

이전 실행 결과와 다르게 값이 저장이 안 되고 오류가 발생하는 것을 확인할 수 있습니다.