2023/06/27 ~ 2023/07/03

15.2 숫자

15.2.5 자동 증가 (AUTO_INCREMENT) 옵션 사용

숫자 타입의 칼럼에 자동 증가 옵션을 사용해 인조 키(Artificial Key)를 생성할 수 있다

테이블의 PK를 구성하는 칼럼의 크기가 너무 크거나 PK로 사용할 만한 칼럼이 없을 때

AUTO_INCREMENT 자동 증가값 변경

auto_increment_increment 와 auto_increment_offset 시스템 설정을 이용
일반적으로 두 시스템 변숫값은 모두 1
만약 auto_increment_increment 10이고 auto_increment_offset 5일 때 자동 생성되는 값은 5, 15, 25, ... 와 같이 증가

AUTO_INCREMENT 옵션을 사용한 칼럼

반드시 그 테이블에서 PK나 유니크 키의 일부로 정의해야 함

PK나 유니크키가 여러 개의 칼럼을 구성되면?

AUTO_INCREMENT 속성의 칼럼값이 증가하는 패턴이 스토리지 엔진마다 달라짐
MyISAM : 자동 증가 옵션이 사용된 칼럼이 PK나 유니크 키의 아무 위치에나 사용될 수 있다
InnoDB : 자동 증가 칼럼으로 시작되는 인덱스(PK 또는 일반 인덱스)를 생성해야 한다 아래 예제 처럼 InnoDB 테이블에서 AUTO_INCREMENT 칼럼을 PK의 뒤쪽에 배치하면 오류가 발생함

// AUTO_INCREMENT 칼럼을 PK의 뒤쪽에 배치해 테이블 생성 시 오류 발생
CREATE TABLE tb_autoinc_innodb (
    fd_pk1 INT NOT NULL DEFAULT '0',
    fd_pk2 INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (fd_pk1, fd_pk2)
) ENGINE=INNODB

// AUTO_INCREMENT 칼럼이 PK 뒤에 배치되어 있으나 유니크 키에서 제일 선두에 위치하여 정상적으로 테이블이 생성됨
CREATE TABLE tb_autoinc_innodb (
    fd_pk1 INT NOT NULL DEFAULT '0',
    fd_pk2 INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (fd_pk1, fd_pk2)
    UNIQUE KEY ux_fdpk2 (fd_pk2)
) ENGINE=INNODB

AUTO_INCREMENT

테이블당 하나만 사용
해당 칼럼이 없는 테이블에 새로운 AUTO_INCREMENT 칼럼을 추가하면 새로 추가된 칼럼은 1부터 자동으로 증가된 값이 할당됨
해당 칼럼의 현재 증가 값은 테이블의 메타 정보에 저장됨
다음 증가 값을 보려면 SHOW CREATE TABLE 명령으로 조회할 수 있다

CREATE TABLE tb_autoinc_innodb (
    fd_pk1 INT NOT NULL DEFAULT '0',
    fd_pk2 INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (fd_pk1, fd_pk2)
    UNIQUE KEY ux_fdpk2 (fd_pk2)
) ENGINE=INNODB AUTO_INCREMENT=7;

자동 증가값이 1이 아닌 7부터 시작되니 개발용 서버에서 SHOW CREATE TABLE명령으로 조회한 DDL 내용 그대로 서비스용 MySQL에다가 사용할 때 자동 증가값이 1이 아닌 값으로 설정되니 조심

15.3 날짜와 시간

날짜 혹은 시간만 따로 저장할 수 있다

날짜와 시간을 합쳐서 하나의 칼럼에 저장할 수 있게 여러 가지 타입을 지원

DATE, DATETIME 타입을 많이 사용함

MySQL 5.6부터 TIME과 DATETIME, TIMESTAMP 타입은 밀리초 단위의 데이터를 저장할 수 있게됨

칼럼의 저장 공간 크기는 밀리초 단위를 몇 자리 까지 저장하느냐에 따라 달라짐 389p
밀리초 단위는 2자리당 1바이트씩 공간 더 필요
MySQL 8.0 에서 마이크로초까지 저장 가능한 DATETIME(6) 타입은 8바이트(5+3)를 사용
밀리초 단위로 데이터를 저장하기 위해 시간 타입 뒤에 (숫자) 표기하면된다
NOW도 NOW(6) NOW(3) 과 같이 사용하여 밀리초의 자릿수를 명시할 수 있다
NOW()는 NOW(0)으로 실행된다

MySQL 날짜 타입은 칼럼 자체에 타임존 정보가 저장되지 않는다

DATETIME이나 DATE 타입은 현재 DBMS 커넥션의 타임존과 관계없이 클라이언트로부터 입력된 값을 그대로 저장하고 조회할 때도 변환없이 그대로 출력

하지만 TIMESTAMP는 항상 UTC 타임존으로 저장되므로 타임존이 달라져도 값이 자동으로 보정된다

주의

SET timezone='...' 명령을 실행할 때 Unknown or incorrect time zone: 'Asia/Seoul' 와 같은 에러가 발생하면?

타임존 정보가 MySQL 서버에 준비돼 있지 않기 때문
메뉴얼 참고해서 MySQL 서버에 타임존 정보를 적재하고 SET timezone 명령을 실행하면 된다
타임존 정보를 적재하기 어려운 환경이라면 대안으로 타임존의 이름 대신 시간 차이 타임존을 사용하면 된다
- `SET time_zone='+09:00'; / 'Asia/Seoul' /

자바 응용 앱에서 타임존을 한국 서울로 한 경우와 미국 로스앤젤레스로 변경했을 때 MySQL 서버의 데이터 차이

JVM 타임존이 한국 서울
- datetime, timestamp 모두 2020-09-10 09:25:23.0
JVM 타임존이 미국 로스앤젤레스
- datetime, timestamp 모두 2020-09-10 17:25:23.0

MySQL 서버의 칼럼 타입 datetime, timestamp에 상관없이 JDBC 드라이버는 날짜 및 시간 정보를 MySQL 타임존에서 JVM의 타임존으로 변환해서 출력함

자바의 ResultSet 클래스에서 MySQL 서버의 DATETIME 칼럼의 값을 온전히 가져올 수 있는 함수가 getTimestamp() 뿐이기 때문에 datetime, timestamp 타입 칼럼 모두 타임존 변환이 된 것이기도 함
다른 방식으로 가져온다면 타임존 변환이 되지 않을 수도 있다
단순히 조회뿐만 아니라 응용 프로그램에서 DB로 날짜 및 시간 데이터를 저장할 때도 동일한 규칙이 적용된다

요즘은 Hibernate나 MyBatis 등과 같은 ORM을 사용하기에 코드 내부에서 DATETIME 칼럼값을 어떤 JDBC API를 이용해서 페치하는지, 타임존 변환이 기대하는데로 작동하는지 테스트 해보기를 권장함

최대한 응용 프로그램에서 시간 정보를 강제로 타임존 변환을 하거나 MySQL 서버의 SQL 문장으로 CONVERT_TZ() 함수를 이용해 타임존 변환을 하지 않도록 하자

이미 데이터를 가지고 있는 MySQL 서버의 타임존을 변경해야 한다면?

타임존 설정뿐만 아니라 테이블의 DATETIME 타입의 칼럼이 가지고 있는 값도 CONVERT_TZ()와 같은 함수를 이용해 변환해야 한다

하지만 TIMESTAMP 타입의 값은 항상 UTC로 저장되기에 MySQL 서버의 타임존을 변경한다고 해서 별도로 변환 작업이 필요로 하지 않는다

system_time_zone 시스템 변수는 MySQL 서버의 타임존을 의미

일반적으로 OS 타임존을 그대로 상속
MySQL을 기동하는 OS 계정의 환경 변수를 변경하거나 mysqld_safe를 시작할 때 --timezone 옵션을 이용해 변경할 수 있다

time_zone 시스템 변수는 MySQL 서버로 연결하는 모든 클라이언트 커넥션의 기본 타임존을 의미

time_zone 시스템 변수에 아무것도 설정하지 않으면?

SYSTEM으로 자동 설정되고 이는 system_time_zone 시스템 변수의 값을 그대로 사용한다는 의미

15.1.6 문자열 이스케이프 처리

MySQL 에서의 SQL 문장에 사용하는 문자열은 프로그래밍 언어처럼 "\"를 이용해 이스케이프 처리를 하는 것이 가능
- \0: 아스키 NULL 문자
- \': 홑따옴표
- \": 쌍따옴표
- \b: 백스페이스 문자
- \n: 개행문자
- \r: 캐리지 리턴 문자 (유닉스 계열이 아닌 윈도우 운영체제에서 개행문자는 \r\n의 조합으로 개행문자를 사용
- \t: 탭 문자
- \\: 백 슬래시(\) 문자
- \%: 퍼센트 문자. LIKE 패턴에서만 사용
- \_: 언더스코어 문자. LIKE 패턴에서만 사용

15.2 숫자

숫자를 저장하는 타입
- 값의 정확도 기준
- 참값 Exact value: 소수점 이하의 값의 유무와 관계없이 정확히 그 값을 그대로 유지하는 것. INT, DECIMAL
- 근사값 타입: 흔히 부동소수점이라고 불리는 값을 의미. 처음에 저장한 값과 조회된 값이 정확히 일치하지 않음. FLOAT, DOUBLE
- 값 저장 포맷 기준
- 이진 표기법: 흔히 프로그래밍 언어에서 사용하는 정수나 실수 타입을 의미. INTEGER, BIGINT 등 대부분의 숫자타입
- 십진 표기법 DECIMAL: 숫자 값의 각 자릿값을 표현하기 위해 4비트나 한 바이트를 사용해서 표기하는 방법. DECIMAL
DBMS에서는 근사값은 저장할 때와 조회할 때의 값이 정확하게 일치하지 않고 유효 자릿수를 넘어서는 소수점 이하의 값은 계속 바뀔 수 있음
- STATEMENT 포맷을 사용하는 복제에서는 소스 서버와 레플리카 서버 간 데이터 차이가 발생할 수 있음
- 매우 큰 숫자 값이나 고정 소수점을 저장해야하는 것이 아니라면 일반적으로 INTEGER나 BIGINT 타입을 자주 사용

15.2.1 정수

DECIMAL 타입을 제외하고 정수를 저장하는데 사용할 수 있는 데이터 타입은 5가지
- TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
- 숫자값의 법위만 다를 뿐 차이는 거의 없으므로 입력이 가능한 수의 범위 내에서 최대한 저장 공간을 적게 사용하는 타입을 선택하면 됨
SIGNED vs UNSIGNED
- 기본적으로 정수 컬럼을 생성할 때 UNSIGNED 옵션을 명시하지 않으면 SIGNED 타입으로 음수, 양수를 모두 저장할 수 있음
- UNSIGNED 옵션은 .보다 큰 양의 정수만 저장할 수 있고 최대값은 SIGNED 보다 2배 커짐
- 옵션 사용 유무에 따라 저장되는 값의 범위가 다르므로 외래키로 사용하는 칼럼이나 조인 조건이 되는 칼럼은 옵션을 일치시키는 것이 좋음

15.2.2 부동 소수점

부동: 소수점의 위치가 고정적이지 않다 -> 숫자 값의 길이에 따라 유효 범위의 소수점 자릿수가 바뀜
- 정확한 유효 소수점 값을 식별하기 어렵고 그 값을 따져서 크다 작다 비교를 하기가 쉽지 않음. 동등 비교는 사용할 수 없음
부동 소수점을 저장하기 위해 FLOAT와 DOUBLE 타입을 사용할 수 있음
부동 소수점 값을 저장하기위해 유효 소수점의 자릿수만큼 10을 곱해서 정수로 만들고 그 값을 정수 타입의 칼럼에 저장하는 방법도 있음

15.2.3 DECIMAL

부동 소수점과 달리 소수점의 위치가 가변적이지 않은 고정 소수점 타입
소수점 이하의 값까지 정확하게 관리해야 하는 경우 DECIMAL 타입을 이용해야 함
(DECIMAL로 저장하는 숫자의 자릿수) / 2 의 결과값을 올림 처리한 만큼의 바이트 수가 필요함
소수가 아닌 정수값을 관리하기 위해 사용하는 것은 성능상으로나 공간 사용면에서 좋지 않음

15.2.4 정수 타입의 칼럼을 생성할 때의 주의사항

부동 소수점이나 DECIMAL 타입을 이용해 칼럼을 정의할 때는 타입의 이름 뒤에 괄호로 정밀도를 표시하는 것이 일반적
- ex) DECIMAL(20, 5): 정수부 - 15자리 (20-5) / 소수부 - 5자리 까지 저장할 수 있는 DECIMAL 타입 생성
- ex) DECIMAL(20): 소수부 없이 정수부만 20자리 저장 가능한 DECIMAL 타입 생성
MySQL 5.7 버전까지는 부동 소수점(FLOAT, DOUBLE)이나 고정 소수점(DECIMAL) 이 아닌 정수 타입을 생성할 때도 똑같이 BIGINT(10)과 같이 괄호로 값의 크기를 명시할 수 있는 문법을 지원함
- 하지만 모든 정수 타입은 이미 고정형 데이터 타입이며, 괄호는 저장 가능한 값을 제한하는 용도가 아님. 단순히 화면 표시 자릿수를 의미
MySQL 8.0 버전 부터는 이렇게 정수 타입에 화면 표시 자릿수를 사용하는 기능이 제거되고, 뒤에 괄호로 명시한 "(10)" 은 무시됨

mojh7 / real-mysql-study