mysql - 有什么方法可以列出或证明MySQL中的检查约束（例如使用主键和外键）吗？(Is there any way to list or evidence Check constraints (like is possible with Primary and Foreign Keys) in MySQL?)

Question

If I want to see constraints on any particular table, I have used

(如果我想查看任何特定表的约束，我已经使用了)

describe table_name;

or

(要么)

show create table table_name;

and this will show everything I need regarding my primary and foreign keys, but not Check constraints.

(这将显示我需要的有关主键和外键的所有内容，但不会显示Check约束。)

I have also looked at getting information from:

(我还看过从以下方面获取信息：)

select * from information_schema.constraints where constraint_schema = database_name;

but again, same problem of no evidence of my check constraints.

(但是同样，同样的问题也没有我的检查约束的证据。)

I have tested my check constraints to ensure they work by intentionally violating the parameters I set, and found that they do successfully give warnings and work as intended, so I am confident in their use.

(我已经对检查约束进行了测试，以确保通过有意违反我设置的参数来确保它们起作用，并且发现它们确实能够成功发出警告并按预期工作，因此我对它们的使用充满信心。)

From what I've read, Check constraints are something that have only been properly supported in newer iterations of MySQL, and that before they were parsed but ignored.

(从我的阅读中可以看出，Check约束只有在MySQL的较新版本中才得到适当支持，并且在解析但忽略之前就得到了支持。)

Any assistance on how to evidence the constraints like a primary or foreign key would be amazing.

(任何有关如何证明约束（例如主键或外键）的帮助都将是惊人的。)

Also, my apologies for any incorrect formatting of my question, it's my first question on StackOverflow and I greatly appreciate what an invaluable resource it is.

(另外，对于问题的任何不正确格式，我深表歉意，这是我关于StackOverflow的第一个问题，我非常感谢它是无价的资源。)

If there is any feedback on what you deem to be improper formatting I would welcome it, I tried to stick to the suggestions.

(如果有任何关于您认为格式不正确的反馈，我欢迎您，我尝试坚持这些建议。)

  ask by fourteen14 translate from so

Answer 1

You could use metadata tables(MySQL 8.0.16):

(您可以使用元数据表（MySQL 8.0.16）：)

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'CHECK';

or:

(要么：)

SELECT *
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS;

db<>fiddle demo

(db <> fiddle演示)

---

EDIT: (编辑：)

In your demo I see the constraints were added in the table creation process, whereas mine were added to existing tables.

(在您的演示中，我看到在表创建过程中添加了约束，而我的约束已添加到现有表中。)

Does this have a consequence?

(这会产生后果吗？)

Not that all:

(并非全部：)

CREATE TABLE t(id INT);
ALTER TABLE t ADD CONSTRAINT t_chk_1 CHECK (id > 2);

db<>fiddle demo2

(db <> fiddle demo2)