MySQL-"PRIMARY KEY"，"UNIQUE KEY"的含义和"KEY"当创建表格一起使用时 [英] MySQL - Meaning of "PRIMARY KEY", "UNIQUE KEY" and "KEY" when used together while creating a table

问题描述

如果将PRIMARY KEY，UNIQUE KEY和KEY放在MySQL中的单个CREATE TABLE语句中，谁能解释一下?

Can anyone explain about the purpose of PRIMARY KEY, UNIQUE KEY and KEY, if it is put together in a single CREATE TABLE statement in MySQL?

CREATE TABLE IF NOT EXISTS `tmp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `tag` int(1) NOT NULL DEFAULT '0',
  `description` varchar(255),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uid` (`uid`),
  KEY `name` (`name`),
  KEY `tag` (`tag`)
) ENGINE=InnoDB AUTO_INCREMENT=1 ;

如何将该查询转换为MSSQL?

How do I convert this query to MSSQL?

推荐答案

键只是普通索引.一种简化的方法是将其视为图书馆中的卡片目录.它为MySQL指明了正确的方向.

A key is just a normal index. A way over simplification is to think of it like a card catalog at a library. It points MySQL in the right direction.

还使用唯一键来提高搜索速度，但是它有一个约束，即不能有重复项(没有两个x和y，其中x不是y并且x == y).

A unique key is also used for improved searching speed, but it has the constraint that there can be no duplicated items (there are no two x and y where x is not y and x == y).

手册对其进行了如下解释:

The manual explains it as follows:

UNIQUE索引创建约束，以使索引中的所有值 必须与众不同.如果您尝试添加带有行的新行，则会发生错误. 与现有行匹配的键值.此约束不适用 设置为NULL值(BDB存储引擎除外).对于其他引擎， UNIQUE索引允许可以包含多个列的多个NULL值 空值.如果您为UNIQUE索引中的列指定前缀值， 列值在前缀中必须唯一.

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

主键是特殊"唯一键.基本上，它是唯一的密钥，除了用于识别事物.

A primary key is a 'special' unique key. It basically is a unique key, except that it's used to identify something.

该手册介绍了索引的一般使用方式:此处.

The manual explains how indexes are used in general: here.

在MSSQL中，概念是相似的.有索引，唯一约束和主键.

In MSSQL, the concepts are similar. There are indexes, unique constraints and primary keys.

未经测试，但我相信等效的MSSQL是:

Untested, but I believe the MSSQL equivalent is:

CREATE TABLE tmp (
  id int NOT NULL PRIMARY KEY IDENTITY,
  uid varchar(255) NOT NULL CONSTRAINT uid_unique UNIQUE,
  name varchar(255) NOT NULL,
  tag int NOT NULL DEFAULT 0,
  description varchar(255),
);

CREATE INDEX idx_name ON tmp (name);
CREATE INDEX idx_tag ON tmp (tag);

上面的代码经测试正确无误；但是，我怀疑这样做有更好的语法.自从我使用SQL Server以来已经有一段时间了，而且显然我已经忘记了很多:).

the code above is tested to be correct; however, I suspect that there's a much better syntax for doing it. Been a while since I've used SQL server, and apparently I've forgotten quite a bit :).

这篇关于MySQL-"PRIMARY KEY"，"UNIQUE KEY"的含义和"KEY"当创建表格一起使用时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！