1. Overview
Bash provides means for automating various types of tasks in Linux. We can also use Bash scripting and the command-line interface to interact with databases running on a Linux system, such as MySQL or MariaDB.
In this tutorial, we’ll explore different ways to insert values into a MySQL database in Linux.
2. Sample Task
Generally, our objective is to execute SQL statements for data insertion from the shell.
Let’s suppose we have a user named sysadmin with access to a MySQL database. First, we’ll use the sysadmin account to create a database and a table containing some records. Then, we’ll insert more records into the table.
We start by writing SQL code in a file named create_db.sql for creating a database and a table:
$ cat create_db.sql
CREATE DATABASE EmployeeDB;
USE EmployeeDB;
CREATE TABLE employees
(
ID int NOT NULL,
FIRST_NAME varchar(50) NOT NULL,
LAST_NAME varchar(50) NOT NULL,
DEPARTMENT varchar(50),
primary key (ID)
);
INSERT INTO employees VALUES ('1','Lin','Day','HR');
INSERT INTO employees VALUES ('2','Ron','Lee','IT');
INSERT INTO employees VALUES ('3','Mat','Ray','Finance');
In this case, the database name is EmployeeDB and the table within it is called employees. The table has four columns, and three records are already in it.
Next, we run the code by supplying the SQL file to the mysql command via stdin:
$ mysql --user=sysadmin --password=mypassword < create_db.sql
The –user option of the mysql command specifies the user, while the –password switch specifies the user password. Importantly, another approach is to save the password in a Bash variable and subsequently access it through parameter expansion, so that it isn’t exposed on the command line.
We can now inspect the table by running an SQL query:
$ mysql --user=sysadmin --password=mypassword -e "SELECT * FROM EmployeeDB.employees;"
+----+------------+-----------+------------+
| ID | FIRST_NAME | LAST_NAME | DEPARTMENT |
+----+------------+-----------+------------+
| 1 | Lin | Day | HR |
| 2 | Ron | Lee | IT |
| 3 | Mat | Ray | Finance |
+----+------------+-----------+------------+
The -e option of mysql executes SQL statements from strings. Notably, the table consists of only three initial records from the database and table creation.
Let’s explore different ways to insert more data into the employees table.
3. Using mysql -e
We’ve already seen that the mysql -e command executes SQL statements from the command line.
We can use the command to insert two more records into the table:
$ mysql --user=sysadmin --password=mypassword \
-e "INSERT INTO EmployeeDB.employees (ID,FIRST_NAME,LAST_NAME,DEPARTMENT) VALUES ('4','Kim','Zed','Marketing');"
$ mysql --user=sysadmin --password=mypassword \
-e "INSERT INTO EmployeeDB.employees (ID,FIRST_NAME,LAST_NAME,DEPARTMENT) VALUES ('5','Val','Ash','R&D');"
In this case, we just specify the INSERT INTO statement between quotes after the -e option.
Let’s inspect the table following the data insertion:
$ mysql --user=sysadmin --password=mypassword -e "SELECT * FROM EmployeeDB.employees;"
+----+------------+-----------+------------+
| ID | FIRST_NAME | LAST_NAME | DEPARTMENT |
+----+------------+-----------+------------+
| 1 | Lin | Day | HR |
| 2 | Ron | Lee | IT |
| 3 | Mat | Ray | Finance |
| 4 | Kim | Zed | Marketing |
| 5 | Val | Ash | R&D |
+----+------------+-----------+------------+
We notice that two more rows have been added to the table.
Finally, let’s undo the data insertion using a DELETE FROM statement:
$ mysql --user=sysadmin --password=mypassword -e "DELETE FROM EmployeeDB.employees WHERE (ID=4 or ID=5);"
Here, we deleted the rows where the ID is 4 or 5.
Consequently, we can verify that we’ve deleted the newly inserted records:
$ mysql --user=sysadmin --password=mypassword -e "SELECT * FROM EmployeeDB.employees;"
+----+------------+-----------+------------+
| ID | FIRST_NAME | LAST_NAME | DEPARTMENT |
+----+------------+-----------+------------+
| 1 | Lin | Day | HR |
| 2 | Ron | Lee | IT |
| 3 | Mat | Ray | Finance |
+----+------------+-----------+------------+
This way, we’ve restored the original table which has only three entries.
4. Piping SQL Statements to mysql
Another approach for inserting data into a MySQL table is to echo the INSERT INTO statement and pipe the output to the mysql command:
$ echo "INSERT INTO EmployeeDB.employees (ID,FIRST_NAME,LAST_NAME,DEPARTMENT) VALUES ('4','Kim','Zed','Marketing');" |
mysql --user=sysadmin --password=mypassword
$ echo "INSERT INTO EmployeeDB.employees (ID,FIRST_NAME,LAST_NAME,DEPARTMENT) VALUES ('5','Val','Ash','R&D');" |
mysql --user=sysadmin --password=mypassword
$ mysql --user=sysadmin --password=mypassword -e "SELECT * FROM EmployeeDB.employees;"
+----+------------+-----------+------------+
| ID | FIRST_NAME | LAST_NAME | DEPARTMENT |
+----+------------+-----------+------------+
| 1 | Lin | Day | HR |
| 2 | Ron | Lee | IT |
| 3 | Mat | Ray | Finance |
| 4 | Kim | Zed | Marketing |
| 5 | Val | Ash | R&D |
+----+------------+-----------+------------+
Additionally, we can undo the data insertions using the same approach:
$ echo "DELETE FROM EmployeeDB.employees WHERE (ID=4 or ID=5);" |
mysql --user=sysadmin --password=mypassword
$ mysql --user=sysadmin --password=mypassword -e "SELECT * FROM EmployeeDB.employees;"
+----+------------+-----------+------------+
| ID | FIRST_NAME | LAST_NAME | DEPARTMENT |
+----+------------+-----------+------------+
| 1 | Lin | Day | HR |
| 2 | Ron | Lee | IT |
| 3 | Mat | Ray | Finance |
+----+------------+-----------+------------+
This restores the table to its original form.
5. Using a Here-Document
Alternatively, we can use a here-document to specify SQL statements to be executed by the mysql command:
$ mysql --user=sysadmin --password=mypassword << EOF
> INSERT INTO EmployeeDB.employees (ID,FIRST_NAME,LAST_NAME,DEPARTMENT) VALUES ('4','Kim','Zed','Marketing');
> INSERT INTO EmployeeDB.employees (ID,FIRST_NAME,LAST_NAME,DEPARTMENT) VALUES ('5','Val','Ash','R&D');
> EOF
The two data records are inserted, one after the other. Inspecting the table, we see that it now has five entries:
$ mysql --user=sysadmin --password=mypassword -e "SELECT * FROM EmployeeDB.employees;"
+----+------------+-----------+------------+
| ID | FIRST_NAME | LAST_NAME | DEPARTMENT |
+----+------------+-----------+------------+
| 1 | Lin | Day | HR |
| 2 | Ron | Lee | IT |
| 3 | Mat | Ray | Finance |
| 4 | Kim | Zed | Marketing |
| 5 | Val | Ash | R&D |
+----+------------+-----------+------------+
We can also undo the data insertions using a here-document:
$ mysql --user=sysadmin --password=mypassword << EOF
> DELETE FROM EmployeeDB.employees WHERE (ID=4 or ID=5);
> EOF
Let’s verify the result:
$ mysql --user=sysadmin --password=mypassword -e "SELECT * FROM EmployeeDB.employees;"
+----+------------+-----------+------------+
| ID | FIRST_NAME | LAST_NAME | DEPARTMENT |
+----+------------+-----------+------------+
| 1 | Lin | Day | HR |
| 2 | Ron | Lee | IT |
| 3 | Mat | Ray | Finance |
+----+------------+-----------+------------+
We see that the table now has only the three original entries.
6. Using an External File
Another approach for inserting data is to use an external text file directly.
Let’s suppose we want to add data records from a CSV file named data.csv:
$ cat data.csv
4,Kim,Zed,Marketing
5,Val,Ash,R&D
Consequently, we can either loop over each line, adding records one by one, or we can directly load the data into the MySQL table.
Let’s explore both approaches.
6.1. Looping Over Data Records
From each line in data.csv, we extract column entries to build an INSERT INTO statement, which we can then pipe to the mysql command:
$ cat mysql_insert.sh
#!/usr/bin/env bash
IFS=','
cat data.csv | while read id first_name last_name department; do
echo "INSERT INTO EmployeeDB.employees (ID,FIRST_NAME,LAST_NAME,DEPARTMENT) VALUES ('$id','$first_name','$last_name','$department');"
done | mysql --user=sysadmin --password=mypassword
The mysql_insert.sh script sets the Internal Field Separator (IFS) to a comma. Then, as we read each line of the file, we store the column values in variables. We use these variables when constructing the INSERT INTO statements which we finally pipe to the mysql command. This way, we add each record sequentially.
Let’s grant the script execute permissions via chmod and run it:
$ chmod +x mysql_insert.sh
$ ./mysql_insert.sh
$ mysql --user=sysadmin --password=mypassword -e "SELECT * FROM EmployeeDB.employees;"
+----+------------+-----------+------------+
| ID | FIRST_NAME | LAST_NAME | DEPARTMENT |
+----+------------+-----------+------------+
| 1 | Lin | Day | HR |
| 2 | Ron | Lee | IT |
| 3 | Mat | Ray | Finance |
| 4 | Kim | Zed | Marketing |
| 5 | Val | Ash | R&D |
+----+------------+-----------+------------+
Next, let’s undo the insertions via mysql -e to restore the original table.
6.2. Loading Data Directly From a File
Another approach for adding record entries is to simply load the data directly via the LOAD DATA INFILE statement in SQL:
$ mysql --user=sysadmin --password=mypassword EmployeeDB << EOF
> LOAD DATA INFILE '$HOME/data.csv' INTO TABLE employees FIELDS TERMINATED BY ',';
> EOF
In this case, we used a here-document to specify the SQL statement. The statement itself sets the file path to data.csv. In addition, each field has a comma delimiter as specified by the FIELDS TERMINATED BY value.
We can verify that the output now has five records:
$ mysql --user=sysadmin --password=mypassword -e "SELECT * FROM EmployeeDB.employees;"
+----+------------+-----------+------------+
| ID | FIRST_NAME | LAST_NAME | DEPARTMENT |
+----+------------+-----------+------------+
| 1 | Lin | Day | HR |
| 2 | Ron | Lee | IT |
| 3 | Mat | Ray | Finance |
| 4 | Kim | Zed | Marketing |
| 5 | Val | Ash | R&D |
+----+------------+-----------+------------+
Notably, the data file doesn’t need to be a CSV file. However, the delimiter used in the file must be the same as that specified by the FIELDS TERMINATED BY clause.
7. Conclusion
In this article, we explored how to insert values into a MySQL database in Linux. In particular, we examined the mysql -e command and saw how to use here-documents, as well as how to pipe SQL statements to the mysql command for inserting data records into a table.