SQL Modes and Strict Mode

sql_mode sets how forgiving the server is. MySQL 9.7 524 defaults to six modes, among them STRICT_TRANS_TABLES, ONLY_FULL_GROUP_BY (GROUP BY, HAVING, and ROLLUP) and the zero-date and division-by-zero checks. Strict mode rejects invalid, oversized or missing values instead of adjusting them with a warning:

Bad values in strict mode, then with sql_mode clearedSQL

INSERT INTO customers (email, name, country) VALUES ('lena@example.com', 'Lena Gruber', 'AUT');
INSERT INTO customers (email, country) VALUES ('jo@example.com', 'NZ');
INSERT INTO orders (customer_id, ordered_at) VALUES (2, '2026-02-30 10:00:00');
SET SESSION sql_mode = '';
INSERT INTO customers (email, name, country) VALUES ('lena@example.com', 'Lena Gruber', 'AUT');
SELECT name, country FROM customers WHERE email = 'lena@example.com';

Output

ERROR 1406 (22001): Data too long for column 'country' at row 1
ERROR 1364 (HY000): Field 'name' doesn't have a default value
ERROR 1292 (22007): Incorrect datetime value: '2026-02-30 10:00:00' for column 'ordered_at' at
  row 1
Query OK, 0 rows affected (0.000 sec)
Query OK, 1 row affected, 1 warning (0.013 sec)
+-------------+---------+
| name        | country |
+-------------+---------+
| Lena Gruber | AU      |
+-------------+---------+
1 row in set (0.000 sec)

Without strict mode, Austria's AUT was cut to AU, which is Australia, with only a warning. The other two inserts also succeed in a lax session: Jo's name becomes an empty string, and 30 February is stored as 0000-00-00 00:00:00 (warning 1264). Nothing fails, so nobody notices until a report is wrong.

Keep the defaults. Relax a mode only per session, for a legacy import, and restore it with SET SESSION sql_mode = DEFAULT. The zero-date and division modes are deprecated but still on. Laravel 2,157 sets strict mode on its connections ('strict' => true, Laravel).

MENU