In a Rails 8.1 app on MySQL, why does database.yml set encoding: utf8mb4, and what does adding collation: change?
answer
- four bytes per character, not three
- SET NAMES on every connection
- _ci collations ignore case
- unique index treats Ann and ann alike
- case_sensitive: true forces BINARY
basics
~20 sutf8mb4 is MySQL's full UTF-8, so emoji and other four-byte characters survive; Rails sends SET NAMES utf8mb4 on each connection. collation: adds COLLATE to that, choosing how strings compare and sort, and the common _ci collations are case-insensitive.
solid answer
~40 sThe generated `config/database.yml` for MySQL has `encoding: utf8mb4`, and the adapter turns it into `SET NAMES utf8mb4` on every new connection. MySQL's older `utf8` stores at most three bytes per character, so emoji in a guest's note would be rejected or mangled; utf8mb4 holds every Unicode character, and `create_database` also defaults the database to utf8mb4. Adding `collation:` (for example `utf8mb4_0900_ai_ci`) appends `COLLATE` to that statement; a column can also take `collation:` in a migration. Collation decides comparison: names ending `_ci` are case-insensitive, so `where(email: "[email protected]")` finds `[email protected]` and a unique index rejects both. Rails' uniqueness validator follows the column's collation unless you pass `case_sensitive: true`, which compares with `BINARY`.
go deeper
Recall that encoding: utf8mb4 is full UTF-8 for MySQL and that _ci collations compare without case.
Explain how SET NAMES carries encoding and collation, column-level collation:, and how the uniqueness validator follows it.
Audit a migration from PostgreSQL for case-insensitive lookups, unique indexes and old three-byte tables.
Set a collation policy per kind of data, weighing user-friendly matching against exactness for codes and tokens.
## Two settings, two jobs When a hotel-reservation app moves from PostgreSQL to MySQL, the first lines anyone touches are in `config/database.yml`. The generated MySQL configuration (for the `mysql2` or `trilogy` adapter) starts like this: ```yaml default: &default adapter: trilogy encoding: utf8mb4 max_connections: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> username: root ``` | Key | What Rails does with it | What it controls | |---|---|---| | `encoding:` | `SET NAMES <encoding>` when each connection is configured | the character set strings travel in | | `collation:` | appended as `COLLATE <collation>` to the same `SET NAMES` | how strings compare and sort on that connection | Both are applied in the adapter's `configure_connection`, in a single `SET` statement that also carries the SQL mode and any `variables:`. The collation is only sent when an encoding is set. ## Why utf8mb4 and not utf8 In MySQL, the character set historically called `utf8` stores at most **three bytes** per character. Many characters need four: emoji, some CJK characters, many symbols. A guest who writes "Late arrival, sorry" with a smiley in the special-requests field would have that text rejected or mangled. `utf8mb4` is the real four-byte UTF-8, which is why Rails generates it and why `create_database` defaults a new database to `utf8mb4`. Coming from PostgreSQL, where a UTF8 database simply holds any character, this is a setting that can be silently wrong on an old MySQL server: tables created years ago with the three-byte set keep it until they are converted. ## What collation changes A **collation** is the rule set for comparing strings. Its suffix tells you the rules: - `_ci` means **case-insensitive**; MySQL 8's default for utf8mb4, `utf8mb4_0900_ai_ci`, is also accent-insensitive (`_ai`); - `_cs` means case-sensitive, and `_bin` compares the raw bytes. That matters for a reservation app in very concrete ways: 1. **Lookups.** `Guest.where(email: "[email protected]")` finds `[email protected]` under a `_ci` collation. On PostgreSQL the same query found nothing. 2. **Unique indexes.** A unique index on `guests.email` rejects `[email protected]` if `[email protected]` exists, raising `ActiveRecord::RecordNotUnique`. 3. **Sorting and grouping.** `order(:last_name)` and `group(:city)` follow the collation's rules, so "Zürich" and "Zurich" can land together. ## Choosing collation per column The connection-level `collation:` is a default for that session. A column can override it in a migration: ```ruby add_column :reservations, :confirmation_code, :string, collation: "utf8mb4_bin" ``` A confirmation code like `Ab3x` must not match `AB3X`, so a binary collation suits it, while guest emails usually want the case-insensitive default. `db/schema.rb` records the table's charset and collation, and any column collation, so a schema load recreates them. ## How the uniqueness validator follows collation Rails' `validates :email, uniqueness: true` builds its existence query differently depending on the option: - **no `case_sensitive:` option**: a plain comparison, so the column's collation decides and a `_ci` column treats `Ann` and `ann` as duplicates; - **`case_sensitive: true`**: the MySQL adapter wraps the value in `BINARY` when the column's collation is case-insensitive, forcing an exact match; - **`case_sensitive: false`**: a case-insensitive comparison. Keep the validator and the index in agreement. A validator that says "different" while the unique index says "same" turns a friendly form error into `RecordNotUnique` in production. ## Where else the two keys are used - **Creating databases.** `bin/rails db:create` passes `encoding:` as the database's `CHARACTER SET` and `collation:` as its `COLLATE`, so new development and test databases match production only if the configs agree. - **The schema dump.** `db/schema.rb` writes the table's `charset:` and `collation:` into each `create_table` line, so `db:schema:load` recreates them on a fresh database. - **Tests.** A test database created with different settings makes case-sensitivity bugs pass locally and fail in production; keep the `default` block shared. ## Checklist when moving from PostgreSQL - Set `encoding: utf8mb4` everywhere and check existing tables' charset. - Decide which columns need case-sensitive or binary collation (codes, tokens, slugs). - Re-run queries that relied on PostgreSQL's case-sensitive equality. - Review `uniqueness` validations against the indexes beside them.
- Why can a Rails app on MySQL raise ActiveRecord::RecordNotUnique even though validates :email, uniqueness: { case_sensitive: true } passed?With `case_sensitive: true`, the MySQL adapter compares using `BINARY`, so `[email protected]` and `[email protected]` look different to the validator. The unique index uses the column's `_ci` collation, which treats them as the same value, so the INSERT fails. Make the validator and the index agree, usually by dropping the option.
- When does a database.yml collation: setting have no effect in Rails 8.1 on MySQL?When no `encoding:` is configured: the adapter only adds `COLLATE` to a `SET NAMES <encoding>` clause, so a collation on its own is never sent. The generated config always sets `encoding: utf8mb4`, so in practice this bites hand-written configs.
- Says MySQL's utf8 character set stores every Unicode character
- Assumes MySQL string equality is case-sensitive like PostgreSQL's
- Uses case_sensitive: true while the unique index uses a _ci collation
- Thinks collation only affects ORDER BY, not equality or uniqueness
- Gives a confirmation-code column the default case-insensitive collation