MySQL Compares Text Without Regard to Letter Case and PostgreSQL Compares It Exactly

Sign-ups as [email protected] and [email protected] are two users on PostgreSQL and SQLite. On MySQL and MariaDB a unique key on the email column refuses the second sign-up.

The default collations of MySQL and MariaDB ignore case and accents, so 'abc' = 'ABC' and 'e' = 'é' are true there and false on SQLite and PostgreSQL. The same goes for joins, GROUP BY, DISTINCT and unique keys: two values that differ only in case count as one on MySQL and as two elsewhere. Whether an answer changes depends on the data, so lint reports this as information.

What Each Engine Answered

The example

SELECT 'abc' = 'ABC';

gave these answers:

What to Write Instead

This form answers the same on every engine:

SELECT LOWER('abc') = LOWER('ABC');

Compare LOWER(a) = LOWER(b) where case should not matter, and store values in one case. No form makes MySQL compare exactly without naming a collation, and collation names differ on every engine.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:1:8: info: MySQL and MariaDB compare text without regard to letter case or accents by default; SQLite and PostgreSQL compare it exactly. [text-compare-case]
    'abc' = 'ABC'
    Compare LOWER(a) = LOWER(b) where case should not matter, and store values in one case. No form makes MySQL compare exactly without naming a collation, and collation names differ on every engine.

1 problem: 1 note.

baresql rules text-compare-case prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.