如何将MySQL命令行查询输出转换为CSV格式？

Question

世界另一端的同事向我发送了一些MySQL CLI客户端查询的文本格式输出。她无法将其转换为CSV格式或使用INTO OUTFILE输出标志直接输出到CSV。我该如何将其转换为CSV格式？它看起来像这样：

+-------------+------------------+------------------------------------------+-------------+
| pet_id      | pet_identity_id  | identity_value                           | pet_species |
+-------------+------------------+------------------------------------------+-------------+
|       77626 |          3140819 | [e-mail]                  | dog         |
|       77625 |          3140818 | [e-mail]                  | cat         |
|       77622 |          3140815 | [e-mail]                       | aardvark    |
|       77583 |          3140776 | [e-mail]                  | cow         |
+-------------+------------------+------------------------------------------+-------------+
4 rows in set (0.01 sec)

我希望它看起来像这样的CSV格式（用竖杠或逗号分隔）：

"pet_id"|"pet_identity_id"|"identity_value"|"pet_species"
77626|3140819|"[e-mail]"|"dog"
77625|3140818|"[e-mail]"|"cat"
77622|3140815|"[e-mail]"|"aardvark"
77583|3140776|"[e-mail]"|"cow"

我发现在CLI客户端中使用INTO OUTFILE符号有很多问题可以让您这样做，但没有任何东西可以将以MySQL客户端屏幕上看到的形式发送给您的查询样本转换。

- Sam Critchley

---

可能是如何以CSV格式输出MySQL查询结果？的重复问题。 - Nic3500

感谢@Nic3500，我编辑了问题以更好地解释这适用于您无法访问CLI的输出 - 在这种情况下，有人向您发送（或您在网上找到）从CLI剪切和粘贴的示例输出。 - Sam Critchley

这里还有一个类似的方法：http://tlug.dnho.net/?q=node/209 - Sam Critchley

Answer 1

以下是一个使用sed命令的小型shell脚本，可以实现这一功能：

#!/bin/bash
# A script to convert MySQL CLI output to CSV format

# cat the file and pipe to the next step
cat $1 | \
# grep only the lines with '|' in them
grep "\|" | \
# Remove the lines which begin with '+'
sed -e '/^+/d' | \
# Remove the whitespace around the '|' characters
sed -e 's/[[:space:]]*|[[:space:]]*/|/g' | \
# Put a double quote before every '|' character
sed -e 's/|\(.\{1\}\)/\"&/g' | \
# Put a double quote after every '|' character
sed -e 's/\(.\{1\}\)|/&\"/g' | \
# Remove the extra '"|' from the beginning of each line
sed -e 's/^\"|//g' | \
# Remove the extra '"' from the end of each line
sed -e 's/\"$//g' | \
# Remove the '|' from the end of each line
sed -e 's/|$/\"/g' | \
# Remove the quotes from any purely numeric fields
sed -e 's/"\([[:digit:]]*\)"/\1/g'

将文件另存为例如convert-mysql.sh，然后将MySQL输出复制并粘贴到一个文本文件mysql-output.txt中，运行如下命令：

$ bash ./convert-mysql.sh mysql-output.txt

这将给您以下输出结果:

"pet_id"|"pet_identity_id"|"identity_value"|"pet_species"
77626|3140819|"[e-mail]"|"dog"
77625|3140818|"[e-mail]"|"cat"
77622|3140815|"[e-mail]"|"aardvark"
77583|3140776|"[e-mail]"|"cow"

虽然在各种Linux版本上可能会有一些不同，但这在Mac上是可行的，例如在我上面的shell脚本中，[[:digit:]]*在一些示例中被替换为[[:digit:]]+。

- Sam Critchley

Answer 2

据我所知，您正在寻找一种将MySQL输出的“框架”表转换为CSV的方法。因此，这是我的一个小Lua脚本（http://www.lua.org），它还根据RFC4180进行适当的内部引号转义。

local tabs,counter                      --to keep track of column markers
for line in io.lines(arg[1]) do
  if line:match '^%+[+-]+%+$' then      --frame line
    tabs = {}
    counter = 0
    for ch in line:gmatch '.' do
      counter = counter + 1
      if ch == '+' then tabs[#tabs+1] = counter end
    end
  elseif line:sub(1,1) == '|' then      --data line
    for _,tab in ipairs(tabs) do
      line = line:sub(1,tab-1) .. '\0' .. line:sub(tab+1)
    end
    line = line:gsub('%Z+',
      function(s)
        s = s:gsub('^%s*(.-)%s*$','%1')       --remove leading & trailing spaces (optional)
        if s ~= '' and not s:match '^-?%d-%.?%d+$' then
          s = '"' .. s:gsub('"','""') .. '"'  --add quotes while escaping internal ones
        end
        return s
      end)
    line = line:gsub('%z','|')
    print(line:sub(2,-2))
  end
end

它应该处理除多行字段以外的所有内容。您可以通过文件名作为第一个参数或通过标准输入/管道来提供要处理的文件。

编辑：改进版可正确处理嵌入的|（管道）字符。

- tonypdmtr