Flipping a Column Default: A Behavior Switch for New Rows
Changing one column DEFAULT is instant and metadata-only. The real work is auditing the INSERT paths whose meaning silently changed.
TL;DR
Changing a column default in MySQL is instant metadata-only, no table lock or rebuild. Existing rows keep their values; only INSERTs that omit the column flip behavior. Audit those callers first, then ship the migration.
A new entity appears in the KotaPortal CMS with its review feature already active, while the team actually wanted reviews to stay off until each entity is enabled deliberately. The entire fix lives in a single migration file, api/migrations/000072_rating_default_off.up.sql, containing one statement: ALTER TABLE entities MODIFY COLUMN rating_enabled TINYINT(1) NOT NULL DEFAULT 0. The column had carried a default of 1 since migration 000064.
The wrong guess comes first: ALTER TABLE means a table lock and production downtime. The guess is common because many schema operations really do rebuild a table. Changing only a column default is different. In the online DDL operations table it is marked Instant: yes, In place: yes, Rebuilds table: no, permits concurrent DML, and only modifies metadata [7]. It makes sense once you see where the default lives: it is read at INSERT time, for rows that omit the column, and can be a literal or an expression [6]. Nothing about existing data needs rewriting.
Two Syntax Routes
MySQL offers two ways to change a default. ALTER COLUMN ... SET DEFAULT touches just the default; MODIFY COLUMN rewrites the full column definition, including the type and NOT NULL [5]. The full rewrite also leaves room for a typo that silently changes a type, so when the goal is only flipping the default, the narrower route is harder to get wrong. A DEFAULT clause names the value used by INSERT statements that omit the column, and expression defaults are supported [6].
Schema tools such as golang-migrate manage these changes as versioned up and down files [8]. Flipping a default is the cheapest behavior switch for new rows. The real work is understanding which paths do not change.
Consequences That Ride Along
First, existing rows keep their values. There is no backfill, by design, and the decision is documented where future readers will look: the migration file itself states that only the default changes and no data is rewritten. Entities with reviews on stay on.
Second, INSERT paths that send the column explicitly are untouched. Before the flip, omitting the column still produced 1; after it, omission produces 0. That silent change of meaning is exactly why testutil.SeedEntity pins rating_enabled=1: the shared fixture keeps exercising the enabled path on purpose, instead of drifting onto the new default and testing something else. The admin form needed the same attention, so the wisata-editor.tsx default was flipped in the same commit and admin-created entities match the database from day one. Worth one more distinction: adding a new column is also instant on current MySQL, so the real advantage of a default flip is not avoiding the rebuild. It is that no row is touched at all [7].
Third, the down migration simply sets the default back to 1 and restores no data, because up never changed any. The pair is symmetric by construction.
Fourth, TestNewEntityDefaultsRatingOff, a 106-line integration test, locks the new behavior: an entity created without the column starts with reviews off.
A switch that starts off mirrors deny-by-default: new records opt in instead of opting out [1].
Existing rows do not change, and that is a decision to be aware of rather than an accident. Paths that genuinely need the old behavior, like test seeds or import flows, set the value explicitly. The practical audit before flipping any default is therefore not about the table at all: it is the list of INSERT statements that omit the column, because those are the callers whose behavior just changed. When a schema only needs a behavior change for rows that do not exist yet, changing DEFAULT is the first move to reach for before writing a heavier migration.
Sources
[1] Broken_Access_Control/index.html — OWASP Top 10:2021 A01 Broken Access Control
[5] table.html — MySQL 8.0 Reference Manual 15.1.9 ALTER TABLE Statement
[6] type-defaults.html — MySQL 8.0 Reference Manual 13.6 Data Type Default Values
[8] migrate/migrate — golang-migrate/migrate: Database migrations. CLI and Golang library