3. MySQL metadata 的存放空間 information_schema 在database size上的應用
user:mysample 有權限的database有三個,其中information_schema是系統metadata的存放空間,
我們就可以存取他來獲得一些資訊.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysample |
| test |
+--------------------+
我們可以執行下面的指令:
select table_schema,sum(data_length),sum(index_length),sum(data_length)+sum(index_length) as totsize
from information_schema.tables
where table_type='BASE TABLE'
and engine='MyISAM'
group by table_schema;
+--------------+------------------+-------------------+---------+
| table_schema | sum(data_length) | sum(index_length) | totsize |
+--------------+------------------+-------------------+---------+
| mysample | 37750 | 38912 | 76662 |
| test | 152 | 2048 | 2200 |
+--------------+------------------+-------------------+---------+
2 rows in set (0.01 sec)
結果均相符,因為來源都一樣. 這裡可以看到MySQL方便的地方,我們不需要將information_schema排除.
到目前都是以user:mysample來作例子.當我們要以整個instance為範圍來管理就需要用root權限登入了.
以root的視野來看:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| auth |
| bookmarks |
| books |
| impexp_text |
| joomla |
| joomla2 |
| mail |
| mysample |
| mysql |
| pandora |
| pureftpd |
| simple_blog |
| test |
| test2 |
+--------------------+
mysql> select table_schema,sum(data_length),sum(index_length),sum(data_length)+sum(index_length) as totsize
-> from information_schema.tables
-> where table_type='BASE TABLE'
-> and engine='MyISAM'
-> group by table_schema;
+--------------+------------------+-------------------+---------+
| table_schema | sum(data_length) | sum(index_length) | totsize |
+--------------+------------------+-------------------+---------+
| auth | 231 | 4096 | 4327 |
| bookmarks | 152 | 3072 | 3224 |
| books | 6094 | 10240 | 16334 |
| joomla | 105223 | 118784 | 224007 |
| joomla2 | 108588 | 118784 | 227372 |
| mail | 3545 | 4096 | 7641 |
| mysample | 37750 | 38912 | 76662 |
| mysql | 492362 | 72704 | 565066 |
| pureftpd | 88 | 3072 | 3160 |
| simple_blog | 44 | 3072 | 3116 |
| test | 152 | 2048 | 2200 |
| test2 | 476 | 7168 | 7644 |
+--------------+------------------+-------------------+---------+
有沒有發覺到上面 show databases的結果扣掉 information_schema,共有14個databses;
而我們依據 information_schema.tables 計算的資料卻只有12筆.
因為有些database建立了,但是都沒有在裡面建立資料庫物件(table/view等),所以在
information_schema.tables 裡面就沒有資料.
接下來我們看一下 information_schema.schemata
mysql> select distinct schema_name
-> from information_schema.schemata;
+--------------------+
| schema_name |
+--------------------+
| information_schema |
| auth |
| bookmarks |
| books |
| impexp_text |
| joomla |
| joomla2 |
| mail |
| mysample |
| mysql |
| pandora |
| pureftpd |
| simple_blog |
| test |
| test2 |
+--------------------+
15 rows in set (0.00 sec)
mysql> select distinct table_schema
-> from information_schema.tables;
+--------------------+
| table_schema |
+--------------------+
| information_schema |
| auth |
| bookmarks |
| books |
| joomla |
| joomla2 |
| mail |
| mysample |
| mysql |
| pureftpd |
| simple_blog |
| test |
| test2 |
+--------------------+
13 rows in set (0.03 sec)
現在應該很清楚的看到了 information_schema.schemata, information_schema.tables
與show databases, show table status, show tables的關係了.
***********************************************************************************
4. 建立控制用database及其附屬table/view
接下來我們建立一個database 來作控制之用.
login as root
mysql> create database quotadb;
mysql> use quotadb;
Database changed
CREATE TABLE quota (
dbname CHAR(64) NOT NULL PRIMARY KEY,
limitbyte BIGINT NOT NULL,
exceeded ENUM('Y','N') DEFAULT 'N' NOT NULL);
insert into quota
select distinct schema_name,20971520,'N'
from information_schema.schemata
where schema_name != 'information_schema'
and schema_name != 'mysql'
and schema_name != 'quotadb';
mysql> select * from quota;
+-------------+-----------+----------+
| dbname | limitbyte | exceeded |
+-------------+-----------+----------+
| auth | 20971520 | N |
| bookmarks | 20971520 | N |
| books | 20971520 | N |
| impexp_text | 20971520 | N |
| joomla | 20971520 | N |
| joomla2 | 20971520 | N |
| mail | 20971520 | N |
| mysample | 20971520 | N |
| pandora | 20971520 | N |
| pureftpd | 20971520 | N |
| simple_blog | 20971520 | N |
| test | 20971520 | N |
| test2 | 20971520 | N |
+-------------+-----------+----------+
13 rows in set (0.00 sec)
我們需要將mysql這個系統database排除,information_schema系統metadata也需要排除,
quotadb自己也要排除,以免限制住自己.
接下來建立一個view以方便獲取database size,並且在view中事先將mysql,quotadb排除.
create view v_dbsize as
select table_schema as dbname,sum(data_length) as data_size,sum(index_length) as index_size,sum(data_length)+sum(index_length) as totsize
from information_schema.tables
where table_schema != 'mysql'
and table_schema != 'quotadb'
and table_type='BASE TABLE'
and engine='MyISAM'
group by table_schema;
mysql> select * from v_dbsize;
+-------------+-----------+------------+---------+
| dbname | data_size | index_size | totsize |
+-------------+-----------+------------+---------+
| auth | 231 | 4096 | 4327 |
| bookmarks | 152 | 3072 | 3224 |
| books | 6094 | 10240 | 16334 |
| joomla | 105223 | 118784 | 224007 |
| joomla2 | 108588 | 118784 | 227372 |
| mail | 3545 | 4096 | 7641 |
| mysample | 37750 | 38912 | 76662 |
| pureftpd | 88 | 3072 | 3160 |
| simple_blog | 44 | 3072 | 3116 |
| test | 152 | 2048 | 2200 |
| test2 | 476 | 7168 | 7644 |
+-------------+-----------+------------+---------+
11 rows in set (0.03 sec)
ps:因為這個instance裡面有兩個database impexp_text與pandora目前都是沒有任何資料的,
所以quota table有13筆record, v_dbsize view裡面有11筆record,是正常的.
運用quota與v_dbsize作一下比較.
select q.dbname, q.limitbyte, d.totsize, q.limitbyte - d.totsize as diff, d.totsize / q.limitbyte as percent
from quota q, v_dbsize d
where q.dbname = d.dbname;
+-------------+-----------+---------+----------+---------+
| dbname | limitbyte | totsize | diff | percent |
+-------------+-----------+---------+----------+---------+
| auth | 20971520 | 4327 | 20967193 | 0.0002 |
| bookmarks | 20971520 | 3224 | 20968296 | 0.0002 |
| books | 20971520 | 16334 | 20955186 | 0.0008 |
| joomla | 20971520 | 224007 | 20747513 | 0.0107 |
| joomla2 | 20971520 | 227372 | 20744148 | 0.0108 |
| mail | 20971520 | 7641 | 20963879 | 0.0004 |
| mysample | 20971520 | 76662 | 20894858 | 0.0037 |
| pureftpd | 20971520 | 3160 | 20968360 | 0.0002 |
| simple_blog | 20971520 | 3116 | 20968404 | 0.0001 |
| test | 20971520 | 2200 | 20969320 | 0.0001 |
| test2 | 20971520 | 7644 | 20963876 | 0.0004 |
+-------------+-----------+---------+----------+---------+
2010年8月7日 星期六
MySQL database size part1
MySQL Database size limit
======================================
1. 基本的檔案大小計算
in mysample
# pwd
/var/lib/mysql/mysample
# ls -l *.MYD | awk '{sum += $5} END {print sum}'
37750
# ls -l *.MYI | awk '{sum += $5} END {print sum}'
38912
所以我們計算得到76662 bytes.
注意:這個數字不包含 *.frm (Table Cache), *.TRN (Trigger), *.TRG (Function)等各式檔案.
僅是MyISAM的 data/index 的檔案.
然後我們寫了以下的php 程式:
----------------------------------------------------------------
#!/usr/bin/php -q
mysql_connect("localhost","mysample","mysample");
$result = mysql_query("SHOW TABLE STATUS FROM mysample;");
$sum = 0;
while($array = mysql_fetch_array($result)) {
$total = $array['Data_length'] + $array['Index_length'];
$sum += $total;
print "--------------------------\n";
printf("Table:%s\n", $array['Name']);
printf("Data Size:%d\n", $array['Data_length']);
printf("Index Size:%d\n", $array['Index_length']);
printf("Total Size:%d\n", $total);
printf("Total Rows:%d\n", $array['Rows']);
printf("Engine:%s\n", $array['Engine']);
printf("Avg Size Per Row:%d\n", $array['Avg_row_length']);
print "--------------------------\n";
}
print "*********************\n";
printf("The Sum of all Tables:%d bytes\n", $sum);
printf("The Size of database in K bytes:%f\n", $sum/1024);
?>
----------------------------------------------------------------
計算結果
The Sum of all Tables:93046 bytes
The Size of database in K nytes:90.865234
因為在這個mysample還有table是Engine:InnoDB,因為InnoDB是Table Space的方式,與MyISAM分別用檔案的方式不同.
我們修改一下上面的程式.只計算Engine:MyISAM的Table與Index的總和.
-------------------------------------------------------------------
#!/usr/bin/php -q
# MySQL database size summary version 2
# Only count MyISAM tables
mysql_connect("localhost","mysample","mysample");
$result = mysql_query("SHOW TABLE STATUS FROM mysample WHERE Engine='MyISAM';");
$sum = 0;
while($array = mysql_fetch_array($result)) {
$total = $array['Data_length'] + $array['Index_length'];
$sum += $total;
print "--------------------------\n";
printf("Table:%s\n", $array['Name']);
printf("Data Size:%d\n", $array['Data_length']);
printf("Index Size:%d\n", $array['Index_length']);
printf("Total Size:%d\n", $total);
printf("Total Rows:%d\n", $array['Rows']);
printf("Engine:%s\n", $array['Engine']);
printf("Avg Size Per Row:%d\n", $array['Avg_row_length']);
print "--------------------------\n";
}
print "*********************\n";
printf("The Sum of all Tables:%d bytes\n", $sum);
printf("The Size of database in K bytes:%f\n", $sum/1024);
?>
------------------------------------------------------------------
計算結果
The Sum of all Tables:76662 bytes
The Size of database in K bytes:74.865234
與上面計算檔案的方式結果相同.
小結:因為InnoDB的方式不同,我們就針對MyISAM的data file,index file計算.可以使用
SHOW TABLE STATUS FROM mysample WHERE Engine='MyISAM' 的語法.
*************************************************************************************
2. 使用function來計算
剛才我們是使用mysql client裡面的 show table status 來計算,但是要使用function的話,要用
cursor,不能使用show table status.但是不用擔心,在 information_schema.tables 裡面有需要的資訊.
將mysample各個MyISAM的Table列出table_name,data_length,index_length
select table_name,data_length,index_length
from information_schema.tables
where table_schema = 'mysample'
and table_type = 'BASE TABLE'
and engine = 'MyISAM';
計算總和:
select sum(data_length)+sum(index_length) as totsize
from information_schema.tables
where table_schema = 'mysample'
and table_type = 'BASE TABLE'
and engine = 'MyISAM';
執行結果:
+---------+
| totsize |
+---------+
| 76662 |
+---------+
跟上面的資料相符.
接下來就寫一個 dbszie function.
----------------------------------------------------------
DELIMITER $$
DROP FUNCTION IF EXISTS `mysample`.`dbsize`$$
CREATE FUNCTION `mysample`.`dbsize` (in_dbname varchar(64)) RETURNS BIGINT UNSIGNED
BEGIN
DECLARE rtnSize BIGINT UNSIGNED;
DECLARE c CURSOR FOR select sum(data_length)+sum(index_length) as totsize
from information_schema.tables
where table_schema = in_dbname
and table_type = 'BASE TABLE'
and engine = 'MyISAM';
open c;
fetch c into rtnSize;
close c;
RETURN rtnSize;
END$$
DELIMITER ;
-----------------------------------------------------------
這個測試用的user: mysample,除了對database:mysample有權限外,對database:test也有權限.
我們測試一下
mysql> select dbsize('mysample');
+--------------------+
| dbsize('mysample') |
+--------------------+
| 76662 |
+--------------------+
1 row in set (0.07 sec)
mysql> select dbsize('test');
+----------------+
| dbsize('test') |
+----------------+
| 2200 |
+----------------+
1 row in set (0.00 sec)
可以看到可以依照輸入的database name計算出使用的size.
======================================
1. 基本的檔案大小計算
in mysample
# pwd
/var/lib/mysql/mysample
# ls -l *.MYD | awk '{sum += $5} END {print sum}'
37750
# ls -l *.MYI | awk '{sum += $5} END {print sum}'
38912
所以我們計算得到76662 bytes.
注意:這個數字不包含 *.frm (Table Cache), *.TRN (Trigger), *.TRG (Function)等各式檔案.
僅是MyISAM的 data/index 的檔案.
然後我們寫了以下的php 程式:
----------------------------------------------------------------
#!/usr/bin/php -q
mysql_connect("localhost","mysample","mysample");
$result = mysql_query("SHOW TABLE STATUS FROM mysample;");
$sum = 0;
while($array = mysql_fetch_array($result)) {
$total = $array['Data_length'] + $array['Index_length'];
$sum += $total;
print "--------------------------\n";
printf("Table:%s\n", $array['Name']);
printf("Data Size:%d\n", $array['Data_length']);
printf("Index Size:%d\n", $array['Index_length']);
printf("Total Size:%d\n", $total);
printf("Total Rows:%d\n", $array['Rows']);
printf("Engine:%s\n", $array['Engine']);
printf("Avg Size Per Row:%d\n", $array['Avg_row_length']);
print "--------------------------\n";
}
print "*********************\n";
printf("The Sum of all Tables:%d bytes\n", $sum);
printf("The Size of database in K bytes:%f\n", $sum/1024);
?>
----------------------------------------------------------------
計算結果
The Sum of all Tables:93046 bytes
The Size of database in K nytes:90.865234
因為在這個mysample還有table是Engine:InnoDB,因為InnoDB是Table Space的方式,與MyISAM分別用檔案的方式不同.
我們修改一下上面的程式.只計算Engine:MyISAM的Table與Index的總和.
-------------------------------------------------------------------
#!/usr/bin/php -q
# MySQL database size summary version 2
# Only count MyISAM tables
mysql_connect("localhost","mysample","mysample");
$result = mysql_query("SHOW TABLE STATUS FROM mysample WHERE Engine='MyISAM';");
$sum = 0;
while($array = mysql_fetch_array($result)) {
$total = $array['Data_length'] + $array['Index_length'];
$sum += $total;
print "--------------------------\n";
printf("Table:%s\n", $array['Name']);
printf("Data Size:%d\n", $array['Data_length']);
printf("Index Size:%d\n", $array['Index_length']);
printf("Total Size:%d\n", $total);
printf("Total Rows:%d\n", $array['Rows']);
printf("Engine:%s\n", $array['Engine']);
printf("Avg Size Per Row:%d\n", $array['Avg_row_length']);
print "--------------------------\n";
}
print "*********************\n";
printf("The Sum of all Tables:%d bytes\n", $sum);
printf("The Size of database in K bytes:%f\n", $sum/1024);
?>
------------------------------------------------------------------
計算結果
The Sum of all Tables:76662 bytes
The Size of database in K bytes:74.865234
與上面計算檔案的方式結果相同.
小結:因為InnoDB的方式不同,我們就針對MyISAM的data file,index file計算.可以使用
SHOW TABLE STATUS FROM mysample WHERE Engine='MyISAM' 的語法.
*************************************************************************************
2. 使用function來計算
剛才我們是使用mysql client裡面的 show table status 來計算,但是要使用function的話,要用
cursor,不能使用show table status.但是不用擔心,在 information_schema.tables 裡面有需要的資訊.
將mysample各個MyISAM的Table列出table_name,data_length,index_length
select table_name,data_length,index_length
from information_schema.tables
where table_schema = 'mysample'
and table_type = 'BASE TABLE'
and engine = 'MyISAM';
計算總和:
select sum(data_length)+sum(index_length) as totsize
from information_schema.tables
where table_schema = 'mysample'
and table_type = 'BASE TABLE'
and engine = 'MyISAM';
執行結果:
+---------+
| totsize |
+---------+
| 76662 |
+---------+
跟上面的資料相符.
接下來就寫一個 dbszie function.
----------------------------------------------------------
DELIMITER $$
DROP FUNCTION IF EXISTS `mysample`.`dbsize`$$
CREATE FUNCTION `mysample`.`dbsize` (in_dbname varchar(64)) RETURNS BIGINT UNSIGNED
BEGIN
DECLARE rtnSize BIGINT UNSIGNED;
DECLARE c CURSOR FOR select sum(data_length)+sum(index_length) as totsize
from information_schema.tables
where table_schema = in_dbname
and table_type = 'BASE TABLE'
and engine = 'MyISAM';
open c;
fetch c into rtnSize;
close c;
RETURN rtnSize;
END$$
DELIMITER ;
-----------------------------------------------------------
這個測試用的user: mysample,除了對database:mysample有權限外,對database:test也有權限.
我們測試一下
mysql> select dbsize('mysample');
+--------------------+
| dbsize('mysample') |
+--------------------+
| 76662 |
+--------------------+
1 row in set (0.07 sec)
mysql> select dbsize('test');
+----------------+
| dbsize('test') |
+----------------+
| 2200 |
+----------------+
1 row in set (0.00 sec)
可以看到可以依照輸入的database name計算出使用的size.
2010年6月30日 星期三
民國年與西洋年轉換之MySQL Function
民國年與西洋年轉換之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
declare str_len int;
declare cut_len int;
declare old_year int;
declare new_year int;
declare new_date_str char(10);
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_format()
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)
轉換成功!
=====================================
先開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
declare str_len int;
declare cut_len int;
declare old_year int;
declare new_year int;
declare new_date_str char(10);
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_format()
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)
轉換成功!
訂閱：
文章 (Atom)