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

-- 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

-- 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';

-- 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;

// 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 / 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 / MySQLi
$conn = new mysqli("localhost", "root", "password", "mydb");
$conn->set_charset("utf8mb4");

-- 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), '');

# 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

// 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]);