How to Fix 'Column count doesn't match value count' Error When Inserting Data into MySQL from a Bash Script?

Inserting Values into MySQL Using a Bash Script

You have created a Bash script that aims to connect to a MySQL server and insert data from a text file. However, you have encountered an error in the execution of the script, specifically:

ERROR 1136 (21S01) at line 1: Column count doesn't match value count at row 1

The issue lies in the structure of your command:

echo "INSERT INTO test (IP,MAC,SERVER) VALUES ('cat test.txt');" | mysql -uroot -ptest test;

This command essentially tries to insert the entire contents of the test.txt file as a single value into the test table. Since your test table has three columns (IP, MAC, SERVER), this results in a mismatch between the number of values (1) and the number of columns (3).

To resolve this issue, you will need to modify your Bash script to treat each line of the text file as a separate set of values to be inserted into the database. Here's a revised version of your script:

#!/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;

In this modified script:

• The while loop reads each line from the test.txt file, assigning the values of the three fields (IP, MAC, and SERVER) to the variables $ip, $mac, and $server.
• The values are then formatted into a string that represents the SQL INSERT statement.
• Finally, the mysql command is executed repeatedly, with each iteration inserting a new row into the test table.

By streaming the file read and the execution of the MySQL command separately, you can ensure that each line in the text file is correctly inserted as a distinct row in the database.

The above is the detailed content of How to Fix 'Column count doesn't match value count' Error When Inserting Data into MySQL from a Bash Script?. For more information, please follow other related articles on the PHP Chinese website!