【MySQL 每日一技】控制 mysql 命令执行结果的输出位置和格式

1 问题

你希望将 mysql 命令连接数据库后操作得到的结果输出至除标准输出以外的地方，同时你可能不希望使用默认的输出格式。

2. 解决方案

将输出重定向至一个文件，或者使用管道符将输出发送至其他程序（如 Linux 命令）。同时你也可以控制通过 mysql 命令连接数据库后操作得出的结果的格式，这些格式可以是表格形式，制表符分隔形式，HTML 或者 XML 等格式。你还可以不输出字段名称，或者使用输出的结果更加详尽又或者更加简洁。

3. 讨论

默认情况下，使用 mysql 命令连接数据库之后执行语句的结果会输出至标准输出，也即屏幕。如果你希望将输出结果保存至一个文件中，那么在 Linux 中你可以使用输出重定向符号¹ ，例如：

mysql -h ${host} -u ${user} -e "${sql_stmnt}" -D ${database} -p > outputfile

实际上， SQL 语句 ${sql_stmnt} 也可以写在文件中，然后结合输入重定向，例如：

mysql -h ${host} -u ${user} -D ${database} -p < inputfile > outputfile

更一般地，输出的结果还可以通过管道符发送给另一个程序（比如，将查询结果通过一个叫 mail 的程序邮件发送给某个用户）：

mysql -h ${host} -u ${user} -D ${database} -p < inputfile | mail paul

3.1 以表格形式或制表符分隔形式输出结果

根据具体的执行方式是交互式还是非交互式， mysql 会选择默认的输出格式。在交互式下，mysql 的执行结果以表格形式输出，例如：

mysql> SELECT * FROM limbs WHERE legs=0;
+------------+------+------+
| thing      | legs | arms |
+------------+------+------+
| squid      |    0 |   10 |
| fish       |    0 |    0 |
| phonograph |    0 |    1 |
+------------+------+------+
3 rows in set (0.00 sec)

在非交互模式下（即输入或输出被重定向）， mysql 的执行结果是制表符分隔的形式，例如²：

[root@centos ~]# echo "SELECT * FROM limbs WHERE legs=0" | mysql cookbook
thing   legs    arms
squid   0       10
fish    0       0
phonograph      0       1

针对上述场景，通过指定恰当的选项，你也可以让输出的结果不是以制表符分隔的，例如考虑下列场景：

mysql cookbook < inputfile | mail paul

由于在这种情况下，mysql 以非交互的方式运行，所以输出的结果以制表符分隔，可以想象，结果肯定没有表格形式那么直观，因此你可以通过 -t 选项（等价于 --table）来输出更可读的表格形式的输出结果：

mysql -t cookbook < inputfile | mail paul

相反，如果希望在交互模式下也以制表符分隔的方式输出结果，那么可以使用 -B 选项（等价于 --batch），例如：

[root@centos ~]# mysql -B -D cookbook
select * from limbs;
thing   legs    arms
human   2       2
insect  6       0
squid   0       10
fish    0       0
centipede       100     0
table   4       0
armchair        4       2
phonograph      0       1
tripod  3       0
Peg Leg Pete    1       2
space alien     NULL    NULL

3.2 以 HTML 或 XML 格式输出结果

如果你指定了 -H （等价于 --html）选项，那么 mysql 将会为每一个查询结果生成一个 HTML 格式的表格，你可以直接将输出的结果包含了网页中以显示查询的结果，例如：

[root@centos ~]# mysql -H -e "SELECT * FROM limbs WHERE legs=0" cookbook
<TABLE BORDER=1>
<TR><TH>thing</TH><TH>legs</TH><TH>arms</TH></TR>
<TR><TD>squid</TD><TD>0</TD><TD>10</TD></TR>
<TR><TD>fish</TD><TD>0</TD><TD>0</TD></TR>
<TR><TD>phonograph</TD><TD>0</TD><TD>1</TD></TR>
</TABLE>

实际上，你可以将上述结果输出至一个文件中，然后使用一个浏览器直接打开，例如：

[root@centos ~]# mysql -H -e "SELECT * FROM limbs WHERE legs=0" cookbook > limbs.html

如果你希望生成结果是 XML 的格式，那么你可以使用 -X （等价于 --xml）选项，例如：

[root@centos ~]# mysql -X -e "SELECT * FROM limbs WHERE legs=0" cookbook
<?xml version="1.0"?>

<resultset statement="SELECT * FROM limbs WHERE legs=0
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
        <field name="thing">squid</field>
        <field name="legs">0</field>
        <field name="arms">10</field>
  </row>

  <row>
        <field name="thing">fish</field>
        <field name="legs">0</field>
        <field name="arms">0</field>
  </row>

  <row>
        <field name="thing">phonograph</field>
        <field name="legs">0</field>
        <field name="arms">1</field>
  </row>
</resultset>

3.3 在查询结果中不输出表头

以制表符分隔的格式非常便于生成用于导入其他程序的数据文件。然而，查询结果的第一行一般都默认是表头，这可能不总是你希望得到的结果。如果你只需要数据部分而不想要表头，那么你可以使用 --skip-column-names 选项，例如：

[root@centos ~]# mysql --skip-column-names -e "SELECT arms FROM limbs" cookbook
+------+
|    2 |
|    0 |
|   10 |
|    0 |
|    0 |
|    0 |
|    2 |
|    1 |
|    0 |
|    2 |
| NULL |
+------+

实际上，使用两次 -s 选项（等价于 --silent）也可以实现类似的效果：

[root@centos ~]# mysql -ss -e "SELECT arms FROM limbs" cookbook
2
0
10
0
0
0
2
1
0
2
NULL

3.4 指定输出结果的字段分隔符

如上所述，在非交互式模式下，mysql 命令会使用制表符分隔输出字段，而且没有选项可以用来指定其他分隔符。如果你的确需要使用一个不同的分隔符，那么你需要自己处理 mysql 命令的输出。

假设某个程序需要使用 mysql 的输出结果，但是该程序希望结果文件的值之间通过冒号 : 而非制表符分割。在 Unix 系统下，你可以通过使用 tr 或 sed 等命令将将制表符转换为任意的分隔符，例如：

[root@centos ~]# echo "select * from limbs;" | mysql cookbook | sed -e "s/\t/:/g"
thing:legs:arms
human:2:2
insect:6:0
squid:0:10
fish:0:0
centipede:100:0
table:4:0
armchair:4:2
phonograph:0:1
tripod:3:0
Peg Leg Pete:1:2
space alien:NULL:NULL

[root@centos ~]# echo "select * from limbs;" | mysql cookbook | tr "\t" ":"
thing:legs:arms
human:2:2
insect:6:0
squid:0:10
fish:0:0
centipede:100:0
table:4:0
armchair:4:2
phonograph:0:1
tripod:3:0
Peg Leg Pete:1:2
space alien:NULL:NULL

[root@centos ~]# echo "select * from limbs;" | mysql cookbook | tr "\011" ":"
thing:legs:arms
human:2:2
insect:6:0
squid:0:10
fish:0:0
centipede:100:0
table:4:0
armchair:4:2
phonograph:0:1
tripod:3:0
Peg Leg Pete:1:2
space alien:NULL:NULL

3.5 使得结果更详尽或更简洁

当你在非交互的方式下执行 mysql 命令时，不仅默认的输出格式会改变，其整体输出结果都会显得更加精炼。例如， mysql 不会打印出结果集的行数或该条语句执行的具体耗时等。如果你的确需要这些更加详尽的信息，你可以通过指定 -v 选项（等价于 --verbose）来实现，且随着你指定该选项的次数增加，结果的详实程度也会逐渐增加，例如：

[root@centos ~]# echo "select * from limbs limit 2" | mysql cookbook
thing   legs    arms
human   2       2
insect  6       0
[root@centos ~]# 
[root@centos ~]# echo "select * from limbs limit 2" | mysql -v cookbook
--------------
select * from limbs limit 2
--------------

thing   legs    arms
human   2       2
insect  6       0
[root@centos ~]# echo "select * from limbs limit 2" | mysql -vv cookbook
--------------
select * from limbs limit 2
--------------

thing   legs    arms
human   2       2
insect  6       0
2 rows in set

Bye
[root@centos ~]# echo "select * from limbs limit 2" | mysql -vvv cookbook
--------------
select * from limbs limit 2
--------------

+--------+------+------+
| thing  | legs | arms |
+--------+------+------+
| human  |    2 |    2 |
| insect |    6 |    0 |
+--------+------+------+
2 rows in set (0.00 sec)

Bye

相反，通过指定 -s （等价于 --silent）可以实现相反的效果。

---

1. 实际上，在 Windows 上在支持输出重定向符号 > 。 ↩︎

2. 在 Linux 上使用 root 用户安装 MySQL 之后，默认情况下你可以直接使用 mysql 命令连接数据库。 ↩︎