顯示文章

這裡允許您檢視這個會員的所有文章。請注意, 您只能看見您有權限閱讀的文章。


主題 - bunko

頁: [1]
1
為何發表一篇心得分享,自己都看不到內容..................
就什麼都沒有.廣告文還能在刪除存證區找到.
心得分享卻.....什麼都沒有..........
是系統有狀況嗎?


2
http://phorum.study-area.org/index.php/topic,63768.0.html
討論中,有提到MySQL 一道Query是否能夠跨不同主機來進行查詢呢?
以下是一個簡單的範例,提供大家參考:

MySQL Federated storage engine 簡單範例
----------------------------------------
1.環境介紹
  Mysql #1 Name: Akira
  Mysql #2 Name: Bunko

2.首先先在 Akira 裡面建一個叫test1219a的database

create database test1219a;
use test1219a;
grant all on test1219a.* to 'akira'@'%' identified by 'akiraxyz';
flush privileges;
-------------------
然後再用akira登入

建立一個叫Author的Table
akira@[test1219a] Akira
==>CREATE TABLE Author
    -> (AuthorID smallint PRIMARY KEY,
    -> AuthorName varchar(255)
    -> );



3.然後在Bunko 建立一個叫 test1219b的database

create database test1219b;
use test1219b;
grant all on test1219b.* to 'bunko'@'%' identified by 'bunkonao';
flush privileges;
---------------------
接著用bunko登入,建立一個叫 rAuthor的table


bunko@[test1219b] Bunko
==>CREATE TABLE rAuthor
    -> (AuthorID smallint PRIMARY KEY,
    -> AuthorName varchar(255))
    -> engine=FEDERATED
    -> connection='mysql://akira:akiraxyz@192.168.0.100:3307/test1219a/Author';
Query OK, 0 rows affected (0.07 sec)

可以看到上面要指定engine=FEDERATED ,還有連結的資料.包含登入user,password,host ip,port,
database,table組成.

現在select看看...
bunko@[test1219b] Bunko
==>select * from rAuthor;
Empty set (0.01 sec)

空的....正確!因為我們原始的Table也沒有資料.

4.回到Akira

akira@[test1219a] Akira
==>insert into Author values(1, 'Agatha Christie');

akira@[test1219a] Akira
==>select * from Author;
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
+----------+-----------------+

已插入一筆資料
--------------------------------------
到Bunko看看

bunko@[test1219b] Bunko
==>select * from rAuthor;
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
+----------+-----------------+

可以順利的看到Akira裡面剛加入的資料.
現在我們在Bunko rAuthor新增資料.

bunko@[test1219b] Bunko
==>insert into rAuthor values(2, 'Stephen King');

到Akira

akira@[test1219a] Akira
==>select * from Author;
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
|        2 | Stephen King    |
+----------+-----------------+

到此可以看到Akira/Bunko兩個MySQL透過Federated storage engine
讓兩邊都能輕易的存取到實際上存在Akira上的Table.

5. Join

在Bunko裡面,再建立一個新Table : Books

bunko@[test1219b] Bunko
==>CREATE TABLE Books
    -> (BookID smallint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> BookName varchar(255),
    -> AuthorID smallint NOT NULL,
    -> INDEX (AuthorID)
    -> );

插入4筆資料:

bunko@[test1219b] Bunko
==>INSERT INTO Books Values(NULL, 'Murder on the Orient Express', 1);

bunko@[test1219b] Bunko
==>INSERT INTO Books Values(NULL, 'Death on the Nile', 1);

bunko@[test1219b] Bunko
==>INSERT INTO Books Values(NULL, 'Different Seasons', 2);

bunko@[test1219b] Bunko
==>INSERT INTO Books Values(NULL, 'The Green Mile', 2);

然後Join

bunko@[test1219b] Bunko
==>SELECT b.BookID,b.BookName,a.AuthorName
    -> FROM Books b, rAuthor a
    -> where b.AuthorID = a.AuthorID;
+--------+------------------------------+-----------------+
| BookID | BookName                     | AuthorName      |
+--------+------------------------------+-----------------+
|      1 | Murder on the Orient Express | Agatha Christie |
|      2 | Death on the Nile            | Agatha Christie |
|      3 | Different Seasons            | Stephen King    |
|      4 | The Green Mile               | Stephen King    |
+--------+------------------------------+-----------------+

基本上到此已經運用Federated storage engine 在一道SQL Command裡面進行
兩個不同MySQL的Join查詢.

6. 補充

回到Akira 用root登入,建立另一個user.只有對 test1219a.Author 有select權限.

root@[test1219a] Akira
==>grant select on test1219a.Author to 'selauthor'@'%' identified by 'authorsel';

root@[test1219a] Akira
==>flush privileges;

---------------------------------------------
一樣在Bunko建立第二個 Table rAuthor2.

bunko@[test1219b] Bunko
==>CREATE TABLE rAuthor2
    -> (AuthorID smallint PRIMARY KEY,
    -> AuthorName varchar(255))
    -> engine=FEDERATED
    -> connection='mysql://selauthor:authorsel@192.168.0.100:3307/test1219a/Author';

bunko@[test1219b] Bunko
==>select * from rAuthor2;
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
|        2 | Stephen King    |
+----------+-----------------+


一樣可以查詢.....但是試試看INSERT INTO

bunko@[test1219b] Bunko
==>insert into rAuthor2 values(3, 'Louis Cha');
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1142: INSERT command denied to user 'selauthor'@'192.168.0.100' for table 'A' from FEDERATED


無法INSERT INTO.
可以視實際的需求,選擇適當的方式來連線作業.

3
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

得到排序後的輸出

4
MySQL 標準的函數相對不夠豐富, 所以有些功能就自己寫.
因為採用ASCII轉換的關係,base到由2~72為佳. 超過會出現亂碼或是空白.
在一般的範圍內應該都夠使用了.

dec2tran(target,base)


>select dec2tran(255,16);
+------------------+
| dec2tran(255,16) |
+------------------+
| FF               |
+------------------+

]>select dec2tran(255,12);
+------------------+
| dec2tran(255,12) |
+------------------+
| 193              |
+------------------+

>select dec2tran(255,2);
+-----------------+
| dec2tran(255,2) |
+-----------------+
| 11111111        |
+-----------------+

>select dec2tran(255,8);
+-----------------+
| dec2tran(255,8) |
+-----------------+
| 377             |
+-----------------+


程式碼如下:
----------------------
CREATE FUNCTION `dec2tran`(`in_num` INT, `tran` INT)
   RETURNS VARCHAR(20)
   LANGUAGE SQL
   NOT DETERMINISTIC
   CONTAINS SQL
   SQL SECURITY DEFINER
   COMMENT ''
BEGIN
DECLARE rtn VARCHAR(20);
DECLARE quotient int;
DECLARE remainder int;
DECLARE tmpstr VARCHAR(20);
DECLARE qstr char(1);
DECLARE rstr char(1);

set tmpstr='';

if in_num <0 then
 return NULL;
end if;
if tran < 2 then
   return NULL;
end if;

start_loop:LOOP


set quotient=floor(in_num/tran);
set remainder=in_num%tran;

if remainder >= 10 then
   set rstr=char(remainder+55);
   set tmpstr=CONCAT(tmpstr,rstr); -- 10~11 to 'A' 'B'
   
else
   set rstr=char(remainder+48); -- 1~9 to '1'~'9'
   set tmpstr=CONCAT(tmpstr,rstr);
   
end if;

IF quotient=0 THEN
   LEAVE start_loop;
END IF;
if quotient < tran then
  if quotient < 10 then
-- 1~9 replace to char 1~9
   set qstr=char(quotient+48);
   set tmpstr=CONCAT(tmpstr,qstr);
   
  end if;

  if quotient >= 10 and quotient < tran then
   set qstr=char(quotient+55);
   set tmpstr=CONCAT(tmpstr,qstr);
   
  end if;
 
  LEAVE start_loop;
end if;

set in_num=quotient;

END LOOP start_loop;

set rtn=reverse(tmpstr);
return rtn;
END
-----------------------------

頁: [1]