Linux 用于在 MySQL 中插入值的 Bash 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17997558/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Bash script to insert values in MySQL
提问by user2642601
I want to make a bash script that connects to my MySQL server and inserts some valuse from a txt file. I have written this down:
我想制作一个连接到我的 MySQL 服务器并从 txt 文件中插入一些值的 bash 脚本。我已经写下了这个：
#!/bin/bash
echo "INSERT INTO test (IP,MAC,SERVER) VALUES ('cat test.txt');" | mysql -uroot -ptest test;
but I'm recieving the following error:
但我收到以下错误：
ERROR 1136 (21S01) at line 1: Column count doesn't match value count at row 1
第 1 行的 ERROR 1136 (21S01)：列计数与第 1 行的值计数不匹配
I suppose the error is in my txt file, but I've tried many variations and still no hope of success.
我想错误是在我的 txt 文件中，但我已经尝试了很多变体，但仍然没有成功的希望。
My txt file looks like this:
我的txt文件是这样的：
10.16.54.29 00:f8:e5:33:22:3f marsara
10.16.54.29 00:f8:e5:33:22:3f 玛莎拉
回答by Sylvain Leroux
Assuming you have many rows to add, you probably need LOAD DATA INFILEstatement, not INSERT. The source file has to be on the server, but it seems to be the case here.
假设您要添加多行，您可能需要LOAD DATA INFILE语句，而不是INSERT. 源文件必须在服务器上，但这里似乎是这种情况。
Something like that:
类似的东西：
#!/bin/bash
mysql -uroot -ptest test << EOF
LOAD DATA INFILE 'test.txt'
INTO TABLE tbl_name
FIELDS TERMINATED BY ' ';
EOF
LOAD DATA INFILEhas many options as you will discover by reading the doc.
LOAD DATA INFILE通过阅读文档，您会发现有很多选择。
回答by Simon
You are trying to insert the value "cat test.txt" as a String in the database in an INSERT statement that requires 3 parameters (IP,MAC and SERVER) so this is why you get this error message.
您试图在需要 3 个参数（IP、MAC 和 SERVER）的 INSERT 语句中将值“cat test.txt”作为字符串插入数据库中，因此这就是您收到此错误消息的原因。
You need to read the text file first and extract the IP, MAC and Server values and then use these in the query that would look like this once filled :
您需要先读取文本文件并提取 IP、MAC 和服务器值，然后在查询中使用这些值，一旦填充如下：
#!/bin/bash
echo "INSERT INTO test (IP,MAC,SERVER) VALUES ('10.16.54.29', '00:f8:e5:33:22:3f', 'marsara');" | mysql -uroot -ptest test;
回答by fejese
Try this one:
试试这个：
#!/bin/bash
inputfile="test.txt"
cat $inputfile | while read ip mac server; do
echo "INSERT INTO test (IP,MAC,SERVER) VALUES ('$ip', '$mac', '$server');"
done | mysql -uroot -ptest test;
This way you streaming the file read as well the mysql comand execution.
通过这种方式，您可以流式传输文件读取以及 mysql 命令执行。
回答by redestructa
I use this and it works:
我使用它并且它有效：
mysql -uroot -proot < infile
or select the database first
或者先选择数据库
./mysql -uroot -proot db_name < infile
or copy the whole SQL into the clipboard and paste it with
或将整个 SQL 复制到剪贴板并粘贴
pbpaste > temp_infile && mysql -uroot -proot < temp_infile && rm temp_infile
