boxmoe_header_banner_img

Hello! 欢迎来到悠悠畅享网！

POST
文章导读
数据库

MySQL如何检查约束_MySQL约束查看与完整性检查教程

avatar
作者 2025年9月2日 9
0 评论

查看mysql约束需通过SHOW CREATE table或查询INFORMATION_SCHEMA表，前者直观展示表结构及约束，后者提供主键、唯一键、外键和检查约束的详细元数据，结合TABLE_CONSTRaiNTS与KEY_COLUMN_USAGE可全面分析约束定义与列关系。

MySQL如何检查约束_MySQL约束查看与完整性检查教程

MySQL中查看约束，主要不是通过一个简单的“检查”命令，而是依赖于查询其元数据信息。我们通常会用到

INFORMATION_SCHEMA

数据库下的系统表，尤其是

TABLE_CONSTRAINTS

和

KEY_COLUMN_USAGE

，它们存储了所有数据库、表和约束的详细定义。此外，

SHOW CREATE TABLE

命令也是一个非常直接且常用的方法，能快速展示一张表的完整创建语句，其中就包含了所有的约束定义。

解决方案

要查看MySQL中的约束，最核心的策略是利用系统视图和特定的SQL命令。这就像是翻阅MySQL的“内部档案”来了解它的规则。

首先，

SHOW CREATE TABLE

是最直接的。当你需要快速了解某个表的结构时，它能提供一个清晰的概览，包括主键、唯一键、外键以及（MySQL 8.0.16+）的CHECK约束。它的输出是一条完整的

CREATE TABLE

语句，非常直观。

SHOW CREATE TABLE your_table_name;

这条命令的输出会包含所有列定义、索引定义以及约束定义。比如，

PRIMARY KEY

、

UNIQUE KEY

、

FOREIGN KEY

的定义都会赫然在列。

其次，也是更系统、更精细的方法，是查询

INFORMATION_SCHEMA

数据库。这个数据库是MySQL的“数据字典”，里面包含了关于所有数据库对象（表、列、索引、视图、存储过程等）的元数据。

  • INFORMATION_SCHEMA.TABLE_CONSTRAINTS

    : 这个表记录了所有表级别的约束信息，包括约束名称、约束类型（PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK）以及所属的数据库和表。

      SELECT       CONSTRAINT_NAME,       CONSTRAINT_TYPE,       TABLE_SCHEMA,       TABLE_NAME   FROM       INFORMATION_SCHEMA.TABLE_CONSTRAINTS   WHERE       TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

  • INFORMATION_SCHEMA.KEY_COLUMN_USAGE

    : 这个表则更详细地描述了哪些列参与了键（包括主键、唯一键和外键）。对于外键，它还会指出引用的表和列。

      SELECT       CONSTRAINT_NAME,       TABLE_SCHEMA,       TABLE_NAME,       COLUMN_NAME,       REFERENCED_TABLE_SCHEMA,       REFERENCED_TABLE_NAME,       REFERENCED_COLUMN_NAME   FROM       INFORMATION_SCHEMA.KEY_COLUMN_USAGE   WHERE       TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

通常，我会将这两个表结合起来查询，以获得一个更全面的视图。比如，通过

CONSTRAINT_NAME

进行JOIN，就能看到某个约束具体作用在哪些列上，以及如果是外键，它引用了哪个表和列。

如何快速查看特定表的全部约束信息？

很多时候，我最先想到的就是

SHOW CREATE TABLE

。它直接、粗暴，但管用。它会返回一条完整的

CREATE TABLE

语句，里面清晰地列出了这张表的所有约束定义，包括主键、唯一键、外键以及MySQL 8.0.16及以上版本支持的

CHECK

约束。你甚至不需要去解析复杂的系统表，直接看SQL就行。

SHOW CREATE TABLE your_database_name.your_table_name;

然而，如果你需要的是一个结构化的、可以进一步分析的数据，而不是一段SQL文本，那么查询

INFORMATION_SCHEMA

就成了不二之选。为了获取一个表的全部约束信息，我会将

TABLE_CONSTRAINTS

和

KEY_COLUMN_USAGE

这两个表联接起来。这样，我们不仅能知道有哪些约束（类型、名称），还能知道这些约束具体影响了哪些列，以及外键关系指向何处。

一个整合的查询示例如下：

SELECT     tc.CONSTRAINT_NAME,     tc.CONSTRAINT_TYPE,     tc.TABLE_SCHEMA,     tc.TABLE_NAME,     kcu.COLUMN_NAME,     kcu.REFERENCED_TABLE_NAME,     kcu.REFERENCED_COLUMN_NAME FROM     INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc JOIN     INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu ON     tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME AND     tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA AND     tc.TABLE_NAME = kcu.TABLE_NAME WHERE     tc.TABLE_SCHEMA = 'your_database_name' AND tc.TABLE_NAME = 'your_table_name' ORDER BY     tc.CONSTRAINT_TYPE, tc.CONSTRAINT_NAME, kcu.ORDINAL_POSITION;

这个查询会给你一个非常详细的列表，每个约束及其涉及的列都会清晰呈现。对于外键，你还能看到它引用的父表和父列。这对于理解表的结构和数据关系至关重要。

MySQL中不同类型的约束（主键、唯一键、外键、检查约束）分别如何识别？

这里就得稍微细致点了，每种约束在系统表里的“身份”略有不同，但都逃不出

INFORMATION_SCHEMA

的“法眼”。

  • 主键 (PRIMARY KEY)： 主键是表中最核心的约束，它确保了表中每一行的唯一性和非空性。在

    INFORMATION_SCHEMA.TABLE_CONSTRAINTS

    表中，你可以通过

    CONSTRAINT_TYPE = 'PRIMARY KEY'

    来识别。主键通常也会在

    KEY_COLUMN_USAGE

    中出现，指示其包含的列。

    SELECT     tc.CONSTRAINT_NAME,     tc.TABLE_NAME,     kcu.COLUMN_NAME FROM     INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc JOIN     INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu ON     tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME AND     tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA AND     tc.TABLE_NAME = kcu.TABLE_NAME WHERE     tc.CONSTRAINT_TYPE = 'PRIMARY KEY' AND     tc.TABLE_SCHEMA = 'your_database_name' AND     tc.TABLE_NAME = 'your_table_name';

  • 唯一键 (UNIQUE KEY)： 唯一键确保了指定列（或列组合）中的所有值都是唯一的，但允许NULL值（除非列本身定义为NOT NULL）。识别方式与主键类似，只是

    CONSTRAINT_TYPE

    变为

    'UNIQUE'

    。

    SELECT     tc.CONSTRAINT_NAME,     tc.TABLE_NAME,     kcu.COLUMN_NAME FROM     INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc JOIN     INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu ON     tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME AND     tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA AND     tc.TABLE_NAME = kcu.TABLE_NAME WHERE     tc.CONSTRAINT_TYPE = 'UNIQUE' AND     tc.TABLE_SCHEMA = 'your_database_name' AND     tc.TABLE_NAME = 'your_table_name';

  • 外键 (FOREIGN KEY)： 外键用于建立和强制表与表之间的链接，确保引用完整性。在

    TABLE_CONSTRAINTS

    中，它的

    CONSTRAINT_TYPE

    是

    'FOREIGN KEY'

    。要获取完整的引用信息，你还需要结合

    KEY_COLUMN_USAGE

    ，因为它包含了

    REFERENCED_TABLE_NAME

    和

    REFERENCED_COLUMN_NAME

    这些关键字段。

    SELECT     tc.CONSTRAINT_NAME,     tc.TABLE_NAME,     kcu.COLUMN_NAME AS foreign_key_column,     kcu.REFERENCED_TABLE_NAME,     kcu.REFERENCED_COLUMN_NAME AS referenced_table_column FROM     INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc JOIN     INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu ON     tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME AND     tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA AND     tc.TABLE_NAME = kcu.TABLE_NAME WHERE     tc.CONSTRAINT_TYPE = 'FOREIGN KEY' AND     tc.TABLE_SCHEMA = 'your_database_name' AND     tc.TABLE_NAME = 'your_table_name';

  • 检查约束 (CHECK Constraint)： 检查约束允许你定义一个表达式，数据在插入或更新时必须满足这个表达式。MySQL从8.0.16版本开始才完整支持

    CHECK

    约束。在此之前，虽然可以定义，但它们不会被强制执行。在

    TABLE_CONSTRAINTS

    中，

    CONSTRAINT_TYPE

    是

    'CHECK'

    。但要查看具体的检查表达式，你可能需要查询

    INFORMATION_SCHEMA.CHECK_CONSTRAINTS

    （如果存在，或者直接看

    SHOW CREATE TABLE

    的输出）。

    -- 查找 CHECK 约束 SELECT     CONSTRAINT_NAME,     TABLE_NAME,     CONSTRAINT_TYPE FROM     INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE     CONSTRAINT_TYPE = 'CHECK' AND     TABLE_SCHEMA = 'your_database_name' AND     TABLE_NAME = 'your_table_name';  -- 如果需要查看具体的检查表达式，可能需要结合 SHOW CREATE TABLE 或 INFORMATION_SCHEMA.CHECK_CONSTRAINTS (MySQL 8.0.20+) -- 例如，对于 MySQL 8.0.20+ SELECT     CONSTRAINT_NAME,     CHECK_CLAUSE FROM     INFORMATION_SCHEMA.CHECK_CONSTRAINTS WHERE     CONSTRAINT_SCHEMA = 'your_database_name' AND     CONSTRAINT_NAME IN (         SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS         WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name' AND CONSTRAINT_TYPE = 'CHECK'     );

    对于旧版本，或者当你需要快速查看所有约束的详细定义时，

    SHOW CREATE TABLE

    依然是最直接的方式，因为它会把

    CHECK

    约束的定义也一并输出。

如何主动检查现有数据是否违反了定义的约束？

这块儿就有点意思了，因为MySQL在约束定义后，通常会阻止不符合条件的数据进入。所以，”检查现有数据是否违反”更多时候是在说，”如果我加了这个约束，哪些数据会出问题？”或者，”有没有什么脏数据，是我本来想用约束来避免的？”这通常发生在数据迁移、约束添加失败、或者由于某些原因（比如临时禁用约束或数据导入工具的bug）导致数据完整性被破坏之后。

  • 检查主键/唯一键冲突 (PRIMARY KEY / UNIQUE KEY Violations)： 主键和唯一键的本质是确保唯一性。如果现有数据违反了它们，那意味着有重复的值。你可以通过

    GROUP BY

    和

    HAVING count(*) > 1

    来找出这些重复项。

    -- 假设 'id' 是主键或唯一键 SELECT id, COUNT(*) FROM your_table_name GROUP BY id HAVING COUNT(*) > 1;  -- 对于复合唯一键 (col1, col2) SELECT col1, col2, COUNT(*) FROM your_table_name GROUP BY col1, col2 HAVING COUNT(*) > 1;

    如果存在结果，那么这些就是违反了唯一性约束的数据。

  • 检查外键引用完整性 (FOREIGN KEY Violations)： 外键约束要求子表中的外键值必须在父表中存在对应的引用。如果子表中有数据引用了一个在父表中不存在的记录，就称之为“孤儿记录”。我们可以通过

    LEFT JOIN

    来找出这些记录。

    -- 假设 'orders' 表有一个外键 'customer_id' 引用 'customers' 表的 'id' SELECT     o.* FROM     orders AS o LEFT JOIN     customers AS c ON o.customer_id = c.id WHERE     c.id IS NULL;

    这条查询会返回所有在

    orders

    表中存在，但其

    customer_id

    在

    customers

    表中找不到对应

    id

    的订单记录。

  • 检查检查约束 (CHECK Constraint Violations)： 对于

    CHECK

    约束，它定义了一个布尔表达式。要找出违反了该约束的数据，你只需要在

    WHERE

    子句中反转这个表达式即可。

    -- 假设有一个 CHECK 约束要求 'age' 字段必须大于等于 18 SELECT * FROM your_table_name WHERE NOT (age >= 18);  -- 或者假设 CHECK 约束要求 'status' 只能是 'active' 或 'inactive' SELECT * FROM your_table_name WHERE status NOT IN ('active', 'inactive');

    通过这种方式，你可以找出所有不满足

    CHECK

    约束条件的数据。

进行这些检查非常重要，尤其是在数据库维护、数据清洗或在旧系统上添加新约束之前。这能帮助你识别潜在的数据质量问题，并在正式强制执行约束前进行修正，避免不必要的错误和停机。

ai bug count mysql NULL sql table 对象 工具 数据库

评论（已关闭）

评论已关闭