[分享] MySQL 讀取及輸出文字檔

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

得到排序后的输出

bunko前辈您好
我照著前辈上面说的
到
1.3 用sorter登入并建立table

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

mysql> show columns from sortforge;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| col1  | decimal(8,2) | YES  |     | NULL    |       |
| col2  | decimal(8,2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.05 sec)
这部份没问题
但到了
1.4 输入文字档

mysql> LOAD DATA INFILE '/myimpexp/raw-2010-09-02' INTO TABLE sortforge
    -> FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
就出现
ERROR 13 (HY000): Can't get stat of '/myimpexp/raw-2010-09-02' (Errcode: 2)
我的txt也是用跟前辈一样的档名，但不知道哪里有问题
可否请前辈只导一下
感激不尽！！

会不会是这个地方呢??? 先确定一下目录权限.

1.2 在OS中建立目录供MySQL存取,并建立预备输入的文字档

ps. 目录必须设定让mysql有权限读写,不一定是让mysql当owner

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

上面这段部份有漏掉吗??

或是这段 file 权限的赋予有漏掉吗?
root@[(none)]>grant file on *.* to 'sorter'@'%';

---------------------------------------------------------------------------
另外那个例子是在0902,而后面的一些程式码是会以日期方式来决定档名.
后面的部份就要注意一下作相对应的调整.

# cd /
# mkdir myimpexp
# chown mysql:mysql myimpexp
这部份没有漏掉
# chown mysql:mysql myimpexp
输入完之后没出现任何讯息是正常的吗？？

root@[(none)]>grant file on *.* to 'sorter'@'%';
没问题Query OK, 0 rows affected (0.00 sec)

用mysql -u sorter -p登入

输入
LOAD DATA INFILE '/myimpexp/raw-2010-09-02' INTO TABLE sortforge
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

出现
ERROR 1046 (3D000): No database selected

我在选择资料库
use sorter
出现
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

再输入一次
LOAD DATA INFILE '/myimpexp/raw-2010-09-02' INTO TABLE sortforge
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

还是得到
ERROR 13 (HY000): Can't get stat of '/myimpexp/raw-2010-09-02' (Errcode: 2)

翻了翻书，还是搞不清处哪有问题？！
再麻烦前辈指点一下了！！
感激不尽！！