- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]民國年與西洋年轉換之MySQL Function
- 冷日說在前面:
今天因為某些原因,突然想要找一個『在 php 上便利轉換民國與西元』的函式,結果倒是在酷!學園看到了一篇令人興奮又激動的教學! 不過,這根本不是用 PHP 解決阿!他採用了 MySql 的 Stored Procedure!幾乎是一篇 MySQL SP 的簡單範例教學等級作品。 這麼讚的東西,不把他留作個紀錄,對不起自己阿! 以下開始:
野人獻曝一下.... 民國年與西洋年轉換之MySQL Function ===================================== 先開table吧.使用底下的sql command.
use mysample;
create table olddate(
id int auto_increment primary key,
olddate char(9),
newdate date
);
insert into olddate(olddate)
values(
'99/03/05'
);
insert into olddate(olddate)
values(
'101/10/04'
);
再來就寫個function 將民國年轉成西洋年.程式碼如下:
DELIMITER $$
DROP FUNCTION IF EXISTS `mysample`.`chi2jul`$$
CREATE FUNCTION `mysample`.`chi2jul` (olddate char(9)) RETURNS date
BEGIN
/*
Vincent Chang for phorum.study-area.org
*/
declare str_len int;
declare cut_len int;
declare old_year int;
declare new_year int;
declare new_date_str char(11);
declare new_date date;
set str_len = length(olddate);
if str_len = 8 then
set cut_len = 2;
else
set cut_len = 3;
end if;
set old_year = convert(left(olddate, cut_len),unsigned integer);
set new_year = old_year + 1911;
set new_date_str = concat(cast(new_year as char), right(olddate, 6));
set new_date = cast(new_date_str as date);
/* we can also use str_to_date() and date_to_str()
functions to transfer
*/
return new_date;
END$$
DELIMITER ;
測試一下:
mysql> select id,chi2jul(olddate) from olddate;
+----+------------------+
| id | chi2jul(olddate) |
+----+------------------+
| 1 | 2010-03-05 |
| 2 | 2012-10-04 |
+----+------------------+
2 rows in set (0.00 sec)
結果正確!接下來直接轉換.
mysql> update olddate
-> set newdate = chi2jul(olddate);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0
mysql> select * from olddate;
+----+-----------+------------+
| id | olddate | newdate |
+----+-----------+------------+
| 1 | 99/03/05 | 2010-03-05 |
| 2 | 101/10/04 | 2012-10-04 |
+----+-----------+------------+
2 rows in set (0.00 sec)
成功轉換! *********************** 順便將用暫存table的方式寫到下面,轉檔時可以參考.以免又要來問. 產生一個暫存轉換用的table,sql 如下:
create table tmp_newdate(
id int,
newdate date
);
insert into tmp_newdate (id, newdate)
select id, chi2jul(olddate)
from olddate;
mysql> insert into tmp_newdate (id, newdate)
-> select id, chi2jul(olddate)
-> from olddate;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from tmp_newdate;
+------+------------+
| id | newdate |
+------+------------+
| 1 | 2010-03-05 |
| 2 | 2012-10-04 |
+------+------------+
2 rows in set (0.00 sec)
mysql> update olddate,tmp_newdate
-> set olddate.newdate = tmp_newdate.newdate
-> where olddate.id = tmp_newdate.id;
Query OK, 2 rows affected (0.15 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from olddate;
+----+-----------+------------+
| id | olddate | newdate |
+----+-----------+------------+
| 1 | 99/03/05 | 2010-03-05 |
| 2 | 101/10/04 | 2012-10-04 |
+----+-----------+------------+
2 rows in set (0.00 sec)
成功轉換!
有了民國年轉西洋年,再來要有配對的西洋年轉民國年的Function才完整. 此時要注意1911年及1911年以前的狀況. 1912年為民國1年,1911年為民前1年. 另外為了兼顧轉換時不需要民國yyyy年mm月dd日格式,所以有轉換型態之參數. 程式如下:
DELIMITER $$
DROP FUNCTION IF EXISTS `mysample`.`jul2chi`$$
CREATE FUNCTION `mysample`.`jul2chi` (in_date DATE, in_trantype INT) RETURNS CHAR(18)
BEGIN
DECLARE date_char1 char(30);
DECLARE tran_date date;
DECLARE date_char char(10);
DECLARE cyear char(4);
DECLARE cmonth char(2);
DECLARE cday char(2);
DECLARE iyear int;
DECLARE rtn_date char(18);
DECLARE after_flag int default 1;
DECLARE charyear char(4) default '民國';
-- ************************************
-- vincent chang
-- ************************************
set date_char1 = cast(in_date as char);
set tran_date = str_to_date(date_char1, '%Y-%m-%d');
set date_char = date_format(in_date,'%Y-%m-%d');
-- Force transfered to YYYY-mm-dd format
set cyear = left(date_char,4);
set cmonth = substr(date_char,6,2);
set cday = right(date_char,2);
set iyear = convert(cyear, signed integer);
set iyear = iyear - 1911;
if iyear <= 0 then
set after_flag = -1;
set iyear = iyear - 1;
set iyear = iyear * after_flag;
set charyear = '民前';
end if;
if in_trantype = 1 then
set rtn_date = concat(charyear,convert(iyear,char(4)),'年',
cmonth,'月',cday,'日');
else
set rtn_date = concat(iyear,'-',cmonth,'-',cday);
end if;
RETURN rtn_date;
END$$
DELIMITER ;
接下來進行測試.建立一個table,把一些特別的日子放進去.
use mysample;
create table julchi(
julian date,
chinadate1 char(18),
chinadate2 char(18)
);
insert into julchi(julian)
values (str_to_date('2010-06-30', '%Y-%m-%d'));
insert into julchi(julian)
values (str_to_date('2014-06-30', '%Y-%m-%d'));
insert into julchi(julian)
values (str_to_date('1999-12-31', '%Y-%m-%d'));
insert into julchi(julian)
values (str_to_date('2000-01-01', '%Y-%m-%d'));
insert into julchi(julian)
values (str_to_date('1912-01-01', '%Y-%m-%d'));
insert into julchi(julian)
values (str_to_date('1911-12-31', '%Y-%m-%d'));
insert into julchi(julian)
values (str_to_date('1900-06-30', '%Y-%m-%d'));
mysql> update julchi
-> set chinadate1 = jul2chi(julian,1);
Query OK, 7 rows affected (0.01 sec)
Rows matched: 7 Changed: 7 Warnings: 0
mysql> update julchi
-> set chinadate2 = jul2chi(julian,2);
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7 Changed: 7 Warnings: 0
mysql> select * from julchi;
+------------+------------------------+------------+
| julian | chinadate1 | chinadate2 |
+------------+------------------------+------------+
| 2010-06-30 | 民國99年06月30日 | 99-06-30 |
| 2014-06-30 | 民國103年06月30日 | 103-06-30 |
| 1999-12-31 | 民國88年12月31日 | 88-12-31 |
| 2000-01-01 | 民國89年01月01日 | 89-01-01 |
| 1912-01-01 | 民國1年01月01日 | 1-01-01 |
| 1911-12-31 | 民前1年12月31日 | 1-12-31 |
| 1900-06-30 | 民前12年06月30日 | 12-06-30 |
+------------+------------------------+------------+
7 rows in set (0.00 sec)
轉換成功!
民國年與西洋年轉換之MySQL Function的運用
之前已經建立兩個民國年與西洋年之轉換函數,也進行了測試.接下來就使用此轉換函數 來應對民國年的狀況來作探討. ----------------------------------------------------- 1. 假設原本的系統,是用民國年來存日期資料.
此一方式是較不好的,無法利用日期函數;我們就能利用之前的chi2jul()來將原本的資料改為西洋年格式存放.在前面已經介紹 過轉換方法.現在比較少系統會是以此方式(民國年來存日期資料),會設法改用西洋年格式存放.而進到下一段的情況. ------------------------------------------------------------------------------------------------------------------- 2. 系統已經使用西洋年存放日期資料,但系統部份功能仍需存取民國年.
此一情形在系統中實務應用上多半是以西洋年日期格式來作判斷,展現時轉換為西洋年與民國年同時,或以民國年方式展現, 例如介面與報表等.而介面與報表之程式較多,進行系統升級時,若有漏網之魚就會造成後續運作上的困擾;或是運用一些報表 系統時,增加運算功能會影響報表產生的速度.若我們能在MySQL直接產生,介面與報表系統均向MySQL存取就比較單純與且效率 較高.
2.1 使用 VIEW的方式 -------------------------- 之前有一個測試的table: julchi , 我們根據他來產生一個view.
mysql> select * from julchi;
+------------+------------------------+------------+
| julian | chinadate1 | chinadate2 |
+------------+------------------------+------------+
| 2010-06-30 | 民國99年06月30日 | 99-06-30 |
| 2014-06-30 | 民國103年06月30日 | 103-06-30 |
| 1999-12-31 | 民國88年12月31日 | 88-12-31 |
| 2000-01-01 | 民國89年01月01日 | 89-01-01 |
| 1912-01-01 | 民國1年01月01日 | 1-01-01 |
| 1911-12-31 | 民前1年12月31日 | 1-12-31 |
| 1900-06-30 | 民前12年06月30日 | 12-06-30 |
| 2010-07-02 | NULL | NULL |
| 1985-05-25 | 民國74年05月25日 | 74-05-25 |
+------------+------------------------+------------+
9 rows in set (0.10 sec)
# ---- 可以觀察到此表已經先包含一個未使用 jul2chi()配合update轉換為民國日期的一筆紀錄. -----
mysql> CREATE VIEW v_julchi(julian, chidate1, chidate2) AS
-> SELECT julian, jul2chi(julian,1), jul2chi(julian,2)
-> FROM julchi;
Query OK, 0 rows affected (0.30 sec)
# ---- 其實只有取用julchi table的julian欄位. -----
mysql> select * from v_julchi;
+------------+------------------------+-----------+
| julian | chidate1 | chidate2 |
+------------+------------------------+-----------+
| 2010-06-30 | 民國99年06月30日 | 99-06-30 |
| 2014-06-30 | 民國103年06月30日 | 103-06-30 |
| 1999-12-31 | 民國88年12月31日 | 88-12-31 |
| 2000-01-01 | 民國89年01月01日 | 89-01-01 |
| 1912-01-01 | 民國1年01月01日 | 1-01-01 |
| 1911-12-31 | 民前1年12月31日 | 1-12-31 |
| 1900-06-30 | 民前12年06月30日 | 12-06-30 |
| 2010-07-02 | 民國99年07月02日 | 99-07-02 |
| 1985-05-25 | 民國74年05月25日 | 74-05-25 |
+------------+------------------------+-----------+
9 rows in set (0.09 sec)
# ---- view 就能產生出民國年格式的資料 ----- # ---- 接下插入兩筆紀錄,就用民99年底與民100年初吧. -----
mysql> insert into julchi(julian)
-> values (str_to_date('2010-12-31', '%Y-%m-%d'));
Query OK, 1 row affected (0.10 sec)
mysql> insert into julchi(julian)
-> values (str_to_date('2011-01-01', '%Y-%m-%d'));
Query OK, 1 row affected (0.00 sec)
mysql> select * from v_julchi;
+------------+------------------------+-----------+
| julian | chidate1 | chidate2 |
+------------+------------------------+-----------+
| 2010-06-30 | 民國99年06月30日 | 99-06-30 |
| 2014-06-30 | 民國103年06月30日 | 103-06-30 |
| 1999-12-31 | 民國88年12月31日 | 88-12-31 |
| 2000-01-01 | 民國89年01月01日 | 89-01-01 |
| 1912-01-01 | 民國1年01月01日 | 1-01-01 |
| 1911-12-31 | 民前1年12月31日 | 1-12-31 |
| 1900-06-30 | 民前12年06月30日 | 12-06-30 |
| 2010-07-02 | 民國99年07月02日 | 99-07-02 |
| 1985-05-25 | 民國74年05月25日 | 74-05-25 |
| 2010-12-31 | 民國99年12月31日 | 99-12-31 |
| 2011-01-01 | 民國100年01月01日 | 100-01-01 |
+------------+------------------------+-----------+
11 rows in set (0.00 sec)
# ---- 可以看到結果是順利轉換,底下是 julchi,可以作為對照 -----
mysql> select * from julchi;
+------------+------------------------+------------+
| julian | chinadate1 | chinadate2 |
+------------+------------------------+------------+
| 2010-06-30 | 民國99年06月30日 | 99-06-30 |
| 2014-06-30 | 民國103年06月30日 | 103-06-30 |
| 1999-12-31 | 民國88年12月31日 | 88-12-31 |
| 2000-01-01 | 民國89年01月01日 | 89-01-01 |
| 1912-01-01 | 民國1年01月01日 | 1-01-01 |
| 1911-12-31 | 民前1年12月31日 | 1-12-31 |
| 1900-06-30 | 民前12年06月30日 | 12-06-30 |
| 2010-07-02 | NULL | NULL |
| 1985-05-25 | 民國74年05月25日 | 74-05-25 |
| 2010-12-31 | NULL | NULL |
| 2011-01-01 | NULL | NULL |
+------------+------------------------+------------+
11 rows in set (0.00 sec)
---------------------------------------------------------------------------- 由上面的實做可以觀察到,使用view就能進行轉換,而且view的新欄位還能指定名稱. 當我們要在原本的系統連接時,就能使用view來靈活的中介,讓系統的前端介面與報表 的更動降到最低的程度. 例如可以將原本的table改名,而建立view使用原本table的名字等等方式.
======================================================================
2.2 使用 Table + Trigger的方式 前面我們看到了使用 VIEW的方式,若是產生報表的程式頗多,且資料量也大,用VIEW的方式存取, 每次都需要進行運算,如此效率較差. 但是若是使用Table的方式,可以觀察上面的 julchi,插入新的紀錄,若在插入時沒有呼叫julchi()函數, 像是
INSERT INTO julchi VALUES(
str_to_date('2011-01-02', '%Y-%m-%d'), jul2chi(str_to_date('2011-01-02', '%Y-%m-%d'), 1), jul2chi(str_to_date('2011-01-02', '%Y-%m-%d'), 2));
而只是
mysql> insert into julchi(julian)
-> values (str_to_date('2011-01-01', '%Y-%m-%d'));
就會在民國年的欄位有NULL值. 但若是原本沒有民國年欄位,是後來加上去的,則我們就要在原來的系統去找相關的SQL Command,都要修正, 如此一來工程浩大,且易有遺漏.若我們不想對原本的系統程式進行大規模的修正,則有下面兩種方式: 2.2.1 使用定期 update 方式 之前我們有介紹 UPDATE...SET 的方式
mysql> update julchi
-> set chinadate1 = jul2chi(julian, 1);
Query OK, 3 rows affected (0.01 sec)
Rows matched: 12 Changed: 3 Warnings: 0
mysql> update julchi
-> set chinadate2 = jul2chi(julian, 2);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 12 Changed: 3 Warnings: 0
mysql> select * from julchi;
+------------+------------------------+------------+
| julian | chinadate1 | chinadate2 |
+------------+------------------------+------------+
| 2010-06-30 | 民國99年06月30日 | 99-06-30 |
| 2014-06-30 | 民國103年06月30日 | 103-06-30 |
| 1999-12-31 | 民國88年12月31日 | 88-12-31 |
| 2000-01-01 | 民國89年01月01日 | 89-01-01 |
| 1912-01-01 | 民國1年01月01日 | 1-01-01 |
| 1911-12-31 | 民前1年12月31日 | 1-12-31 |
| 1900-06-30 | 民前12年06月30日 | 12-06-30 |
| 2010-07-02 | 民國99年07月02日 | 99-07-02 |
| 1985-05-25 | 民國74年05月25日 | 74-05-25 |
| 2010-12-31 | 民國99年12月31日 | 99-12-31 |
| 2011-01-01 | 民國100年01月01日 | 100-01-01 |
| 2011-01-02 | 民國100年01月02日 | 100-01-02 |
+------------+------------------------+------------+
12 rows in set (0.00 sec)
這樣就可以修正.但此一方式只適合在將原本的資料轉換到新的table時使用,而無法在系統平時運作時使用. 使用此方式,會進行table scan,效率差;需要執行此一UPDATE...SET指令後資料才會修正.不管是寫一個外部 程式,晚上11點時由工讀生啟動;或是利用cron table;或是利用MySQL的EVENT功能啟動,都會有問題. 當 julian 有變動時, 而系統修正的這兩道指令還沒執行時, chinadate1 與 chinadate2 卻還是舊資料,就會造成錯誤.
2.2.2 Trigger方式 INSERT Trigger: 我們先寫一個 INSERT Trigger.此tigeer呼叫了之前的jul2chi()
DELIMITER $$
DROP TRIGGER IF EXISTS `mysample`.`tri_julchi_insert`$$
CREATE TRIGGER `mysample`.`tri_julchi_insert` BEFORE INSERT ON julchi FOR EACH ROW
BEGIN
SET NEW.chinadate1 = jul2chi(NEW.julian, 1);
SET NEW.chinadate2 = jul2chi(NEW.julian, 2);
END$$
DELIMITER ;
測試看看:
mysql> insert into julchi(julian)
-> values (str_to_date('2011-01-04', '%Y-%m-%d'));
Query OK, 1 row affected (0.00 sec)
mysql> select * from julchi where julian='2011-01-04';
+------------+------------------------+------------+
| julian | chinadate1 | chinadate2 |
+------------+------------------------+------------+
| 2011-01-04 | 民國100年01月04日 | 100-01-04 |
+------------+------------------------+------------+
1 row in set (0.00 sec)
可以正常轉換. 接下來就要再發展一個update trigger,這樣我們INSERT/UPDATE 只要以西洋年格式的julian欄位為主,而對應的chinadate1,chinadate2 兩個民國年欄位自動會跟著julian變化. 為了說明的方便,我們將建立一個新的Table julchi2,增加一個 dataid欄位,以方便辨識.
CREATE TABLE julchi2(
dataid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
julian DATE,
chinadate1 CHAR(18),
chinadate2 CHAR(18)
);
mysql> INSERT INTO julchi2(julian, chinadate1, chinadate2)
-> SELECT julian,chinadate1,chinadate2
-> FROM julchi;
Query OK, 14 rows affected (0.00 sec)
Records: 14 Duplicates: 0 Warnings: 0
mysql> select * from julchi2;
+--------+------------+------------------------+------------+
| dataid | julian | chinadate1 | chinadate2 |
+--------+------------+------------------------+------------+
| 1 | 2010-06-30 | 民國99年06月30日 | 99-06-30 |
| 2 | 2014-06-30 | 民國103年06月30日 | 103-06-30 |
| 3 | 1999-12-31 | 民國88年12月31日 | 88-12-31 |
| 4 | 2000-01-01 | 民國89年01月01日 | 89-01-01 |
| 5 | 1912-01-01 | 民國1年01月01日 | 1-01-01 |
| 6 | 1911-12-31 | 民前1年12月31日 | 1-12-31 |
| 7 | 1900-06-30 | 民前12年06月30日 | 12-06-30 |
| 8 | 2010-07-02 | 民國99年07月02日 | 99-07-02 |
| 9 | 1985-05-25 | 民國74年05月25日 | 74-05-25 |
| 10 | 2010-12-31 | 民國99年12月31日 | 99-12-31 |
| 11 | 2011-01-01 | 民國100年01月01日 | 100-01-01 |
| 12 | 2011-01-02 | 民國100年01月02日 | 100-01-02 |
| 13 | 2011-01-03 | 民國100年01月03日 | 100-01-03 |
| 14 | 2011-01-04 | 民國100年01月04日 | 100-01-04 |
+--------+------------+------------------------+------------+
14 rows in set (0.00 sec)
接下來要對新的table產生配合的trigger.
INSERT Trigger.跟剛才的一樣,只是在trigger名稱與作用的table名稱更改即可.
DELIMITER $$
DROP TRIGGER IF EXISTS `mysample`.`tri_julchi2_bf_insert`$$
CREATE TRIGGER `mysample`.`tri_julchi2_bf_insert` BEFORE INSERT ON julchi2 FOR EACH ROW
BEGIN
SET NEW.chinadate1 = jul2chi(NEW.julian, 1);
SET NEW.chinadate2 = jul2chi(NEW.julian, 2);
END$$
DELIMITER ;
UPDATE Trigger.其實就是將作用的方式改為UPDATE, 當然是不同的trigger名稱.
DELIMITER $$
DROP TRIGGER IF EXISTS `mysample`.`tri_julchi2_bf_update`$$
CREATE TRIGGER `mysample`.`tri_julchi2_bf_update` BEFORE UPDATE ON julchi2 FOR EACH ROW
BEGIN
SET NEW.chinadate1 = jul2chi(NEW.julian, 1);
SET NEW.chinadate2 = jul2chi(NEW.julian, 2);
END$$
DELIMITER ;
接下來進行測試.
mysql> insert into julchi2(julian)
-> values (str_to_date('2011-01-05', '%Y-%m-%d'));
Query OK, 1 row affected (0.00 sec)
mysql> select * from julchi2;
+--------+------------+------------------------+------------+
| dataid | julian | chinadate1 | chinadate2 |
+--------+------------+------------------------+------------+
| 1 | 2010-06-30 | 民國99年06月30日 | 99-06-30 |
| 2 | 2014-06-30 | 民國103年06月30日 | 103-06-30 |
| 3 | 1999-12-31 | 民國88年12月31日 | 88-12-31 |
| 4 | 2000-01-01 | 民國89年01月01日 | 89-01-01 |
| 5 | 1912-01-01 | 民國1年01月01日 | 1-01-01 |
| 6 | 1911-12-31 | 民前1年12月31日 | 1-12-31 |
| 7 | 1900-06-30 | 民前12年06月30日 | 12-06-30 |
| 8 | 2010-07-02 | 民國99年07月02日 | 99-07-02 |
| 9 | 1985-05-25 | 民國74年05月25日 | 74-05-25 |
| 10 | 2010-12-31 | 民國99年12月31日 | 99-12-31 |
| 11 | 2011-01-01 | 民國100年01月01日 | 100-01-01 |
| 12 | 2011-01-02 | 民國100年01月02日 | 100-01-02 |
| 13 | 2011-01-03 | 民國100年01月03日 | 100-01-03 |
| 14 | 2011-01-04 | 民國100年01月04日 | 100-01-04 |
| 15 | 2011-01-05 | 民國100年01月05日 | 100-01-05 |
+--------+------------+------------------------+------------+
可以看到INSERT Trigger發生效用了.
mysql> UPDATE julchi2
-> SET julian=str_to_date('1985-06-06','%Y-%m-%d')
-> WHERE dataid=9;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from julchi2 where dataid=9;
+--------+------------+-----------------------+------------+
| dataid | julian | chinadate1 | chinadate2 |
+--------+------------+-----------------------+------------+
| 9 | 1985-06-06 | 民國74年06月06日 | 74-06-06 |
+--------+------------+-----------------------+------------+
1 row in set (0.00 sec)
可以看到UPDATE Trigger也發生效用了.
這樣我們就可以利用Trigger,增加新的民國欄位,供一些需要民國欄位的報表或介面來使用, 而原本系統裡面程式裡的INSERT/UPDATE也無須更動,保留原本的方式即可.如此就可節省大 量的時間,而且也不會有遺漏. ********************************************** 結論: 使用MySQL的store function來產生日期轉換函數,對原本舊系統更新到新的日期格式,可以方便的轉換. 轉換後需要民國年格式的報表或介面,也介紹了配合stroe function產生view的方式;以及使用 table配合trigger的方式. 對需要更改系統程式以對應民國100年,或是發展系統需要存取民國年格式,提供了以上兩種方法. 可以視狀況靈活使用.
原文出處:請教mysql~民國轉換西元處理
|