使用SpringBoot打造記帳簿專案(九)建立MySQL資料表

本篇將建立記帳簿專案的最開始的資料表。

在使用SpringBoot打造記帳簿專案(七)建立MySQL EER Diagram中，我們利用了MySQL Workbench的EER Diagram設計工具建立了視覺化的ER-Model模型，並在使用SpringBoot打造記帳簿專案(八)使用的MySQL欄位資料型態中說明各欄位使用的資料型態。

MySQL Workbench提供了轉換工具可以輕鬆地將設計好的EER Diagram模型轉換為建立資料庫及資料表的SQL Script。

不過在欄位設計上做了以下幾點修正

1. 將EER Diagram的資料庫schema名稱改為'moneynote'
2. 將'moneynote'的字元集及Collation改為utf8mb4 - utf8mb4_unicode_ci
3. 將資料表的create_time及update_time欄位資料型態改為DATETIME。
4. PK欄位勾選NN(NOT NULL)，避免建立資料表時造成Error Code: 1171. All parts of a PRIMARY KEY must be NOT NULL錯誤。
5. 在create_time欄位設定預設值為CURRENT_TIMESTAMP，如此在新增一筆資料時此欄位會自動插入時間
6. 在update_time欄位設定預設值為CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，如此在新增一筆資料或更新時此欄位會自動插入及更新時間。

在ERR Diagram修改schema名稱及字元集Collation可在左側的Catalog Tree原本的資料庫schema上按滑鼠右鍵 -> Edit Schema打開編輯

其餘設定請參考如下：

透過EER Diagram產生的SQL Script如下：

-- MySQL Script generated by MySQL Workbench
-- Sun Jan  6 23:03:52 2019
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema moneynote
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema moneynote
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `moneynote` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;
USE `moneynote` ;

-- -----------------------------------------------------
-- Table `moneynote`.`member`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `moneynote`.`member` ;

CREATE TABLE IF NOT EXISTS `moneynote`.`member` (
  `member_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `member_account` VARCHAR(90) NOT NULL,
  `member_password` VARCHAR(15) NOT NULL,
  `member_name` VARCHAR(30) NULL,
  `member_status` VARCHAR(1) NOT NULL,
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`member_id`),
  UNIQUE INDEX `member_account_UNIQUE` (`member_account` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `moneynote`.`expense_record`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `moneynote`.`expense_record` ;

CREATE TABLE IF NOT EXISTS `moneynote`.`expense_record` (
  `expense_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `member_member_id` BIGINT UNSIGNED NOT NULL,
  `expense_item` VARCHAR(60) NOT NULL,
  `expense_amount` INT NOT NULL,
  `expense_date` DATE NOT NULL,
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`expense_id`, `member_member_id`),
  INDEX `fk_expense_record_member_idx` (`member_member_id` ASC),
  CONSTRAINT `fk_expense_record_member`
    FOREIGN KEY (`member_member_id`)
    REFERENCES `moneynote`.`member` (`member_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

可以看到產生的語法中包括了建立資料庫，設定字元集，建立資料表及欄位，建立資料表的主鍵(pk)，外鍵(fk)及索引(index)，設定外鍵限制等。

接著連線進入MySQL資料庫並將產生的SQL script貼上SQL編輯區然後執行，執行成功後重新整理左側的Navigator視窗即可看到建立好的moneynote資料庫schema。

到這邊我們成功建立了記帳簿專案初步的資料庫了，未來絕對會因為沒考慮到的問題而回來修改。

請參考使用SpringBoot打造記帳簿專案(十八)MySQL資料表修改記錄

MySQL版本訊息如下

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 8.0.11                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2        |
| version                 | 8.0.11                       |
| version_comment         | MySQL Community Server - GPL |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
| version_compile_zlib    | 1.2.11                       |
+-------------------------+------------------------------+

接著可以開始動手建立記帳簿專案的應用程式了，請參考使用SpringBoot打造記帳簿專案(十)建立SpringBoot專案。

參考：

• MySQL 8.0 Reference Manual - Automatic Initialization and Updating for TIMESTAMP and DATETIME