How to fix ERROR 1300: Invalid character string in MySQL

-- Check database character set
SHOW CREATE DATABASE `your_database`;

-- Check table character sets
SHOW CREATE TABLE `your_table`;

-- Check all character set variables
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

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

-- Convert specific table
ALTER TABLE `your_table` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Convert specific column
ALTER TABLE `your_table` MODIFY `column_name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

mysqlimport \
  --default-character-set=utf8mb4 \
  --user=your_user \
  --password \
  your_database \
  data_file.txt

LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE `your_table`
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

# Remove non-UTF-8 characters (Linux/Mac)
iconv -f UTF-8 -t UTF-8 -c input_file.csv > cleaned_file.csv

# Remove control characters
sed 's/[[:cntrl:]]//g' input_file.csv > cleaned_file.csv

# Validate file encoding
file input_file.csv
chardet input_file.csv

-- In the dump file, change:
-- SET CHARACTER SET utf8;
-- To:
SET CHARACTER SET utf8mb4;

SELECT VERSION();