mysqlは一意のインデックスをサポートしていますか?

mysql は一意のインデックスをサポートしています。 MySQL では、UNIQUE インデックスを使用すると、ユーザーは 1 つ以上のカラムの値の一意性を強制し、テーブル内の 1 つ以上のカラムで値の重複を防ぐことができます。各テーブルは複数の UNIQUE インデックスを持つことができ、UNIQUE インデックスには複数の NULL を含めることができますの値。

このチュートリアルの動作環境: Windows7 システム、mysql8 バージョン、Dell G3 コンピューター。

mysql は一意のインデックスをサポートしています。 MySQL では、UNIQUE インデックスにより、テーブル内の 1 つ以上のカラムでの値の重複が防止されます。

MySQL UNIQUE インデックスの概要

1 つ以上のカラムに一意の値を強制するには、多くの場合 PRIMARY KEY 制約を使用します。

ただし、各テーブルには主キーが 1 つだけあります。複数の列または一意の値を持つ一連の列を使用する場合は、主キー制約を使用できません。

幸いなことに、MySQL には UNIQUE インデックスと呼ばれる別のタイプのインデックスが用意されており、これを使用すると 1 つ以上のカラムの値の一意性を強制できます。 PRIMARY KEY インデックスとは異なり、各テーブルは複数の UNIQUE インデックスを持つことができます。

UNIQUE インデックスを作成するには、次のように CREATE UNIQUE INDEX ステートメントを使用します。

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

1 つ以上の列の値の一意性を強制するもう 1 つの方法は、一意制約を使用することです。一意制約を作成すると、MySQL はバックグラウンドで UNIQUE インデックスを作成します。

次のステートメントは、テーブルの作成時に一意制約を作成する方法を示しています。

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

UNIQUE KEY の代わりに UNIQUE INDEX を使用することもできます。それらは同じものと呼ばれます。

既存のテーブルに一意制約を追加する場合は、次のように ALTER TABLE ステートメントを使用できます:

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

MySQL UNIQUE インデックスと NULL

他のデータベース システムとは異なり、MySQL は NULL 値を別の値として扱います。したがって、UNIQUE インデックスに複数の NULL 値を含めることができます。

これが MySQL の設計方法です。これはバグとして報告されていますが、バグではありません。

もう 1 つの重要な点は、UNIQUE 制約は BDB ストレージ エンジン以外の NULL 値には適用されないことです。

MySQL UNIQUE インデックスの例

アプリケーションで連絡先を管理するとします。また、連絡先テーブルの電子メール列が一意であることも望まれます。

このルールを実装するには、CREATE TABLE ステートメントで次のように一意制約を作成します。

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)
);

SHOW INDEXES ステートメントを使用すると、MySQL が電子メール列に UNIQUE を作成することがわかります。索引。

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

次に、連絡先テーブルに行を挿入します。

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

ここで、電子メールが max.su@yiibai.com である行データを挿入しようとすると、エラー メッセージが表示されます。

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

上記のステートメントを実行すると、次の結果が表示されます。 -

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

電子メールを一意にするだけでなく、first_name、last_name、phone の組み合わせも一意にしたいとします。個性的。この場合、CREATE INDEX ステートメントを使用して、次のようにこれらの列の UNIQUE インデックスを作成できます。

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

次の行を contacts テーブルに追加すると、first_name、last_name、および first_name の組み合わせが原因でエラーが発生します。電話は存在した。

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

上記のステートメントを実行すると、次の結果が表示されます。 -

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

重複した電話番号をテーブルに挿入できないことがわかります。

#一意のインデックスの一意の影響:

一意のインデックス テーブルの作成:

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='学生信息表';

その中に一意のインデックス名が作成されます。つまり、この学生テーブルには同じ名前の学生が 1 人しか存在できないことになります。

一意を追加するコマンド:

alter table sc add unique (name);

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

削除:

alter table sc drop index `name`;

一意のインデックス関数:

最初にデータを挿入します:

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);

テーブル定義を再度確認してください:

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

SQL を再度実行してください:

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

次の場所でテーブル定義を再度確認してください今回は、Auto_Increment=6

unique が重複データの挿入時にエラーを報告するだけでなく、auto_increment が自動的に増加することがわかります

unique キーと主キーの違い:

簡単に言うと、primary key=unique not null です。

具体的な違い:

• (1) 一意制約が配置されている列では NULL 値が許可されますが、主キー制約が配置されている列では NULL 値が許可されません。

• (2) 1 つ以上の列に一意制約を設定できます。これらの列または列の組み合わせは一意である必要があります。ただし、一意制約が配置されている列はテーブルの主キー列ではありません。

• (3) 一意制約により、指定された列に一意のインデックスが強制的に作成されます。デフォルトでは、一意の非クラスター化インデックスが作成されますが、作成されるインデックスがクラスター化インデックスであることを指定することもできます。

• (4) 主キーを確立する目的は、外部キーがそれを参照できるようにすることです。

• (5) テーブルで実行できるのは、次のとおりです。主キーは最大 1 つですが、持つこともできます 一意のキーが多数あります

一意のキーの競合がある場合の回避戦略:

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视频教程】

以上がmysqlは一意のインデックスをサポートしていますか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。