[AWS] CI/CD 배포 4 - MySql 설정

류재성

Jun 10, 2024

Contents

1. MySql 시작하기 2. 테이블 생성

1. MySql 시작하기

mysql worknech 를 실행한다.

플러스 버튼을 누른 후 각 값을 입력한다. hostname은 RDS의 엔드포인트, USERNAME과 PASSWORD 는 RDS를 만들 때 입력한 값을 넣는다.

정상적으로 RDS와 연결이 완료되었다.

2. 테이블 생성

로컬에서 실행하면 이런 테이블 코드를 확인할 수 있다. 이 코드를 MySql에 맞게 변경한다.


create database bodykeydb;

use bodykeydb; 


CREATE TABLE user_tb (
    goal_fat FLOAT,
    goal_muscle FLOAT,
    goal_weight FLOAT,
    height FLOAT,
    id INT AUTO_INCREMENT,
    total_coin INT,
    birth TIMESTAMP(6),
    created_at TIMESTAMP(6),
    gender VARCHAR(255),
    name VARCHAR(255),
    password VARCHAR(255),
    phone VARCHAR(255),
    user_img VARCHAR(255),
    username VARCHAR(255) UNIQUE,
    PRIMARY KEY (id)
) DEFAULT CHARSET=utf8mb4;


CREATE TABLE activity_tb (
    drink_water INT,
    id INT AUTO_INCREMENT,
    user_id INT,
    walking INT,
    created_at TIMESTAMP(6),
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES user_tb(id)
) DEFAULT CHARSET=utf8mb4;

CREATE TABLE bodydata_tb (
    fat FLOAT,
    id INT AUTO_INCREMENT,
    muscle FLOAT,
    user_id INT,
    weight FLOAT,
    created_at TIMESTAMP(6),
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES user_tb(id)
) DEFAULT CHARSET=utf8mb4;

CREATE TABLE meal_tb (
    id INT AUTO_INCREMENT,
    user_id INT,
    created_at TIMESTAMP(6),
    eat_time VARCHAR(255),
    meal_img VARCHAR(255),
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES user_tb(id)
) DEFAULT CHARSET=utf8mb4;

CREATE TABLE food_tb (
    carbo FLOAT,
    fat FLOAT,
    gram INT,
    id INT AUTO_INCREMENT,
    kcal FLOAT,
    protein FLOAT,
    created_at TIMESTAMP(6),
    name VARCHAR(255),
    PRIMARY KEY (id)
) DEFAULT CHARSET=utf8mb4;

CREATE TABLE eat_tb (
    food_id INT,
    id INT AUTO_INCREMENT,
    meal_id INT,
    quantity INT,
    PRIMARY KEY (id),
    FOREIGN KEY (food_id) REFERENCES food_tb(id),
    FOREIGN KEY (meal_id) REFERENCES meal_tb(id)
) DEFAULT CHARSET=utf8mb4;

CREATE TABLE challenge_tb (
    coin INT,
    id INT AUTO_INCREMENT,
    period INT,
    walking INT,
    created_at TIMESTAMP(6),
    background_img VARCHAR(255),
    badge_img VARCHAR(255),
    challenge_name VARCHAR(255),
    content TEXT,
    distance VARCHAR(255),
    sub_title VARCHAR(255),
    PRIMARY KEY (id)
) DEFAULT CHARSET=utf8mb4;


CREATE TABLE attend_challenge_tb (
    challenge_id INT,
    id INT AUTO_INCREMENT,
    status BOOLEAN,
    total_walking INT,
    user_id INT,
    closing_time TIMESTAMP(6),
    created_at TIMESTAMP(6),
    opening_time TIMESTAMP(6),
    PRIMARY KEY (id),
    FOREIGN KEY (challenge_id) REFERENCES challenge_tb(id),
    FOREIGN KEY (user_id) REFERENCES user_tb(id)
) DEFAULT CHARSET=utf8mb4;


CREATE TABLE survey_tb (
    id INT AUTO_INCREMENT,
    created_at TIMESTAMP(6),
    status VARCHAR(255),
    title VARCHAR(255),
    PRIMARY KEY (id)
) DEFAULT CHARSET=utf8mb4;


CREATE TABLE do_survey_tb (
    id INT AUTO_INCREMENT,
    survey_id INT,
    user_id INT,
    created_at TIMESTAMP(6),
    PRIMARY KEY (id),
    FOREIGN KEY (survey_id) REFERENCES survey_tb(id),
    FOREIGN KEY (user_id) REFERENCES user_tb(id)
) DEFAULT CHARSET=utf8mb4;


CREATE TABLE survey_question_tb (
    id INT AUTO_INCREMENT,
    survey_id INT,
    created_at TIMESTAMP(6),
    question_item VARCHAR(255),
    PRIMARY KEY (id),
    FOREIGN KEY (survey_id) REFERENCES survey_tb(id)
) DEFAULT CHARSET=utf8mb4;


CREATE TABLE question_choice_tb (
    choice_number INT,
    id INT AUTO_INCREMENT,
    survey_id INT,
    survey_question_id INT,
    created_at TIMESTAMP(6),
    choice_item VARCHAR(255),
    PRIMARY KEY (id),
    FOREIGN KEY (survey_id) REFERENCES survey_tb(id),
    FOREIGN KEY (survey_question_id) REFERENCES survey_question_tb(id)
) DEFAULT CHARSET=utf8mb4;


CREATE TABLE choice_answer_tb (
    id INT AUTO_INCREMENT,
    question_choice_id INT,
    survey_id INT,
    survey_question_id INT,
    user_id INT,
    created_at TIMESTAMP(6),
    PRIMARY KEY (id),
    FOREIGN KEY (question_choice_id) REFERENCES question_choice_tb(id),
    FOREIGN KEY (survey_id) REFERENCES survey_tb(id),
    FOREIGN KEY (survey_question_id) REFERENCES survey_question_tb(id),
    FOREIGN KEY (user_id) REFERENCES user_tb(id)
) DEFAULT CHARSET=utf8mb4;