Adakah mysql menyokong indeks unik?

mysql menyokong indeks unik. Dalam MySQL, indeks UNIK membenarkan pengguna untuk menguatkuasakan keunikan nilai dalam satu atau lebih lajur, menghalang nilai pendua dalam satu atau lebih lajur dalam jadual setiap jadual boleh mempunyai beberapa indeks UNIK, dan indeks UNIK Terdapat berbilang NULL; nilai dalam.

Persekitaran pengendalian tutorial ini: sistem windows7, versi mysql8, komputer Dell G3.

mysql menyokong indeks unik. Dalam MySQL, indeks UNIK menghalang nilai pendua dalam satu atau lebih lajur dalam jadual.

Pengenalan kepada MySQL UNIQUE Index

Untuk menguatkuasakan nilai unik untuk satu atau lebih lajur, kami sering menggunakan kekangan KUNCI PRIMER.

Walau bagaimanapun, terdapat hanya satu kunci utama bagi setiap jadual. Jika anda ingin menggunakan berbilang lajur atau set lajur dengan nilai unik, anda tidak boleh menggunakan kekangan kunci utama.

Nasib baik, MySQL menyediakan satu lagi jenis indeks yang dipanggil indeks UNIK, yang membolehkan anda menguatkuasakan keunikan nilai dalam satu atau lebih lajur. Tidak seperti indeks PRIMARY KEY, setiap jadual boleh mempunyai berbilang indeks UNIK.

Untuk mencipta indeks UNIK, gunakan pernyataan CREATE UNIQUE INDEX seperti berikut:

CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

Cara lain untuk menguatkuasakan keunikan nilai dalam satu atau lebih lajur ialah menggunakan kekangan unik. Apabila anda mencipta kekangan unik, MySQL mencipta indeks UNIK di belakang tabir.

Pernyataan berikut menggambarkan cara mencipta kekangan unik semasa mencipta jadual.

CREATE TABLE table_name(
...
   UNIQUE KEY(index_column_,index_column_2,...) 
);

Anda juga boleh menggunakan UNIQUE INDEX dan bukannya UNIQUE KEY. Mereka dipanggil perkara yang sama.

Jika anda ingin menambah kekangan unik pada jadual sedia ada, anda boleh menggunakan pernyataan ALTER TABLE seperti berikut:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);

Indeks UNIK MySQL dan NULL

Tidak seperti sistem pangkalan data lain, MySQL menganggap nilai NULL sebagai nilai yang berbeza. Oleh itu, anda boleh mempunyai berbilang nilai NULL dalam indeks UNIK.

Beginilah cara MySQL direka bentuk. Ini bukan pepijat, walaupun ia dilaporkan sebagai pepijat.

Satu lagi perkara penting ialah kekangan UNIK tidak terpakai pada nilai NULL kecuali untuk enjin storan BDB.

Contoh Indeks UNIK MySQL

Andaikan anda ingin mengurus kenalan dalam aplikasi. Kami juga berharap lajur e-mel dalam jadual kenalan mestilah unik.

Untuk menguatkuasakan peraturan ini, buat kekangan unik dalam kenyataan CREATE TABLE seperti berikut:

USE testdb;

CREATE TABLE IF NOT EXISTS contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(15) NOT NULL,
    email VARCHAR(100) NOT NULL,
    UNIQUE KEY unique_email (email)
);

Jika anda menggunakan pernyataan SHOW INDEXES, anda akan melihat bahawa MySQL mencipta lajur e-mel A indeks UNIK.

SHOW INDEXES FROM contacts;

+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| contacts |          0 | PRIMARY      |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
| contacts |          0 | unique_email |            1 | email       | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set

Seterusnya, masukkan satu baris ke dalam jadual kenalan.

INSERT INTO contacts(first_name,last_name,phone,email)
VALUES('Max','Su','(+86)-999-9988','max.su@yiibai.com');

Sekarang, jika anda cuba memasukkan data baris yang e-melnya ialah max.su@yiibai.com, anda akan menerima mesej ralat.

INSERT INTO contacts(first_name,last_name,phone,email)
VALUES('Max','Su','(+86)-999-9988','max.su@yiibai.com');

Selepas melaksanakan pernyataan di atas, anda seharusnya melihat hasil berikut -

1062 - Duplicate entry 'max.su@yiibai.com' for key 'unique_email'

Anggapkan bahawa anda bukan sahaja mahu e-mel itu unik, tetapi juga gabungan nama_pertama , nama keluarga dan telefon. Dalam kes ini, anda boleh menggunakan penyataan CREATE INDEX untuk mencipta indeks UNIK untuk lajur ini seperti berikut:

CREATE UNIQUE INDEX idx_name_phone
ON contacts(first_name,last_name,phone);

Menambah baris berikut pada jadual kenalan mengakibatkan ralat kerana first_name, last_name dan phone The gabungan sudah wujud.

INSERT INTO contacts(first_name,last_name,phone,email)
VALUES('Max','Su','(+86)-999-9988','john.d@yiibai.com');

Selepas melaksanakan pernyataan di atas, anda seharusnya melihat hasil berikut -

1062 - Duplicate entry 'Max-Su-(+86)-999-9988' for key 'idx_name_phone'

Seperti yang anda lihat, nombor telefon pendua tidak boleh dimasukkan ke dalam jadual.

Impak unik indeks unik:

Penciptaan jadual indeks unik:

DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `class` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_user_id` bigint(11) DEFAULT NULL COMMENT '创建人id',
  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  `modify_user_id` bigint(11) DEFAULT NULL COMMENT '最后修改人id',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='学生信息表';

Nama indeks unik dicipta di dalamnya, yang bermaksud bahawa jadual pelajar ini hanya boleh mempunyai seorang pelajar dengan nama yang sama.

Arahan untuk menambah unik:

alter table sc add unique (name);

alter table sc add unique key `name_score` (`name`,`score`);

Padam:

alter table sc drop index `name`;

Fungsi indeks unik:

Masukkan beberapa data dahulu:

insert into sc (name,class,score) values ('吕布','一年二班',67);
insert into sc (name,class,score) values ('赵云','一年二班',90);
insert into sc (name,class,score) values ('典韦','一年二班',89);
insert into sc (name,class,score) values ('关羽','一年二班',70);

Lihat definisi jadual sekali lagi:

show create table sc;

CREATE TABLE `sc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `class` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_user_id` bigint(11) DEFAULT NULL COMMENT '创建人id',
  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  `modify_user_id` bigint(11) DEFAULT NULL COMMENT '最后修改人id',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='学生信息表';

Auto_Increment=5 pada masa ini

Laksanakan sql sekali lagi:

insert into sc (name,class,score) values ('吕布','二年二班',77)
> 1062 - Duplicate entry '吕布' for key 'name'
> 时间: 0.01s

Melihat definisi jadual sekali lagi pada masa ini, anda akan mendapati bahawa Auto_Increment=6

unik bukan sahaja akan melaporkan ralat semasa memasukkan data pendua, tetapi juga menyebabkan auto_increment berkembang secara automatik

Perbezaan antara kunci unik dan primer:

Ringkasnya, kunci utama=unik+bukan null

Perbezaan khusus:

• (1) Lajur tempat kekangan unik terletak membenarkan nilai nol, tetapi lajur di mana kekangan kunci utama terletak tidak membenarkan nilai nol.

• (2) Anda boleh meletakkan kekangan unik pada satu atau lebih lajur dan lajur atau gabungan lajur ini mestilah unik. Walau bagaimanapun, lajur yang menempatkan kekangan unik bukanlah lajur kunci utama jadual.

• (3) Kekangan unik memaksa penciptaan indeks unik pada lajur yang ditentukan. Secara lalai, indeks bukan berkelompok unik dicipta, walau bagaimanapun, anda juga boleh menentukan bahawa indeks yang dibuat ialah indeks berkelompok.

• (4) Tujuan mewujudkan kunci utama adalah untuk dirujuk oleh kunci asing.

• (5) Jadual hanya boleh mempunyai satu kunci utama paling banyak, tetapi ia boleh Terdapat banyak kunci unik

Apabila terdapat konflik kunci yang unik, strategi mengelak:

insert ignore:

insert ignore会忽略数据库中已经存在的数据(根据主键或者唯一索引判断)，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据.

insert ignore into sc (name,class,score) values ('吕布','二年二班',77)

执行上面的语句，会发现并没有报错，但是主键还是自动增长了。

replace into:

• replace into 首先尝试插入数据到表中。 如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据，然后插入新的数据，否则，直接插入新数据。
• 使用replace into，你必须具有delete和insert权限

replace into sc (name,class,score) values ('吕布','二年二班',77);

此时会发现吕布的班级跟年龄都改变了，但是id也变成最新的了，所以不是更新，是删除再新增

insert on duplicate key update:

• 如果在insert into 语句末尾指定了on duplicate key update，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致重复的问题，则插入新行，跟普通的insert into一样。
• 使用insert into，你必须具有insert和update权限
• 如果有新记录被插入，则受影响行的值显示1；如果原有的记录被更新，则受影响行的值显示2；如果记录被更新前后值是一样的，则受影响行数的值显示0

insert into sc (name,class,score) values ('关羽','二年二班',80) on duplicate key update score=100;
> Affected rows: 2
> 时间: 0.008s

旧数据中关羽是一年二班，70分，现在插入，最后发现只有分数变成了100，班级并没有改变。

4	关羽	一年二班	100	2018-11-16 15:32:18		2018-11-16 15:51:51

id没有发生变化，数据只更新，但是auto_increment还是增长1了。

死锁：

insert ... on duplicate key 在执行时，innodb引擎会先判断插入的行是否产生重复key错误，
如果存在，在对该现有的行加上S(共享锁)锁，如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作，
然后对该记录加上X(排他锁)，最后进行update写入。

如果有两个事务并发的执行同样的语句，
那么就会产生death lock，如

解决办法：

1、尽量对存在多个唯一键的table使用该语句

2、在有可能有并发事务执行的insert 的内容一样情况下不使用该语句

结论：

• 这三种方法都能避免主键或者唯一索引重复导致的插入失败问题。
• insert ignore能忽略重复数据，只插入不重复的数据。
• replace into和insert ... on duplicate key update，都是替换原有的重复数据，区别在于replace into是删除原有的行后，在插入新行，如有自增id，这个会造成自增id的改变；insert ... on duplicate key update在遇到重复行时，会直接更新原有的行，具体更新哪些字段怎么更新，取决于update后的语句。

【相关推荐：mysql视频教程】

Atas ialah kandungan terperinci Adakah mysql menyokong indeks unik?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!