mysql - 直接终端输出到Mysql表

Question

我想将此 Linux 命令的输出发送到 MySql 表:命令:

bzcat filename.bz2 | head -200 | cut -f2,13-15,17 | ruby -ne 'puts "#{$_}" if $_ =~ /\s\d+\.\d+/; ' | ruby -ne 'puts "#{$_}"' | cut -f1-5 

命令文本输出:

6985418911  -81.804885  24.550558   12  http://farm8.staticflickr.com/7205/6985418911_df7747990d.jpg

10201275523 -79.365637  43.649708   16  http://farm6.staticflickr.com/5465/10201275523_3e6ea67c7f.jpg

7289030198  -73.985495  40.740067   15  http://farm8.staticflickr.com/7231/7289030198_1f1ba44113.jpg

4572998878  -71.047843  42.33719    16  http://farm4.staticflickr.com/3373/4572998878_658b45226f.jpg

3973434963  -0.384016   39.474441   15  http://farm3.staticflickr.com/2526/3973434963_76c26e3c88.jpg

2932067831  -109.4995   38.737861   16  http://farm4.staticflickr.com/3026/2932067831_8885c3d53f.jpg

此时，URL、自动标签和照片/视频字段需要为空。

Answer 1

您必须将输出格式化为 INSERT 语句，以便 Mysql 知道如何处理它。您还必须有一个表来捕获输出。架构由您决定。一个简单的例子是:

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.01 sec)

mysql> use test;
Database changed
mysql> CREATE TABLE stdout_dump (line VARCHAR(5000));
Query OK, 0 rows affected (0.18 sec)

mysql> exit
Bye

现在您可以使用sed或awk或what-have-you格式化输出并通过管道传输到mysql:

$ seq 1 10 | sed -r "s/(.*)/INSERT INTO test.stdout_dump VALUES ('\1');/g" | mysql -u youruser -p

这些值将被加载到您的表中:

mysql> SELECT * FROM test.stdout_dump;
+------+
| line |
+------+
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
| 6    |
| 7    |
| 8    |
| 9    |
| 10   |
+------+
10 rows in set (0.00 sec)

或者:

$ echo "this is another line" | sed -r "s/(.*)/INSERT INTO test.stdout_dump VALUES ('\1');/g" | mysql -u yourusername -p

mysql> SELECT * FROM test.stdout_dump;
+----------------------+
| line                 |
+----------------------+
| 1                    |
| 2                    |
| 3                    |
| 4                    |
| 5                    |
| 6                    |
| 7                    |
| 8                    |
| 9                    |
| 10                   |
| this is another line |
+----------------------+
11 rows in set (0.00 sec)

<小时/>

这是一个具有更复杂架构的示例(表中有多个列)。同样，这实际上归结为在命令行上使用 awk 或 sed 或其他工具为您的表生成 INSERT 语句，您可以通过管道将其传输到 mysql:

$ cat testoutput
10201275523 -79.365637  43.649708   16  http://farm6.staticflickr.com/5465/10201275523_3e6ea67c7f.jpg
7289030198  -73.985495  40.740067   15  http://farm8.staticflickr.com/7231/7289030198_1f1ba44113.jpg
4572998878  -71.047843  42.33719    16  http://farm4.staticflickr.com/3373/4572998878_658b45226f.jpg
3973434963  -0.384016   39.474441   15  http://farm3.staticflickr.com/2526/3973434963_76c26e3c88.jpg
2932067831  -109.4995   38.737861   16  http://farm4.staticflickr.com/3026/2932067831_8885c3d53f.jpg

$ cat testoutput | awk  '{printf "INSERT INTO test.Images (Hash, Lng, Lat, Accuracy, URL) VALUES (%s,%s,%s,%s,'\''%s'\'');\n", $1, $2, $3, $4, $5}' | mysql -u yourusername -p

mysql> SELECT * FROM test.Images;

+------+-------------+-----------+---------------------------------------------------------------+-------------+----------+----------+-------------+
| id   | Lng         | Lat       | URL                                                           | Hash        | Autotags | Accuracy | Photo/Video |
+------+-------------+-----------+---------------------------------------------------------------+-------------+----------+----------+-------------+
| NULL |  -79.365637 | 43.649708 | http://farm6.staticflickr.com/5465/10201275523_3e6ea67c7f.jpg | 10201275523 | NULL     |       16 |        NULL |
| NULL |  -73.985495 | 40.740067 | http://farm8.staticflickr.com/7231/7289030198_1f1ba44113.jpg  |  7289030198 | NULL     |       15 |        NULL |
| NULL |  -71.047843 | 42.337190 | http://farm4.staticflickr.com/3373/4572998878_658b45226f.jpg  |  4572998878 | NULL     |       16 |        NULL |
| NULL |   -0.384016 | 39.474441 | http://farm3.staticflickr.com/2526/3973434963_76c26e3c88.jpg  |  3973434963 | NULL     |       15 |        NULL |
| NULL | -109.499500 | 38.737861 | http://farm4.staticflickr.com/3026/2932067831_8885c3d53f.jpg  |  2932067831 | NULL     |       16 |        NULL |
+------+-------------+-----------+---------------------------------------------------------------+-------------+----------+----------+-------------+
5 rows in set (0.00 sec)

如果您经常做这项工作，最好花一些时间熟悉 sed 和 awk 以及 mysql INSERT 语句。