中年人之碎碎念

MySQL的資料分組統計

2011-02-21T04:45:00.001-08:00

在ITPub看到有人問MySQL的資料分組統計.
http://www.itpub.net/thread-1390472-1-2.html
表数据分组统计，SQL的写法请教？

有表格式如下：
ID        NO        SEL1        SEL2        SEL3
1        001        A        A        A
2        001        A        B        C
3        001        B        B        C
4        001        C        B        C
5        001        B        B        C
6        001        B        C        C
7        002        A        A        B
8        002        B        A        B
9        002        C        A        B
10        002        C        B        B
11        002        C        B        B
12        002        C        B        C

想统计出如下结果：
1、统计每个NO号（如001）的SEL1，SEL2，SEL3中各自包含A的个数，B的个数，C的个数；
2、统计每个NO号（如001）的SEL1，SEL2，SEL3中A、B、C分别占总数的比例。

我的解法:

CREATE TABLE x0221 (
id INT AUTO_INCREMENT PRIMARY KEY,
no CHAR(3) NOT NULL,
sel1 CHAR(1) NOT NULL,
sel2 CHAR(1) NOT NULL,
sel3 CHAR(1) NOT NULL);

INSERT INTO x0221(no, sel1, sel2, sel3) VALUES
("001", "A", "A", "A"),
("001", "A", "B", "C"),
("001", "B", "B", "C"),
("001", "C", "B", "C"),
("001", "B", "B", "C"),
("001", "B", "C", "C"),
("002", "A", "A", "B"),
("002", "B", "A", "B"),
("002", "C", "A", "B"),
("002", "C", "B", "B"),
("002", "C", "B", "B"),
("002", "C", "B", "C");

先做sel的ABC總數計算:
SELECT no,SUM(CASE WHEN sel1="A" THEN 1 ELSE 0 END) as sel1a,
           SUM(CASE WHEN sel1="B" THEN 1 ELSE 0 END) as sel1b,
           SUM(CASE WHEN sel1="C" THEN 1 ELSE 0 END) as sel1c
FROM x0221
GROUP BY no;
+-----+-------+-------+-------+
| no | sel1a | sel1b | sel1c |
+-----+-------+-------+-------+
| 001 |     2 |     3 |     1 |
| 002 |     1 |     1 |     4 |
+-----+-------+-------+-------+
這是很標準的作法,利用CASE
那要算比例呢?大家都會用COUNT(1)來算總數,但是要把它用一道SQL呢?
其實我們可以用
SELECT no, sel1a, sel1a / b.cnt AS percent_sel1a,
            sel1b, sel1b / b.cnt AS percent_sel1b,
            sel1c, sel1c / b.cnt AS percent_sel1c
FROM (SELECT no,SUM(CASE WHEN sel1="A" THEN 1 ELSE 0 END) as sel1a,
           SUM(CASE WHEN sel1="B" THEN 1 ELSE 0 END) as sel1b,
           SUM(CASE WHEN sel1="C" THEN 1 ELSE 0 END) as sel1c
       FROM x0221
       GROUP BY no) a CROSS JOIN
      (SELECT COUNT(1) as cnt FROM x0221) b
GROUP BY no
ORDER BY no;
+-----+-------+---------------+-------+---------------+-------+---------------+
| no | sel1a | percent_sel1a | sel1b | percent_sel1b | sel1c | percent_sel1c |
+-----+-------+---------------+-------+---------------+-------+---------------+
| 001 |     2 |        0.1667 |     3 |        0.2500 |     1 |        0.0833 |
| 002 |     1 |        0.0833 |     1 |        0.0833 |     4 |        0.3333 |
+-----+-------+---------------+-------+---------------+-------+---------------+
善用CROSS JOIN,讓總數的值都可以在每一個結果裡面引用.
sel2,sel3就不用了,因為都一樣,只是佔篇幅而已.

MySQL CASE的應用 part2

2011-02-17T01:56:00.000-08:00

還是藍色小鋪的同樣的問題.
http://www.blueshop.com.tw/board/FUM20041006152746MYF/BRD20110215141046DIE.html
---------------------------------------
先建立Table跟測試資料
USE test;

CREATE TABLE IF NOT EXISTS xhome (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(10),
sname VARCHAR(20),
contact VARCHAR(20)
);

CREATE TABLE IF NOT EXISTS xpatient (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
meter_id INT,
name VARCHAR(10),
gender ENUM('M','F'),
age INT,
home INT NOT NULL
);

CREATE TABLE IF NOT EXISTS xrecords (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
uid INT NOT NULL,
datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
value INT NOT NULL);

CREATE TABLE IF NOT EXISTS xflag (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
rid INT NOT NULL,
flag INT);

INSERT INTO xhome VALUES
(1, "home1", "home1s", "home1contact");

INSERT INTO xpatient VALUES
(1,1,"John", "M", 30, 1),
(2,2,"Mary", "F", 28, 1);

INSERT INTO xrecords VALUES
(NULL, 1, NULL, 133),
(NULL, 1, NULL, 134),
(NULL, 1, NULL, 123),
(NULL, 2, NULL, 135),
(NULL, 2, NULL, 136);
-------------------------------------
然後先用這道SQL Command 模擬原本3個 Tables的狀況
SELECT r.*, p.name, h.name AS home
FROM xhome h INNER JOIN xpatient p
ON p.home = h.id
INNER JOIN xrecords r
ON p.id = r.uid
ORDER BY r.datetime DESC;

+----+-----+---------------------+-------+------+-------+
| id | uid | datetime            | value | name | home |
+----+-----+---------------------+-------+------+-------+
| 1 |   1 | 2011-02-17 15:51:38 |   133 | John | home1 |
| 2 |   1 | 2011-02-17 15:51:38 |   134 | John | home1 |
| 3 |   1 | 2011-02-17 15:51:38 |   123 | John | home1 |
| 4 |   2 | 2011-02-17 15:51:38 |   135 | Mary | home1 |
| 5 |   2 | 2011-02-17 15:51:38 |   136 | Mary | home1 |
+----+-----+---------------------+-------+------+-------+
接下來輸入 xflag 的測試資料,
INSERT INTO xflag VALUES
(NULL, 1, 22),
(NULL, 2, 27),
(NULL, 4, 38);

再用這道SQL
SELECT r.*, p.name, h.name AS home,(CASE WHEN f.flag THEN f.flag ELSE 0 END) AS flag
FROM xhome h INNER JOIN xpatient p
ON p.home = h.id
INNER JOIN xrecords r
ON p.id = r.uid
LEFT JOIN xflag f
ON r.id = f.rid
ORDER BY r.datetime DESC;

+----+-----+---------------------+-------+------+-------+------+
| id | uid | datetime            | value | name | home | flag |
+----+-----+---------------------+-------+------+-------+------+
| 5 |   2 | 2011-02-17 15:51:38 |   136 | Mary | home1 |    0 |
| 1 |   1 | 2011-02-17 15:51:38 |   133 | John | home1 |   22 |
| 2 |   1 | 2011-02-17 15:51:38 |   134 | John | home1 |   27 |
| 3 |   1 | 2011-02-17 15:51:38 |   123 | John | home1 |    0 |
| 4 |   2 | 2011-02-17 15:51:38 |   135 | Mary | home1 |   38 |
+----+-----+---------------------+-------+------+-------+------+

MySQL CASE的應用

2011-02-14T23:32:00.000-08:00

今天有人在藍色小舖問了一個問題:
http://www.blueshop.com.tw/board/FUM20041006152746MYF/BRD20110215141046DIE.html
-----------------
這是原本的Record Table
id uid datetime value
1 9 2009-08-31 18:15:29 133

我想在Record Table 加一欄,叫FLAG來記載標記標籤,最快捷的方法是在Record Table 直接加上去,但是因為之前PROGRAM問題,而這Windows PROGRAM己經在使用中,我並不能改動Record TABLE.
我就設計另一個TABLE來儲FLAG的內容,叫FLAG TABLE, 內容以下
id pid rid flag
1 34 400 1

因為不是每一個在RECORD的紀錄都有FLAG的,所以我想問QUERY是怎麼寫來連合這兩個TABLE,顯示以下格式?
id uid datetime value flag
1 9 2009-08-31 18:15:29 133 0

如果FLAG TABLE沒有紀錄,就變成0

我的表達不好,大家有不明白的地方,請告訴我,謝謝..
-------------------
我回答如下:
先建立測試用的Table與資料:

CREATE TABLE blue0215 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
uid INT NOT NULL,
datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
value INT NOT NULL);

CREATE TABLE blue0215flag (
id INT NOT NULL,
pid INT NOT NULL,
rid INT NOT NULL,
flag INT NOT NULL);

INSERT INTO blue0215 VALUES
(1,9,NULL,133),
(2,10,NULL,134),
(3,11,NULL,135),
(4,12,NULL,136);

INSERT INTO blue0215flag VALUES
(1,34,400,1),
(2,35,401,2);

使用如下SQL Command:
SELECT o.id,o.uid,o.datetime,o.value, (CASE WHEN f.flag THEN f.flag ELSE 0 END) AS flag
FROM blue0215 o LEFT JOIN blue0215flag f
ON o.id = f.id;

Result:
+----+-----+---------------------+-------+------+
| id | uid | datetime            | value | flag |
+----+-----+---------------------+-------+------+
| 1 |   9 | 2011-02-15 14:45:17 |   133 |    1 |
| 2 | 10 | 2011-02-15 14:45:17 |   134 |    2 |
| 3 | 11 | 2011-02-15 14:45:17 |   135 |    0 |
| 4 | 12 | 2011-02-15 14:45:17 |   136 |    0 |
+----+-----+---------------------+-------+------+

當blue0215flag Table有值時,flag 就取出,若沒有則show出 0

使用以下SQL Command產生一個view,會更方便使用.

CREATE VIEW vblue0215 AS
SELECT o.id,o.uid,o.datetime,o.value, (CASE WHEN f.flag THEN f.flag ELSE 0 END) AS flag
FROM blue0215 o LEFT JOIN blue0215flag f
ON o.id = f.id;

直接 SELECT * FROM vblue0215;
就可以得到你需要的了.

最近Pidgin 登入MSN時 SSL憑證錯誤的解決方法

2010-11-18T20:37:00.001-08:00

最近用Pidgin登入MSN會出現SSL憑證問題.
解決方法:
當然要先把Pidgin關掉.
download 新的授權憑證檔

here ===> http://files.andreineculau.com/projects/pidgin/omega.contacts.msn.com.txt

將 .txt 的副檔名拿掉, 存成 omega.contacts.msn.com 即可.
然後蓋掉原本在 ~/.purple/certificates/x509/tls_peers 目錄下的 omega.contacts.msn.com 檔案

然後再啟動Pidgin

有意思的sql問題

2010-10-06T19:24:00.000-07:00

在藍色小舖看到有人問一個sql問題.
http://www.blueshop.com.tw/board/FUM20041006152746MYF/BRD201010051423497Z5.html
我已經回答了.

use test;

CREATE TABLE `blue1` (
`data1` smallint(6) NOT NULL,
PRIMARY KEY (`data1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

CREATE TABLE `blue2` (
`q1` smallint(6) NOT NULL,
`q2` smallint(6) NOT NULL,
`q3` smallint(6) NOT NULL,
KEY `fk1` (`q1`),
KEY `fk2` (`q2`),
KEY `fk3` (`q3`),
CONSTRAINT `fk1` FOREIGN KEY (`q1`) REFERENCES `blue1` (`data1`),
CONSTRAINT `fk2` FOREIGN KEY (`q2`) REFERENCES `blue1` (`data1`),
CONSTRAINT `fk3` FOREIGN KEY (`q3`) REFERENCES `blue1` (`data1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

insert into blue1(data1) values (1),(2),(3),(4),(5);

insert into blue2 values
(1,3,4),
(2,1,5),
(3,4,1);

root@[test]>select count(q1) into @total from blue2;
Query OK, 1 row affected (0.00 sec)

root@[test]>select @total;
+--------+
| @total |
+--------+
| 3 |
+--------+

root@[test]>select q1, count(q1)/@total
-> from blue2
-> group by q1
-> union
-> select data1, 0/@total
-> from blue1 where data1 not in (select q1 from blue2);
+----+------------------+
| q1 | count(q1)/@total |
+----+------------------+
| 1 | 0.3333 |
| 2 | 0.3333 |
| 3 | 0.3333 |
| 4 | 0.0000 |
| 5 | 0.0000 |
+----+------------------+

root@[test]>select q2, count(q2)/@total
-> from blue2
-> group by q2
-> union
-> select data1, 0/@total
-> from blue1 where data1 not in (select q2 from blue2);
+----+------------------+
| q2 | count(q2)/@total |
+----+------------------+
| 1 | 0.3333 |
| 3 | 0.3333 |
| 4 | 0.3333 |
| 2 | 0.0000 |
| 5 | 0.0000 |
+----+------------------+

root@[test]>select q3, count(q3)/@total
-> from blue2
-> group by q3
-> union
-> select data1, 0/@total
-> from blue1 where data1 not in (select q3 from blue2);
+----+------------------+
| q3 | count(q3)/@total |
+----+------------------+
| 1 | 0.3333 |
| 4 | 0.3333 |
| 5 | 0.3333 |
| 2 | 0.0000 |
| 3 | 0.0000 |
+----+------------------+

subquery 部份 select q3 from blue2 加上 distinct執行速度會比較快.
當資料量大的時候最好加上,現在三筆資料,就不加了.

2010-09-16T22:48:00.000-07:00

Powerful tool for MySQL stored procedure developer.
我剛試用.
網址在:
http://www.mydebugger.com/index.php

MySQL Database Quota Control

2010-09-01T22:31:00.000-07:00

之前在酷學園有人問到,所以後來就開發了這功能.
詳細的資料整理成pdf了.
http://www.filesonic.com/file/17520903/mysqlquotabunko.rar

MySQL 讀取及輸出文字檔

2010-09-01T16:32:00.000-07:00

在酷學園看到有人問到排序的問題,有兩欄位.
http://phorum.study-area.org/index.php/topic,62396.msg315492.html#new

推測是讀取儀表獲得的數據,要進行後續處理.若這類的需求只是偶爾,其實用試算表就很方便.但若是常態性需求,用程式是比較方便,排序/分組這些是資料庫的強項.
問題在於處理文字檔的方法,一般人較少知道.思考時就被侷限了,另外MySQL的文件
或是很多網站上的例子,輸入檔及輸出檔的檔名都是手動輸入固定的,顯得就是手工
作業. MySQL 的LOAD DATA指令不能在Stored Procedure,也不能prepare,彈性
受到限制,但是我們可以利用Shell Script.以下就是連grant privilege都有說明的範例,我也一併發到酷學園供發問者參考.

MySQL 讀取及輸出文字檔
===============================
1. 基本環境建立與初始測試

1.1 用 roo登入 mysql,建立作業用的資料庫與使用者,並賦予相關權限.

root@[(none)]>create database sorter;

root@[(none)]>grant all on sorter.* to 'sorter'@'%' identified by 'sorter';

root@[(none)]>grant file on *.* to 'sorter'@'%';

1.2 在OS中建立目錄供MySQL存取,並建立預備輸入的文字檔

ps. 目錄必須設定讓mysql有權限讀寫,不一定是讓mysql當owner

# cd /
# mkdir myimpexp
# chown mysql:mysql myimpexp

# cd myimpexp
# vim raw-2010-09-02
# cat raw-2010-09-02
10.2    25.2
10.2    50.4
10.2    100.8
20.4    25.2
20.4    50.4
20.4    100.8

ps. txt file裡面用tab隔開

1.3 用sorter登入並建立table

sorter@[sorter]>create table sortforge (
col1 decimal(8,2),
col2 decimal(8,2)
);

1.4 輸入文字檔

sorter@[sorter]>LOAD DATA INFILE '/myimpexp/raw-2010-09-02' INTO TABLE sortforge
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

sorter@[sorter]>select * from sortforge;
+-------+--------+
| col1 | col2   |
+-------+--------+
| 10.20 | 25.20 |
| 10.20 | 50.40 |
| 10.20 | 100.80 |
| 20.40 | 25.20 |
| 20.40 | 50.40 |
| 20.40 | 100.80 |
+-------+--------+

sorter@[sorter]>select *
    -> from sortforge
    -> order by col2,col1 desc;
+-------+--------+
| col1 | col2   |
+-------+--------+
| 20.40 | 25.20 |
| 10.20 | 25.20 |
| 20.40 | 50.40 |
| 10.20 | 50.40 |
| 20.40 | 100.80 |
| 10.20 | 100.80 |
+-------+--------+

1.5 排序後輸出為文字檔

sorter@[sorter]>SET @OutfileCmd := concat("SELECT col1,col2 FROM sortforge ORDER BY col2,col1 DESC INTO OUTFILE '/myimpexp/sort-", DATE_FORMAT(now(),'%Y-%m-%d_%H%i%s'), ".txt' FIELDS TERMINATED BY '\t' ESCAPED BY '\"' LINES TERMINATED BY '\n';");
Query OK, 0 rows affected (0.00 sec)

sorter@[sorter]>PREPARE statement FROM @OutfileCmd;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

sorter@[sorter]>EXECUTE statement;
Query OK, 6 rows affected (0.00 sec)

# cat sort-2010-09-02_063030.txt
20.40   25.20
10.20   25.20
20.40   50.40
10.20   50.40
20.40   100.80
10.20   100.80

2. 使用Shell Script 呼叫處理
將上面的操作方式改用Shell Script並配合使用date.如欲排序之原始檔為儀器等輸出,
可以將檔案改名為raw-yyyy-mm-dd格式,並可將Shell Script配合cron進行自動化處理.

程式碼如下:
-------- script start ----------
#!/bin/bash
# ---- Basic info set up ----
MYSQL_PATH="/opt/mysql554/bin/mysql"
MYSQL_USER="sorter"
MYSQL_PASS="sorter"
MYSQL_DB="sorter"
# ---- Chnage the basic info for your environment

# Query MySQL database function
query() {
echo "$1" | ${MYSQL_PATH} -s --user=${MYSQL_USER} --password=${MYSQL_PASS} ${MYSQL_DB}
}

today=$( date +%F )
in_file="'/myimpexp/raw-$today'"

moment=$( date +%F_%H%M%S )
out_file="'/myimpexp/sort-$moment'"

sql="truncate sortforge; LOAD DATA INFILE $in_file INTO TABLE sortforge FIELDS TERMINATED BY '\t' ESCAPED BY '\"' LINES TERMINATED BY '\n'; SELECT col1,col2 FROM sortforge ORDER BY col2,col1 DESC INTO OUTFILE $out_file FIELDS TERMINATED BY '\t' ESCAPED BY '\"' LINES TERMINATED BY '\n';"

RESULT=$(query "${sql}")

----------script end ----------------

# ./sh1.sh
# cat sort-2010-09-02_064313
20.40   25.20
10.20   25.20
20.40   50.40
10.20   50.40
20.40   100.80
10.20   100.80

得到排序後的輸出

MySQL database size part2

2010-08-07T01:27:00.000-07:00

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 |
+-------------+-----------+---------+----------+---------+

MySQL database size part1

2010-08-07T01:26:00.000-07:00

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.

民國年與西洋年轉換之MySQL Function的運用

2010-07-25T01:23:00.000-07:00

民國年與西洋年轉換之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方式
在使用Trigger以前,要先改權限.
在MySQL 5.1.6之前要使用trigger,必須要有 Super Privilege.

Super Privilege is global privilege.

mysql> GRANT SUPER ON *.* TO 'mysample'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user, Super_priv from user where Super_priv='Y';
+-----------------+----------+------------+
| host            | user     | Super_priv |
+-----------------+----------+------------+
| localhost       | root     | Y          |
| 127.0.0.1       | root     | Y          |
| %               | mysample | Y          |
+-----------------+----------+------------+

   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年,或是發展系統需要存取民國年格式,提供了以上兩種方法.
可以視狀況靈活使用.

也發表在酷學園討論區.

Taiwan Weather Location Code

2010-07-07T09:01:00.001-07:00

Taiwan Weather Location Code:
--------------------------------
TWXX0001 (Chang-hua) 彰化
TWXX0002 (Chia-i) 嘉義
TWXX0003 (Chi-lung) 基隆
TWXX0004 (Chingmei) 景美
TWXX0005 (Ch'i-shan) 旗山
TWXX0006 (Chu-tung) 竹東
TWXX0007 (Feng-yuan) 豐原
TWXX0008 (Hengch'un) 恆春
TWXX0009 (Hsin-chu) 新竹
TWXX0010 (Hsin-tien) 新店
TWXX0011 (Hua-lien) 花蓮
TWXX0012 (Kangshan) 岡山
TWXX0013 (Kao-hsiung) 高雄
TWXX0014 (Miao-li) 苗栗
TWXX0015 (P'ing-tung) 屏東
TWXX0016 (Su-ao) 蘇澳
TWXX0017 (Ta-cho-shui) 南澳 (大濁水)
TWXX0018 (Ta-fan-lieh) 南灣 (大阪埒)
TWXX0019 (T'ai-chung) 台中
TWXX0020 (T'ai-nan) 台南
TWXX0021 (Taipei) 台北
TWXX0022 (T'aipeihsien) 台北縣
TWXX0023 (T'aitung) 台東
TWXX0024 (Tan-shui) 淡水
TWXX0025 (T'ao-yuan) 桃園
TWXX0026 (Ta-wu) 大武
TWXX0027 (Hengchun) 恆春
TWXX0028 (Taidong) 台東

Linux安裝新字型

2010-07-07T07:17:00.001-07:00

otf字型轉ttf字型
使用fontforge就能將otf轉ttf. openoffice 還是以ttf 字型為主.
所以還是轉成ttf字型較方便.

fontforge 要寫一個script比較好轉.
$ cat otf2ttf.sh
-------------------------------------
#!/usr/bin/fontforge
# Quick and dirty hack: converts a font to truetype (.ttf)
Print("Opening "+$1);
Open($1);
Print("Saving "+$1:r+".ttf");
Generate($1:r+".ttf");
Quit(0);
-----------------------
單檔轉換:
fontforge -script otf2ttf.sh FONTNAME.otf
多檔轉換:
for i in *.otf; do fontforge -script otf2ttf.sh $i; done

===================================

安裝新字型
[root@nana ~]# cd /usr/share/fonts
[root@nana fonts]# mkdir radiospace
[root@nana fonts]# cd radiospace/
[root@nana radiospace]# mv /home/vincent/ttf_font/*.ttf .
[root@nana radiospace]# ll
總計 204
-rw-rw-rw- 1 vincent vincent 29532 9月 2 2002 Radiofbi.ttf
-rw-rw-rw- 1 vincent vincent 33428 9月 2 2002 Radiofb.ttf
-rw-rw-rw- 1 vincent vincent 34528 9月 2 2002 Radiofc.ttf
-rw-rw-rw- 1 vincent vincent 29808 9月 2 2002 Radiofi.ttf
-rw-rw-rw- 1 vincent vincent 45632 9月 2 2002 Radiof.ttf
[root@nana radiospace]# pwd
/usr/share/fonts/radiospace
[root@nana radiospace]# fc-cache -v /usr/share/fonts/radiospace
/usr/share/fonts/radiospace: caching, 5 fonts, 0 dirs
/var/cache/fontconfig: cleaning cache directory
/var/cache/fontconfig: 3830d5c3ddfd5cd38a049b759396e72e-x86.cache-2: cache outdated: /usr/share/fonts
/root/.fontconfig: not cleaning unwritable cache directory
fc-cache: succeeded
安裝完成,列出字型
[root@nana radiospace]# fc-list

紀錄Console 操作過程的相關命令, script 跟 tee. Script - record your command line input and output

2010-07-06T00:34:00.000-07:00

紀錄Console 操作的相關命令, script 跟 tee.
對系統管理員來說,能把操作的過程保留下來,不管是工作紀錄或是學習來說都是很方便的.
script 使用很簡單, 直接打script.然後系統會出現:

Script started, file is typescript

告訴我們存檔名稱叫 typescript
接下來就繼續操作..... 要結束時輸入exit, 或是ctrl-d,然後系統會出現:

Script done, file is typescript

我們去查看 typescript, 前面還有紀錄起始時間,後面有紀錄結束時間.

當然我們也可以使用 script -a logfile

的方式來指定紀錄檔,這在使用幾個terminal時很有用.

----------------------------------------------------------

 tee 命令是同時將 stdout 的輸出轉到我們指定的file, 這在做make時很有用.或是其他指令都可.

make 2>&1 | tee make.log

這樣同時將 stdout , stderr 都轉向重導至 make.log

----------------------------------------------------------

mysql client 也有 --tee=file

這樣可以把操作過程都紀錄的功能.當然他是很忠實的把過程紀錄,不會幫我們添加起始時間.

不過我們只要先來個  select now(); 這樣就把時間紀錄下來了.

民國年與西洋年轉換之MySQL Function

2010-06-30T01:43:00.001-07:00

民國年與西洋年轉換之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)

轉換成功!

FCron : Vixie Cron的替代品

2010-06-18T01:54:00.000-07:00

現在CentOS系統上的cron,是vixie cron, Paul Vixie寫的.有一些其他的cron程式可以提供
更多功能,例如fcron
http://fcron.free.fr/
也可以精確到秒.或是視系統loading調整.
另外有前端GUI,協助我們使用,fcronq
http://code.google.com/p/fcronq/
http://sourceforge.net/projects/fcronq/
是用python開發的

cron with date tip

2010-06-18T01:29:00.000-07:00

If need to run a job on the 3rd Wednesday of every month, but cron won’t let me do this. Run every day in the 3rd week, but then check that the day is a Wednesday. e.g.

00 02 15-21 * * if [ `date +\%u` -eq 3 ] ; then run_myjob; fi

Note that the \ is used to escape the %, otherwise cron treats it as a newline.

start-stop-daemon on CentOS

2010-06-17T22:18:00.000-07:00

有些package會使用start-stop-daemon 來啟動/停止 daemon,例如Secrond就是.
但是這是Debian/Ubuntu 的,在RHEL/Fedora/CentOS裡沒有,這樣就會發生錯誤,
造成無法正常控制Daemon. 我們可以修改/etc/init.d/daemon-script
但是這樣以後每個使用此機制的都要修改,也是麻煩.那我們要生一個這個指令出來,
有人是找一台Debian copy過來.其實在我們的CentOS裡面已經有source code了.
先檢查一下sysvinit有沒有安裝,一般都是有的啦.然後在 /usr/share/doc/SysVinit-2.86
裡面有 start-stop-daemon.c
這樣就可以gcc start-stop-daemon.c -o start-stop-daemon
然後 cp start-stop-daemon /usr/sbin
這世界沒那麼美好..... compile時會發生錯誤 (>"<)
我已經修正好程式了, 按我download
用這個compile 就可以用了,因為我沒有做什麼了不起的修改,所以都沒有在source code裡附加上名字,都是原作者的名字.

可以精確到秒的cron daemon : Secrond , cron handling tasks in intervals of seconds

2010-06-17T20:07:00.000-07:00

cron是系統管理的好幫手,但是只能設定到分鐘.網路上有些是用shell script / sleep
方法來設法達到秒,但是sleep會有誤差的.
有人就開發了可以精確到秒的cron wrap daemon.
Secrond:
https://launchpad.net/secrond

目前是0.41版.
安裝以後會有說明:
----------------------------
Installing init script at /etc/init.d/secrond ...
Creating settings directory at /etc/secrond ...
Installing schedule file at /etc/secrond/schedule ...
EDIT schedule in: /etc/secrond/schedule
START secrond with: /etc/init.d/secrond start
RELOAD schedule with: /etc/init.d/secrond reload
-----------------------------
這樣我們就知道怎樣設定跟啟動daemon.

接下來就寫個 shell script來測試一下:
script name: testsecron.sh
內容很簡單,就是touch一個file, file name format要有時間戳.
如下:
======================
#!/bin/bash
touch /home/vincent/myshell/test_`date +%Y:%m:%d-%H:%M:%S`
======================
然後到/etc/secrond/
vim schedule
======================
# SECOND(0-59) MINUTE(0-59) HOUR(0-23) DAY(1-31) COMMAND
10 * * * /home/vincent/myshell/testsecron.sh
======================
檔案第一行就有format的說明,我們就設定精確到逢每分鐘的10秒就執行剛才的script.
接下來當然要把daemon啟動.
然後去喝杯咖啡,抽根煙.再來檢查執行結果:
-rw-r--r-- 1 root root 0 6月 18 10:58 test_2010:06:18-10:58:10
-rw-r--r-- 1 root root 0 6月 18 10:59 test_2010:06:18-10:59:10
-rw-r--r-- 1 root root 0 6月 18 11:00 test_2010:06:18-11:00:10
-rw-r--r-- 1 root root 0 6月 18 11:01 test_2010:06:18-11:01:10
-rw-r--r-- 1 root root 0 6月 18 11:02 test_2010:06:18-11:02:10
--------------------
效果不錯 (^.^)
===========================
再來試試一次每隔20秒執行一次.
到/etc/secrond/
vim schedule
=========================================================
# SECOND(0-59) MINUTE(0-59) HOUR(0-23) DAY(1-31) COMMAND
10,30,50 * * * /home/vincent/myshell/testsecron.sh
-------------------------------------------
# service secrond start
Starting periodic command scheduler secrond
-------------------------------------------
-rw-r--r-- 1 root root 0 6月 22 19:43 test_2010:06:22-19:43:10
-rw-r--r-- 1 root root 0 6月 22 19:43 test_2010:06:22-19:43:30
-rw-r--r-- 1 root root 0 6月 22 19:43 test_2010:06:22-19:43:50
-rw-r--r-- 1 root root 0 6月 22 19:44 test_2010:06:22-19:44:10
-rw-r--r-- 1 root root 0 6月 22 19:44 test_2010:06:22-19:44:30
-rw-r--r-- 1 root root 0 6月 22 19:44 test_2010:06:22-19:44:50
-rw-r--r-- 1 root root 0 6月 22 19:45 test_2010:06:22-19:45:10
-rw-r--r-- 1 root root 0 6月 22 19:45 test_2010:06:22-19:45:30

果然就每隔20秒執行一次了.