linux mysql 插入,從linux終端插入mysql

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文件，但我不知道=(

5 个解决方案

#1

2

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 load as @Kevin suggested:

執行一系列插入操作並不是最佳的性能選擇。既然你的輸入數據是以CSV格式存在的，你最好像@Kevin建議的那樣做大量的加載:

mysql dbTest -e "LOAD DATA INFILE './file' INTO TABLE tablename FIELDS TERMINATED BY ','"

#2

2

You can create a custom sql file using sed.

您可以使用sed創建一個自定義sql文件。

From a terminal execute the following code:

從終端執行以下代碼:

sed 's/\(^[0-9]*, [a-z]*, [a-z]*$\)/INSERT INTO tablename VALUES(\1);/g' source_file > sql_script.sql

After you can easily use the source command to insert the records.

在您可以輕松地使用source命令插入記錄之后。

$ mysql -u mysqlusername -p -h host

Enter password: your_secret_password

Mysql > use yourdatabasename

Mysql > source sql_script.sql

#3

0

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 (" $0 ");"} END {print "EOF"}' file |bash -v

#4

0

mysql -e "use databasename;insert into TABLENAME values ('POST',........);"

“使用數據庫名稱;插入到TABLENAME值(‘POST’，……);”

#5

0

Install Mysql :

安裝Mysql: sudo apt-get install mysql-server

Goto Mysql shell:

轉到Mysql殼: mysql -u root -p

password:******

Show Database( if already exist):

顯示數據庫(如果已經存在): show databases;

Create a databases:

創建一個數據庫: create database employees;

Access the created database:

訪問創建數據庫: use employees;

Create a table (Be careful on "``" and " " : use acutesign in each field):

創建一個表(在“' '”和“:在每個字段中使用acutesign): 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','sumantasilwal10@gmail.com','Computer Design', '5000.00');

Look the created tale:

創建的故事: select * from employees;

Note: use "`" sign in each field like employees, id, first_name, last_name, email, department and salary.

注意:在員工、id、first_name、last_name、電子郵件、部門和薪水等每個字段中使用“'”符號。

**Enjoy coding.

* *享受編碼。