Shulou(Shulou.com)06/01 Report--

Today, what I want to share with you is what is the difference between key, primary key, unique key and index in mysql. Many people don't know much about it. Today, in order to make you understand better, I summarized the following contents for you. Let's look down together. I'm sure you'll get something.

I. the difference between key and primary key

CREATE TABLE wh_logrecord (logrecord_id int (11) NOT NULL auto_increment,user_name varchar, default NULL,operation_time datetime default NULL,logrecord_operation varchar (100) default NULL,PRIMARY KEY (logrecord_id), KEY wh_logrecord_user_name (user_name))

Parsing:

KEY wh_logrecord_user_name (user_name)

The user_name field of this table establishes a foreign key with the user _ name field of the wh_logrecord_user_ name table

Outside the parentheses is the corresponding table for establishing the foreign key, and inside the parentheses are the corresponding fields

Similarly, there is KEY user (userid)

Of course, key is not always a foreign key.

Summary:

Key is an index constraint, and the constrained index of the fields in the table is created by primary foreign unique and so on. There are common foreign key, foreign key association with.

KEY forum (status,type,displayorder) # is a multi-column index (key) KEY tid (tid) # is a single-column index (key)

When creating a table: KEY forum (status,type,displayorder)

Does select * from table group by status,type,displayorder automatically use this index?

But is this index useful when select * from table group by status?

The purpose of key: mainly used to speed up the query.

II. The difference between KEY and INDEX

Note: this part of me is still in the clouds.

KEY is usually synonymous with INDEX. If the keyword attribute PRIMARY KEY is given in the column definition, PRIMARY KEY can also be specified as KEY only. The purpose of this is to be compatible with other database systems. PRIMARY KEY is a unique KEY, at which point all key columns must be defined as NOT NULL. If these columns are not explicitly defined as NOT NULL,MySQL, they should be implicitly defined. There is only one PRIMARY KEY in a table.

The difference between Index and Key in MySQL

Key, namely key value, is a part of relational model theory, such as primary key (Primary Key), foreign key (Foreign Key), etc., which is used for data integrity check and uniqueness constraints. While Index is at the implementation level, for example, any column of the table can be indexed, so when the indexed column is in the Where condition of the SQL statement, it can get fast data location and rapid retrieval. As for Unique Index, it only belongs to one of the Index. Unique Index is established to indicate that the data in this column is not repeatable. Guess that MySQL can make further special optimization to the index of Unique Index type.

Therefore, when designing the table, the Key only needs to be at the model level, and when query optimization is needed, the relevant columns can be indexed.

In addition, in MySQL, for a Primary Key column, MySQL has automatically Unique Index it, so there is no need to index it repeatedly.

An explanation found in the search:

Note that "primary" is called PRIMARY KEY not INDEX.KEY is something on the logical level, describes your table and database design (i.e. Enforces referential integrity...) INDEX is something on the physical level, helps improve access time for table operations.Behind every competes for there is (usually) unique index created (automatically).

3. What is the difference between UNIQUE KEY and PRIMARY KEY in mysql

1 one or more columns of the primary key must be NOT NULL. If the column NULL is added, the column will automatically change to NOT NULL when the PRIMARY KEY is added. UNIQUE KEY does not have this requirement for columns.

2, a table can have only one PRIMARY KEY, but can have multiple UNIQUE KEY

3, primary key and unique key constraints are implemented through the reference index, if the inserted values are NULL, then according to the principle of the index, full NULL values are not recorded on the index, so when inserting full NULL values, there can be duplicates, while others cannot insert duplicate values.

Alter table t add constraint uk_t_1 unique (aformab); insert into t (ameme b) values (null,1); # can not repeat insert into t (ameme b) values (null,null); # can be repeated

4. Use UNIQUE KEY

CREATE TABLE `secure_vulnerability_ warning` (`id` int (10) NOT NULL auto_increment, `date` date NOT NULL, `type` varchar (100) NOT NULL, `sub_ type` varchar (100) NOT NULL, `domain_ name` varchar (128) NOT NULL, `url`text NOT NULL, `parameters` text NOT NULL, `hash`varchar (100) NOT NULL, `deal`int (1) NOT NULL, `deal_ date`date default NULL, `remark` text, `last_push_ time` datetime default NULL, `push_ times` int (11) default'1' `first_set_ok_ time` datetime default NULL, `last_set_ok_ time` datetime default NULL, PRIMARY KEY (`id`), UNIQUE KEY `date` (`date`, `hash`) ENGINE=InnoDB DEFAULT CHARSET=utf8

The purpose of UNIQUE KEY: it is mainly used to prevent repetition when data is inserted.

1, when creating a table

CREATE TABLE Persons (Id_P int NOT NULL,LastName varchar (255) NOT NULL,FirstName varchar (255), Address varchar (255), City varchar (255), UNIQUE (Id_P)

If you need to name UNIQUE constraints and define UNIQUE constraints for multiple columns, use the following SQL syntax:

CREATE TABLE Persons (Id_P int NOT NULL,LastName varchar (255) NOT NULL,FirstName varchar (255), Address varchar (255), City varchar (255), CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

2. To create a UNIQUE constraint in the "Id_P" column when the table has been created, use the following SQL:

ALTER TABLE PersonsADD UNIQUE (Id_P)

To name UNIQUE constraints and define UNIQUE constraints for multiple columns, use the following SQL syntax:

ALTER TABLE PersonsADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

3. Undo the UNIQUE constraint

To undo the UNIQUE constraint, use the following SQL:

ALTER TABLE PersonsDROP INDEX uc_PersonID

After reading the above, do you have a general idea of the difference between key, primary key, unique key and index in mysql? If you want to know more, welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.