MySQL：information_schema查找某个表的主键是否在数据的其他位置出现

引言：

最近遇到一个问题，需要替换某张表的主键id，那么相关联的字段也需要替换，但是有不知道哪些字段？

正文：

MySQL中，要查找某个表的主键是哪些其他表的外键

在MySQL中，要查找某个表的主键是哪些其他表的外键，你通常需要查看数据库中的外键约束定义。MySQL没有直接提供一个简单的SQL查询来立即显示某个主键被哪些表的外键所引用，但你可以通过查询INFORMATION_SCHEMA数据库中的KEY_COLUMN_USAGE和TABLE_CONSTRAINTS表来手动构建这样的查询。

以下是一个查询示例，该查询将帮助你找到某个特定表（假设表名为your_primary_key_table）的主键列被哪些表的外键所引用：

SELECT   
    tc.table_name AS referencing_table,   
    kcu.column_name AS referencing_column,  
    ccu.table_name AS referenced_table,  
    ccu.column_name AS referenced_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  
    JOIN information_schema.constraint_column_usage AS ccu  
      ON ccu.constraint_name = tc.constraint_name  
      AND ccu.table_schema = tc.table_schema  
WHERE   
    tc.constraint_type = 'FOREIGN KEY'  
    AND ccu.table_name = 'your_primary_key_table' -- 这里替换为你的主键表名  
    AND ccu.referenced_column_name IS NOT NULL;

注意：这个查询实际上返回的是哪些表的外键引用了your_primary_key_table表中的列。如果你的目标是直接找出your_primary_key_table表的主键列被哪些外键所引用，你可能需要先知道your_primary_key_table的主键列名，然后在查询中适当替换ccu.column_name条件来精确匹配。

如果你的主键列名已知（比如主键列名为id），你可以进一步限定查询条件：

SELECT   
    tc.table_name AS referencing_table,   
    kcu.column_name AS referencing_column,  
    ccu.table_name AS referenced_table,  
    ccu.column_name AS referenced_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  
    JOIN information_schema.constraint_column_usage AS ccu  
      ON ccu.constraint_name = tc.constraint_name  
      AND ccu.table_schema = tc.table_schema  
WHERE   
    tc.constraint_type = 'FOREIGN KEY'  
    AND ccu.table_name = 'your_primary_key_table'  
    AND ccu.column_name = 'id' -- 这里替换为你的主键列名  
    AND ccu.referenced_column_name IS NOT NULL;

请注意，你可能需要根据你的具体数据库和表结构调整这个查询。此外，确保你有足够的权限来查询INFORMATION_SCHEMA数据库。

这样我遇到了报错

1109- Unknown table 'constraint_column_usage' in information_schema

对不起，我之前的回答中有一个错误。在 MySQL 的 INFORMATION_SCHEMA 数据库中，并没有直接名为 constraint_column_usage 的表。实际上，你应该继续使用 KEY_COLUMN_USAGE 表来查找外键约束及其引用的列。

要查找某个表（假设表名为 your_primary_key_table）的主键列被哪些其他表的外键所引用，你可以使用类似下面的 SQL 查询：

SELECT   
    kcu.TABLE_NAME AS referencing_table,  
    kcu.COLUMN_NAME AS referencing_column,  
    ccu.TABLE_NAME AS referenced_table,  
    ccu.COLUMN_NAME AS referenced_column  
FROM   
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu  
    JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc  
      ON kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME  
    JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc  
      ON rc.UNIQUE_CONSTRAINT_NAME = tc.CONSTRAINT_NAME  
      AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'  
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ccu  
      ON ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME  
      AND ccu.TABLE_SCHEMA = tc.TABLE_SCHEMA  
      AND ccu.TABLE_NAME = 'your_primary_key_table' -- 这里替换为你的主键表名  
WHERE   
    kcu.CONSTRAINT_SCHEMA = 'your_database_name' -- 这里替换为你的数据库名  
    AND ccu.COLUMN_NAME IS NOT NULL  
    AND kcu.TABLE_SCHEMA = ccu.TABLE_SCHEMA;

但请注意，上面的查询可能需要一些调整，因为直接查找“哪些表的外键引用了特定表的主键”并不是一条直接的 SQL 语句可以完成的。上面的查询尝试通过连接 KEY_COLUMN_USAGE、REFERENTIAL_CONSTRAINTS 和 TABLE_CONSTRAINTS 来找到引用关系，但它可能不会直接给出你想要的完整答案，特别是如果 your_primary_key_table 的主键列名不是唯一的，或者如果有多列组成复合主键。

更直接的方法是，你首先确定 your_primary_key_table 的主键列名（或列名列表），然后查找引用这些列的外键。但是，这通常需要你分别检查每个可能引用该主键的表，或者编写一个更复杂的查询来动态地构建这些检查。

如果你知道主键列的确切名称（比如 id），并且想要找到所有引用这个列的外键，你可以使用类似下面的查询（但请注意，这仍然需要你指定数据库名和可能的表名前缀）：

SELECT   
    kcu.TABLE_NAME AS referencing_table,  
    kcu.COLUMN_NAME AS referencing_column,  
    ccu.TABLE_NAME AS referenced_table,  
    ccu.COLUMN_NAME AS referenced_column  
FROM   
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu  
    JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc  
      ON kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME  
    JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc  
      ON rc.UNIQUE_CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA  
      AND rc.UNIQUE_CONSTRAINT_NAME = tc.CONSTRAINT_NAME  
      AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'  
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ccu  
      ON ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME  
      AND ccu.TABLE_SCHEMA = tc.TABLE_SCHEMA  
      AND ccu.TABLE_NAME = 'your_primary_key_table'  
      AND ccu.COLUMN_NAME = 'id' -- 这里替换为你的主键列名  
WHERE   
    kcu.TABLE_SCHEMA = 'your_database_name' -- 这里替换为你的数据库名  
    AND ccu.COLUMN_NAME IS NOT NULL  
    AND kcu.REFERENCED_TABLE_NAME = ccu.TABLE_NAME  
    AND kcu.REFERENCED_COLUMN_NAME = ccu.COLUMN_NAME;

但是，请注意，这个查询可能仍然需要调整，因为 kcu.REFERENCED_TABLE_NAME 和 kcu.REFERENCED_COLUMN_NAME 实际上在 KEY_COLUMN_USAGE 表中并不总是存在或用于表示外键引用的表和列；它们通常用于表示主键或唯一约束所引用的表和列（如果有的话，比如在自引用外键的情况下）。在外键约束的上下文中，你更可能想要查看 kcu.TABLE_NAME 和 kcu.COLUMN_NAME 来找到外键所在的表和列

MySQL中，查询哪些表的注释（comment）中包含某个特定的字符或字符串

在MySQL中，如果你想查询哪些表的注释（comment）中包含某个特定的字符或字符串，你需要查询information_schema数据库中的TABLES表。information_schema是一个特殊的数据库，它包含了所有其他数据库的信息，如表结构、列定义、索引等。

以下是一个查询示例，它展示了如何找到所有其注释中包含特定字符串（例如'your_search_term'）的表：

SELECT   
    TABLE_SCHEMA AS `Database`,  
    TABLE_NAME AS `Table`,  
    TABLE_COMMENT AS `Comment`  
FROM   
    information_schema.TABLES  
WHERE   
    TABLE_COMMENT LIKE '%your_search_term%';

请将your_search_term替换为你想要搜索的字符串。这个查询会返回所有数据库的表名以及它们的注释，但只包括那些注释中包含你指定字符串的表。

请注意，这个查询默认是区分大小写的。如果你想要进行不区分大小写的搜索，你可以使用LOWER()函数来转换TABLE_COMMENT和搜索字符串为小写（或大写），如下所示：

SELECT   
    TABLE_SCHEMA AS `Database`,  
    TABLE_NAME AS `Table`,  
    TABLE_COMMENT AS `Comment`  
FROM   
    information_schema.TABLES  
WHERE   
    LOWER(TABLE_COMMENT) LIKE LOWER('%your_search_term%');

这样，无论注释中的文本是大写、小写还是混合大小写，只要包含你指定的字符串（不区分大小写），该表就会被查询出来。

请确保你有足够的权限来查询information_schema数据库，因为不是所有用户都有权访问这个数据库中的所有信息。

MySQL中，表的属性（如列）的注释（comment）包含某个特定的字符或字符串

在MySQL中，表的属性（如列）的注释（comment）并不直接存储在information_schema.TABLES表中，而是存储在information_schema.COLUMNS表中。因此，如果你想查询哪些表的列（属性）的注释中包含某个特定的字符或字符串，你需要查询information_schema.COLUMNS表。

以下是一个查询示例，它展示了如何找到所有其列注释中包含特定字符串（例如'your_search_term'）的表及其对应的列名：

SELECT   
    TABLE_SCHEMA AS `Database`,  
    TABLE_NAME AS `Table`,  
    COLUMN_NAME AS `Column`,  
    COLUMN_COMMENT AS `Comment`  
FROM   
    information_schema.COLUMNS  
WHERE   
    COLUMN_COMMENT LIKE '%your_search_term%';

请将your_search_term替换为你想要搜索的字符串。这个查询会返回所有数据库的表名、列名以及它们的注释，但只包括那些注释中包含你指定字符串的列。

同样地，这个查询默认是区分大小写的。如果你想要进行不区分大小写的搜索，你可以使用LOWER()函数来转换COLUMN_COMMENT和搜索字符串为小写（或大写），如下所示：

SELECT   
    TABLE_SCHEMA AS `Database`,  
    TABLE_NAME AS `Table`,  
    COLUMN_NAME AS `Column`,  
    COLUMN_COMMENT AS `Comment`  
FROM   
    information_schema.COLUMNS  
WHERE   
    LOWER(COLUMN_COMMENT) LIKE LOWER('%your_search_term%');

请注意，由于information_schema.COLUMNS表可能包含大量数据（特别是当你有多个数据库和很多表时），这个查询可能会比较慢。因此，如果你经常需要执行这样的查询，可能需要考虑优化你的数据库架构或查询逻辑，或者将结果缓存起来以供将来使用。

另外，请确保你有足够的权限来查询information_schema数据库，因为不是所有用户都有权访问这个数据库中的所有信息。

--end--