20. Database 생성(GUI) , mysql2, time_zone

JSLEE·2024년 5월 12일
데브코스웹개발

지난 포스트에서 CLI로 database를 생성하고 관리해보았다면,
이번 포스트에서는 MySQL Workbench를 통하여 GUI로 데이터베이스를 생성, 관리하는 것에 대해 알아본다.

MySQL Workbench

MySQL Workbench는 구글에서 검색하여 쉽게 설치할 수 있다.
설치후 화면은 다음과 같다.

이 화면에서, MySQL Connections 의 우측의 + 버튼을 눌러서 새로운 연결을 생성한다.
Connection의 Name 을 입력하고, DB의 정보들을 일치시켜주면 된다.

제대로 연결이 생성되었다면, 다음과 같은 화면이 출력된다.

화면에는 총 4가지 구역이 보인다. (하지만, 조회를 하면 새로운 영역이 하나 더 생긴다)

1. Navigator

여기선 하단에 Administration과 Schemas 탭이 있다. Schemas 탭을 눌러보면, 내가 작성한 데이터베이스들이 존재하는 것을 확인할 수 있다.
▶ 버튼을 눌러보면, 해당 데이터베이스의 테이블까지 확인할 수 있다.


Navigator 빈 영역에 우클릭을 하여 새로운 데이터베이스 (Create Schema) 를 생성할 수 있다.
이는 CLI에서 CREATE DATABASE 데이터베이스명 ; 과 같은 기능을 수행한다.
마찬가지로, 스키마를 선택하고 Tables에 우클릭하여 테이블을 생성할 수 있다.

CLI에서 CREATE TABLE new_table (id INT); 라고 작성했던 것을 이렇게 클릭하여 사용할 수 있다.
또한 Workbench에서는 처음 컬럼을 생성하면 자동적으로 id라는 컬럼이 등록되고, Primary Key로 지정되어 있다.

체크박스의 기능들은 각각 다음과 같다.

  • PK : Primary Key - 기본키 지정
  • NK : Not Null - Null값을 허용하지 않음.
  • UQ : Unique - 중복된 값을 허용하지 않음.
  • B : Binary - 데이터를 이진데이터로 저장함.
  • UN : Unsigned - 숫자형 데이터에 부호가 없어짐. (0>=)
  • ZF : Zero Fill - 숫자형 데이터의 빈칸에 0을 채워넣음. ex) INT(4) 에 5를 입력하면, 0005
  • AI : Auto Increment - 숫자형 데이터의 값이 자동으로 1씩 증가해서 입력됨.
    주의사항 : Auto Increment를 적용시키면, INSERT에 실패해도 값이 증가함.
    ex) id = 1, id = 2, INSERT 실패 , id = 4
  • G : Generated Column - 다른 컬럼들을 이용해 값을 계산해서 입력됨. ex) id * age

이외에도, Foreign Key까지 하단의 Foregin Keys탭을 이용하여 손쉽게 등록이 가능하다.

2. Information


Navigator에서 선택된 부분에 대한 정보가 나온다.
myPractice 스키마의 first 테이블을 선택했을 때의 화면이다.
first 테이블이 가지고 있는 컬럼과 자료형들에 대해 나타나 있는 것을 확인할 수 있다.

3. Query 1

쿼리를 작성하는 영역이다.

주로 SELECT 문을 많이 사용한다. CREATE, INSERT, DELETE, ADD, DROP 등은 대부분 GUI로 클릭해서 할 수 있기 때문이다.
쿼리문을 작성하고, 상단의 번개모양을 클릭하면 된다.

4. Output

쿼리를 수행했을 때 성공/실패의 결과를 알려주는 영역이다.

5. Result Grid

Query 영역에서 조회를 수행했을 때 생기는 영역이다.

이 영역에서 데이터에 값을 입력하고 제거함으로써, UPDATE, INSERT, DELETE의 기능을 수행할 수 있다.
CLI에 비하면 정말 간편해진 것을 느낄 수 있다.

mysql2 모듈로 DB 데이터 불러오기

외부모듈을 사용하려면 역시 npm을 사용한다.
npm에서 mysql을 검색하면 mysql과 mysql2가 존재하는데,
mysql 모듈은 최신 업데이트가 2020년이고, mysql2는 현재도 계속 업데이트 중이므로 mysql2를 사용하겠다.

mysql2에도 역시 공식문서가 따로 존재한다.

Callback을 이용한 예제 코드를 살펴보자.

// Get the client
const mysql = require('mysql2');

// Create the connection to database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password : '', // 비밀번호 추가
  database: 'test',
  dateStrings : true // 날짜 깔끔하게
});

// A simple SELECT query
connection.query(
  'SELECT * FROM `table` WHERE `name` = "Page" AND `age` > 45',
  function (err, results, fields) {
    console.log(results); // results contains rows returned by server
    console.log(fields); // fields contains extra meta data about results, if available
  }
);

// Using placeholders
connection.query(
  'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
  ['Page', 45],
  function (err, results) {
    console.log(results);
  }
);

Create the connection to database 부분은, DB에 대한 연결정보를 입력한다.
예제 코드에서는 password란이 없는데, 비밀번호를 추가해줬다. 그리고, TIMESTAMP 자료형을 사용하면 "2024-05-10T12:12:34.000Z" 처럼 불필요한 날짜값이 들어가게 때문에, dateStrings : true 으로 설정하면 2024-05-10 12:12:34로 날짜가 깔끔하게 출력된다.

예제코드에서, query를 사용하는 방법이 2가지 있다.
하나는 // A simple SELECT query 부분이고,
다른 하나는 // Using placeholders 부분이다.

사실, // A simple SELECT query 방법으론 잘 사용되지 않는다.
그 이유는, SQL문에 직접 저렇게 문자열로 name = "Page"나 age = "45" 처럼 데이터값을 적는 경우는 드물다. 해당 값들은 모두 변수로 입력받아 사용되는 경우가 많기 때문이다.

그럼 템플릿 문자열 ${ } 을 사용하면 되지 않겠냐라는 생각을 할 수 있는데, 템플릿 문자열을 함부로 사용하면 SQL injection이라는 보안에 취약한 문제가 생긴다.
SQL injection이란 사용자가 악의적으로 변수를 입력하는 부분에 SQL문을 사용하여 데이터베이스를 조작하는 행동이다.

따라서, 가장 추천되는 방법은 placeholder ? 를 사용한 쿼리문 작성이다.

connection.query(
  'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
  ['Page', 45],
  function (err, results) {
    console.log(results);
  }
);

처럼, 변수로 이용할 부분을 ? 기호로 표시해놓고, 대괄호에 값을 입력해주면 된다.

즉, connection.query("SQL", [ values ] , callback ) 인 것이다.

callback 함수의 매개변수에는 err와 results, fields 값이 들어간다.
이 중, results에 SELECT SQL문이 수행된 데이터를 받는다.
이 때, results는 JSON ARRAY 형태로 값을 돌려주기 때문에, 하나의 객체를 조회해서 값을 얻으려면 results[0]과 같이 코드를 작성해야한다.
ex) { id, name, created_at } = results[0]

time_zone 이란?

지난 포스트에서, 자료형에 TIMESTAMP 라는 자료형이 존재하고,
이 자료형은 시간데이터를 UTC 시간을 사용한다고 했다.
데이터베이스 서버는, 이 time_zone의 설정에 따라 시간값을 다르게 뿌려준다.

예를들어, 페이스북을 예시로 들어보자.
페이스북은 이용자들이 전세계적으로 존재한다.
미국에서 페이스북을 사용하는 사람은 미국시간으로 글 작성시간을 보여줘야 할 것이고,
한국에서 페이스북을 사용하는 사람은 한국시간으로 글 작성시간을 보여줘야 한다.
따라서, 페이스북 개발자들은 한국의 데이터베이스에는 time_zone을 'Asia/Seoul' 로 설정하고,
미국의 데이터베이스에는 'America/New_York' 으로 설정할 것이다.
이러면 데이터베이스는 UTC 시간으로 관리하되, 데이터를 뿌려줄 때 지역에 맞게 시간을 뿌려줄 것이다.

이제 내 데이터베이스 time_zone 설정을 바꾸어보자.
먼저, 현재 내 데이터베이스 서버나 세션이 어떤 시간대를 사용하고 있는지 확인할 필요가 있다.
다음을 입력해보자.

  • 데이터베이스 서버 전체에 적용된 time_zone 조회
    SELECT @@global.time_zone;

  • 현재 세션이 적용된 time_zone 조회
    SELECT @@session.time_zone;

데이터베이스에 아무런 time_zone을 설정하지 않았다면, SYSTEM이라는 시간대를 사용하고 있을 것이다. SYSTEM은 데이터베이스 서버가 설치된 운영체제의 로컬 시간대를 따른다.

그럼 time_zone을 어떻게 바꿀까?

  • 데이터베이스 서버 전체에 적용
    SET @@global.time_zone = 'Asia/Seoul' 또는
    SET GLOBAL time_zone = 'Asia/Seoul'

  • 현재 세션에 대해 적용
    SET @@session.time_zone = 'Asia/Seoul' 또는
    SET SESSION time_zone = 'Asia/Seoul'

여기서, 데이터베이스 서버는 뭔지 대충 알겠는데, 세션이 뭐지? 라는 의문을 가질 수 있다.

세션은 현재 연결을 의미한다고 보면 된다.
예를 들어서, 내가 Workbench를 통해 'test'라는 connection을 생성하고 해당 세션에 들어가서 작업하는 것이다. 이 때, 세션에 서울시간을 적용시키고 작업했다. 세션이 끊어질 때까지는 서울시간이 적용되는 것이다.
세션을 끊고 다시 세션에 접속하면, 적용시킨 서울시간은 사라지고 데이터베이스 서버의 설정을 따라가게 된다.

profile
JSLEE
공부한 내용들을 정리하기 위해 사용하는 블로그입니다.
이전 포스트

19. Database 생성(CLI)

다음 포스트

회고록 (24.05.06 - 24.05.12 )

0개의 댓글