TIL. Local에 MySQL DB 구축, utf8mb4

데이터베이스 구축

테이블 생성 쿼리를 직접 작성할 수 있지만, 

이번에는 erdcloud의 기능을 이용해보았다. 

 

erd 다이어그램을 그려주고,

왼쪽 하단에서, 내보내기 - SQL 다운로드를 클릭하면 된다.

다운 받은 sql파일을 열어서 내용을 확인해보았다.

테이블 생성 쿼리문과, PK 설정 쿼리가 따로 있다.

 

PK 설정은 create문 안에서도 가능하다.

위의 구문에서는 테이블을 수정하는 Alter 문법을 이용해 Primary key를 설정해주었다.

ALTER TABLE 테이블이름 ADD [CONSTRAINT 제약조건이름] PRIMARY KEY (필드이름)

 

포린키(FK)는 식별관계로 연결한 FK에만 설정되었다.

Alter문에서 FK를 설정하는 문법은 아래과 같다.

ALTER TABLE 테이블이름 ADD [CONSTRAINT 제약조건이름] FOREIGN KEY (필드이름) REFERENCES 테이블이름 (필드이름)

 

식별관계는 기본키에 외래키가 포함되어 있고, pk는 not null이므로

• user없이 todo가 생성되지 않게 식별관계로 설정하였다.

 

비식별관계는 외부 테이블을 참조만 한다. null이 가능하다.

위 erd에서 볼 수 있듯이

• 카테고리가 없어도 할일 엔티티를 만들 수 있게 하였다.
• 할일과 날짜 테이블에서도
  할일은 입력하였어도, 아직 날짜에 배정 안할수도 있고, 할일만 적어놓을 수 있게 하려고 비식별관계로 정의했다.

---

MySQL에 접속을 해보자

mysql workbench나 heidisql같은 gui 프로그램을 통해서도 접속할 수 있고

cmd 창에서도 접속할 수 있다.

 

현재 노트북의 환경변수에 path를 설정해주기 위해

• 내 PC - 우클릭 - 설정 - 고급 시스템 설정 - 환경변수
• path - 편집
• 새로만들기 - C:\Program Files\MySQL\MySQL Server 8.0\bin 

를 추가해주었다.

MySQL 실행을 위해

시작 검색창 - 서비스 - MySQL80 을 찾고 시작을 눌러준다.

 

당분간 윈도우 실행시 자동으로 실행되도록

속성에서 시작유형을  수동 -> 자동 으로 변경하였다.

 

cmd 창에서

>> mysql -u [계정] -p   입력 후 엔터

     비밀번호를 입력한다.

DB 목록 조회

>>  show databases;   

 

데이터베이스 생성

먼저 데이터베이스를 생성해주고

>> CREATE DATABASE today DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

• DEFAULT CHARACTER SET은 한글 사용을 위해 UTF-8  유니코드 문자셋으로 설정하고
• COLLATE는 '수집하다', '순서대로 모으다' 라는 뜻으로, 데이터베이스에서 검색이나 정렬을 할 때 비교를 위한 집합

데이터베이스를 선택하고

>> use today;

 

문자셋 확인하기

>> show variables like 'character_set%'

character_set_database 항목에 utf8mb3 로 잘 설정되어 있다.

mb3은 maximum of 3 bytes per character, 한글자당 최대 3bytes 라는 의미이다.

 

최근 추가된 이모티콘 Emoji는 4byte여서, MySQL 5.5.3(2010년)부터 utf8mb4 문자셋이 추가되었다.

서버의 기본 collation은 utf8mb4_0900_ai_ci

• ai : accent Insensitive - 액센트 구분 안함
• ci : case Insensitive - 대소문자 구분 안함

utf8mb4로 바꾸면, 한 글자에 4byte로 문자 수는 더 적어지기 때문에

인덱스할 컬럼이 varchar(255) 라면 -> varchar(191)로 변경해줘야 한다.

 

현재 테이블에서는 PK로 다 숫자만 되어있고,
varchar(255) 중에는 인덱스 할 컬럼이 없고, 추후 where문에 자주 사용되어 index를 추가하게 되면 수정해야겠다.

 

utf8_unicode_ci과 utf8_general_ci의 차이는

- general_ci는 성능 우선, 비교/정렬 속도가 빠르다. Åå 같은 문자가 없어서 A로 치환된다.

- unicode_ci는 느린데, 러시아어, 불가리아어 등 광범위한 모든 언어에 적합하고, 정확하게 비교, 정렬이 가능하다.

 

이모지 등록도 가능하게 utf8mb3를 utf8mb4로 변경해주고, 속도를 위해 general로 수정하였다.

 

>> ALTER DATABASE today CHARACTER SET=utf8mb4 COLLATE= utf8mb4_general_ci;

 

외부 SQL파일을 읽기 위해

• source 명령어 : 외부 SQL문을 실행

>> source [경로포함 파일명]

>> source C:\Users\sesan\Desktop\todaymaker\TodayMaker.sql

테이블 구조를 확인하였다.

>> describe user;

 

---

Alter 제약조건: http://www.tcpschool.com/mysql/mysql_constraint_primaryKey

식별/비식별 참고

- https://bamdule.tistory.com/43

- https://deveric.tistory.com/108

collate: http://www.tcpschool.com/mysql/mysql_basic_alter)

문자셋 관련 참고

- mb3, length: https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-conversion.html

- 문자셋 varchar(n): https://www.letmecompile.com/mysql-utf8-utf8mb4-migration/
  https://mathiasbynens.be/notes/mysql-utf8mb4#column-index-length

- utf8_unicode_ci과 utf8_general_ci: https://www.dante2k.com/530

- utf8mb4, ai, ci: https://www.happyjung.com/lecture/2356?page=29

- utf8_genaral, unicode 정렬비교 : https://blog.lael.be/post/917