MySQL Configuration

See the MySQL Server SQL Mode specs at https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

On Windows:

Edit C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

Change:

sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

To:

sql-mode="ANSI,TRADITIONAL"

Restart the MySQL server (search for services.msc → search for MySQL 8 in the list of services → click restart link)

Note that ProgramData is a hidden folder and that my.ini is read-only for non-admins, so its perms have to be adjusted before editing it. To edit the permissions:

• Right click on my.ini
• Go to Properties
• Go to Security tab
• Click Edit button
• Click Users
• Check "Full Control" in Allow column
• Click OK, OK

On Macs:

Edit: /private/etc/my.cnf (if the file doesn't already exist, you can just create it)

That's specified at System Preferences → MySQL → Configuration tab → Configuration File

Include:

[mysqld]
sql_mode  = "ANSI,TRADITIONAL"

Restart the MySQL server with System Preferences → MySQL → Configuration tab → Stop MySQL Server

The MySQL server has to be restarted after editing my.cnf (or my.ini on Windows; use services.msc to restart it)

Temporary Changes

To just change the settings for the current session, this query can be used:

SET SESSION sql_mode = "ANSI,TRADITIONAL";

And can be checked with:

SELECT @@SESSION.SQL_MODE;

The global settings can be checked with:

SELECT @@GLOBAL.SQL_MODE;

Why?

Given this example in the GROUP BY spec:

CREATE TABLE mytable (
   id INT UNSIGNED NOT NULL PRIMARY KEY,
   a VARCHAR(10),
   b INT
);

INSERT INTO mytable
VALUES (1, 'abc', 1000),
       (2, 'abc', 2000),
       (3, 'def', 4000);

This should return an error:

SELECT a, SUM(b) FROM mytable;

Also,

SELECT "foo";

Will return

Unknown column 'foo' in 'field list'

since strings should be enclosed in single quotes, not double quotes.

And the SQL concatenation operator will work. I.e.,

SELECT 'foo' || 'bar';

Will return

foobar

instead of 0

Notes

Both ANSI and TRADITIONAL are combination modes. Therefore,

sql_mode="ANSI,TRADITIONAL"

is actually the equivalent of:

sql_mode="REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

MySQL Workbench has an error relating to ANSI_QUOTES. If sql_mode contains ANSI_QUOTES, the Reverse Engineering functionality will throw this error for all tables in the selected database: is not valid at this position for this server version, expecting an identifier.

You can get around this error by omitting ANSI_QUOTES:

SET GLOBAL sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,IGNORE_SPACE,ONLY_FULL_GROUP_BY,TRADITIONAL';