What does MySQL unique index mean?

If you are sure that a data column only contains values ​​that are different from each other, when creating an index for this data column, you should use the keyword UNIQUE to define it as a unique index.

Mysql will automatically check whether the value of this field of the new record has already appeared in this field of a record when a new record is inserted into the data table. If so, mysql will refuse to insert that new record.

In other words, the unique index can ensure the uniqueness of the data record. In fact, on many occasions, the purpose of creating unique indexes is not to increase access speed, but just to avoid data duplication.

How to create a unique index

Operation table

CREATE TABLE `wb_blog` (      
  `id` smallint(8) unsigned NOT NULL,      
  `catid` smallint(5) unsigned NOT NULL DEFAULT '0',      
  `title` varchar(80) NOT NULL DEFAULT '',      
  `content` text NOT NULL,      
   PRIMARY KEY (`id`),      

 )

To create a unique index, you can use the keyword UNIQUE to create it with the table

注：这是在命令行窗口进行操作
mysql> CREATE TABLE `wb_blog` ( 
    ->   `id` smallint(8) unsigned NOT NULL, 
    ->   `catid` smallint(5) unsigned NOT NULL DEFAULT '0', 
    ->   `title` varchar(80) NOT NULL DEFAULT '', 
    ->   `content` text NOT NULL, 
    ->   PRIMARY KEY (`id`), 
    ->   UNIQUE KEY `catename` (`catid`) 
    -> ) ; 
Query OK, 0 rows affected (0.24 sec)

The above code creates a unique index named catename for the 'catid' field of the wb_blog table

2. Use the CREATE command after creating the table

mysql> CREATE UNIQUE INDEX catename ON wb_blog(catid); 
Query OK, 0 rows affected (0.47 sec)

If not If you need a unique index, you can delete it like this

mysql> ALTER TABLE wb_blog DROP INDEX catename; 
Query OK, 0 rows affected (0.85 sec)

The above is the detailed content of What does MySQL unique index mean?. For more information, please follow other related articles on the PHP Chinese website!