mysql - 将逗号分隔的字符串从管道导入到 mysql 数据库

Question

我需要将来自 stdout (speedtest-cli) 的以下逗号分隔字符串插入到 mysql 数据库中

1746,Vodafone DE,Frankfurt,2017-04-24T16:00:13.708464,292.1655728839447,267.019,119047915.27025849,12007236.796291795

列描述为:SERVER_ID、SPONSOR、SERVER_NAME、时间戳、距离、PING、下载、上传

无需将数据保存到 csv 然后将其导入 mysql 即可完成此操作的最佳方法是什么？

我能够将逗号分隔的数据转换为可sqlable数据

SQL_RESULT_DATA=$(echo $RESULT_DATA | sed -e "s/,/','/g;s/^\(.*\)$/'\1'/")

但是

mysql --host=10.8.0.18 --user speedtest --password=speedtest < echo "INSERT INTO `TEST_DATA` (`SERVER_ID`, `SPONSOR`, `SERVER_NAME`, `TIMESTAMP`, `DISTANCE`, `PING`, `DOWNLOAD`, `UPLOAD`) VALUES ( $SQL_RESULT_DATA )"

失败并显示

-bash: TEST_DATA: command not found
-bash: SERVER_ID: command not found
-bash: SPONSOR: command not found
-bash: SERVER_NAME: command not found
-bash: TIMESTAMP: command not found
-bash: DISTANCE: command not found
-bash: PING: command not found
-bash: DOWNLOAD: command not found
-bash: UPLOAD: command not found
-bash: echo: No such file or directory

表说明:

MySQL [speedtest]> DESCRIBE TEST_DATA
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| TEST_ID     | int(11) | NO   | PRI | NULL    | auto_increment |
| SERVER_ID   | int(11) | NO   |     | NULL    |                |
| SPONSOR     | text    | NO   |     | NULL    |                |
| SERVER_NAME | text    | NO   |     | NULL    |                |
| TIMESTAMP   | text    | NO   |     | NULL    |                |
| DISTANCE    | int(11) | NO   |     | NULL    |                |
| PING        | int(11) | NO   |     | NULL    |                |
| DOWNLOAD    | int(11) | NO   |     | NULL    |                |
| UPLOAD      | int(11) | NO   |     | NULL    |                |
+-------------+---------+------+-----+---------+----------------+

Answer 1

让我解释一下你的测试字符串上的错误，

mysql --host=10.8.0.18 --user speedtest --password=speedtest < echo

表示“从名为 echo 的文件中读取标准输入”

" "以字符串形式开始另一个 mysql 参数，此参数中包含空格"

INSERT INTO `TEST_DATA`

“执行命令TEST_DATA并将其输出插入到该字符串中”

(`SERVER_ID`, `SPONSOR`, `SERVER_NAME`, `TIMESTAMP`, `DISTANCE`, `PING`, `DOWNLOAD`, `UPLOAD`) VALUES ( $SQL_RESULT_DATA )"

包含与上面相同的错误，因为反引号用于生成子shell并获取其结果

您想要使用的是mysql命令的-e参数来传递查询，并且在该查询中您需要转义反引号，例如

mysql --host=10.8.0.18 --user speedtest --password=speedtest -e "INSERT INTO\`TEST_DATA\` (\`SERVER_ID\`,\`SPONSOR\`,\`SERVER_NAME\`、\`时间戳\`、\`距离\`、\`PING\`、\`下载\`、\`上传\`)值($SQL_RESULT_DATA)"

另请参阅:

• Bash + MySQL -d backtick issue对于其他反引号选项
• https://explainshell.com/了解您正在排除故障的 shell 命令的说明。

确保像这样引用 SQL_RESULT_DATA 表达式

SQL_RESULT_DATA="$(echo $RESULT_DATA | sed -e "s/,/','/g;s/^\(.*\)$/'\1'/")"

否则，将来可能会导致令人讨厌的通配和分词问题，这可能会导致安全问题，请参阅 https://unix.stackexchange.com/questions/171346/security-implications-of-forgetting-to-quote-a-variable-in-bash-posix-shells