0929 MySQL (mariadb) 날짜/시간 타입, FK컬럼 추가, auto_increment

DB 테이블 생성 실습

[database 만들기]

• 방확인 : SHOW DATABASES;
• 방 만들기 : CREATE DATABASE Board;
• 방 들어가기 : USE Board;

1. "board" 스키마 생성

CREATE DATABASE Board;
USE Board;

2. 사용자 테이블 생성

CREATE TABLE users( 
            id INT NOT NULL AUTO_INCREMENT
                  , name VARCHAR(30) NOT NULL
                  , job VARCHAR(100), birth DATE -- => "YYYY-MM-DD" 
                  , PRIMARY KEY (id) 
                  );

• 테이블 정의 보기 : DESC users;

3. 게시글 테이블 생성

CREATE TABLE posts( 
id INT NOT NULL AUTO_INCREMENT, 
title VARCHAR(100) NOT NULL, 
content VARCHAR(2000), 
created_at TIMESTAMP DEFAULT NOW(), 
PRIMARY KEY (id) 
);

4. 사용자 데이터 삽입

INSERT INTO users (name, job, birth) VALUES('gongu','actor','1980-01-23');

• not null인 컬럼에 데이터를 넣지 않았을 때 발생하는 에러

INSERT INTO users (job) VALUES('developer'); 
-- => Field 'name' doesn't have a default value`

• not null인 컬럼에 데이터를 null로 지정해서 넣을 때 발생하는 에러

  INSERT INTO users (name, job) VALUES(null, 'developer'); 
  -- => Column 'name' cannot be null`

• NULL을 넣을 수 없다.

MySQL (mariadb) 날짜 / 시간 타입

1) DATE

• 날짜만
• YYYY-MM-DD

2) DATETIME

• 날짜+시간
• YYYY-MM-DD HH:MM:SS(24H)

3) TIME

• 시간
• HH:MM:SS

4) TIMESTAMP : 자동 입력

• 날짜 + 시간
• YYYY-MM-DD HH:MM:SS(24H)
• 시스템 시간대 정보에 맞게 일시를 저장한다.
• cf. UTC(국제표준시) : 한국시간 - 9

※ DATETIME과 TIMESTAMP 차이
- 자동입력 가능 여부 차이
- TIMESTAMP는 자동입력이 가능하다.

 

※ 인서트 시 NOT NULL vs DEFAULT

NOT NULL
- 직접 null이라고 작성해 넣는 것도 안됨

DEFAULT
- 공란으로 insert => Default 설정해준 기본 값이 insert
- 직접 null 이라고 작성해서 넣으면, null 셋팅

 

게시글 테이블에 수정일자 추가

• ALTER 이용해 컬럼 updated_at 추가
• updated_at 데이터타입 DATETIME으로 주고 기본값 NOW()로 주기
• ON UPDATE 로 수정 시 현재 날짜 (NOW()) 찍어주기

ALTER TABLE posts 
ADD COLUMN updated_at DATETIME DEFAULT NOW() 
ON UPDATE NOW();

• 게시글 테이블 수정 시 updated_at에 수정날짜가 자동으로 들어온다.

UPDATE posts 
SET content = 'updated!' 
WHERE id = 2;

게시글 테이블에 작성자 컬럼 FK 추가

1) 테이블 컬럼 추가

ALTER TABLE posts 
ADD COLUMN user_id INT
;

2) FK 설정

ALTER TABLE posts
ADD FOREIGN KEY(user_id)
REFERENCES users(id);

 

※ FK와 MUL의 차이

• MULTIPLE : PK를 가져다 쓴 FK지만 중복이 가능하다.

 

※ FK 삽입 시 주의사항

• 참조 테이블에 해당 ID값이 없으면 삽입되지 않는다.

INSERT INTO posts (title, content, user_id) VALUES('title4','content4', 5);
-- => Cannot add or update a child row: a foreign key constraint fails 
-- (`Board`.`posts`, CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`))
-- => 참조할 user\_id 없다.

INSERT INTO posts (title, content, user_id)  
VALUES('title4','content4', 1);

 

JOIN

• 기준 테이블을 FROM 뒤에 놓고, 참조 테이블을 LEFT JOIN 뒤에 놓는다.
• 테이블 각각의 참조값과 키값을 ON()으로 비교한다.

SELECT *  
FROM posts  
LEFT JOIN users ON(users.id = posts.user_id);

• 사용자번호 안보이게 하기
  • *대신 직접 컬럼명 검색

SELECT posts.id, content, created_at, updated_at, name, job, birth
FROM posts
LEFT JOIN users ON(users.id = posts.user_id);

 

---

<이슈>

mariadb에서 id값을 auto_increment로 설정했을 때 인서트를 실패해도 id값이 증가한다. 즉, id 갭이 생긴다.

 

MariaDB에서 INSERT 실패 시에도 AUTO_INCREMENT 값이 증가하는 이유는 성능과 동시성을 위한 설계 결정이다.

innodb_autoinc_lock_mode 기본값이 변경되었기 때문에 발생한 현상인데 

 

1. innodb_autoinc_lock_mode=0
 - Auto-increment lock이 테이블 레벨로 동작

 - Insert into로 실제 삽입된 경우가 아닌 업데이트 경우에는 해당 테이블의 Auto-increment 값을 증가시키지 않는 옵션

2. innodb_autoinc_lock_mode=1 
 - 단순한 insert에 대해서는 lock을 걸지 않고, INSERT 되는 레코드 건수를 정확히 예측할 수 있을 때 사용해야 함

 - latch(mutex)를 이용하여 처리함

3. innodb_autoinc_lock_mode=2
 - Auto-increment lock을 사용하지 않고, 항상 latch(mutex)를 사용함
 - 대량의 INSERT 실행 도중 다른 커넥션에서 INSERT를 수행할 수 있어. 동시 처리 성능이 높음.

 - 그러나 복제를 사용하는 경우 마스터와 슬레이브의 자동 증가 값이 달라질 가능성이 있으니 사용 시 주의가 필요

 

결과적으로 문제를 해결하기 위해서 innodb_autoinc_lock_mode 값을 기본값이 아닌 0으로 설정하여 사용해야 함
이 경우, Concurrent insert가 매우 빈번히 발생할 경우 Auto-increment lock이 병목이 될 가능성이 있으니 주의해야 함

SET global innodb_autoinc_lock_mode = 0;

커맨드 상에서 위와 같이 입력했을 때 Variable 'innodb_autoinc_lock_mode' is a read only variable 와 같은 에러가 나올 수 있다.

 

이때는 /etc/mysql/mysql.conf.d/mysql.cnf 파일을 열어서 innodb_autoinc_lock_mode = 0 이라고 추가해 준다.

 

※ 참고 : https://kkensu.tistory.com/16#google_vignette

MySQL auto_increment locking 설정

 

이에 대한 내용은 좀 더 자세히 공부해보기 위해 새로운 글로 작성해서 첨부하겠다.