How to fix ERROR 1366: Incorrect value for column in MySQL

MySQLINTERMEDIATEHIGHUpdated Dec 25, 2025· 253 views

MySQL ERROR 1366 occurs when data cannot be stored in a column due to character set mismatches, type incompatibility, or invalid values. This commonly happens with UTF-8 special characters in latin1 columns, empty strings in integer columns, or numeric precision issues in strict mode.

Quick fix

Fixes 1366

-- The error typically appears in your MySQL error log or application error output
-- Example: ERROR 1366 (HY000): Incorrect integer value: '' for column 'user_id' at row 5
  1. Check the exact error message and column name
  2. Verify your database and table character sets
  3. Convert your database to utf8mb4 (recommended long-term fix)
  4. Set connection character set in your application
mysql — error
-- The error typically appears in your MySQL error log or application error output
-- Example: ERROR 1366 (HY000): Incorrect integer value: '' for column 'user_id' at row 5
ERROR 1366: Incorrect value for column

What this error means

MySQL ERROR 1366 is a data validation error that occurs when the database server rejects a value being inserted or updated because it cannot be stored in the target column. This error typically means that MySQL detects an incompatibility between the incoming data and the column's defined characteristics (character set, data type, or constraints). The error manifests differently depending on the root cause: - **Character set mismatch**: When UTF-8 data (especially emojis or special characters) is being stored in a latin1 or utf8 (3-byte only) column - **Type mismatch**: When non-numeric data is being inserted into numeric columns (INT, DECIMAL, etc.) - **Invalid values**: When invalid dates, out-of-range numbers, or truncated data conflict with column definitions In strict SQL mode (the default in MySQL 8.0+), this error stops the operation entirely. In non-strict mode, MySQL may silently truncate or convert the value, which can mask data corruption.

Typical symptoms

  • INSERT or UPDATE statements fail with "ERROR 1366" message
  • Error message specifies the problematic value and column name (e.g., "Incorrect integer value: '' for column 'user_id'")
  • Special characters, emojis, or Unicode data cause failures in seemingly correct statements
  • Empty strings or NULL values are rejected by integer or numeric columns
  • Data import operations (CSV, SQL dumps) fail at specific rows
  • Application throws database error during form submission or data processing

Common causes

  1. Character set mismatch: Table or columns use latin1 or utf8 (3-byte) charset while data contains 4-byte UTF-8 characters like emojis, Arabic, Chinese, or mathematical symbols
  2. Type incompatibility: Inserting empty strings ('') into INT, DECIMAL, or numeric columns in strict mode (strict mode treats '' as invalid numeric data)
  3. Database/table/column charset inconsistency: Database upgraded to utf8mb4 but individual columns were never updated from older charset
  4. Connection charset mismatch: Application connection uses latin1 while database uses utf8mb4, or vice versa
  5. Numeric precision exceeded: Value exceeds the range or decimal precision defined for DECIMAL/NUMERIC columns
  6. Invalid date/time formats: Malformed date strings (e.g., '2024-13-45') cannot be parsed into DATE or DATETIME columns
  7. String length exceeded: Multi-byte UTF-8 characters consume more bytes than expected, causing varchar overflow

How to fix "ERROR 1366: Incorrect value for column"

1Check the exact error message and column name

The error message contains crucial details. Look for patterns like:
- "Incorrect integer value: ''" - empty string in a numeric column
- "Incorrect string value: '\xF0\x9F\x94\x8D'" - unparseable bytes in the column charset
- "Incorrect value: '2024-13-45'" - invalid date/time format

sql
-- The error typically appears in your MySQL error log or application error output
-- Example: ERROR 1366 (HY000): Incorrect integer value: '' for column 'user_id' at row 5

Note which column is failing and what value was rejected. This tells you whether you're facing a character set issue, type issue, or data validation issue.

2Verify your database and table character sets

Check what character set your database, tables, and columns actually use. Many legacy systems use latin1 which cannot store UTF-8 properly.

sql
-- Check database charset
SELECT schema_name, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE schema_name = 'your_database';

-- Check table charset
SHOW CREATE TABLE your_table;

-- Check specific column charset
SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'your_table';

If you see "latin1", "utf8" (not utf8mb4), or "ascii", these are the likely culprits. Modern MySQL should use utf8mb4 to support all Unicode characters including emojis.

3Convert your database to utf8mb4 (recommended long-term fix)

utf8mb4 is the only MySQL charset that properly implements full UTF-8 encoding. It supports all Unicode characters (including 4-byte ones like emojis) and is the default in MySQL 8.0+.

sql
-- Convert entire database to utf8mb4
ALTER DATABASE your_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Convert specific table (do this for each table that needs it)
ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Verify the conversion succeeded
SHOW CREATE TABLE your_table;

This is a one-time operation. For large tables, it may take time and temporarily lock the table. Consider running during maintenance windows. Backup your database first!

4Set connection character set in your application

Even if your database uses utf8mb4, your application's connection must also use the same character set. Otherwise, data gets corrupted during transmission.

javascript
// Node.js / mysql2
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb',
  charset: 'utf8mb4', // Important!
});

// Or set after connecting
connection.query("SET NAMES utf8mb4");
python
# Python / mysql-connector-python
import mysql.connector
conn = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password",
  database="mydb",
  charset="utf8mb4",  # Important!
  collation="utf8mb4_unicode_ci"
)
php
// PHP / MySQLi
$conn = new mysqli("localhost", "root", "password", "mydb");
$conn->set_charset("utf8mb4");

Always match your application's connection charset to your database charset.

5Handle empty strings and NULL values properly in imports

If you're importing data (CSV, SQL dumps) and hitting ERROR 1366 on numeric columns with empty values, use data cleansing techniques.

sql
-- When importing with LOAD DATA INFILE, use NULLIF to convert empty strings to NULL
LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','
(column1, @user_id_raw, column3)
SET user_id = NULLIF(TRIM(@user_id_raw), '');
bash
# Or pre-process CSV data with sed/awk to remove empty fields
# Replace empty numeric fields with 0 or NULL
sed 's/,,,/,0,0,/' data.csv > data_cleaned.csv

In your application code, validate numeric inputs before sending to MySQL:

javascript
// Node.js example
const userId = input.user_id?.trim() || null;
if (userId && isNaN(userId)) {
  throw new Error('user_id must be numeric or empty');
}
await db.query('INSERT INTO users (user_id) VALUES (?)', [userId]);

Advanced notes

Strict SQL Mode vs. Lenient Mode: In strict mode (default in MySQL 5.7+), ERROR 1366 is thrown immediately. In lenient mode, MySQL truncates or converts the value and issues a warning instead. For data integrity, always use strict mode.

utf8 vs. utf8mb4 Confusion: MySQL's "utf8" charset is NOT true UTF-8—it only supports 3-byte characters. It cannot store emojis or other 4-byte Unicode sequences. Use utf8mb4 for full Unicode support. Many developers waste hours debugging this.

Collation Implications: When changing charset, also update collation (utf8mb4_unicode_ci for case-insensitive, utf8mb4_bin for binary). Wrong collation can affect sorting and comparisons.

Large Table Conversions: Converting a 1GB+ table to utf8mb4 may lock it for extended periods. Use online DDL tools (Percona Toolkit, pt-online-schema-change) for zero-downtime migrations on production systems.

Backward Compatibility: If you're supporting legacy systems that send latin1-encoded data, consider a middleware layer that re-encodes data to utf8mb4 before inserting. This is safer than converting your entire database.

Frequently asked questions

What does "1366" mean?
MySQL ERROR 1366 is a data validation error that occurs when the database server rejects a value being inserted or updated because it cannot be stored in the target column. This error typically means that MySQL detects an incompatibility between the incoming data and the column's defined characteristics (character set, data type, or constraints). 0+), this error stops the operation entirely.
How do I fix "1366"?
MySQL ERROR 1366 occurs when data cannot be stored in a column due to character set mismatches, type incompatibility, or invalid values. This commonly happens with UTF-8 special characters in latin1 columns, empty strings in integer columns, or numeric precision issues in strict mode.
What causes "1366"?
Character set mismatch: Table or columns use latin1 or utf8 (3-byte) charset while data contains 4-byte UTF-8 characters like emojis, Arabic, Chinese, or mathematical symbols Type incompatibility: Inserting empty strings ('') into INT, DECIMAL, or numeric columns in strict mode (strict mode treats '' as invalid numeric data) Database/table/column charset inconsistency: Database upgraded to utf8mb4 but individual columns were never updated from older charset
Is the "1366" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading