How to Fix MySQL Error 1406: Data Too Long for Column?

Error Code: 1406 - Truncating Data to Fit Column Length in MySQL

In MySQL, data insertion can fail with the error "1406: Data too long for column" when the inserted value exceeds the defined column width. This occurs because MySQL truncates any value that exceeds the specified column length during insertion.

To resolve this issue, one can adjust the SQL mode to not use STRICT.

Adjusting SQL Mode

The SQL mode can be modified in two ways:

1. Modifying my.ini/my.cnf File

Locate the "sql-mode" line in the MySQL configuration file and modify it to remove the "STRICT" flag. For example:

# Set the SQL mode to strict 
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Replace with:

# Set the SQL mode to strict 
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

2. Running SQL Query

Execute the following SQL query to disable the STRICT mode:

SET @@global.sql_mode= '';

By adjusting the SQL mode, MySQL will no longer truncate values that exceed the column width. However, it's important to note that switching to a non-STRICT mode may compromise data integrity. Consider carefully before implementing this solution.

The above is the detailed content of How to Fix MySQL Error 1406: Data Too Long for Column?. For more information, please follow other related articles on the PHP Chinese website!