关于mysqlcheck是否检查外键约束及--check选项的技术问询

Hey there, let's break this down clearly for you, especially given your scenario where you inserted a large dataset with SET FOREIGN_KEY_CHECKS = 0;.

What does --check (the default option) actually verify? #

The --check flag (which runs automatically if you don't specify other mysqlcheck options) focuses on table structural integrity, not logical constraints like foreign keys. Specifically, it checks things like:

• Corrupted data pages or invalid record formats
• Consistency between table data and its associated indexes
• For MyISAM tables, matching sizes of data and index files
• General table health issues that could block reads/writes

As you noticed, the official docs are vague on exact error types—but foreign key constraint violations aren't part of this default check. That's intentional: mysqlcheck automatically disables foreign key checks (SET FOREIGN_KEY_CHECKS = 0) when working with InnoDB tables to avoid long lock times and performance hits during routine maintenance.

Will --check validate your foreign key constraints? #

No, not by default. If you want mysqlcheck to verify that your inserted data doesn't break foreign key rules, you need to explicitly enable foreign key checks alongside the --check option.

To do this for an entire database:

mysqlcheck --check --foreign-key-checks=ON your_database_name

Or for a specific table:

mysqlcheck --check --foreign-key-checks=ON your_database_name your_table_name

Keep in mind this might lock your tables longer (especially large ones), since InnoDB has to validate every foreign key relationship in the dataset.

A more direct way to check foreign keys #

If you want precise, targeted feedback about violations, use the ALTER TABLE command to force InnoDB to validate all foreign key constraints for a table:

ALTER TABLE your_table_name CHECK CONSTRAINT ALL;

This will immediately report specific issues (like orphaned child records referencing non-existent parent rows) with clear details about the problematic data.

Quick Recommendation #

Given you inserted a large amount of data with foreign key checks disabled, I'd suggest:

1. Using the ALTER TABLE ... CHECK CONSTRAINT command on individual tables first—it gives you exact error messages to fix issues.
2. If you need to batch-check all tables in a database, use mysqlcheck with --foreign-key-checks=ON, but plan for potential lock delays on large datasets.

内容的提问来源于stack exchange，提问作者Alberto