Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 75 ยท Getting Started with MySQL

SQL Modes and Strictness

Learn what MySQL sql_mode does: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_DATE and ANSI_QUOTES, why strict mode matters, and how to set it.

MySQL has one setting that changes how forgiving it is: sql_mode. It decides whether bad data is rejected with an error or quietly "fixed" with a warning, whether ambiguous GROUP BY queries are allowed, and even what double quotes mean. Old MySQL versions defaulted to a very lenient mode, which is why you will still read stories of data silently truncated or invalid dates stored as zeros. Modern MySQL defaults to a strict mode, and keeping it that way is one of the best decisions you can make.

The statements that read and set sql_mode need a MySQL server, so they are read-only blocks here. The behaviors themselves are shown with live SQLite demos that enforce the same rules by hand using CHECK constraints and explicit queries.

What sql_mode is

sql_mode is a comma-separated list of flags. You can read it at three levels:

SQL
SELECT @@GLOBAL.sql_mode;    -- the server default for new connections
SELECT @@SESSION.sql_mode;   -- this connection's current value

On a fresh MySQL 8.4 server the value is:

TEXT
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Each flag turns a rule on. You rarely need to know them all, but four of them shape your daily work.

Four flags that matter

ONLY_FULL_GROUP_BY No ambiguous grouped columns.
STRICT_TRANS_TABLES Bad values fail, not truncate.
NO_ZERO_DATE No 0000-00-00 dates.
ANSI_QUOTES Double quotes name things.

The first three are on by default.

1 / 2

ONLY_FULL_GROUP_BY

When you group rows, every selected column must either be in the GROUP BY, be wrapped in an aggregate such as MAX(), or be functionally determined by the grouped columns. Otherwise the database would have to guess which row's value to show. With the flag on, MySQL refuses with error 1055:

SQL
SELECT author, title, COUNT(*)
FROM books
GROUP BY author;
-- ERROR 1055: Expression #2 of SELECT list is not in GROUP BY clause
-- and contains nonaggregated column 'books.title' ... incompatible with
-- sql_mode=only_full_group_by

Which title should be printed for an author with three books? There is no right answer. Without the flag, MySQL returned an arbitrary one, so reports looked fine and were wrong. The fix is usually to change the query, but when you truly do not care which value you get, ANY_VALUE() states that explicitly: SELECT author, ANY_VALUE(title), COUNT(*) ....

SQLite is as lenient as the old MySQL here, which makes it a good place to see the danger. Run the query: SQLite accepts it and picks a value:

SQL The ambiguity ONLY_FULL_GROUP_BY prevents

Press Run (or Ctrl + Enter) to see the result here.

For Mara you get one title out of three, with no hint that the other two exist. A strict MySQL server would stop you at the query. The correct versions state the intent: MIN(title) for the alphabetically first one, or GROUP_CONCAT(title) for all of them, which the GROUP BY lesson covers.

STRICT_TRANS_TABLES

This flag decides what happens when an INSERT or UPDATE supplies a value that does not fit its column. In strict mode the statement fails with an error and changes nothing (for a transactional table such as InnoDB). Without strict mode, MySQL "adjusts" the value, issues a warning and carries on:

Bad input Strict mode Non-strict mode
'abc' into an INT column Error 1366, row rejected Stored as 0, warning
300 into a TINYINT UNSIGNED Error 1264, out of range Stored as 255, warning
15 characters into VARCHAR(10) Error 1406, data too long Cut to 10 characters, warning
Omitting a NOT NULL column with no default Error 1364 Stored as '' or 0, warning
SQL
CREATE TABLE tags (name VARCHAR(5) NOT NULL);
INSERT INTO tags VALUES ('database');
-- Strict:     ERROR 1406 (22001): Data too long for column 'name' at row 1
-- Non-strict: Query OK, 1 row affected, 1 warning  (stored as 'datab')

The non-strict outcome is data corruption: you asked to store database and got datab. The warning is easy to miss, because most application drivers do not look at warnings. That is the entire case for strict mode: you want the error.

A related flag, ERROR_FOR_DIVISION_BY_ZERO, makes an insert or update that divides by zero fail instead of storing NULL.

SQLite has no strict mode for plain tables. You must recreate the rules yourself, for example with CHECK constraints, which both systems support (MySQL enforces them since 8.0.16). This live demo makes a table reject the bad value, which is what strict mode does automatically for length and range:

SQL Rejecting too-long values with CHECK

Press Run (or Ctrl + Enter) to see the result here.

The first two inserts succeed. The third fails with CHECK constraint failed, and nothing is stored. This is the behavior you get from VARCHAR(5) in a strict MySQL server, and what you do not get in a lenient one.

STRICT_TRANS_TABLES applies the rules to transactional engines (InnoDB). The stricter STRICT_ALL_TABLES also covers non-transactional engines such as MyISAM, where a multi-row insert could otherwise stop halfway with some rows already stored. With InnoDB as the default, the default flag is enough.

NO_ZERO_DATE and NO_ZERO_IN_DATE

Before strict defaults, MySQL allowed the "zero date" 0000-00-00, which is not a real date, and dates with a zero month or day such as 2026-00-15. They appeared whenever a conversion failed. NO_ZERO_DATE rejects the first kind and NO_ZERO_IN_DATE rejects the second:

SQL
CREATE TABLE events (starts DATE NOT NULL);
INSERT INTO events VALUES ('0000-00-00');
-- ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'starts'
INSERT INTO events VALUES ('2026-02-30');
-- ERROR 1292: February 30 is not a date, rejected in strict mode

Zero dates break application code, because most date libraries cannot represent them. If you inherit an old database full of them, you will meet this flag the day you restore it onto a modern server.

ANSI_QUOTES and other flags

By default, double quotes in MySQL delimit strings, like single quotes, and identifiers use backticks. So SELECT "hello" returns the text hello, and SELECT "name" FROM users returns the literal text name for every row, not the column. With ANSI_QUOTES, double quotes become identifier quotes, as in standard SQL:

SQL
SET SESSION sql_mode = CONCAT(@@SESSION.sql_mode, ',ANSI_QUOTES');
SELECT "name" FROM users;   -- now this reads the column

Standard-minded code and tools that generate SQL for several databases sometimes need this mode. Plain MySQL tutorials and ORMs normally use backticks and leave it off. Two more flags you will see in the wild:

  • PIPES_AS_CONCAT makes || concatenate strings (as in standard SQL and SQLite) instead of acting as logical OR.
  • ANSI is a shortcut combination: REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE and ONLY_FULL_GROUP_BY.

SQLite accepts double-quoted identifiers, and it demonstrates the difference with ||. This is a trap for portable code: the live query uses the standard operator, while the MySQL default would read it as OR and return 0 or 1.

SQL || means concatenation in standard SQL

Press Run (or Ctrl + Enter) to see the result here.

SQLite returns MySQL. In default MySQL, 'My' || 'SQL' would evaluate to 0 because both strings convert to the number 0 and are ORed. Use CONCAT('My', 'SQL') for code that must run everywhere. (SQLite also prints hello for the second column as a fallback, because it treats an unknown double-quoted name as a string. That is another leniency, and it is the reason to prefer single quotes for strings.)

Setting the mode

Change the session value when you need to for a single connection, for example while testing a legacy script:

SQL
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

To change it for the whole server, set it in my.cnf under [mysqld] and restart, or with MySQL 8's SET PERSIST:

INI
[mysqld]
sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
SQL
SET PERSIST sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

SET PERSIST writes the value to a file the server reads at startup, so it survives restarts without editing my.cnf. Remember that sessions that are already open keep their old session value; only new connections get the new global one.

Do not "fix" an application error by turning strict mode off. The error is the server telling you that your data or query is wrong. Disabling strictness hides the problem, and you will find it later as corrupted data. Fix the query or the value instead. Also keep every environment (laptop, CI, staging, production) on the same sql_mode, or code will pass on one and fail on another.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does STRICT_TRANS_TABLES change?

    In strict mode a bad value makes the statement fail. Without it MySQL truncates or converts the value and only issues a warning.
  2. 2. Why does SELECT author, title, COUNT(*) FROM books GROUP BY author fail under ONLY_FULL_GROUP_BY?

    Each author has several titles and the query does not say which one to show. Use an aggregate such as MIN(title), ANY_VALUE(title) or add the column to GROUP BY.
  3. 3. With the default sql_mode (no ANSI_QUOTES), what does SELECT "name" FROM users return?

    Double quotes delimit strings in default MySQL. With ANSI_QUOTES they become identifier quotes and the statement would read the column.
  4. 4. Which is the best response to an application error Data too long for column?

    The error means the data does not fit. Fix the data or the schema; turning strict mode off would silently truncate the value instead.

Summary

  • sql_mode is a list of flags that sets how strict MySQL is. Read it with SELECT @@SESSION.sql_mode;.
  • ONLY_FULL_GROUP_BY rejects ambiguous grouped queries; ANY_VALUE() says you do not care which value is shown.
  • STRICT_TRANS_TABLES turns silent truncation and conversion into errors, and the NO_ZERO_* flags reject impossible dates.
  • ANSI_QUOTES makes double quotes identifier quotes; || is OR in default MySQL, so use CONCAT() for portability.
  • Set the mode per session with SET SESSION, or permanently in my.cnf or with SET PERSIST. Keep all environments identical, and keep strict mode on.

Next: Numeric Types.