MySQL shell TABLE 관련 설정

CREATE TABLE IF NOT EXISTS `Blinds` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `userId` bigint unsigned DEFAULT NULL,
  `targetUserId` bigint unsigned DEFAULT NULL,
  `domain` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `author` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `ip` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `blockDate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

 

unsinged(부호 없음) : you cannot insert negative numbers.

 

NOT NULL(Null 허용 하지 않음) : 별도 설정하지 않으면 NULL 허용

 

DEFAULT : 말 그대로 기본값

 => AUTO_INCREMENT : 자동으로 1씩 오르도록 설정 (특수한 형태의 기본값)

 => NULL : 기본값이 NULL

 => CURRENT_TIMESTAMP : 현재 시간

 => '0', '1', '37', 'whatever' : 말 그대로 특정한 문자열이나 값을 넣을 수 있습니다.

 

 

CHARACTER SET : 문자 인코딩 설정

  => utf8 : 불충분함. 

  => utf8mb4 : 4바이트 가변 길이 인코딩 방식. 이걸 사용하자. (이모지 때문에)

 

🚨 왜 utf8로 충분하지 않은가?

Emoji같은 글자들은 utf8 인코딩 되는경우 글자당 최대 4bytes까지 필요하다. 하지만 기존 MySQL의 utf8 필드의 경우 글자당 최대 3bytes 까지만 지원하는 한계점이 있었다. 때문에 MySql database에서 utf8mb4 설정을 해두지 않으면 해당 글자들이 포함된 텍스트가 입력되었을때 제대로 저장을 하지못하고 문자가 깨져버리는 사태가 발생하게 된다.

 

mysql 쉘에서 현재 기본으로 사용중인 CHARACTER SET을 확인해보기 위해서는 다음 명령어를 쳐보자.

관련 자료는 https://medium.com/oldbeedev/mysql-utf8mb4-character-set-%EC%84%A4%EC%A0%95%ED%95%98%EA%B8%B0-da7624958624

여기서 살피자.

show variables like 'c%';

 

 

COLLATE :

  => utf8mb4_unicode_ci

 

 

sql에서 collation이 왜 중요할까요?

 

 

 

ENGINE:

  => InnoDB : 따로 스토리지 엔진을 명시하지 않으면 default 로 설정되는 스토리지 엔진이다. InnoDB는 transaction-safe 하며, 커밋과 롤백, 그리고 데이터 복구 기능을 제공하므로 데이터를 효과적으로 보호 할 수 있다.

 

  => MyISAM : 트랜잭션을 지원하지 않고 table-level locking을 제공한다. 따라서 multi-thread 환경에서 성능이 저하 될 수 있다. 특정 세션이 테이블을 변경하는 동안 테이블 단위로 lock이 잡히기 때문이다.

 

  => Archive :  '로그 수집'에 적합한 엔진이다. 데이터가 메모리상에서 압축되고 압축된 상태로 디스크에 저장이 되기 때문에 row-level locking이 가능하다. 다만, 한번 INSERT된 데이터는 UPDATE, DELETE를 사용할 수 없으며 인덱스를 지원하지 않는다. 따라서 거의 가공하지 않을 원시 로그 데이터를 관리하는데에 효율적일 수 있고, 테이블 파티셔닝도 지원한다. 다만 트랜잭션은 지원하지 않는다.

 

 

---

 

데이터 타입

 

 

 

게시판형에서 게시물은 대체로 MEDIUMTEXT 정도면 되는 듯하다. (PM 코드가 그렇게 되어 있다)

 

CHAR(n)	고정 길이 데이터 타입(최대 255byte)- 지정된 길이보다 짦은 데이터 입력될 시 나머지 공간 공백으로 채워진다.
VARCHAR(n)	가변 길이 데이터 타입(최대 65535byte)- 지정된 길이보다 짦은 데이터 입력될 시 나머지 공간은 채우지 않는다.

TINYTEXT(n)	문자열 데이터 타입(최대 255byte)
TEXT(n)	문자열 데이터 타입(최대 65535byte)
MEDIUMTEXT(n)	문자열 데이터 타입(최대 16777215byte)
LONGTEXT(n)	문자열 데이터 타입(최대 4294967295byte)

 

유저 id, 게시물 id 등은 대체로 bigint를 넣는다. 추후 사업이 확장되면 이를 수정하는 것이 복잡하기 때문이다.

 

TINYINT(n)	정수형 데이터 타입(1byte) -128 ~ +127 또는 0 ~ 255수 표현 가능하다.
SMALLINT(n)	정수형 데이터 타입(2byte) -32768 ~ 32767 또는 0 ~ 65536수 표현 가능하다.
MEDIUMINT(n)	정수형 데이터 타입(3byte) -8388608 ~ +8388607 또는 0 ~ 16777215수 표현 가능하다.
INT(n)	정수형 데이터 타입(4byte) -2147483648 ~ +2147483647 또는 0 ~ 4294967295수 표현 가능하다.
BIGINT(n)	정수형 데이터 타입(8byte) - 무제한 수 표현 가능하다.

 

DATETIME

날짜와 시간 형태의 기간 표현 데이터 타입(8byte)

 

enum('String') 

 

말그대로 enum이다

 

---

 

키

 

PRIMARY KEY (PK)

 

NULL 을 허용하지 않고, Unique 성질을 지니므로 NOT NULL & UNIQUE 옵션이 포함되며 테이블 당 단 하나의 정의만 가질 수 있다.

Foreign Key (FK)

 

Foreign Key 란 JOIN 등으로 다른 DB 와의 Relation 을 맺는 경우, 다른 테이블의 PK를 참조하는 Column 을 FK 라고 한다.

 

FULLTEXT KEY

 

검색을 위한 키이다.

https://kmongcom.wordpress.com/2014/03/28/mysql-%ED%92%80-%ED%85%8D%EC%8A%A4%ED%8A%B8fulltext-%EA%B2%80%EC%83%89%ED%95%98%EA%B8%B0/

 

 

참고) 

https://jins-dev.tistory.com/entry/MySQL-%EC%97%90%EC%84%9C-%EC%82%AC%EC%9A%A9%ED%95%98%EB%8A%94-Key-%EC%9D%98-%EC%A0%95%EC%9D%98%EC%99%80-%EC%A2%85%EB%A5%98%EB%93%A4%EC%97%90-%EB%8C%80%ED%95%98%EC%97%AC