📋

Description

MySQL Error 1058 indicates that an INSERT statement is attempting to provide a different number of values than the number of columns specified or implied in the target table. This error prevents data insertion because the database cannot correctly map the provided data to the table's defined structure.

💬

Error Message

Column count doesn't match value count

🔍

Known Causes

3 known causes

⚠️

Missing Values for Defined Columns

You have explicitly listed more columns in your INSERT statement than the number of values provided in the VALUES clause.

⚠️

Excess Values for Defined Columns

The INSERT statement provides more data values than the number of columns explicitly listed or implicitly available in the target table.

⚠️

Implicit Insert Mismatch

For INSERT statements that do not specify column names, the number of values must precisely match the total number of columns in the target table.

🛠️

Solutions

4 solutions available

1. Verify Column and Value Counts in INSERT Statements easy

Ensure the number of columns specified matches the number of values provided in INSERT statements.

1

Examine your `INSERT` statement. Count the number of columns listed in the `(column1, column2, ...)` part.

2

Now, count the number of values provided in the `VALUES (value1, value2, ...)` part.

3

If the counts do not match, adjust either the column list or the value list to make them equal. If you intend to insert into all columns, you can often omit the column list entirely.

UPDATE your_table SET column1 = 'value1', column2 = 'value2' WHERE condition;
-- or if inserting all columns:
INSERT INTO your_table VALUES ('value1', 'value2', 'value3');

2. Check Column Order and Values in INSERT Statements Without Explicit Columns easy

If you omit the column list in an INSERT, ensure the values are provided in the exact order of the table's columns.

1

Execute a `DESCRIBE` or `SHOW COLUMNS` command on your table to see the current column order.

DESCRIBE your_table;
-- or
SHOW COLUMNS FROM your_table;

2

Compare the order of columns returned by the `DESCRIBE` command with the order of values in your `INSERT` statement.

3

Rearrange the values in your `INSERT` statement to match the column order obtained in the previous step. Alternatively, explicitly list the columns to avoid this issue.

INSERT INTO your_table (column_a, column_b, column_c) VALUES ('value_for_a', 'value_for_b', 'value_for_c');

3. Review Stored Procedures or Functions for Mismatched Counts medium

Inspect stored procedures, functions, and triggers for INSERT or UPDATE statements with incorrect column/value counts.

1

Identify any stored procedures, functions, or triggers that perform `INSERT` or `UPDATE` operations on the affected table.

SHOW PROCEDURE STATUS WHERE Db = 'your_database';
SHOW FUNCTION STATUS WHERE Db = 'your_database';
SHOW TRIGGERS FROM your_database;

2

For each identified object, retrieve its definition using `SHOW CREATE PROCEDURE`, `SHOW CREATE FUNCTION`, or `SHOW CREATE TRIGGER`.

SHOW CREATE PROCEDURE your_procedure_name;
SHOW CREATE FUNCTION your_function_name;
SHOW CREATE TRIGGER your_trigger_name;

3

Carefully examine the `INSERT` or `UPDATE` statements within the object's definition for discrepancies between the number of columns and values, similar to Solution 1.

4

Modify the object's definition to correct the column/value count mismatch and then `DROP` and `CREATE` the object again with the corrected definition.

DELIMITER $$
CREATE PROCEDURE your_procedure_name(...)
BEGIN
  -- Corrected INSERT statement here
END $$
DELIMITER ;

-- After creating, drop and recreate if necessary to apply changes.

4. Validate Data Loading Scripts or ORM Configurations medium

Ensure scripts or Object-Relational Mappers (ORMs) correctly map data to table columns.

1

If you are using a script (e.g., Python, PHP) or an ORM (e.g., SQLAlchemy, Hibernate, Eloquent) to insert data, examine the code responsible for constructing the `INSERT` statements.

2

Verify that the number of fields or attributes being populated in your code corresponds to the number of columns in your MySQL table.

// Example in Python with a hypothetical ORM:
user_data = {'username': 'test_user', 'email': 'test@example.com'}
# Ensure user_data keys match ORM model fields which map to table columns.

3

Pay close attention to how the data is being mapped. If you're dynamically generating SQL, ensure that the generated `INSERT` statement has a matching number of columns and values.

4

Adjust your script or ORM configuration to align the data structure with the table's schema.

🔗

Related Errors

5 related errors

MySQL Error 1058: Mismatched Column and Value Count