MySQL 动态确定表的主键

阅读更多：MySQL 教程

介绍

MySQL数据库中经常需要使用主键来管理表中的数据，常规操作是创建表的时候就指定主键字段，或者通过修改表结构来增加或删除主键。但是，在某些情况下，我们需要动态的确定表的主键，即在查询表结构的时候，才确定主键字段。本文将演示如何使用MySQL中的信息schema来动态确定表的主键。

MySQL信息schema

MySQL有一个名为信息schema的模式，其中存储了所有存储在MySQL服务器中的数据库、表、列等的元数据。每个MySQL版本都支持不同的信息schema版本，下面介绍MySQL 5.7版本的信息schema。

表

我们可以使用information_schema.tables视图来查询数据库中所有的表，它包含了表的名称、所属数据库、引擎类型、表的创建时间等信息。

SELECT table_schema, table_name, engine, create_time 
FROM information_schema.tables 
WHERE table_schema = 'test' 
ORDER BY create_time DESC;

上面的查询语句会显示test数据库中所有表的名称、引擎类型、创建时间等信息，并且按照创建时间降序排序。我们可以根据这些信息来确定我们需要操作的表。

列

MySQL的information_schema.columns视图可以查找所有表的所有列，并获取每个列的元数据。我们可以使用这些元数据来分析表结构和确定表的主键。

SELECT table_name, column_name, column_key, is_nullable, data_type 
FROM information_schema.columns 
WHERE table_schema = 'test' AND table_name = 'users';

上面的查询语句会显示test数据库中users表的所有列名、列的键类型（主键、外键等）、是否可以为空、数据类型等信息。

索引

information_schema.statistics是一个视图，可以查看所有表的索引。它包含了索引的名称、所属表、索引类型等信息。

SELECT table_name, index_name, index_type 
FROM information_schema.statistics
WHERE table_schema = 'test' AND table_name = 'users';

上面的查询语句会显示test数据库中users表的索引名称、所属表名、索引类型等信息。我们可以根据这些信息来确定主键是否存在和主键字段的名称。

确定表的主键

了解了信息schema之后，我们可以开始动态的确定表的主键字段。下面是一些演示。

查询表的主键

我们可以通过information_schema.columns视图查找表的主键字段。首先，我们需要确定表的主键是否存在：

SELECT column_name 
FROM information_schema.columns 
WHERE table_schema = 'test'
    AND table_name = 'users' 
    AND column_key = 'PRI';

上面的查询语句查找test数据库中users表的主键字段名称，如果没有主键字段，则不会返回任何结果。

查询表中的字段列表

下一步是查询表中的所有字段，我们可以使用类似下面的查询语句：

SELECT column_name 
FROM information_schema.columns 
WHERE table_schema = 'test' AND table_name = 'users';

上面的查询语句会列出test数据库中users表的所有字段名称。

查询表的索引

如果表没有明确定义的主键字段，我们可以查找表的索引。例如，我们可以找到具有唯一性限制的索引来作为主键字段。下面的查询语句查找test数据库中users表的唯一索引：

SELECT index_name, column_name 
FROM information_schema.statistics 
WHERE table_schema = 'test' AND table_name = 'users' AND non_unique = 0;

上面的查询语句会显示test数据库中users表的唯一索引名称和索引列名称。我们可以根据这些信息来确定主键字段。

确定主键的顺序

查询索引时，可以找到所有适用于表的索引并且是唯一的。然而，在某些情况下，表可能有多个唯一索引，我们需要确定哪一个应该用作主键。以下是一些技术来确定主键顺序：

1. 基于索引列顺序

可以使用information_schema.statistics视图查找索引列的顺序，例如：

SELECT column_name 
FROM information_schema.statistics 
WHERE table_schema = 'test' AND table_name = 'users' AND index_name = 'idx_username';

上面的查询语句将返回唯一索引idx_username中的列顺序，按照从左到右的顺序。

2. 基于列的数据类型和大小

在某些情况下，列的数据类型和大小可以决定哪个索引应该用作表的主键。例如，对于数字列，我们可能要选择最小的唯一索引，对于字符串列，我们可能要选择长度最小的唯一索引。下面是一些示例查询：

-- 选择值最小的唯一索引
SELECT index_name, MIN(column_name) AS pk_column 
FROM information_schema.statistics 
WHERE table_schema = 'test' AND table_name = 'users' AND non_unique = 0
GROUP BY index_name 
ORDER BY MIN(column_name);

-- 选择长度最短的唯一索引
SELECT index_name, MIN(CHARACTER_LENGTH(column_name)) AS pk_length 
FROM information_schema.statistics 
WHERE table_schema = 'test' AND table_name = 'users' AND non_unique = 0
GROUP BY index_name 
ORDER BY MIN(CHARACTER_LENGTH(column_name));

上面的查询语句将分别返回test数据库中users表中，值最小或长度最短的唯一索引名称和主键列名。

应用

在上述查询中，我们可以使用单个查询语句来动态确定表的主键。例如：

SELECT 
  COALESCE(
    (SELECT column_name 
     FROM information_schema.columns 
     WHERE table_schema = 'test' AND table_name = 'users' AND column_key = 'PRI'),
    (SELECT column_name 
     FROM information_schema.statistics 
     WHERE table_schema = 'test' AND table_name = 'users' AND non_unique = 0 
     ORDER BY seq_in_index LIMIT 1),
    (SELECT column_name 
     FROM information_schema.columns 
     WHERE table_schema = 'test' AND table_name = 'users'
     ORDER BY data_type, character_maximum_length, column_name LIMIT 1)
   ) AS pk_column;

上述查询语句将尝试寻找test数据库中users表的主键列，并基于序列顺序和数据类型/大小选择如果有多个。

总结

MySQL的information_schema模式允许我们在运行时动态确定表的主键。我们可以使用information_schema.columns视图查找表的列，information_schema.statistics视图查找表的索引来确定主键。在确定主键时，我们可以使用基于索引列顺序或基于列的数据类型和大小来选择哪个唯一索引应该被用作主键。