作者 主題: 請教mysql~民國轉換西元處理  (閱讀 23642 次)

0 會員 與 1 訪客 正在閱讀本文。

taiwancmh

  • 可愛的小學生
  • *
  • 文章數: 3
    • 檢視個人資料
請教mysql~民國轉換西元處理
« 於: 2010-06-30 00:40 »
資料表abc中有一個欄位aaa

例如
欄位資料1為99/03/05
欄位資料2為101/10/04

怎麼下mysql來做指定日期今天2010-06-30
30天以內的資料篩選

西元年的表示式呢?

謝謝

Yamaka

  • 俺是博士!
  • *****
  • 文章數: 4913
    • 檢視個人資料
    • http://www.ecmagic.com
回覆: 請教mysql~民國轉換西元處理
« 回覆 #1 於: 2010-06-30 07:50 »
資料表abc中有一個欄位aaa

例如
欄位資料1為99/03/05
欄位資料2為101/10/04

怎麼下mysql來做指定日期今天2010-06-30
30天以內的資料篩選

西元年的表示式呢?
謝謝

可能的話, 建議另外加一個西元欄位, 這樣會方便許多
而且如果資料量多時, 效能也會比較好.
如果無法加欄位, 那就要抓年份加1911再做判斷了

taiwancmh

  • 可愛的小學生
  • *
  • 文章數: 3
    • 檢視個人資料
回覆: 請教mysql~民國轉換西元處理
« 回覆 #2 於: 2010-06-30 09:56 »
資料表abc中有一個欄位aaa

例如
欄位資料1為99/03/05
欄位資料2為101/10/04

怎麼下mysql來做指定日期今天2010-06-30
30天以內的資料篩選

西元年的表示式呢?
謝謝

可能的話, 建議另外加一個西元欄位, 這樣會方便許多
而且如果資料量多時, 效能也會比較好.
如果無法加欄位, 那就要抓年份加1911再做判斷了


資料、系統轉換中
還是需要讀取舊的資料
不代表未來還是要用民國
謝謝~
請看題目回答~不要搞其他路途~感恩

Yamaka

  • 俺是博士!
  • *****
  • 文章數: 4913
    • 檢視個人資料
    • http://www.ecmagic.com
回覆: 請教mysql~民國轉換西元處理
« 回覆 #3 於: 2010-06-30 10:16 »
請看題目回答~不要搞其他路途~感恩

這位老大~
要人家看題目回答
那請將標目寫清楚好嗎
你以為大家都是三太子嗎 = =+

Darkhero

  • 酷!學園 學長們
  • 俺是博士!
  • *****
  • 文章數: 3728
  • 性別: 男
    • 檢視個人資料
    • ㄚ凱隨手紀
回覆: 請教mysql~民國轉換西元處理
« 回覆 #4 於: 2010-06-30 10:26 »
資料、系統轉換中
還是需要讀取舊的資料
不代表未來還是要用民國
謝謝~
請看題目回答~不要搞其他路途~感恩

問個問題態度真是差!!!!

我想..人家已經給了你他的答案了, 我覺的他也沒看錯你的問題...
更何況你本來的問題就沒說清楚到底是怎樣的情況... 誰知道是新舊系統轉換?...

一般來說mysql都是外國人寫得,用西元是天經地義..
大部分的人來說都是用西元存然後顯示再調整為民國...

你自己喜歡用民國,搞得自己不好弄是你家的事情.. 搞得好像你是誰, 人家提的意見就不是意見就是了...
希望我們的討論是為了把問題解決,而不是爭論誰對誰錯.
『灌水才是重點,發文只是順便』
『我寧可讓不會釣魚的工程師餓死,也不想讓會餓死的工程師去攪沉公司....』
Blog: http://blog.darkhero.net/
秘密基地: http://www.darkhero.net/comic/
目前服務的網站: http://www.libook.com.tw/

micmic3

  • 俺是博士!
  • *****
  • 文章數: 1692
    • 檢視個人資料
回覆: 請教mysql~民國轉換西元處理
« 回覆 #5 於: 2010-06-30 10:28 »
欄位格式不是DATE 相關的時間格式,是沒法用時間去比的
如 Yamaka 說的
1.先開一個時間格式的欄位
2.寫個程式把資料轉到新開的時間格式欄位
之前就可以用時間比了

hikohan

  • 俺是博士!
  • *****
  • 文章數: 1288
    • 檢視個人資料
回覆: 請教mysql~民國轉換西元處理
« 回覆 #6 於: 2010-06-30 11:08 »
文字切開成陣列
陣列運算為數值
西元民國何來分
只是加減帶乘除

好,打完收工。

黑老大,這樣跟他說就對了,馬步還沒練好就練降龍27掌...

基本上時間格式這樣存就是完全錯誤。

lifeIsFunWithPHP.

taiwancmh

  • 可愛的小學生
  • *
  • 文章數: 3
    • 檢視個人資料
回覆: 請教mysql~民國轉換西元處理
« 回覆 #7 於: 2010-06-30 12:43 »
欄位格式不是DATE 相關的時間格式,是沒法用時間去比的
如 Yamaka 說的
1.先開一個時間格式的欄位
2.寫個程式把資料轉到新開的時間格式欄位
之前就可以用時間比了

1.新舊系統轉換
2.由於是不同資料庫~我先把舊資料庫系統資料轉換到mysql
3.當然欄位資料不一樣~所以才需要下mysql command去修正格式與update資料

就因為如此~才問格式怎麼組合與修正
譬如
LEFT(DATE, 3)

MID(DATE,4,2)

RIGHT(DATE,2)

然後組合在一起成一個資料去格式化

command要如何下~!??

Darkhero

  • 酷!學園 學長們
  • 俺是博士!
  • *****
  • 文章數: 3728
  • 性別: 男
    • 檢視個人資料
    • ㄚ凱隨手紀
回覆: 請教mysql~民國轉換西元處理
« 回覆 #8 於: 2010-06-30 12:50 »
用 Select ... into ... 阿...
http://webdesign.piipo.com/sql/sql_select_into

另外... left(DATE,3) 肯定是會錯得...

建議還是用程式去處理..抓出來一筆一筆的去用 split 切割, 然後重新 + 1911 去變成西元再塞到對應的欄位吧...

再來..這裡應該沒有人有義務要提供你 command 怎麼下... 提出意見跟方法了, 剩下是你自己要想辦法處理的...
希望我們的討論是為了把問題解決,而不是爭論誰對誰錯.
『灌水才是重點,發文只是順便』
『我寧可讓不會釣魚的工程師餓死,也不想讓會餓死的工程師去攪沉公司....』
Blog: http://blog.darkhero.net/
秘密基地: http://www.darkhero.net/comic/
目前服務的網站: http://www.libook.com.tw/

bunko

  • 懷疑的國中生
  • **
  • 文章數: 67
    • 檢視個人資料
回覆: 請教mysql~民國轉換西元處理
« 回覆 #9 於: 2010-06-30 13:15 »
野人獻曝一下....
民國年與西洋年轉換之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)

成功轉換!

Darkhero

  • 酷!學園 學長們
  • 俺是博士!
  • *****
  • 文章數: 3728
  • 性別: 男
    • 檢視個人資料
    • ㄚ凱隨手紀
回覆: 請教mysql~民國轉換西元處理
« 回覆 #10 於: 2010-06-30 13:23 »
真的要拍拍手... 寫得真好...

說真的現在用 mysql SP 的人或許還比較少, 這篇幾乎可以當作寫 MySQL SP 的一個簡單的範例了! :)

不過這整串..我想晚點轉到 Database 版, 目前看起來已經跟 php 沒關係囉.. :)

野人獻曝一下....
民國年與西洋年轉換之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)

成功轉換!

希望我們的討論是為了把問題解決,而不是爭論誰對誰錯.
『灌水才是重點,發文只是順便』
『我寧可讓不會釣魚的工程師餓死,也不想讓會餓死的工程師去攪沉公司....』
Blog: http://blog.darkhero.net/
秘密基地: http://www.darkhero.net/comic/
目前服務的網站: http://www.libook.com.tw/

bunko

  • 懷疑的國中生
  • **
  • 文章數: 67
    • 檢視個人資料
回覆: 請教mysql~民國轉換西元處理
« 回覆 #11 於: 2010-06-30 16:26 »
有了民國年轉西洋年,再來要有配對的西洋年轉民國年的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)

轉換成功!

Yamaka

  • 俺是博士!
  • *****
  • 文章數: 4913
    • 檢視個人資料
    • http://www.ecmagic.com
回覆: 請教mysql~民國轉換西元處理
« 回覆 #12 於: 2010-06-30 18:11 »
有了民國年轉西洋年,再來要有配對的西洋年轉民國年的Function才完整.
此時要注意1911年及1911年以前的狀況.
1912年為民國1年,1911年為民前1年.
另外為了兼顧轉換時不需要民國yyyy年mm月dd日格式,所以有轉換型態之參數.
---略---

這麼精彩的東西, bunko大乾脆北、中、南各來幾場啦  ;D ;D

wenlien

  • 憂鬱的高中生
  • ***
  • 文章數: 119
  • 性別: 男
    • 檢視個人資料
    • Open or not open, that is the stupid question.
回覆: 請教mysql~民國轉換西元處理
« 回覆 #13 於: 2010-07-06 22:16 »
有了民國年轉西洋年,再來要有配對的西洋年轉民國年的Function才完整.
此時要注意1911年及1911年以前的狀況.
1912年為民國1年,1911年為民前1年.
另外為了兼顧轉換時不需要民國yyyy年mm月dd日格式,所以有轉換型態之參數.
---略---

這麼精彩的東西, bunko大乾脆北、中、南各來幾場啦  ;D ;D

贊成! 我報名先!!
regards,

Stanley Huang

wenlien

  • 憂鬱的高中生
  • ***
  • 文章數: 119
  • 性別: 男
    • 檢視個人資料
    • Open or not open, that is the stupid question.
回覆: 請教mysql~民國轉換西元處理
« 回覆 #14 於: 2010-07-06 22:52 »
資料表abc中有一個欄位aaa

例如
欄位資料1為99/03/05
欄位資料2為101/10/04

怎麼下mysql來做指定日期今天2010-06-30
30天以內的資料篩選

西元年的表示式呢?

謝謝

暴力法:

代碼: [選擇]
select d, lpad(d,9,0)
from a
order by lpad(d,9,0);
+-----------+-------------+
| d         | lpad(d,9,0) |
+-----------+-------------+
| 97/01/01  | 097/01/01   |
| 99/02/28  | 099/02/28   |
| 99/06/07  | 099/06/07   |
| 99/06/16  | 099/06/16   |
| 99/07/05  | 099/07/05   |
| 99/07/06  | 099/07/06   |
| 99/12/31  | 099/12/31   |
| 100/01/01 | 100/01/01   |
+-----------+-------------+
8 rows in set (0.00 sec)
代碼: [選擇]
select
d c1,
lpad(d,9,0) c2,
concat(
  lpad(
    year(
      current_date
    )-1911,3,0
  ),
  '/',
  lpad(
    month(
      current_date
    ),2,0),
  '/',
  lpad(
    day(
      current_date
    ),2,0
  )
) c3,
concat(
  lpad(
    year(
      date_add(
        current_date, interval -30 day
      )
    )-1911,3,0
  ),
  '/',
  lpad(
    month(
      date_add(
        current_date, interval -30 day
      )
    ),2,0
  ),
  '/',
  lpad(
    day(
      date_add(
        current_date, interval -30 day
      )
    ),2,0
  )
) c4
from a
where
lpad(d,9,0) <=
concat(
  lpad(
    year(
      current_date
    )-1911,3,0
  ),
  '/',
  lpad(
    month(
      current_date
    ),2,0),
  '/',
  lpad(
    day(
      current_date
    ),2,0
  )
)
and
lpad(d,9,0) >=
concat(
  lpad(
    year(
      date_add(
        current_date, interval -30 day
      )
    )-1911,3,0
  ),
  '/',
  lpad(
    month(
      date_add(
        current_date, interval -30 day
      )
    ),2,0
  ),
  '/',
  lpad(
    day(
      date_add(
        current_date, interval -30 day
      )
    ),2,0
  )
);
+----------+-----------+-----------+-----------+
| c1       | c2        | c3        | c4        |
+----------+-----------+-----------+-----------+
| 99/07/06 | 099/07/06 | 099/07/06 | 099/06/06 |
| 99/06/16 | 099/06/16 | 099/07/06 | 099/06/06 |
| 99/07/05 | 099/07/05 | 099/07/06 | 099/06/06 |
| 99/06/07 | 099/06/07 | 099/07/06 | 099/06/06 |
+----------+-----------+-----------+-----------+
4 rows in set (0.00 sec)

regards,

Stanley Huang

bunko

  • 懷疑的國中生
  • **
  • 文章數: 67
    • 檢視個人資料
回覆: 請教mysql~民國轉換西元處理
« 回覆 #15 於: 2010-07-25 12:02 »
民國年與西洋年轉換之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年,或是發展系統需要存取民國年格式,提供了以上兩種方法.
可以視狀況靈活使用.

wenlien

  • 憂鬱的高中生
  • ***
  • 文章數: 119
  • 性別: 男
    • 檢視個人資料
    • Open or not open, that is the stupid question.
回覆: 請教mysql~民國轉換西元處理
« 回覆 #16 於: 2010-07-25 12:35 »
恕刪
...
2.1 使用 VIEW的方式
...
2.2 使用 Table + Trigger的方式
...

好久沒有看到這個有心的文章了,
一定要先拍拍手.... ;D

補充一些個人的經驗給大家參考/切磋~

使用view並沒有讓performance有太大的幫助,
但是還要讓DBA額外多管理一個view.
我個人比較喜歡在select中直接引用function.

至於Table + Trigger,
Trigger是一個很耗資源的方法,
通常只有外萬不得已時才會使用.

而Table + 使用定期 update 方式,
可能還要加上一些判斷條件(ex. 轉換資料的起始位置),
否則當時間一長(部分資料已轉換過), 且檔案越來越大,
就會浪費時間在重複處理已經處理的資料.
如果, 表格的時間會變動,
變成要同步處理時間轉換,
否則會有資料不一致的問題.

regards,

Stanley Huang
regards,

Stanley Huang

bunko

  • 懷疑的國中生
  • **
  • 文章數: 67
    • 檢視個人資料
回覆: 請教mysql~民國轉換西元處理
« 回覆 #17 於: 2010-07-25 13:17 »
恕刪
...
2.1 使用 VIEW的方式
...
2.2 使用 Table + Trigger的方式
...

好久沒有看到這個有心的文章了,
一定要先拍拍手.... ;D

補充一些個人的經驗給大家參考/切磋~

使用view並沒有讓performance有太大的幫助,
但是還要讓DBA額外多管理一個view.
我個人比較喜歡在select中直接引用function.

至於Table + Trigger,
Trigger是一個很耗資源的方法,
通常只有外萬不得已時才會使用.

而Table + 使用定期 update 方式,
可能還要加上一些判斷條件(ex. 轉換資料的起始位置),
否則當時間一長(部分資料已轉換過), 且檔案越來越大,
就會浪費時間在重複處理已經處理的資料.
如果, 表格的時間會變動,
變成要同步處理時間轉換,
否則會有資料不一致的問題.

regards,

Stanley Huang


系統在規劃時就要考慮清楚,使用function轉換的方式,這樣不管是效能與正確性都佳. 如Stanley Huang所提到的 ;)
至於剛才所提到的方式如view/trigger其實是屬於在後面添加,都要付出執行效能的代價.
所以就要對面對的狀況來進行取捨,若是只是需要產生月報表時使用, 那就在產生報表前update民國年欄位為NULL的
就好,或是再行添加欄位來進行狀況判斷.
僅就此一民國年與西洋年轉換之題目,提供一些小小的程式與經驗談.也歡迎大家提供補充與修正. :)

micmic3

  • 俺是博士!
  • *****
  • 文章數: 1692
    • 檢視個人資料
回覆: 請教mysql~民國轉換西元處理
« 回覆 #18 於: 2010-07-26 14:26 »
trigger 有效能的問題,那 store procedure呢?

wenlien

  • 憂鬱的高中生
  • ***
  • 文章數: 119
  • 性別: 男
    • 檢視個人資料
    • Open or not open, that is the stupid question.
回覆: 請教mysql~民國轉換西元處理
« 回覆 #19 於: 2010-07-26 19:16 »
trigger 有效能的問題,那 store procedure呢?
Stored Procedure 也是會suffer到一些效能。
所以,我通常只有在需要在backend整理(需定期準備預先處理/或migrate data)資料時,
才會使用到Stored Procedure.
有一派說法(大家自行評斷), 系統中使用過多stored procedure,
或將邏輯拆開寫在Stored Procedure,
會增加開發/管理的複雜度,
非萬不得已, 不要輕易嘗試.

regards,

Stanley Huang
regards,

Stanley Huang

micmic3

  • 俺是博士!
  • *****
  • 文章數: 1692
    • 檢視個人資料
回覆: 請教mysql~民國轉換西元處理
« 回覆 #20 於: 2010-07-27 10:03 »
trigger 有效能的問題,那 store procedure呢?
Stored Procedure 也是會suffer到一些效能。
所以,我通常只有在需要在backend整理(需定期準備預先處理/或migrate data)資料時,
才會使用到Stored Procedure.
有一派說法(大家自行評斷), 系統中使用過多stored procedure,
或將邏輯拆開寫在Stored Procedure,
會增加開發/管理的複雜度,
非萬不得已, 不要輕易嘗試.

regards,

Stanley Huang
會增加開發/管理的複雜度, 這一點我十分同意(trigger 也是)...