Ch10-1 : Tables & Tirrger
[Tables & Tirrger]
在開始之前,我先說明這章節用到的SQL語法部份是用MySQL相關函式的,因為我們採用的資料庫是MySQL的分支MairaDB,部份語法只能適用在MySQL上,並不完全相容於其它類型資料庫,像SQL Server或Oracle DB這些資料庫。
另外這章節之後,會大量使用到資料庫語言,若是完全沒有接觸過的朋友,建議先參閱Reference,簡單先學習MySQL的建立(CREATE)、查尋(SELECT)、新增(INSERT)、刪除(DELETE)、更新(UPDATE)的基本語法後,再往下學習才會比較順暢。
我設計的註冊功能,目前會需要設計出6個表單(Tables)與1個觸發器(Tirrger),幫助使用者可以註冊與認證帳號,以下每張表單與觸發器,請使用資料庫管理工具(ex : HeidiSQL),一一建立起來。那我們往下看每張表單與觸發器各自的用途與目的是什麼吧!!
Table - 會員主檔(member_main)
現階段,為了註冊簡單,只設計出主要表單。未來有需要,可以再新增detail,寫入需要的資料。
而這張表的功能是,紀錄使用者註冊的信箱,密碼,與是否啟用帳號等等資訊,這個表單再下一個章節登入(Login)也是必須使用到的表單。每張表的設計,通常都會有主鍵PRIMARY KEY,最主要要特別注意memberNo,它是一個主鍵,未來相關表單都會需要這個Key當作關聯或查尋相關表單使用,要特別注意這部份。以下是會員主檔表單的設計。
-- 會員表單
-- memberNo 會員編號 (不可以重複,主鍵。當每個使用者註冊時,都會有唯得一個編號,可以查尋到會員相關資料)
-- email 使用者信箱 (不可以重複,不重覆鍵。每個帳號註冊,只允許一個信箱註冊一個會員資料)
-- status 認證狀態 (初次註冊者,會給預設值1,尚未認證)
-- password 密碼
-- username 使用者名稱 (索引鍵。之後每個使用者註冊時,會檢查使用者名稱不可以重複的功能。)
-- createDate 創立日期 (ex:20160921080102)
-- modifyDate 修改日期 (ex:20160921080102)
CREATE TABLE `member_main` (
`memberNo` VARCHAR(15) NOT NULL NULL COLLATE 'utf8_unicode_ci',
`email` VARCHAR(150) NOT NULL NULL COLLATE 'utf8_unicode_ci',
`status` VARCHAR(1) NOT NULL DEFAULT '1' NULL COLLATE 'utf8_unicode_ci',
`password` VARCHAR(50) NOT NULL NULL COLLATE 'utf8_unicode_ci',
`username` VARCHAR(50) NOT NULL NULL COLLATE 'utf8_unicode_ci',
`createDate` VARCHAR(50) NOT NULL NULL COLLATE 'utf8_unicode_ci',
`modifyDate` VARCHAR(50) NOT NULL NULL COLLATE 'utf8_unicode_ci',
CONSTRAINT pk_memberNo PRIMARY KEY (memberNo),
UNIQUE KEY (email),
INDEX (username)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;
Table - 會員流水號表單(member_main_seq)
這表單用途是,每次呼叫時,會建立一組自動累加數字當作編號,用來給member_main.memberNo會員編號使用。
EX : 0000000001
-- 會員流水號表單,用於取得最新的ㄧ筆auto key
CREATE TABLE member_main_seq
(
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
Trigger - 會員流水號觸發器(tg_member_main_insert)
觸發器(Trigger),可以用來當某些情況發生時,主動替我們做ㄧ些事情的功能。以下這段觸發器是說明,當註冊會員,準備新增資料時,就會觸發到tg_member_main_insert這個功能,就自動寫入最新一組編號id到member_main_seq,再把最新一筆的member_main_seq.id,給新註冊會員的會員編號使用,就不需要用SQL檢查最後一筆會員資料,以免造成會員編號重複的問題。
EX memberNo : mem000000000001
-- 建立觸發器,在insert member_main之前
-- 會先insert到member_main_seq,取得最新一組編號後
-- 再用英文mem與數字組一個最新的memberNo
-- 給註冊會員主檔使用
DELIMITER $$
CREATE TRIGGER tg_member_main_insert
BEFORE INSERT ON member_main
FOR EACH ROW
BEGIN
INSERT INTO member_main_seq VALUES (NULL);
SET NEW.memberNo = CONCAT('mem', LPAD(LAST_INSERT_ID(), 12, '0'));
END$$
DELIMITER ;
Table - 會員修改紀錄表單(member_main_log)
會員資料的修改,是需要主動紀錄下來的動作,這樣我們就可以知道會員的修改歷程,當有會員資料出現問題時,就可以檢查member_main_log的紀錄。這張表單,會是紀錄上一次的會員資料,而不是當下member_login的會員資料。
-- 會員修改紀錄表單
-- memberNo 會員編號 (索引鍵)
-- email 使用者信箱
-- status 會員狀態
-- password 密碼
-- username 使用者名稱
-- createDate 創立日期 (ex:20160921080102)
CREATE TABLE `member_main_log` (
`memberNo` VARCHAR(15) NOT NULL NULL COLLATE 'utf8_unicode_ci',
`email` VARCHAR(150) NOT NULL NULL COLLATE 'utf8_unicode_ci',
`status` VARCHAR(1) NOT NULL DEFAULT '1' NULL COLLATE 'utf8_unicode_ci',
`password` VARCHAR(50) NOT NULL NULL COLLATE 'utf8_unicode_ci',
`username` VARCHAR(50) NOT NULL NULL COLLATE 'utf8_unicode_ci',
`createDate` VARCHAR(50) NOT NULL NULL COLLATE 'utf8_unicode_ci',
index (memberNo)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;
Table - 會員登入紀錄(member_login_log)
當使用者登入網站,或加入會員時,會紀錄目前使用者的各種登入或加入會員的狀態。
-- 會員登入紀錄
-- memberNo 會員編號 (索引鍵)
-- email 使用者信箱
-- status 登入狀態
-- ipAddress 網路位址
-- device 登入的裝置
-- loginDate 登入日期
CREATE TABLE `member_login_log` (
`memberNo` VARCHAR(15) NOT NULL NULL COLLATE 'utf8_unicode_ci',
`status` VARCHAR(2) NOT NULL NULL COLLATE 'utf8_unicode_ci',
`ipAddress` VARCHAR(50) NOT NULL NULL COLLATE 'utf8_unicode_ci',
`device` VARCHAR(50) NOT NULL NULL COLLATE 'utf8_unicode_ci',
`loginDate` VARCHAR(50) NOT NULL NULL COLLATE 'utf8_unicode_ci',
index (memberNo)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;
Table - 會員認證表單(member_token)
當使用者成功註冊會員時,我們會寫一段認證字串到資料庫裡,再寄送認證信件給新會員使用,若是成功點選認證信,認證成功之後,他們才能登入會員,使用相關的服務。而這個表單未來會給後面章節使用
-- 會員Token表單
-- tokenString Token字串 (主鍵)
-- memberNo 會員編號 (索引鍵,可以關聯到會員主檔,知道哪個會員尚未認證)
-- type 類型 , ex:Singup 註冊
-- isUse 認證字串 (代表是否使用過該字串)
-- sendDate 寄信日期 (ex:20160920080102)
-- createDate 創立日期 (ex:20160921080102)
-- modifyDate 修改日期 (ex:20160921090102)
-- expiryDate 逾期日期 (ex:20160921100102)
CREATE TABLE `member_token` (
`tokenString` VARCHAR(100) NOT NULL NULL COLLATE 'utf8_unicode_ci',
`memberNo` VARCHAR(15) NOT NULL NULL COLLATE 'utf8_unicode_ci',
`type` VARCHAR(20) NOT NULL NULL COLLATE 'utf8_unicode_ci',
`sendDate` VARCHAR(50) NOT NULL NULL COLLATE 'utf8_unicode_ci',
`isUse` BOOL NOT NULL NULL COLLATE 'utf8_unicode_ci',
`createDate` VARCHAR(50) NOT NULL NULL COLLATE 'utf8_unicode_ci',
`modifyDate` VARCHAR(50) NOT NULL NULL COLLATE 'utf8_unicode_ci',
`expiryDate` VARCHAR(50) NOT NULL NULL COLLATE 'utf8_unicode_ci',
CONSTRAINT pk_authString PRIMARY KEY (tokenString),
index (memberNo)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;
Table - 表單狀態對照表(table_status_desc)
未來,表單可能會需要其它的狀態碼,為了集中管理,我設計出一個表單,去紀錄表單相關的狀態碼,來說明各各表單的status,各代表的意思。
-- tableName 表單名稱
-- status 狀態碼
-- statusDesc 狀態說明
-- 表單狀態對照表
CREATE TABLE `table_status_desc` (
`tableName` VARCHAR(50) NOT NULL NULL COLLATE 'utf8_unicode_ci',
`status` VARCHAR(10) NOT NULL NULL COLLATE 'utf8_unicode_ci',
`statusDesc` VARCHAR(50) DEFAULT NULL NULL COLLATE 'utf8_unicode_ci',
index (tableName)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;
-- member_main 狀態對照
INSERT table_status_desc VALUES ('member_main','1','尚未認證');
INSERT table_status_desc VALUES ('member_main','2','已認證');
INSERT table_status_desc VALUES ('member_main','3','帳號停用');
-- member_login_log 狀態對照
INSERT table_status_desc VALUES ('member_login_log','1','加入會員成功');
INSERT table_status_desc VALUES ('member_login_log','2','登入成功');
INSERT table_status_desc VALUES ('member_login_log','3','登入失敗');
[Final]
最後我們整理一下這小節建立的表單與觸發器,它們都是之後寫實際註冊功能時,會需要用到的表單。
member_main : 會員主檔表單
member_main_seq : 會員流水號表單
tg_member_main_insert : 會員流水號觸發器
member_main_log : 會員修改紀錄表單
member_login_log : 會員登入紀錄
member_token : 會員Token表單
table_status_desc : 表單狀態對照表
下面一個小章節,就會依據這些資料庫表單的設計,設計出對應的網頁畫面。
[Reference]
SQL Tutorial
http://www.w3schools.com/sql/
MySQL Tutorial
http://www.tutorialspoint.com/mysql/
Create insert trigger to auto increment int field of composite PK
http://stackoverflow.com/questions/6618139/create-insert-trigger-to-auto-increment-int-field-of-composite-pk-string-int