在MySQL中，MUL、PRI和UNI有什么区别？

从Mysql 5.7文件：

• 如果键是PRI，则列是主键或多列主键中的列之一。
• 如果键是UNI，则该列是唯一索引的第一列。(唯一索引允许多个空值，但可以通过检查Null字段来判断该列是否允许空。)
• 如果键为MUL，则该列是非唯一索引的第一列，其中允许在列中多次出现给定值。

例子：

此示例既没有PRI、MUL，也没有UNI：

mysql> create table penguins (foo INT);
Query OK, 0 rows affected (0.01 sec)

mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| foo   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

MUL的示例：

mysql> create table penguins (foo INT, index(foo));
Query OK, 0 rows affected (0.01 sec)

mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| foo   | int(11) | YES  | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

具有主键的列。

mysql> create table penguins (foo INT primary key);
Query OK, 0 rows affected (0.02 sec)

mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| foo   | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

具有唯一键的列Uni：

mysql> create table penguins (foo INT unique);
Query OK, 0 rows affected (0.01 sec)

mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| foo   | int(11) | YES  | UNI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

具有MUL：

mysql> create table penguins (foo INT, bar INT, index(foo, bar));
Query OK, 0 rows affected (0.01 sec)

mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| foo   | int(11) | YES  | MUL | NULL    |       |
| bar   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

每个列都有MUL。

mysql> create table penguins (foo INT, bar int, index(foo), index(bar));
Query OK, 0 rows affected (0.01 sec)

mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| foo   | int(11) | YES  | MUL | NULL    |       |
| bar   | int(11) | YES  | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

第一列中具有MUL：

mysql> create table penguins (foo INT, 
       bar INT, 
       baz INT, 
       INDEX name (foo, bar, baz));
Query OK, 0 rows affected (0.01 sec)

mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| foo   | int(11) | YES  | MUL | NULL    |       |
| bar   | int(11) | YES  |     | NULL    |       |
| baz   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

带有外键引用但主键的表是MUL

mysql> create table penguins(id int primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> create table skipper(id int, foreign key(id) references penguins(id));
Query OK, 0 rows affected (0.01 sec)

mysql> desc skipper;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

参考资料：在MySQL中，MUL、PRI和UNI有什么区别？ – 问答 – 云+社区 – 腾讯云 (tencent.com)