MySQL ERROR 1366 (HY000)错误咨询及加州住房库处理求助

Hey, I’ve run into this exact headache when importing CSV data into MySQL—let’s break down what’s going on and walk through the fixes that work.

First, the root cause: Your processed CSV has empty strings ('') in columns that are defined as integer types in your MySQL table. By default, MySQL’s strict SQL mode blocks inserting empty strings into integer columns because they don’t match the data type.

Here are the most reliable solutions, ordered from cleanest to quick-and-dirty:

1. Clean the CSV file upfront (best practice) #

Since you’re already using CSVed to trim columns, you can use the same tool to fix empty values:

• Open your California housing CSV in CSVed.
• Select all the integer columns that have empty cells.
• Batch replace empty values with NULL (make sure it’s uppercase—MySQL recognizes this as a null marker for imports).
• Save the cleaned CSV before importing. This ensures your data matches the table schema from the start.

2. Adjust MySQL’s SQL mode temporarily #

If you don’t want to edit the CSV, you can turn off strict mode just for the import session:

1. Log into MySQL via command line:

   mysql -u your_username -p
   

2. First, save your current SQL mode (so you can restore it later):

   SELECT @@sql_mode;
   

3. Disable strict mode for the session and globally:

   SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
   SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
   

4. Run your import command, then restore the original SQL mode using the value you saved earlier.

Note: This is a temporary workaround—strict mode helps enforce data integrity, so don’t leave it off long-term.

3. Modify your table schema to allow NULLs #

Check the model you built in MySQL Workbench:

• If the integer columns with empty values should logically allow missing data (e.g., some housing records don’t have that metric), edit the table properties and uncheck the NOT NULL constraint for those columns.
• This lets MySQL accept NULL values instead of throwing an error when it encounters empty strings during import.

4. Handle empty values directly in the import command #

If you’re using LOAD DATA INFILE to import, you can add logic to convert empty strings to NULL on the fly:

LOAD DATA INFILE '/path/to/your/california_housing.csv'
INTO TABLE your_housing_table
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS -- Skip header row if your CSV has one
(col1, col2, @temp_int_col, col4) -- Map empty column to a temporary variable
SET int_column_name = NULLIF(@temp_int_col, ''); -- Convert empty string to NULL

Replace @temp_int_col and int_column_name with your actual column names. This is great for targeted fixes without modifying the entire CSV.

内容的提问来源于stack exchange，提问作者René Martínez