데이터베이스 시간 기록: created_at 과 updated_at 자동 업데이트 하기

SQL문을 이용하면, 데이터베이스의 열이 업데이트 되면,

갱신된 현재의 시간으로 데이터가 바뀌도록 쉽게 할 수 있는데요.

오늘은 이것을 NodeJS과 MySQL을 이용해 구현해 보겠습니다.

 

1. 데이터베이스 생성 쿼리

먼저 student라는 학생에 관한 데이터를 다루는 테이블을 생성하는 SQL문을 생성하겠습니다.

주의해서 볼 부분이 'created_at'과 'updated_at'인데요.

 

• 'DEFAULT CURRENT_TIMESTAMP': 값이 지정되지 않은 경우, 현재 시간이 기본값으로 설정 됨
• 'ON UPDATE CURRENT_TIMESTAMP': 열을 UPDATE 할 때 해당 열에 현재 시간을 설정함.
  • 특정 열의 값을 업데이트할 때마다 해당 열의 값이 현재 시간으로 자동으로 갱신됨.

'ON UPDATE CURRENT_TIMESTAMP' 를 넣음으로서,

현재시간을 자동으로 업데이트 되도록 하였습니다.

 

const createTableQuery = `
CREATE TABLE IF NOT EXISTS student (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255),
  score INT,
  parent_name VARCHAR(255),
  phone_number VARCHAR(20),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)`;

 

 

2. NodeJS로 구현

2-1. 테이블 생성

SQL문이 준비되었으니, NodeJS로 구현해 보겠습니다.

가장 먼저 할 것은 위에서 준비된 테이블 쿼리를 이용해,

테이블을 생성하는 것 입니다.

 

const mysql = require('mysql');

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

const createTableQuery = `
CREATE TABLE IF NOT EXISTS student (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255),
  score INT,
  parent_name VARCHAR(255),
  phone_number VARCHAR(20)
)`;

// 테이블 생성
connection.query(createTableQuery, (err, result) => {
  if (err) throw err;
  console.log('테이블이 생성되었습니다.');
});

// MySQL 연결 종료
connection.end();

 

2-2. 데이터 추가하기

아래와 같이 데이터를 입력해 주면,

현재 시각을 넣지 않았음에도 created_at과 updated_at에,

데이터를 추가한 시간이 들어갑니다.

 

...

const insertDataQuery = `
INSERT INTO student (name, score, parent_name, phone_number) 
VALUES ('박이서', 90, '박기원', '010-3333-222'),
       ('이지원', 85, '이현원', '010-3333-222'),
       ('서연히', 95, '서응수', '010-3333-222')`;

connection.query(insertDataQuery, (err, result) => {
  if (err) throw err;
  console.log('데이터가 추가되었습니다.');
});

// MySQL 연결 종료
connection.end();

 

한번 더 데이터를 넣으면,

created_at에는 변화가 없고,

updated_at만 다른 timestamp가 찍히게 됩니다.

 

3. ON UPDATE CURRENT_TIMESTAMP 는 SQLite 지원 안됨

참고로 SQLite에서는,

DEFAULT CURRENT_TIMESTAMP는 지원이 되지만,

ON UPDATE CURRENT_TIMESTAMP는 지원이 되지 않습니다.

 

그래서, 조금 복잡할 수 있는 TRIGGER를 사용하거나,

update시에 아래 코드로 구한 시간을 직접 넣어주는 방법을 대안으로 선택할 수 있습니다.

 

function getUpdatedAt() {
  return new Date().toISOString();
}