获取 MySQL 中每个约束中的字段
mysqlmysqli database
假设我们有一个包含多个表的数据库"business"。如果您想获取每个约束中的字段，请使用以下查询。
以下查询用于获取每个约束中的字段 −
mysql> select * −> from information_schema.key_column_usage −> where constraint_schema = 'business';
以下是输出 −
+--------------------+-------------------+--------------------------+---------------+--------------+------------------------------+--------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | +--------------------+-------------------+--------------------------+---------------+--------------+------------------------------+--------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ | def | business | PRIMARY | def | business | primarytable | FKPK | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | primarytable1 | Fk_pk | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | autoincrementtable | id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | demoauto | id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | autoincrement | IdAuto | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | primarytabledemo | FK | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | sequencedemo | SequenceId | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | updtable | IncId | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | transcationdemo | id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | triggedemo | User_id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | usernameandpassworddemo | U_Id | 1 | NULL | NULL | NULL | NULL | | def | business | UserId | def | business | usernameandpassworddemo | UserId | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | tblp | FK_PK | 1 | NULL | NULL | NULL | NULL | | def | business | name | def | business | uniquedemo | name | 1 | NULL | NULL | NULL | NULL | | def | business | name | def | business | uniqueconstdemo | name | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | keydemo | id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | nextiddemo | id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | tablepri | id | 1 | NULL | NULL | NULL | NULL | | def | business | ConstFK | def | business | tabledemo2 | id | 1 | NULL | NULL | NULL | NULL | | def | business | ConstFK | def | business | tabledemo3 | id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | college | StudentFKPK | 1 | NULL | NULL | NULL | NULL | | def | business | id | def | business | uniquedemo1 | id | 1 | NULL | NULL | NULL | NULL | | def | business | id | def | business | uniquedemo1 | name | 2 | NULL | NULL | NULL | NULL | | def | business | id | def | business | uniqueautoid | id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | schemadatabasemethoddemo | TheKey | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | employeeinformation | EmpId | 1 | NULL | NULL | NULL | NULL | | def | business | name | def | business | addingunique | name | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | parentdemo | FKPK | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | lastinsertrecordiddemo | id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | demoindex | Id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | compositeprimarykey | Id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | compositeprimarykey | StudentName | 2 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | addingautoincrement | Id | 1 | NULL | NULL | NULL | NULL | | def | business | Id | def | business | uniqueconstrainttable | Id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | mergedemo1 | id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | mergedemo2 | id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | foreigntable1 | StudentId | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | twoprimarykeytabledemo | Result | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | twoprimarykeytabledemo | StudentId | 2 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | showconstraintsdemo | BookId | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | showconstraintsdemo | BookName | 2 | NULL | NULL | NULL | NULL | | def | business | BookAuthor | def | business | showconstraintsdemo | BookAuthor | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | autoincrementtozero | Id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | altertabletoaddautoincrement | StudentId | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | addingprimarykeydemo | UniversityId | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | resetprimarykey | Id | 1 | NULL | NULL | NULL | NULL | | def | business | constFKPK | def | business | foreigntable | Fk_pk | 1 | 1 | business | primarytable1 | fk_pk | | def | business | FKConst | def | business | foreigntabledemo | FK | 1 | 1 | business | primarytabledemo | fk | | def | business | ConstFK | def | business | tblf | FK_PK | 1 | 1 | business | tblp | fk_pk | | def | business | StudCollegeConst | def | business | studentenrollment | StudentFKPK | 1 | 1 | business | college | studentfkpk | | def | business | ConstChild | def | business | childdemo | FKPK | 1 | 1 | business | parentdemo | fkpk | | def | business | primarytable1demo_ibfk_1 | def | business | primarytable1demo | ForeignId | 1 | 1 | business | foreigntable1 | studentid | +--------------------+-------------------+--------------------------+---------------+--------------+------------------------------+--------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ 52 rows in set, 2 warnings (0.21 sec)
