How to Make MySQL Truncate Data Instead of Raising an Error on Insert?

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:

<code class="sql">SET SESSION sql_mode=NO_STRICT_TRANS_TABLES,NO_STRICT_ALL_TABLES;</code>

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

The above is the detailed content of How to Make MySQL Truncate Data Instead of Raising an Error on Insert?. For more information, please follow other related articles on the PHP Chinese website!