데이터 타입을 선언할 때 int, bigint가 뭔지는 알겠는데 뒤에 () 괄호에 대해 애매하다는 얘기가 있어서
이번에 정리한다.
1. 결론
()는 자리수를 선언하는 것이다. (10)이면 10자리, (20)이면 20자리까지 넣겠다는 의미
하지만 int나 bigint에서 괄호 ()안에 숫자는 Zerofill 이라는 옵션을 추가로 사용할 때만
의미가 있고 그것이 아니라면 큰 의미없다.
2. 이유
괄호안의 숫자는 디스플레이 너비 속성 display width attribute
즉, 자릿수를 나타내는데 zorefill 속성을 사용할 경우 자릿수 만큼 유효숫자 0을 채우는 것이다.
자릿수를 지정하지 않았을 경우에는 INT의 기본값은 11, BIGINT의 기본값은 20으로 자릿수가 자동 설정된다.
예컨대 INT(3)으로 zerofill속성을 사용해 id컬럼을 생성하면
id값으로 1, 2, 3, … 998, 999, 1000, 1001을 저장했을 때,
데이터베이스에는 001, 002, 003, … 998, 999, 1000, 1001로 기록이 된다.
즉, 숫자형뒤의 괄호안의 숫자만큼 자릿수에 유효숫자가 채워진다.
그리고 괄호안의 숫자를 넘는 자릿수의 수에 대해서는 상관없이 기록이되고, 숫자형이 허용하는 범위까지 값을 저장할 수 있다.
헷갈리면 안되는 것이 괄호안의 숫자가 자릿수를 규정하는 것은 아니라는 것이다.
즉, zerofill 속성을 사용하지 않는 경우라면 괄호안의 숫자는 의미가 없다.
그리고 zerofill 속성을 사용하는 것은 양수에만 가능하므로, 자동으로 unsigned속성이 적용된다.
3. 테스트
괄호안에 별다른 선언을 하지 않으면 default가 int(11), bigint(20)이 된다고 한다.
그럼 만약 int(11)을 초과해서 int(20)을 선언한다면 이것은 문제가 될까?
#테이블 생성
CREATE TABLE TMP_LOAD_20220816 (
x INT(20) NOT NULL,
y INT(20) ZEROFILL NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='zerofill 테스트';
#데이터 insert
INSERT INTO TMP_LOAD_20220816 (x,y) VALUES
(1, 1),
(12, 12),
(123, 123),
(1234, 1234),
(123456789, 123456789);
commit;
#데이터 확인
SELECT x, y FROM TMP_LOAD_20220816;
result)
-> int(20)으로 선언한 대로라면 00000000000123456789 이렇게 출력될것이라고 생각했지만
int의 최대허용범위는 10자리이므로 그 이상은 출력하지 않았다.
별의미가 없다.
#번외 테스트
#int형 최대치로 넣어보기
INSERT INTO TMP_LOAD_20220816 (x,y) VALUES
(2147483647, 4294967295);
commit;
SELECT x, y FROM TMP_LOAD_20220816;result)
-> zerofill 일때 unsigned로 자동변환이라고 하여 실제로 값을 넣어보니
최대허용치 4294967295 까지 insert 되는것을 확인함
# unsigned 최대치에서 1만 더 올려볼까? 4294967295-> 4294967296
INSERT INTO TMP_LOAD_20220816 (x,y) VALUES
(2147483647, 4294967296);
SQL Error [1264] [22001]: Data truncation: Out of range value for column 'y' at row 1
Data truncation: Out of range value for column 'y' at row 1
Data truncation: Out of range value for column 'y' at row 1
컬럼에 약 43억 이상의 수가 들어간다면 int보단 bigint를 선언하는 것이 좋겠다.
# 큰의미는 없지만 int(10) vs int(11) 보통 혼용하는데 둘 중 뭘 써야할까?
-> 괄호 ()에 아무런 선언을 하지 않으면 mysql default 옵션은 int(11) 이다.
#int는 10자리로 알고있는데 왜 default는 11자리 일까?
-> 음수까지 표현하기 위함
int 값은 -2147483648일 수 있으며 11자리이므로 기본 표시 크기는 11입니다 .
unsigned int는 음수를 허용하지 않으므로 기본적으로 표시 크기 10 만 필요합니다.
아래 문서에서 볼 수 있듯이 SIGNED INT 및 UNSIGNED INT를 저장하는 데 필요한 비트 수는 동일하고 저장 가능한 숫자의 범위는 이동됩니다.
참조 : https://stackoverflow.com/questions/5256560/mysql-datatype-int11-whereas-unsigned-int10
4. mysql 8 특정버전부터는 ZEROFILL 옵션 종료
mysql 8.0.17부터 Zerofill 속성은 사용되지 않는다
zerofill속성이 제외되면 LPAD()함수를 사용하여 원하는 자릿수만큼 0으로 채우거나, 형식이 지정된 숫자를 CHAR형 컬럼에 저장하면 된다.
혼란만 가중시키는 옵션이라 걍 제거하고 LPAD() 함수 쓰도록 유도하는 것으로 보인다.
참조:
https://dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html
https://dogleg.co.kr/?p=163
'RDB > mysql' 카테고리의 다른 글
| mysqldump 사용시 주의점 (0) | 2023.01.10 |
|---|---|
| mysql 특정 테이블에 권한 추가 에러 해결 / ERROR 1144 (42000): Illegal GRANT/REVOKE command (0) | 2022.10.14 |
| .TRG 파일 이건 뭐야 (0) | 2021.03.23 |
| mysql 디스크 용량이 거의 다 찼어요 (0) | 2021.03.06 |
| 특정 컬럼 뒤에 여러개 컬럼 추가 (0) | 2020.12.03 |
dung beetle
취미는 데이터 수집 직업은 MYSQL과 함께 일하는 DBA의 소소한 일상 이야기
