bash 从linux终端插入mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16630748/
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
Insert into mysql from linux terminal
提问by Code Geas Coder
I have a question. i can insert into mysql database from linux terminal a record with this command:
我有个问题。我可以使用以下命令从 linux 终端将记录插入到 mysql 数据库中：
mysql dbTest insert into tablename values(1,"b","c")
Now i have a file in Linux with some records for example:
现在我在 Linux 中有一个文件，其中包含一些记录，例如：
$cat file
2, "c", "e"
3, "r", "q"
4, "t", "w"
5, "y", "e"
6, "u", "r"
7, "g", "u"
8, "f", "j"
9, "v", "k"
i don't know how can i insert all records to the file to mysql database from linux terminal.
我不知道如何从 linux 终端将所有记录插入到 mysql 数据库的文件中。
I intent with a bash file but i don't know =(
我打算使用 bash 文件，但我不知道 =(
采纳答案by Ansgar Wiechers
Doing a series of inserts is not the best choice performance-wise. Since your input data exist as CSV you'd be better off doing a bulk loadas @Kevin suggested:
执行一系列插入并不是性能方面的最佳选择。由于您的输入数据以 CSV 格式存在，因此最好按照@Kevin 的建议进行批量加载：
mysql dbTest -e "LOAD DATA INFILE './file' INTO TABLE tablename FIELDS TERMINATED BY ','"
回答by slackmart
You can create a custom sql file using sed.
您可以使用sed.
From a terminal execute the following code:
从终端执行以下代码：
sed 's/\(^[0-9]*, [a-z]*, [a-z]*$\)/INSERT INTO tablename VALUES();/g' source_file > sql_script.sql
After you can easily use the sourcecommand to insert the records.
之后就可以方便地使用source命令插入记录了。
$ mysql -u mysqlusername -p -h host
Enter password: your_secret_password
Mysql > use yourdatabasename
Mysql > source sql_script.sql
回答by Sumanta
Install Mysql :
sudo apt-get install mysql-serverGoto Mysql shell:
mysql -u root -p password:******Show Database( if already exist):
show databases;Create a databases:
create database employees;Access the created database:
use employees;</code>Create a table (Be careful on "``" and "
" : use acutesign in each field):create table `employees`( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(64) DEFAULT NULL,`last_name` varchar(64) DEFAULT NULL, `email` varchar(64) DEFAULT NULL, `department` varchar(64) DEFAULT NULL, `salary` DECIMAL(10,2) DEFAULT NULL, PRIMARY KEY(`id`)) ENGINE = InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET = latin1;Add Information to a table:
INSERT INTO `employees` (`id`,`first_name`,`last_name`,`email`,`department`,`salary`) VALUES (1, 'Sumanta','Silwal','[email protected]','Computer Design', '5000.00');Look the created tale:
select * from employees;
安装Mysql：
sudo apt-get install mysql-server转到 MySQL 外壳：
mysql -u root -p password:******显示数据库（如果已经存在）：
show databases;创建数据库：
create database employees;访问创建的数据库：
use employees;</code>创建一个表（注意
" : use acute每个字段中的“``”和“符号” ）：create table `employees`( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(64) DEFAULT NULL,`last_name` varchar(64) DEFAULT NULL, `email` varchar(64) DEFAULT NULL, `department` varchar(64) DEFAULT NULL, `salary` DECIMAL(10,2) DEFAULT NULL, PRIMARY KEY(`id`)) ENGINE = InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET = latin1;向表中添加信息：
INSERT INTO `employees` (`id`,`first_name`,`last_name`,`email`,`department`,`salary`) VALUES (1, 'Sumanta','Silwal','[email protected]','Computer Design', '5000.00');看看创造的故事：
select * from employees;
Note: use "`" sign in each field like employees, id, first_name, last_name, email, department and salary.
注意：在每个字段中使用“`”符号，例如员工、ID、名字、姓氏、电子邮件、部门和工资。
**Enjoy coding.
**享受编码。
回答by n3rV3
Using awk you could use the existing file and insert values from bash itself:
使用 awk，您可以使用现有文件并从 bash 本身插入值：
awk 'BEGIN {print "mysql -u root -p dbTest << EOF"} {print "insert into tablename values (" ##代码## ");"} END {print "EOF"}' file |bash -v
回答by K Adithyan
mysql -e "use databasename;insert into TABLENAME values ('POST',........);"
mysql -e "使用数据库名；插入 TABLENAME 值 ('POST',........);"
