When to use strict mode in MySql or MariaDB

Question

I can find plenty of information on what strict mode does in MySql and MariaDB, but nothing on when to use it. This is common sense to a degree, but I would still like to have some general guidelines. For example, perhaps you:

• Always use strict mode
• Never use strict mode
• Always use strict mode on tables that have financial data
• etc

Answer 1

First, from MySQL documentation:

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.) Strict mode also affects DDL statements such as CREATE TABLE.

So, as @rick-james said: Always use strict mode. That is, until you get fed up with its restrictions. The mode is there to help you, but it may be painful.

Strict mode is also default on MariaDB since >10.2.3.

• Get current mode: SHOW VARIABLES LIKE 'sql_mode';
• Disable: mysql> SET sql_mode = '';
• Enable: mysql> SET sql_mode = 'STRICT_ALL_TABLES'; (or STRICT_TRANS_TABLES).

For permanent change edit /etc/mysql/my.conf, [mysqld] section, sql_mode= variable.