The Dump - Code and Beyond > Insert Records into MySQL Table Directly with Bash

Insert Records into MySQL Table Directly with Bash

If you've ever needed to quickly insert records into a MySQL table, doing it directly from a Bash script can save time and effort. This guide will walk you through the process with a real-world example: inserting coupon codes into a MySQL table.

Example Scenario

We have a file, `coupons.txt`, containing coupon codes that looks something like this:

xxxTTv8
8WxyZm

Each line in the file represents a separate coupon value.

Step 1: Log in to MySQL

To get started, log in to MySQL from the terminal:

$ mysql -u root -p

Enter your MySQL password when prompted.

Once inside MySQL, navigate to the desired database:

mysql> USE <database_name>;

Step 2: Prepare the MySQL Table

Ensure the table where you want to insert the records exists. Here's an example schema for a coupons table:

CREATE TABLE IF NOT EXISTS coupons (
 id INT AUTO_INCREMENT PRIMARY KEY,
 coupon VARCHAR(255) NOT NULL,
 used TINYINT(1) DEFAULT 0,
 sent_date DATETIME DEFAULT NULL,
 used_date DATETIME DEFAULT NULL
);

Step 3: The bash script

Save the following script to a file, e.g., `insert_coupons.sh`:

#!/bin/bash

# Database credentials
DB_USER="your_username"
DB_PASS="your_password"
DB_NAME="your_database"

# Read coupons from file and insert into MySQL
while IFS= read -r coupon; do
 mysql -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "
 INSERT INTO coupons (coupon) VALUES ('$coupon');
 "
done < "coupons.txt"

Explanation:

DB_USER, DB_PASS, and DB_NAME should be replaced with your database credentials.
The script reads each line from `coupons.txt` and inserts it into the `coupons` table using the INSERT command.

Step 4: Run the Script

To execute the script, follow these steps:

Make the script executable:
```
$ chmod +x insert_coupons.sh
```
Run the script:
```
$ ./insert_coupons.sh
```

Step 5: Verify the Inserted Data

SELECT * FROM rt_coupons LIMIT 10;

This will display the first 10 rows from the rt_coupons table.

Conclusion

This method is efficient for inserting data from text files into a MySQL table using a Bash script. It’s especially useful for bulk data inserts or automation tasks. With the script in place, you can easily adapt it for other similar tasks by modifying the table schema or input file.

Happy scripting!

4. Disclaimer

As far as I know, the script within this page is functional and harmless. However I make no guarantees. I also take no responsibility for any damage you may do when using the script (the chances of any problems occuring are slim but who knows). Please use this script responsibly and judgementally.