[TIL] 9/13 SQL workbench 사용하기, DB 연동, timezone 셋팅

1. SQL workbench 사용하기

 

1.1. 스키마 만들기

 

 

1.2. 테이블 만들기

 

여기서 컬럼을 추가할 수 있다. 필요한 데이터 타입, 속성들을 체크하면 된다. 

 

만들고 Apply 를 누르면, 내가 만든 컬럼들을 만드는 SQL 문을 보여준다. 

(ASC: 오름차순 <-> DSC: 내림차순)

 

1.3. FK 설정하기

 

PK 는 컬럼을 만들 때 설정해주면 되고, FK 는 만드는 탭에 들어가서 설정해주면 된다.

 

 

FK 설정하고 테이블로 돌아오면 user_id 컬럼 옆 마름모 색이 바뀐 것을 확인할 수 있다. 

 

Apply 누르면

 

1.4. 테이블에 데이터 넣기

 

여기서 INSERT, UPDATE 다 할 수 있다. 직접 칸을 선택해서 값을 넣고(또는 있던 값을 수정하고) apply 해주면 된다.

 

나는 id 에 AUTO_INCREMENT 를 걸어주었기 때문에 빈칸으로 놔두고 apply 해주었다.

직접 id 에 값을 넣어도 되지만, AUTO_INCREMENT 는 이전에 자신이 넣은 값을 기억하기 때문에 내가 입력한 값과 AUTO_INCREMENT 가 기억하고 있는 값이랑 꼬여서 원치 않는 값이 들어갈 수 있으니 계속 내가 넣던지 or 계속 AUTO_INCREMENT 에 맡기던지 하는 것이 좋다. 

 

 

 

2. DB 연동

 

MYSQL2 설치

npm install mysql2

 

연동 코드

const mysql = require('mysql2');

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'Youtube'
});

connection.query(
    'SELECT * FROM users',
    function(err, results, fields) {
        console.log(results); // 테이블 데이터 값 출력 -> rows
        console.log(fields); // 테이블을 만들 때 테이블을 어떻게 구성했는지 -> extra meta data
    }
)

 

 

비구조화 문법으로 안에 내용 뽑아서 출력하기

connection.query(
    'SELECT * FROM users',
    function(err, results, fields) {
        results.forEach(({id, email, name, joined_at}) => {
            console.log(id, email, name, joined_at); // 테이블 데이터 값 출력 -> rows
        }) 
        console.log(fields); // 테이블을 만들 때 테이블을 어떻게 구성했는지 -> extra meta data
    }
)

/*
1 kim@mail.com tester1 2024-09-12T21:20:27.000Z
2 lee@mail.com tester2 2024-09-12T21:20:27.000Z
3 park@mail.com tester3 2024-09-12T21:21:42.000Z
[
  `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `email` VARCHAR(100) NOT NULL UNIQUE_KEY,
  `name` VARCHAR(45) NOT NULL,
  `password` VARCHAR(45) NOT NULL,
  `contact` VARCHAR(45),
  `joined_at` TIMESTAMP(19) UNSIGNED
]
*/

 

 

 

3. TIMEZONE

그런데 문제가 있다. node.js 의 시간과 db 의 시간이 다르게 찍힌다. 

 

db

 

vscode 로 db 데이터를 출력했을 때(node.js)

 

 

3.1. db 에서 time_zone 셋팅

 

GLOBAL 을 붙여 서버 전체의 글로벌 타임존을 설정해주었다. 

SET GLOBLA time_zone = 'Asia/Seoul';

 

그리고 db 에 새롭게 데이터를 집어넣어봤는데, 여전히 시간이 현재와 다르다. (현재 19:03분)

 

vscode 에서 확인해보니 현재 시간과는 다르지만, db 와 node.js 의 시간은 동일하게 맞춰졌다. 

 

 

GLOBAL 과 현재 세션의 설정 값을 확인해보았는데, SESSION 의 값은 'Asia/Seoul' 로 설정되어 있지 않았다. 

SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;

 

GLOBAL 은 설정해둔 'Asia/Seoul' 로 잘 설정되어 있는데, @@SESSION 은 설정되어 있지 않은 것을 볼 수 있다. 

 

그래서 SESSION 의 time_zone 을 'Asia/Seoul' 로 다시 설정.

SET time_zone = 'Asia/Seoul';

 

 

이제 다시 돌아가서 테이블을 확인해보면, joined_at 컬럼의 시간이 현재 시간으로 바뀌어 있는 것을 확인할 수 있다. (예전에 집어넣은 데이터들도 표준시가 아닌 서울의 시간으로 바뀌었음)

 

하지만, vscode 에서 출력해보면, node.js 는 여전히 해결되지 않은 것을 확인할 수 있다.

 

 

3.2. DATETIME 의 Z 떼기

 

시간이 출력될 때 뒤에 Z 가 붙어서 출력이 되는데, 이것은 Zulu Time 을 의미하며, 해당시간이 UTC (세계 협정시) 기준임을 나타내는 표시이다. 이걸 떼보도록 하자. 

 

이렇게 연결할 때, dateStrings 속성을 추가하고, true 로 설정해두면 Z 가 떼어서 출력되고 시간도 내가 db 에서 설정한대로 출력된다.

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'Youtube',
    dateStrings: true // 이렇게 설정
});

 

 

db 에서 DATETIME 을 저장할 때, 날짜시간이 픽스되어 스트링 값이 픽스되어 저장되는 것이 아니라 표준 시간대로 저장시켜놓고 셋팅된 값으로 뿌려주는 것이다. 그래서 vscode 에서 날짜시간을 불러올 때 string 상태로 불러오는 것이 아니라, 날 것(표준 시간대로 저장된) 의 상태로 불러오기 때문에 dateStrings 를 설정해주어야 원하는 시간으로 출력할 수 있게 된다.