SQL键，MUL与PRI对比UNI

4 回答

• 130

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

  show create table <table>;
  

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

  回复于 2024-04-26T19:07:32+08:00
• 6

  DESCRIBE <table>;
  

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

  SHOW COLUMNS FROM <table>;
  

  无论如何，“Key”属性有三个可能的值：

  • PRI

  • UNI

  • MUL

  PRI和UNI的含义非常清楚：

  • PRI =>主键

  • UNI =>唯一键

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

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

  还有一个最后的警告：

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

  总的来说，MySQL文档非常好 . 如有疑问，请查看！

  回复于 2024-04-26T19:07:32+08:00
• 351

  继续讨论MySQL中的MUL，PRI和UNI是什么？

  来自MySQL 5.7文档：

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

  实例

  Control group, this example has neither PRI, MUL, nor 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:

  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

  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:

  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:

  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

  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:

  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

  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)
  

  坚持在你的新皮层，并将表盘设置为“冰沙” .

  回复于 2024-04-26T19:07:32+08:00
• 68

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

  “MUL表示密钥允许多行具有相同的值 . 也就是说，它不是UNIque密钥 . ”

  例如，假设您有两个模型，Post和Comment . Post与Comment有很多关系 . 然后，对于Comment表，有一个MUL键（Post id）是有道理的，因为许多注释可以归属于同一个Post .

  回复于 2024-04-26T19:07:32+08:00