[우리FISA] SQL Backgrounds

💡 charset (문자 집합)

UTF-8 문자 집합은 1~4바이트를 저장할 수 있도록 설계되었다.

세상의 모든 언어는 21bit(3byte 보다 약간 작음)로 저장되기 때문에 MYSQL은 utf8을 3바이트 변수 데이터 유형으로 설계했다.

BUT, 이모지가 등장하면서,
최근 4바이트 문자열(예: Emoji)을 utf8에 저장하려고 하면 값이 손실된다.

따라서 MySQL은 버전 5.5.3부터 utf8mb4라는 문자 집합을 도입했다.

• utf8mb4-unicode-ci: utf8mb4의 기본 콜레이션
• utf8mb4-unicode-520-ci: 유니코드 5.20.0의 utf8mb4 콜레이션

유니코드

💡 Collation (콜레이션)

Collation 은 텍스트 데이터를 정렬(ORDER BY)할 때 사용한다. 즉 text 계열 자료형에서만 사용할 수 있는 속성입니다.

• utf8_bin (or utf8mb4_bin) : 바이너리 저장 값 그대로 정렬합니다.(아스키 문자가 높은 순서대로 정렬)
  
   
• utf8_general_ci (or utf8mb4_general_ci): 텍스트 정렬할 때 a 다음에 b 가 나타나야 한다는 생각으로 나온 정렬방식. 문자열 자체의 오름차순을 사용합니다.
  
  ㅑ
• utf8_unicode_ci (or utf8mb4_unicode_ci) : general_ci 보다 조금 더 사람의 기준에 맞게 특수문자를 정렬합니다.

📌 MySQL에서 콜레이션 확인하기

SHOW COLLATION;

📌 MySQL에서 콜레이션 설정하기

CREATE DATABASE 테이블명 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Column 생성 시 제약조건

• NOT NULL : NULL 값을 허용하지 않음
• UNIQUE : 중복 값을 허용하지 않음
• PRIMARY KEY : 기본 키로 설정
• FOREIGN KEY : 외래 키로 설정
• CHECK : 조건을 설정
• DEFAULT : 기본값 설정
• AUTO_INCREMENT : 자동 증가
• COMMENT : 컬럼에 대한 설명

등등 다양한 제약조건을 설정할 수 있다.

• 칼럼 개수:
  • 한 테이블에서 최대 4,096개까지 칼럼을 만들 수 있다.
• 칼럼명 중복 불가:
  • 한 테이블에서 같은 칼럼명을 사용할 수 없다.
• 칼럼명 길이:
  • 최대 64자까지 사용할 수 있다.

Primary Key

한 테이블에서 유일한 값이 들어가야 하는 칼럼에 지정하는 제약조건이다.
기본 키로 설정된 칼럼에는 같은 테이블에서 동일한 값이 들어갈 수 없으며,
중복 값이 입력되면 오류가 나고 데이터 입력 작업이 모두 취소된다. (Transaction Rollback)
NULL도 허용하지 않습니다.

Foreign Key

다른 테이블의 기본 키를 참조하는 칼럼에 지정하는 제약조건이다.
외래키를 사용하면 두 테이블 간의 관계를 설정할 수 있으며,
데이터 무결성을 보장할 수 있다.

FOREIGN KEY (열 이름) REFERENCES 참조하는 테이블(참조하는 열 이름)

Referential Integrity

• 참조 무결성 제약조건을 설정하면, 참조하는 테이블에 있는 데이터를 삭제하거나 수정할 때
  참조되는 테이블에 있는 데이터도 삭제하거나 수정할 수 있다.

CREATE TABLE orders (order_id INT, customer_id INT, PRIMARY KEY(order_id), FOREIGN KEY(customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT ON UPDATE CASCADE);

위 예시에서 ON DELETE RESTRICT는 orders 테이블에서 참조된 customers 테이블의 기본 키 값을 삭제할 수 없도록 하는 제약 조건이며,
ON UPDATE CASCADE는 customers 테이블에서 기본 키 값이 업데이트되면 이에 해당하는 외래키 값을 orders 테이블에서 자동으로 업데이트하도록 하는 제약 조건이다.

예시

CREATE TABLE students (
  id TINYINT NOT NULL AUTO_INCREMENT, -- 숫자 자동 생성
  name VARCHAR(10) NOT NULL, 
  gender ENUM('man','woman') NOT NULL,
  birth DATE NOT NULL,
  english TINYINT NOT NULL,
  math TINYINT NOT NULL,
  korean TINYINT NOT NULL,
  PRIMARY KEY (id) -- 기본키
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

키워드 설명

• id : 학생의 고유번호.
  • 숫자로 자동 생성, NULL값 허용하지 않음, 기본키로 설정
• name : 학생의 이름.
  • 문자열, NULL값 허용하지 않음
• gender : 학생의 성별.
  • ENUM 타입으로 선언, NULL값 허용하지 않음
• birth : 학생의 생년월일.
  • DATE 타입으로 선언, NULL값 허용하지 않음
• english, math, korean : 학생의 영어, 수학, 국어 성적.
  • 숫자로 선언, NULL값 허용하지 않음
• PRIMARY KEY (id) : id 칼럼을 기본키로 설정
• ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 : 테이블의 엔진을 InnoDB로 설정하고, 문자 집합을 utf8mb4로 설정
• AUTO_INCREMENT : 자동 증가