Ch 15 데이터 타입 - 숫자~TEXT와 BLOB

[kihyuk-sung]

p383 ~ p407
숫자 ~ TEXT와 BLOB

[janeljs]

15.2 숫자

• MYSQL의 바이너리 로그 포맷이 STATEMENT인 경우 소스 서버와 레플리카 서버 간 데이터 불일치 발생 가능
  • binary logging format
    Replication capabilities in MySQL originally were based on propagation of SQL statements from source to replica. This is called statement-based logging. You can cause this format to be used by starting the server with --binlog-format=STATEMENT.
• 정수타입(BIGINT, INTEGER, SMALLINT, TINYINT)은 고정형 타입이기 때문에 괄호에 숫자를 지정해도 저장가능한 값 제한하는 것 X
• auto increment 칼럼을 pk 뒤쪽에 배치하면 에러가 난다.
  • https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html
    use the AUTO_INCREMENT mechanism with an InnoDB table, an AUTO_INCREMENT column must be defined as the first or only column of some index such that it is possible to perform the equivalent of an indexed SELECT MAX(ai_col) lookup on the table to obtain the maximum column value. The index is not required to be a PRIMARY KEY or UNIQUE, but to avoid duplicate values in the AUTO_INCREMENT column, those index types are recommended.

15.3 날짜와 시간

• DATETIME은 8바이트, TIMESTAMP는 4바이트
• 밀리초 단위로 저장하려면 타입 뒤에 괄호로 숫자 표기, Now(6)과 같은 형태로도 밀리초 단위로 가져올 수 있음
• TIMESTAMP
  • 항상 UTC 타임존으로 저장, 타임존이 달라져도 값이 자동으로 보정
• DATETIME
  • DBMS 커넥션 타임존과 상관없이 클라이언트에서 입력한 값을 저장, 조회할 때도 변환없이 출력
  • ORM을 사용할 경우 DATETIME 컬럼 값을 어떤 JDBC API로 fetch 하는지, 타임존 변환은 어떻게하는지 테스트해보는 것이 좋다.
• ON UPDATE CURRENT TIMESTAMP로 설정하면 시점 자동 업데이트 가능

15.4 ENUM과 SET

• ENUM 또는 SET 타입의 컬럼으로 숫자 연산을 하면 매핑된 문자열이 아닌 내부적으로 저장된 숫자값으로 연산 실행(정수 타입의 컬럼이기 때문)
• ENUM
  • 데이터베이스 서버의 디스크 저장공간의 크기를 줄여준다는 장점
    • 디스크의 데이터는 InnoDB 버퍼 풀에 적재되어야 쿼리에서 사용할 수 있으므로 디스크 저장공간을 save할 수있다면 메모리도 save됨
    • 백업이나 복구 시간도 줄일 수 있음
  • 새로운 값을 추가하고 싶으면 테이블 구조 변경이 필요하다는 단점
  • 5.6 버전 이후로는 ENUM이 제일 마지막에 추가된다면 메타데이터 변경만으로 수정 가능(리빌드는 안해도 됨)

15.5 TEXT와 BLOB

• 대량의 데이터 저장 시 사용
• TEXT는 문자열 저장용이라 charset, collation 가지고 BLOB은 안 가짐
• ROW_FORMAT 옵션에서 TEXT, BLOB 저장 방식 결정

[kihyuk-sung]

숫자

• 참값 (INTEGER, DECIMAL)
• 근삿값 (FLOAT, DOUBLE)
• 이진표기법 vs 십진표기법

정수

• bytes 1,2,3,4,8
• signed / unsigned
• TINYINT(1) 뒤의 숫자는 화면에 표기한 자릿수를 의미한다.

부동소수점

• 비교 시 주의할 사항이 많음
• 정확한 값을 저장해야한다면 자릿수를 곱해서 올리고, 그 값을 정수로 저장하는 것도 방법

DECIMAL

• 자릿수 별로 수를 저장
• 성능 / 공간 상에서 좋지 않지만, 돈 등의 데이터를 관리해야한다면 필요하다.

AUTO_INCREAMENT

• offset / increament 변경 가능
• 사용 시 테이블의 PK나 유니크 키의 일부로 정의해야한다.
• MyISAM은 해당 복합 키의 위치 아무 곳에나 사용 가능하다.
• InnoDB는 해당 키가 반드시 앞에 와야 한다.
• 테이블 당 하나만 존재 가능하다

날짜와 시간

• 날짜 / 시간 / 날짜 + 시간 모두 저장 가능
• 저장공간 요구 사항 (https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html)
• 데이터 표기법 (https://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html)
• 밀리초단위가 더 필요하면 뒤에 자릿수를 붙인다 (ex. DATETIME(6))
• TIMESTAMP 는 unix timestamp 이므로 언제나 UTC로 저장
• DATETIME 등은 타임존이 없음
• jdbc의 경우 MySQL 타임존을 JVM 타임존으로 변환해서 출력한다.
• (서버 / MySQL 타임존을 UTC로 통일하기)
• 자동 업데이트 설정 가능

ENUM과 SET

• 문자열 값을 숫자 값으로 매핑하여 관리
• 숫자로 변환되므로 데이터만 보는 것으로는 의미를 이해하기 어려울 수 있음
• 그러나 문자열 값으로 저장하는 것보다 성능 / 공간 이점이 있다.
• SET은 하나의 칼럼에 여러개의 값을 저장할 수 있다.

TEXT와 BLOB

• 대용량 데이터를 저장하는 방법
• 레코드당 최대 제한치인 64KB가 넘는 데이터를 저장할 수 있다.
• 인덱스 레코드의 칼럼에도 제한이 있으므로, TEXT / BLOB 으로 저장하면 인덱스로 사용할 데이터를 제한해야할 수 있다.
• 저장 방식은 모든 데이터 포함 64KB가 넘지 않으면 데이터 레코드에 한번에 저장하며, 그렇지 않은 경우 페이지 체인으로 저장한다.

JPA / MySQL JDBC Type 참고

• https://docs.jboss.org/hibernate/orm/6.1/userguide/html_single/Hibernate_User_Guide.html#basic
• https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-type-conversions.html

[sanhee]

숫자

• 숫자를 저장하는 타입은 정확도에 따라 참값, 근삿값 타입으로 나뉨
• 근삿값은 저장할 때와 조회할 때 값이 정확히 일치 하지 않음

부동 소수점

• 부동 소수점은 근삿값을 저장하는 방식이라서 동등 비교는 사용할 수 없다.
• MySQL 서버의 바이너리 로그 포맷이 STATEMENT 타입인 경우, 소스 서버와 레플리카 서버 간의 데이터가 달라질 수 있다.

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

• 정수 타입(BIGINT, INTEGER, SMALINT, TINYINT 등)은 고정형 데이터 타입이며, 정수 타입 뒤에 명시되는 괄호는 화면에 표시할 자릿수를 의미할 뿐 저장 가능한 값을 제한하는 용도가 아니다.
  • MySQL 8.0 부터는 정수 타입에 화면 표시 자릿수를 사용하는 기능을 제거함

AUTO_INCREMENT 옵션

• 해당 칼럼은 테이블당 하나만 사용할 수 있다.
• SHO CREATE TABLE로 스키마를 복사할 때 AUTO_INCREMENT의 초깃값에 주의

날짜와 시간

• DATETIME이나 DATE 타입은 현재 DBMS 커넥션의 타임존과 관계없이 클라이언트로부터 입력된 값을 그대로 저장/출력
• TIMESTAMP는 항상 UTC 타임존으로 저장되므로, 타임존이 달라져도 값이 자동으로 보정
• JDBC 드라이버는 MySQL 서버의 칼럼 타임이 TIMESTAMP이든 관계 없이 JVM 타임존으로 자동 변환해서 출력 해준다.
  • 이유: 자바의 ResultSet 클래스에서 MySQL 서버의 DATETIME 칼럼의 값을 가져오는 함수가 getTimestaml()이기 때문임

날짜 자동 업데이트

• MySQL 5.6 버전부터는 TIMESTAMP와 DATETIME 칼럼 모두 INSERT와 UPDATE 문장이 실행될 때마다 해당 시점으로 자동 업데이트 되게 하려면 테이블을 생성할 때 칼럼 정의 뒤에 옵션을 정의하면 된다.
  • DEFAULT CURRENT_TIMESTAMP ...

ENUM과 SET

• 모두 문자열 값을 MySQL 내부적으로 숫자 값으로 매핑해서 관리하는 타입

ENUM

• 테이블 정의에 나열된 문자열 순서대로 1부터 할당, 빈 문자열은 0으로 매핑
• 테이블 구조에 정의된 코드 값만 사용할 수 있게 해줌
• 데이터베이스 서버의 디스크 저장 공간의 크기를 줄여줌
• 새로운 값을 추가 해야할 때, 구조를 변경해야 한다.
  • 중간에 삽입되는 경우 락과 리빌드 필요
• ENUM 타입의 칼럼값으로 정렬을 수행하면 매핑된 코드 값으로 정렬이 수행됨
  • 문자열로 정렬하고 싶다면 CAST() 함수 이용 (단, 인덱스를 이용할 수 없음.

SET

• 테이블의 구조에 정의된 아이템을 정숫값으로 매핑해서 저장하는 방식 (ENUM과 비슷)
• 하나의 칼럼에 1개 이상의 값을 저장할 수 있음.

TEXT와 BLOB

• TEXT 타입

  • 문자열을 저장하는 대용량 칼럼
  • 문자 집합이나 콜레이션을 갖는다.

• BLOB

  • 이진 데이터 대용량 칼럼
  • 별도의 문자 집합이나 콜레이션을 갖지 않는다.

• MySQL에서 TEXT, BLOB 칼럼 모두 대용량 칼럼, 사용 시 주의 필요

[ghojeong]

숫자

• 참값: INT, DECIMAL

• 근사값: FLOAT, DOUBLE

• 이진 표기법: INTERGER, BIGINT

• 십진 표기법: DECIMAL

부동 소수점

• 근사값이라서 저장할 떄와 조회할 때의 값이 일치하지 않을 수도 있다.

DECIMAL

이 DECIMAL 이 읽다가 흥미로웠는데,
한 자리를 디스크에 기록하기 위해 4비트 (0~15) 를 활용함.
DECIMAL 타입을 통해 BIGINT 보다 많은 65자리의 숫자와, 소수점의 정확한 값을 저장 가능.
돈 계산할 때 DECIMAL 타입을 써야한다.

소수점 이하의 값을 계산할 때는 부동 소수점 방식을 컴퓨터가 고집하기에,
DECIMAL 이라는 타입이 새로 생긴것 같은데 왜 굳이 십진법을 고수할 필요가 있었는지는 의문이다.

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

• DECIMAL(20, 5) : 정수부를 15자리까지, 소수부를 5자리까지 허용한다.
• 괄호는 정밀도의 조절이지, VARCHAR 와 다르게 저장 공간의 크기를 조절하는게 아니다.

AUTO_INCREMENT 옵션

• InnoDB 에서는 AUTO_INCREMENT 을 프라이머리 키의 뒤쪽에 배치하면 오류가 발생하지만, MyISAM 에서는 아무 위치에서나 가능하다.

날짜와 시간

• YEAR, DATE, TIME, DATETIME, TIMESTAMP
• 저장 공간의 크기는 밀리초 단위를 몇 자리까지 저장하느냐에 따라 달라진다.
• DATETIME 은 클라이언트로 부터 입력된 값을 그대로 출력하지만, TIMESTAMP 는 UTC 타임존으로 저장된다.
• 따라서 글로벌 서비스에서는 TIMESTAMP 를 우선시하는 것 같다.
• MySQL 서버에서는 time_zone 시스템 변수를 활용한다.

자동 업데이트

• DEFAULT CURRENT_TIMESTAMP옵션을 통해, 시간 자동 업데이트를 강제할 수 있다.
• created_at, updated_at 등을 설정할 때 유용한것 같다.

ENUM과 SET

ENUM

• 문자열이 아니라 정수값을 저장하기에 디스크 공간의 크기를 확 줄일 수 있다.
• MySQL 은 숫자를 1부터 센다. 주의하자.
• ENUM 타입에 새로운 값을 추가한다면, 테이블의 구조를 변경해야한다.

SET

• ENUM 처럼 문자열이 아니라 정수값을 저장한다.
• 차이는 한개의 칼럼에 1개 이상의 값을 저장한다는 점이다.
• 사용자 권한을 정의할 때 매우 유용할 듯하다.
• BIT-OR 연산을 사용한다고 하니, 서버 코드에서 직접 비트 연산을 할 필요가 없을 것 같다.

TEXT와 BLOB

• BLOB(Binary Large Object)
• TEXT 와 BLOB 의 유일한 차이점은 TEXT 는 문자 집합과 콜레이션을 가진다는 점이다.
• 오라클 DB 에서는 BLOB 이 TEXT 보다 대용량인 칼럼이지만, MySQL 에서는 차이가 없다

[kyupid]

15.2 숫자

• DBMS에서는 근사값은 저장할 때와 조회 할때의 값이 정확히 일치하지 않고, 유효 자릿수를 넘어서는 소수점 이하의 값은 계속 바뀔 수 있다.

15.2.1 정수

SIGNED, UNSIGNED 조인할 때 인덱스를 이용하지 못한다거나 하진 않는다.

15.2.2 부동소수점

부동이라는 말은 소수점의 위치가 고정적이지 않다는 말이다.
즉, 근삿값을 저장하는 방식이라 비교(Equal)가 불가능하다.

FLOAT 타입을 WHERE 절에 사용해서 SELECT 해보면 결과가 없다. (385p 상단)

그런데 만약에 GPS 좌표 같은 걸 저장해야한다면 어떻게 해야할까?
해당 좌표르 정수로 만들어서 저장한뒤에 꺼낼떄는 나누어서 가지고 와야한다.
예를 들어 0.1234 이면 1만을 곱해서 1234를 저장하고 꺼낼떄 1만을 나누어서 0.1234로 만든다.

15.2.3 DECIMAL

정확한 소수점 자리가 필요할때 쓰면 된다. (대출, 이자 돈관련 등)