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

Bash Script Solution for Inserting Values into MySQL

When attempting to insert values into MySQL using a bash script, users may encounter an error indicating a mismatch between column count and value count. This error typically arises when the input data format does not align with the expected columns in the database table. Consider the following example:

The script below aims to insert values from a text file into a MySQL table named 'test':

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

However, upon execution, the script returns an error:

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

To resolve this issue, verify the data format in the text file 'test.txt'. If the data is not formatted correctly, it will lead to a discrepancy between the expected number of columns in the MySQL table and the values being inserted.

Here's a revised script that takes a different approach:

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

This script iterates over each line in the text file and extracts the values for each column (IP, MAC, SERVER). It then constructs an SQL statement for each row and pipes it to the MySQL command, ensuring that the column count and value count match.

By modifying the script and properly formatting the input data in the text file, you can successfully insert values into the MySQL table using a bash script.

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