How to validate data in MySQL

How to validate data quality in MySQL: the six checks every table needs, the SQL for each, and the coercion, collation and zero-date traps that let bad data slip through.

2026-08-28 · 7 min read

To validate data in MySQL, express each expectation as an aggregate query returning a violation count, run them as a batch, and fail when a count crosses its threshold. The six checks that matter are nulls in required columns, duplicate keys, values outside an allowed set, numbers outside a plausible range, malformed strings, and stale rows. MySQL makes this harder than it looks because it accepts bad data more readily than most engines. Implicit type coercion, permissive dates and collation-dependent equality can all make invalid rows look valid.

Why MySQL hides bad data

Three behaviours account for most surprises.

Implicit coercion. Comparing a string column to a number doesn't raise an error; MySQL just casts the string. WHERE amount_text = 0 matches 'abc', '' and '0.00' alike, because all three coerce to 0. Any validation that compares across types is measuring something other than what you think.

Zero dates. Unless NO_ZERO_DATE and NO_ZERO_IN_DATE are in sql_mode, '0000-00-00' is a storable DATE. Because it isn't NULL, completeness checks pass, and because it isn't a real date, freshness checks treat the row as ancient.

Collation-dependent equality. The default utf8mb4_0900_ai_ci is accent- and case-insensitive, so 'PAID', 'paid' and 'páid' are one value. On a _bin or _cs column they are three. The same allowed-values check means different things on two tables in the same database.

These are defaults rather than bugs, and you have to validate around them. The checks themselves are the same six you would write on any engine (the complete guide to validating data covers the shared workflow). Below is the MySQL-correct version of each.

The six checks every table needs

1. Completeness — nulls in required columns

SELECT COUNT(*) AS violations
FROM `sales`.`orders`
WHERE `order_id` IS NULL;

On a table that predates strict mode, widen this to catch the empty-string equivalent of null:

WHERE `order_id` IS NULL OR `order_id` = '';

2. Uniqueness — duplicate business keys

SELECT COUNT(*) AS violations
FROM (
    SELECT `order_id`
    FROM `sales`.`orders`
    WHERE `order_id` IS NOT NULL
    GROUP BY `order_id`
    HAVING COUNT(*) > 1
) AS dupes;

Remember the collation caveat: on a case-insensitive column this correctly reports 'ORD-1' and 'ord-1' as duplicates. On a _bin column it does not. Decide which you mean and pin the collation.

3. Conformity — values outside an allowed set

SELECT COUNT(*) AS violations
FROM `sales`.`orders`
WHERE `status` IS NOT NULL
  AND `status` NOT IN ('pending', 'paid', 'shipped', 'refunded');

The IS NOT NULL guard is mandatory. NULL NOT IN (...) is NULL, so the row drops out and a mostly-null column reports zero violations.

MySQL's ENUM type seems to make this check redundant, but it doesn't: outside strict mode an invalid ENUM value is stored as the empty string rather than rejected, so the column can hold a value that is not in its own definition.

4. Accuracy — numbers outside a plausible range

SELECT COUNT(*) AS violations
FROM `sales`.`orders`
WHERE `total_amount` IS NOT NULL
  AND (`total_amount` < 0 OR `total_amount` > 100000);

Use DECIMAL for money. FLOAT and DOUBLE compare with rounding error, and an unsigned integer column silently wraps a negative value to a very large positive one, which a range check catches and the schema does not.

5. Conformity — malformed identifiers

SELECT COUNT(*) AS violations
FROM `sales`.`orders`
WHERE `reference` IS NOT NULL
  AND `reference` NOT REGEXP '^ORD-[0-9]{6}
#39;;

MySQL 8.0 replaced the old POSIX engine with ICU, so \d, \w, lazy quantifiers and Unicode classes all work. On MySQL 5.7 or MariaDB the older engine is stricter — stick to POSIX-safe patterns like [0-9] and [[:alpha:]] if you need to support both. REGEXP is also collation-sensitive: on a _ci collation the match is case-insensitive regardless of the pattern, so use REGEXP BINARY when case matters.

6. Timeliness — freshness

SELECT COUNT(*) AS violations
FROM `sales`.`orders`
WHERE `created_at` < UTC_TIMESTAMP() - INTERVAL 24 HOUR;

Use UTC_TIMESTAMP(), not NOW(). NOW() returns the session time zone, which for a client connecting from another region is not the server's, and the check silently shifts. If the column is TIMESTAMP rather than DATETIME, MySQL converts to UTC on write, one of the few times its implicit behaviour helps.

Add a zero-date guard where strict mode is not enforced:

WHERE `created_at` = '0000-00-00 00:00:00'
   OR `created_at` < UTC_TIMESTAMP() - INTERVAL 24 HOUR;

Getting a safe read-only user

CREATE USER 'catalyst_ro'@'%' IDENTIFIED BY '<generated>';
GRANT SELECT ON `analytics`.* TO 'catalyst_ro'@'%';

SELECT alone is enough — information_schema is readable by any account, restricted automatically to the objects that account can see, so schema import needs no extra grant. If you run a replica, point the connection at it: these are aggregate scans and belong off the write primary.

Set a ceiling so a scan on an unindexed table cannot pin a thread:

SET SESSION max_execution_time = 60000;   -- milliseconds, SELECT only

From ad-hoc SQL to a data contract

Hand-written checks drift out of date. It holds up better to declare the expectations next to the schema, in a format you can review in a pull request and a runner can execute. Catalyst uses the Open Data Contract Standard:

apiVersion: v3.0.0
kind: DataContract
info:
  title: orders
  version: 1.1.0
  owner: data-platform
schema:
  - name: orders
    physicalName: orders
    physicalType: table
    properties:
      - name: order_id
        logicalType: string
        physicalType: char(36)
        required: true
        primaryKey: true
        quality:
          - rule: nullCount
            dimension: completeness
            severity: error
            mustBe: "0"
          - rule: duplicateCount
            dimension: uniqueness
            severity: error
            mustBe: "0"
      - name: status
        logicalType: string
        quality:
          - rule: validValues
            dimension: conformity
            severity: error
            mustBe: "['pending', 'paid', 'shipped', 'refunded']"
      - name: total_amount
        logicalType: number
        physicalType: decimal(12,2)
        quality:
          - rule: between
            dimension: accuracy
            severity: error
            mustBe: "[0, 100000]"
      - name: reference
        logicalType: string
        quality:
          - rule: regex
            dimension: conformity
            severity: warning
            mustBe: "'^ORD-[0-9]{6}
#39;"
      - name: created_at
        logicalType: timestamp
        quality:
          - rule: freshness
            dimension: timeliness
            severity: error
            mustBe: "<= 24h"

Catalyst imports the columns and types from information_schema, proposes a baseline contract, compiles each rule to the MySQL SQL above, and records pass, warn or fail per check with the offending rows available for inspection. The YAML round-trips, so editing a rule in the builder does not reformat the file or drop your comments.

MySQL gotchas worth knowing

Scheduling and alerting

Run each dataset's checks just after the job that loads it, and let the schedule follow the data's cadence rather than a round-number cron. Alert on transitions — a check moving from pass to fail — rather than on every run of a still-failing check, and reserve error severity for the rules that should actually stop downstream consumers. Pattern checks on free-text fields usually belong at warning, so they show up as a trend instead of paging someone.

Frequently asked questions

Can I validate MySQL data without writing SQL?

Yes. Declare the expectation — required, unique, allowed values, numeric range, regex, maximum age, foreign key — and the tool compiles it to MySQL-correct SQL. Catalyst reads your columns from information_schema, suggests a starting contract from the types and nullability it finds, and only asks for SQL when the logic is genuinely bespoke, through a customSql rule.

Does data validation need write access to MySQL?

No. GRANT SELECT is sufficient — every check is an aggregate SELECT returning a single number. Catalyst connects read-only and stores metadata and results only; your rows stay in your database.

Does this work with MariaDB and Amazon Aurora MySQL?

Yes, with one caveat: MariaDB kept the older POSIX regex engine, so patterns using \d, \w or lazy quantifiers behave differently than on MySQL 8.0. Stick to POSIX character classes for portable rules. Aurora MySQL matches upstream MySQL behaviour for everything in this guide.

Why does my completeness check pass on a column full of empty strings?

Because '' is not NULL. MySQL outside strict mode converts many invalid inserts into empty strings or zero values rather than rejecting them, so a column can be fully populated and entirely meaningless. Write the completeness check to cover both, and add a validValues or regex rule for what the column is actually supposed to contain.

How does this compare to CHECK constraints?

MySQL only started enforcing CHECK constraints in 8.0.16. Before that they were parsed and silently ignored, which is a trap of its own. Even where enforced, constraints reject rows at write time, which is often the wrong behaviour for an analytics table you would rather land and quarantine. A data contract describes the guarantee, runs on a schedule, and gives you the failing rows instead of a rejected load.