📋

Description

Error 1058 indicates that an `INSERT` statement attempts to provide a number of values that does not match the number of columns targeted for insertion. This typically occurs when inserting new rows into a table, preventing the successful completion of the data insertion operation.

💬

Error Message

Column count doesn't match value count

🔍

Known Causes

3 known causes

⚠️

Incomplete Column List

You have explicitly listed a certain number of columns in your `INSERT` statement, but the `VALUES` clause provides a different count of data elements.

⚠️

Mismatched Value Count

The number of values in the `VALUES` clause does not correspond to the total number of columns in the table, especially when no column list is explicitly provided.

⚠️

SELECT Statement Column Discrepancy

When using `INSERT INTO ... SELECT FROM ...`, the `SELECT` query returns a different number of columns than the target table or the explicitly listed columns in the `INSERT` part.

🛠️

Solutions

3 solutions available

1. Verify Column and Value Counts easy

Ensure the number of columns in your INSERT statement matches the number of values provided.

1

Examine your `INSERT` statement carefully. Count the number of columns listed after the table name (e.g., `INSERT INTO my_table (col1, col2, col3) ...`).

SELECT 'Count the columns here'

2

Now, count the number of values provided within the `VALUES` clause (e.g., `VALUES (val1, val2, val3)`).

SELECT 'Count the values here'

3

Adjust either the column list or the value list so that the counts match exactly. If you intend to insert values for all columns, you can often omit the column list, assuming the order of values matches the table's column order.

INSERT INTO my_table (col1, col2, col3) VALUES ('value1', 'value2', 'value3');
-- OR if inserting for all columns in order:
INSERT INTO my_table VALUES ('value1', 'value2', 'value3');

2. Handle NULL Values and Defaults medium

Account for columns that allow NULL or have default values when constructing your INSERT statement.

1

Determine which columns in your target table can accept `NULL` values or have `DEFAULT` values defined. You can check this using `DESCRIBE` or `SHOW COLUMNS`.

DESCRIBE my_table;
-- OR
SHOW COLUMNS FROM my_table;

2

If a column allows `NULL` and you don't want to provide a value for it in your `INSERT` statement, either omit the column from the column list and the corresponding value from the values list, or explicitly provide `NULL` as the value.

INSERT INTO my_table (col1, col3) VALUES ('value1', 'value3'); -- Omitting col2
-- OR
INSERT INTO my_table (col1, col2, col3) VALUES ('value1', NULL, 'value3'); -- Explicitly NULL

3

If a column has a `DEFAULT` value, you can omit it from the `INSERT` statement, and the database will automatically assign its default. If you want to use the default value explicitly, you can use the `DEFAULT` keyword in the `VALUES` clause.

INSERT INTO my_table (col1, col3) VALUES ('value1', 'value3'); -- col2 will get its default
-- OR
INSERT INTO my_table (col1, col2, col3) VALUES ('value1', DEFAULT, 'value3');

3. Dynamically Generate INSERT Statements advanced

Use scripting to dynamically build INSERT statements, reducing manual errors.

1

If you are programmatically generating `INSERT` statements (e.g., from application code or scripts), ensure your logic correctly counts and maps columns to values. Use a language like Python, PHP, or Node.js to fetch column names and then construct the SQL query.

import mysql.connector

config = {
  'user': 'your_user',
  'password': 'your_password',
  'host': 'your_host',
  'database': 'your_database'
}

try:
    cnx = mysql.connector.connect(**config)
    cursor = cnx.cursor()

    cursor.execute("DESCRIBE my_table;")
    columns = [col[0] for col in cursor.fetchall()]

    values_to_insert = ['value1', 'value2', 'value3'] # Example values

    if len(columns) != len(values_to_insert):
        print(f"Error: Column count ({len(columns)}) does not match value count ({len(values_to_insert)}).")
    else:
        placeholders = ', '.join(['%s'] * len(columns))
        column_names = ', '.join(columns)
        sql = f"INSERT INTO my_table ({column_names}) VALUES ({placeholders})"
        cursor.execute(sql, values_to_insert)
        cnx.commit()
        print("Insert successful.")

except mysql.connector.Error as err:
    print(f"Error: {err}")

finally:
    if cnx.is_connected():
        cursor.close()
        cnx.close()

2

Implement error handling in your code to catch discrepancies between the number of columns retrieved from the database schema and the number of values intended for insertion.

if len(columns) != len(values_to_insert):
    raise ValueError(f"Mismatched column and value counts. Expected {len(columns)}, got {len(values_to_insert)}.")

🔗

Related Errors

5 related errors

MariaDB Error 1058: Mismatched Column and Value Counts