mysqlmariadbsql-mode

Truncate on INSERT as an exception while in strict mode?

I'm using MariaDB 10.2.4. By default, sql_mode includes STRICT_TRANS_TABLES, which is good for the most part. But in specific cases I would like the data to just be truncated. Is there a way to override the sql_mode temporarily, or an option for the INSERT statement to explicitly permit truncation? I can't find anything in documentation or by googling.

You might be tempted to think this is a duplicate of Should I disable MySQL strict mode?, but that question was in 2011, so there might be new options since then. Plus, that question was about best practices regarding the mode in general, not about the ability to override in runtime.

Solution

  • Is there a way to override the sql_mode temporarily

    Yes, you can change this by firing a SET sql_mode = 'mode'; statement before your INSERT.

    https://mariadb.com/kb/en/mariadb/sql-mode/#setting-sql_mode:

    The session value only affects the current client, and can be changed by the client when required.

    So if you set it back right after, you can be sure that it takes effect for this specific INSERT statement only.