DML

DML

Data Manipulation Language

INSERT

  • ON DUPLICATE KEY UPDATE

    • 중복키 제약조건에 위배되면 지정한 데이터만 업데이트하는 명령어

  • NOT EXISTS

    • 이미 존재하는 것을 제외하는 명령어

      INSERT INFO users (user_id, email, name, version)
SELECT user_id, email, name, 'v2'
FROM users AS u1
WHERE version = 'v1'
AND NOT EXISTS(
    SELECT 1
    FROM users AS u2
    WHERE  u1.user_id = u2.user_id
    AND u2.version = 'v2'
);

  • INSERT IGNORE INTO

  • REPLACE INFO

Types

VARCHAR

  • 최대 65,535 Byte

  • MySQL 4.x 에서는 Byte 단위

  • MySQL 5.x 에서는 Character 단위

    • utf8 기준으로 문자당 최대 3Bytes이므로 최대 21,844자 선언 가능

      • 테이블이 utf8 기준으로 생성되었다면 emoji 에러 발생할 수 있음

      • == utf8mb3

    • utf8mb4

      • 한 문자를 4Bytes로 취급

  • Unicode Character Sets

DATETIME

The DATETIME type is used for values that contain both date and time parts. MariaDB retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

  • "1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999"를 표현할 수 있음

  • 소수점(fractional seconds part)까지 포함하려면 DATETIME(6) 과 같이 지정할 수 있음(기본 0, 최대 6)

  • 예전에 cubrid 였는지 oracle이었는지 찾아봤던 것 같은데.. datetime은 내부적으로 string으로 저장했던것 같음. (https://github.com/wicksome/TIL/issues/5)

  • 입력 받은 값 그대로를 저장

    • 즉, 아예 시간대에 영향이 없음

    • 입력하고, 가져다 쓰는 곳에서 고민하면 됨

  • 이 값은 어플리케이션과 DB 서버 장비의 설정들만 처음에 잘 설정해두면 가장 깔끔할 것 같음

    • Instant 인스턴스 값이 나노초를 가지고 있고, 변환시 일부 날려먹거나 반올림 하는 것들을 아예 회피할 수 있으니…​.

  • MariaDB에서는 zero-date 값을 표현하기 위해 0000-00-00 을 지원함 (참고)

References

TIMESTAMP

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

  • UTC를 기준으로 '1970-01-01 00:00:01' ~ '2038-01-19 03:14:07'.

  • 입력받은 리터럴을 UTC로 변경하여 저장.

    • 즉 시간대 정보를 가지고 있음을 뜻함. (UTC 시간대)

  • Y2K, Y2K38

  • 인덱스가 더 빠르게 생성된다?

References

JSON

  • mariadb에선 LONGTEXT 의 alias라고 보고, 최대 4GB 까지 가능하다. (참고)

  • mysql에서도 LONGTEXT or LONGBLOB이랑 거의 동일하나, JSON 문서의 크기는 max_allowed_packet 시스템 변수의 값으로 제한된다. (참고)

    show variables like 'max_allowed_packet';

함수

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP(): Returns the current date time with your timezone configured.

-- CURRENT_TIMESTAMP
SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP+0, CURRENT_TIMESTAMP(6), CURRENT_TIMESTAMP(6)+0;

  • 현재 DB 타임존 기준으로 생성

  • current_timestamp(6) ref

  • 아래와 같이 설정이 가능해보이는데 mysql 버전에 따라 다른듯…​

    created_at bigint not null default current_timestamp(6)

UTC_TIMESTAMP

UTC_TIMESTAMP(): Returns the current date and time using UTC timezone.

UNIX_TIMESTAMP

UNIX_TIMESTAMP(): 현재 유닉스 타임스탬프 반환

UNIX_TIMESTAMP(str): str 을 유닉스 타임스탬프로 반환

-- UTC_TIMESTAMP
SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP()+0, UTC_TIMESTAMP(6), UTC_TIMESTAMP(6)+0;

  • 현재 시간을 Unix timestamp 형식으로 반환(second)

References

파티션

  • MySQL 서버 입장에서는 데이터를 별도의 테이블로 분리해서 저장하지만, 사용자 입장에서는 여전히 하나의 테이블로 읽기와 쓰기를 할 수 있게 해주는 솔루션

  • 일반적으로 DBMS의 파티션은 하나의 서버에서 테이블을 분산하는 것이며, 원격 서버 간에 분산을 지원하는 것은 아니다.

파티션 종류

  • Range

  • List

  • Composite

  • Hash

파티셔닝 방법

  • Horizontal

  • Vertical

샤딩, Sharding

  • 수평 파티셔닝하고 동일

  • join 연산을 사용할 수 없음

  • auto_increment를 사용하면 샤드별로 달라질 수 있음

참고

  • MySQL v5.6 부터 DATETIME 타입에 기본으로 날짜/시간이 들어가게 설장할 수 있음

    CREATED TABLE t1 (
    col1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

  • MySQL v5.7.2 부터 log_timestamps 라는 시스템 변수 추가

    • 로그 파일들은 어떤 시간대로 저장할지 설정하는 값.

    • 운영중에 변경 가능하고, SYSTEM, UTC 둘 중 하나로 설정 가능

  • SQL92-Standard의 TIMESTAMP는 second까지 지원

    • MySQL v5.6.4 부터 TIME, DATETIME, TIMESTAMP 값에 대한 소수 자릿수 초도 지원함

  • 숫자 값에 의해서만 파티션 가능했으나, MySQL 5.5 부터는 문자열이나 날짜 타입모두 사용할 수 있도록 개선됨

  • kotlin `Instant`를 그대로 저장할 때 지정된 타입에 따라 반올림되거나 밀리초 이하가 제거되는 이슈 발생할 수 있음. TC 비교시 시간 변환이 필요함

    // 00:00:00.1234567 → 00:00:00.123
private fun Instant.deleteNanoSecond(): Instant = this.truncatedTo(ChronoUnit.MILLIS)

// 00:00:00.1234567 → 00:00:00
private fun Instant.deleteMilliSecond(): Instant = this.truncatedTo(ChronoUnit.SECONDS)

인덱스