테마
숫자·날짜 타입과 AUTO_INCREMENT
숫자 타입은 범위와 정확도를 고르는 일이다. 정수는 몇 바이트로 어디까지 셀지, 소수는 근삿값으로 둘지 정확한 값으로 둘지를 정한다. 날짜 타입은 시간대를 어떻게 다룰지를 고르는 일이다. 이 문서는 정수·소수·날짜 타입을 보고, 일련번호를 자동으로 매기는 AUTO_INCREMENT는 이어지는 2편에서 본다.
정수 타입
| 타입 | 크기 | 부호 있음 최대 | UNSIGNED 최대 |
|---|---|---|---|
TINYINT | 1바이트 | 127 | 255 |
SMALLINT | 2바이트 | 32,767 | 65,535 |
MEDIUMINT | 3바이트 | 8,388,607 | 16,777,215 |
INT | 4바이트 | 약 21억 | 약 42억 |
BIGINT | 8바이트 | 약 922경 | 약 1,845경 |
UNSIGNED를 붙이면 음수를 버리는 대신 양수 범위가 두 배가 된다. INT(11)의 괄호 숫자는 표시 폭(display width)이라 부르며, 저장 범위와 아무 관계가 없다. 원래 조회 도구가 몇 자리로 보여 줄지 알려 주는 힌트였을 뿐이다. MySQL 8.0.17부터 정수의 표시 폭과, 빈자리를 0으로 채워 보여 주는 ZEROFILL은 폐기 예정(deprecated)이다. 앞자리 0이 필요하면 LPAD() 함수로 조회할 때 채운다.
타입 선택에서 가장 비싼 실수는 PK(Primary Key, 기본 키)를 작게 잡는 것이다. 부호 있는 INT 일련번호는 약 21억에서 멈추고, 그때 BIGINT로 바꾸는 것은 타입 변경이라 테이블 전체를 복사하는 작업이 된다. 행이 계속 쌓이는 테이블의 PK는 처음부터 BIGINT로 두는 편이 싸다.
부동 소수점과 DECIMAL
FLOAT(4바이트)와 DOUBLE(8바이트)은 부동 소수점(floating point) 타입이다. 부동 소수점은 수를 2진 소수로 근사해 저장하는 방식이라, 0.1처럼 10진수로는 깔끔한 값도 2진수로는 끝없이 이어져 가장 가까운 근삿값만 남는다. 그래서 0.1 + 0.2가 정확히 0.3이 아니게 되고, WHERE price = 19.99 같은 같음 비교가 기대와 다르게 실패할 수 있다.
돈·수량처럼 한 자리도 틀리면 안 되는 값은 DECIMAL(M,D)를 쓴다. DECIMAL은 10진수 자릿수를 그대로 저장하는 고정 소수점(fixed point) 타입이다. M은 전체 자릿수(최대 65), D는 소수점 아래 자릿수다. DECIMAL(5,2)는 -999.99부터 999.99까지 담는다. 공간은 9자리마다 4바이트 정도로 부동 소수점보다 크고 계산도 조금 느리지만, 오차가 없다는 값어치가 훨씬 크다.
MySQL 8.0.17부터 폐기 예정이 되어 MySQL 8.4에서도 피해야 할 문법이 둘 있다. FLOAT(7,4)처럼 자릿수를 주는 MySQL 전용 문법은 폐기 예정이다. 또 FLOAT·DOUBLE·DECIMAL에 붙이는 UNSIGNED도 폐기 예정이다. 음수를 막고 싶으면 CHECK (price >= 0) 같은 제약 조건(constraint)을 쓴다.
DATETIME과 TIMESTAMP
DATETIME | TIMESTAMP | |
|---|---|---|
| 범위 | 1000-01-01 ~ 9999-12-31 | 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07 (UTC) |
| 크기 | 5바이트 + 소수 초 | 4바이트 + 소수 초 |
| 시간대 | 넣은 값 그대로 저장 | 저장할 때 UTC로, 읽을 때 세션 시간대로 변환 |
가장 큰 차이는 시간대다. TIMESTAMP는 넣을 때 접속 세션(session, 클라이언트 연결 하나)마다 정하는 시간대(time_zone)를 기준으로 UTC(Coordinated Universal Time, 협정 세계시)로 바꿔 저장하고, 읽을 때 다시 그 세션의 시간대로 바꿔 보여 준다. 그래서 같은 행이라도 세션 시간대를 바꾸면 다른 시각으로 보인다. DATETIME은 이런 변환 없이 적힌 글자 그대로의 날짜·시각을 저장한다. 여러 시간대 사용자가 "같은 순간"을 봐야 하면 TIMESTAMP가 편하고, 예약 일시처럼 "적은 그대로"여야 하면 DATETIME이 맞다.
TIMESTAMP의 약점은 범위다. 1970년 1월 1일부터 지난 초를 부호 있는 4바이트 정수로 담는 방식이라, 그 최댓값(약 21억 초)에 닿는 2038년 1월 19일 03:14:07(UTC)을 넘는 값을 저장할 수 없다. 이것을 2038년 문제라 부른다. 만료일처럼 먼 미래가 들어갈 칼럼은 DATETIME으로 둔다. 두 타입 모두 DATETIME(6)처럼 소수 초를 6자리(마이크로초)까지 담을 수 있고, 자릿수에 따라 0~3바이트가 더 든다.
자동 입력과 자동 갱신
DEFAULT CURRENT_TIMESTAMP는 행을 넣을 때 현재 시각을 채우고, ON UPDATE CURRENT_TIMESTAMP는 행을 고칠 때마다 현재 시각으로 바꾼다. 둘 다 TIMESTAMP와 DATETIME 모두에 쓸 수 있다.
sql
CREATE TABLE orders (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
);주의할 점이 둘 있다. UPDATE가 모든 칼럼을 지금과 같은 값으로 쓰면 실제로 바뀐 것이 없으므로 updated_at도 그대로다. 또 소수 초 자릿수를 한 곳에 적었으면 칼럼 정의 전체에서 같은 자릿수를 써야 하고, 다르게 쓰면 허용되지 않는다. 위 예처럼 (3)을 세 곳에 모두 맞춘다.
AUTO_INCREMENT가 동시 INSERT에 번호를 나눠 주는 방식과 빈틈·재시작 동작은 숫자·날짜 타입과 AUTO_INCREMENT 2에서 이어 본다.
면접 포인트
- INT(11)의 11은 표시 폭이라 범위와 무관, 8.0.17부터 폐기 예정. 커지는 테이블 PK는 BIGINT.
- 돈은 DECIMAL. FLOAT·DOUBLE은 2진 근삿값이라 같음 비교가 깨진다.
- TIMESTAMP는 UTC 변환 + 2038년 한계, DATETIME은 변환 없음 + 9999년까지.
ON UPDATE CURRENT_TIMESTAMP는 TIMESTAMP·DATETIME 둘 다 된다. 값이 실제로 안 바뀌면 갱신되지 않는다.