在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就不用了,因為都一樣,只是佔篇幅而已.
2011年2月21日 星期一
2011年2月17日 星期四
MySQL CASE的應用 part2
還是藍色小鋪的同樣的問題.
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 |
+----+-----+---------------------+-------+------+-------+------+
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月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年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
得到排序後的輸出
2010年8月7日 星期六
MySQL database size part2
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 |
+-------------+-----------+---------+----------+---------+
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
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年7月6日 星期二
紀錄Console 操作過程的相關命令, script 跟 tee. Script - record your command line input and output
紀錄Console 操作的相關命令, script 跟 tee.
對系統管理員來說,能把操作的過程保留下來,不管是工作紀錄或是學習來說都是很方便的.
script 使用很簡單, 直接打script.然後系統會出現:
告訴我們存檔名稱叫 typescript
接下來就繼續操作..... 要結束時輸入exit, 或是ctrl-d,然後系統會出現:
對系統管理員來說,能把操作的過程保留下來,不管是工作紀錄或是學習來說都是很方便的.
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(); 這樣就把時間紀錄下來了.
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)