MySQL SQL 键，MUL 与 PRI 与 UNI

SQL keys, MUL vs PRI vs UNI

提问by themaestro

What is the difference between MUL, PRIand UNIin MySQL?

MySQL 中的MUL,PRI和 有什么区别UNI？

I'm working on a MySQL query, using the command:

我正在使用以下命令处理 MySQL 查询：

desc mytable; 

One of the fields is shown as being a MULkey, others show up as UNIor PRI.

其中一个字段显示为MUL键，其他字段显示为UNI或PRI。

I know that if a key is PRI, only one record per table can be associated with that key. If a key is MUL, does that mean that there could be more than one associated record?

我知道如果一个键是PRI，则每个表只能有一条记录与该键相关联。如果一个键是MUL，这是否意味着可能有多个关联记录？

Here's the response of mytable.

这是 的回应mytable。

+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| courseid  | int(11) | YES  | MUL | NULL    |       | 
| dept      | char(3) | YES  |     | NULL    |       | 
| coursenum | char(4) | YES  |     | NULL    |       | 
+-----------+---------+------+-----+---------+-------+

采纳答案by Matt Healy

It means that the field is (part of) a non-unique index. You can issue

这意味着该字段是（部分）非唯一索引。你可以发出

show create table <table>;

To see more information about the table structure.

查看有关表结构的更多信息。

回答by robguinness

DESCRIBE <table>; 

This is acutally a shortcut for:

这实际上是一个快捷方式：

SHOW COLUMNS FROM <table>;

In any case, there are three possible values for the "Key" attribute:

在任何情况下，“Key”属性都有三个可能的值：

1. PRI
2. UNI
3. MUL

1. PRI
2. UNI
3. MUL

The meaning of PRIand UNIare quite clear:

的意义PRI和UNI十分清晰：

• PRI=> primary key
• UNI=> unique key

• PRI=> 主键
• UNI=> 唯一键

The third possibility, MUL, (which you asked about) is basically an index that is neither a primary key nor a unique key. The name comes from "multiple" because multiple occurrences of the same value are allowed. Straight from the MySQL documentation:

第三种可能性，MUL, （你问过）基本上是一个既不是主键也不是唯一键的索引。该名称来自“multiple”，因为允许多次出现相同的值。直接来自MySQL 文档：

If Keyis MUL, the column is the first column of a nonunique index in which multiple occurrences of a given value are permitted within the column.

如果Key是MUL，则该列是非唯一索引的第一列，其中允许在该列中多次出现给定值。

There is also a final caveat:

还有一个最后的警告：

If more than one of the Key values applies to a given column of a table, Key displays the one with the highest priority, in the order PRI, UNI, MUL.

如果多个 Key 值应用于表的给定列，则 Key 显示优先级最高的值，顺序为PRI、UNI、MUL。

As a general note, the MySQL documentation is quite good. When in doubt, check it out!

作为一般说明，MySQL 文档非常好。如有疑问，请查看！

回答by Eric Leschinski

Walkthough on what is MUL, PRI and UNI in MySQL?

关于 MySQL 中的 MUL、PRI 和 UNI 是什么？

From the MySQL 5.7documentation:

来自MySQL 5.7文档：

• If Key is PRI, the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY.
• If Key is UNI, the column is the first column of a UNIQUE index. (A UNIQUE index permits multiple NULL values, but you can tell whether the column permits NULL by checking the Null field.)
• If Key is MUL, the column is the first column of a nonunique index in which multiple occurrences of a given value are permitted within the column.

• 如果 Key 是 PRI，则该列是 PRIMARY KEY 或多列 PRIMARY KEY 中的列之一。
• 如果 Key 是 UNI，则该列是 UNIQUE 索引的第一列。（UNIQUE 索引允许多个 NULL 值，但您可以通过检查 Null 字段来判断该列是否允许 NULL。）
• 如果 Key 是 MUL，则该列是非唯一索引的第一列，其中允许在该列中多次出现给定值。

Live Examples

活生生的例子

Control group, this example has neither PRI, MUL, nor UNI:

控制组，这个例子既没有 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)

A table with one column and an index on the one column has a MUL:

具有一列且该列上有索引的表具有 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)

A table with a column that is a primary key has PRI

具有主键列的表具有 PRI

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)

A table with a column that is a unique key has UNI:

具有唯一键列的表具有 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)

A table with an index covering foo and bar has MUL only on foo:

索引覆盖 foo 和 bar 的表仅在 foo 上有 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)

A table with two separate indexes on two columns has MUL for each one

一个表在两列上有两个单独的索引，每一个都有 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)

A table with an Index spanning three columns has MUL on the first:

索引跨越三列的表在第一列上有 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)

A table with a foreign key that references another table's primary key is MUL

具有引用另一个表主键的外键的表是 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)

Stick that in your neocortex and set the dial to "frappe".

把它贴在你的新皮质中，然后将表盘设置为“frappe”。

回答by committedandroider

For Mul, this was also helpful documentation to me - http://grokbase.com/t/mysql/mysql/9987k2ew41/key-field-mul-newbie-question

对于 Mul，这对我来说也是有用的文档 - http://grokbase.com/t/mysql/mysql/9987k2ew41/key-field-mul-newbie-question

"MUL means that the key allows multiple rows to have the same value. That is, it's not a UNIque key."

“MUL 表示该键允许多行具有相同的值。也就是说，它不是唯一键。”

For example, let's say you have two models, Post and Comment. Post has a has_many relationship with Comment. It would make sense then for the Comment table to have a MUL key(Post id) because many comments can be attributed to the same Post.

例如，假设您有两个模型，Post 和 Comment。Post 与 Comment 有 has_many 关系。因此，Comment 表有一个 MUL 键（Post id）是有意义的，因为许多评论可以归因于同一个 Post。