📋

Description

This error occurs during an `INSERT` or `REPLACE` statement when the number of values provided does not match the number of columns specified (either explicitly or implicitly). It indicates a fundamental data integrity issue where the database expects a certain quantity of data for a row but receives a different amount.

💬

Error Message

Column count doesn't match value count at row %ld

🔍

Known Causes

4 known causes

⚠️

Missing Values for Columns

You have provided fewer values in your `INSERT` statement than there are columns listed or implied for the table.

⚠️

Excess Values Provided

Your `INSERT` statement contains more values than the number of columns explicitly listed or implicitly expected by the table structure.

⚠️

Implicit Column Mismatch

When no columns are explicitly listed in an `INSERT` statement, MySQL expects values for *all* columns in the table, and the count of values provided does not match the total column count.

⚠️

Dynamic Query Construction Error

Errors in dynamically generating SQL queries can lead to an incorrect number of columns or values being passed to the `INSERT` statement.

🛠️

Solutions

5 solutions available

1. Match Column Count with Values easy

Ensure INSERT has same number of columns and values

1

Count columns vs values

-- Wrong (3 columns, 2 values):
INSERT INTO users (id, name, email) VALUES (1, 'John');

-- Right:
INSERT INTO users (id, name, email) VALUES (1, 'John', 'john@test.com');

2

Check table structure if not specifying columns

-- If using INSERT without column list:
DESCRIBE users;  -- See how many columns exist

-- Wrong (table has 4 columns):
INSERT INTO users VALUES (1, 'John', 'john@test.com');

-- Right:
INSERT INTO users VALUES (1, 'John', 'john@test.com', NOW());

2. Specify Column List Explicitly easy

Always list columns for safer inserts

1

Add column names to INSERT

-- Better practice - explicit columns:
INSERT INTO users (name, email) VALUES ('John', 'john@test.com');

2

Skip auto-increment columns

-- Don't include auto-increment id:
INSERT INTO users (name, email, created_at) 
VALUES ('John', 'john@test.com', NOW());

3. Fix Multi-Row INSERT easy

Each value set must have same column count

1

Ensure consistent values per row

-- Wrong (inconsistent value counts):
INSERT INTO users (id, name, email) VALUES 
  (1, 'John', 'john@test.com'),
  (2, 'Jane'),  -- Missing email!
  (3, 'Bob', 'bob@test.com');

-- Right:
INSERT INTO users (id, name, email) VALUES 
  (1, 'John', 'john@test.com'),
  (2, 'Jane', 'jane@test.com'),
  (3, 'Bob', 'bob@test.com');

4. Use NULL or DEFAULT for Missing Values easy

Provide placeholder for missing data

1

Use NULL for optional columns

INSERT INTO users (id, name, email, phone) 
VALUES (1, 'John', 'john@test.com', NULL);

2

Use DEFAULT keyword

INSERT INTO users (id, name, email, status) 
VALUES (1, 'John', 'john@test.com', DEFAULT);

5. Fix INSERT ... SELECT medium

SELECT columns must match INSERT columns

1

Match column counts

-- Wrong:
INSERT INTO new_users (id, name, email)
SELECT id, name FROM old_users;  -- Missing email!

-- Right:
INSERT INTO new_users (id, name, email)
SELECT id, name, email FROM old_users;

2

Add constants for missing columns

INSERT INTO new_users (id, name, email, status)
SELECT id, name, email, 'active' FROM old_users;

🔗

Related Errors

5 related errors

MySQL Error 1136: Mismatching Column and Value Counts