mysql pri_mysql - SQL密钥，MUL与PRI对比UNI

关于MySQL中的MUL，PRI和UNI的演练？

从MySQL 5.7文档：

如果Key是PRI，则该列是PRIMARY KEY或者是多列PRIMARY KEY中的列之一。

如果Key是UNI，则该列是UNIQUE索引的第一列。 (UNIQUE索引允许多个NULL值，但您可以通过检查Null字段来判断该列是否允许NULL。)

如果Key为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)

具有作为主键的列的表具有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)

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

包含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)

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

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