還是藍色小鋪的同樣的問題.
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 |
+----+-----+---------------------+-------+------+-------+------+
2011年2月17日 星期四
2011年2月14日 星期一
MySQL CASE的應用
今天有人在藍色小舖問了一個問題:
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;
就可以得到你需要的了.
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;
就可以得到你需要的了.
2010年11月18日 星期四
最近Pidgin 登入MSN時 SSL憑證錯誤的解決方法
最近用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
解決方法:
當然要先把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
2010年10月6日 星期三
有意思的sql問題
在藍色小舖看到有人問一個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執行速度會比較快.
當資料量大的時候最好加上,現在三筆資料,就不加了.
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年9月1日 星期三
MySQL Database Quota Control
之前在酷學園有人問到,所以後來就開發了這功能.
詳細的資料整理成pdf了.
http://www.filesonic.com/file/17520903/mysqlquotabunko.rar
詳細的資料整理成pdf了.
http://www.filesonic.com/file/17520903/mysqlquotabunko.rar
MySQL 讀取及輸出文字檔
在酷學園看到有人問到排序的問題,有兩欄位.
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
得到排序後的輸出
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
得到排序後的輸出
訂閱：
文章 (Atom)