MySQL Insert Behavior: Truncation vs Error

MySQL exhibits varying behaviors when attempting to insert data that exceeds the column length limit: truncation or error. In this scenario, we aim to modify a MySQL instance to truncate data instead of raising an error.

Solution: Disable STRICT_TRANS_TABLES and STRICT_ALL_TABLES

By default, MySQL enforces strict mode, which includes the settings STRICT_TRANS_TABLES and STRICT_ALL_TABLES. These settings prohibit invalid or missing values in data-change operations like INSERT or UPDATE.

To allow automatic truncation of inserted strings, we can disable STRICT_TRANS_TABLES and STRICT_ALL_TABLES. Here's how to do it:

SET SESSION sql_mode=NO_STRICT_TRANS_TABLES,NO_STRICT_ALL_TABLES;

Explanation:

By disabling these settings, we remove the restriction on invalid or missing values. This allows MySQL to silently truncate data that exceeds the column length limit, adhering to the default behavior of truncation.

Reference:

MySQL Server SQL Modes: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html