一、唯一索引添加与变更
0、建表加唯一索引ukey_o_d_t，使用关键字unique key(如果只是单列唯一索引，可以在字段后加unique, 如topic)：

CREATE TABLE `tv_episode_checkin_summary_log` (
  `id` int(11) unsigned NOT NULL auto_increment COMMENT '自增ID',
  `oid` varchar(128) NOT NULL default '0' COMMENT '选项ID',
  `topic` varchar(256) NOT NULL default '' unique COMMENT '话题词',
  `num` int(11) unsigned NOT NULL default '0' COMMENT '数量',
  `type` varchar(2) NOT NULL default '' COMMENT '类型: 1、小时数；2、天数',
  `dtime` bigint(11) unsigned NOT NULL default '0' COMMENT '数据汇总时间',
  `ctime` bigint(11) unsigned NOT NULL default '0' COMMENT '创建时间',
  `mtime` bigint(11) unsigned NOT NULL default '0' COMMENT '修改时间',
  `ext` varchar(64) NOT NULL default '' COMMENT '扩展字段',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `ukey_o_d_t` (`oid`,`dtime`,`type`),
  KEY `key_t_d_t` (`topic`(255),`dtime`,`type`),
  KEY `key_o_d_t` (`oid`,`dtime`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='打卡汇总数据表'

1、修改表加唯一索引(表无记录)

# 语句
alter table table_name add unique index_name(column_name1, colunm_name2)
# 示例
alter table tv_episode_checkin_summary_log add unique ukey_o_d_t(oid,dtime,type)
-------------------------------------------------------------------------------

2、修改表加唯一索引(表有记录)

# 语句
alter ignore table table_name add unique index_name(column_name1, colunm_name2)
# 示例
alter ignore table tv_episode_checkin_summary_log add unique ukey_o_d_t(oid,dtime,type)
-------------------------------------------------------------------------------

注意添加索引后，会自动删除重复的数据，删除后添加的记录。

添加唯一索引前：

mysql> select * from tv_episode_checkin_summary_log where oid = '617749130020181218' and dtime ='1552492800';
+-----+--------------------+-----------------+-----+------+------------+------------+------------+-----+
| id  | oid                | topic           | num | type | dtime      | ctime      | mtime      | ext |
+-----+--------------------+-----------------+-----+------+------------+------------+------------+-----+
| 168 | 617749130020181218 | 演员的诞生      |   0 | 2    | 1552492800 | 1552620335 | 1552620335 |     |
| 169 | 617749130020181218 | wcb             |   1 | 2    | 1552492800 |          2 |          2 |     |
+-----+--------------------+-----------------+-----+------+------------+------------+------------+-----+

添加唯一索引后：

mysql> select * from tv_episode_checkin_summary_log where oid = '617749130020181218' and dtime ='1552492800';
+-----+--------------------+-----------------+-----+------+------------+------------+------------+-----+
| id  | oid                | topic           | num | type | dtime      | ctime      | mtime      | ext |
+-----+--------------------+-----------------+-----+------+------------+------------+------------+-----+
| 168 | 617749130020181218 | 演员的诞生      |   0 | 2    | 1552492800 | 1552620335 | 1552620335 |     |
+-----+--------------------+-----------------+-----+------+------------+------------+------------+-----+
1 row in set (0.00 sec)

3、删除唯一索引

# 命令
alter table talbe_name drop index index_name
# 示例
alter table tv_episode_checkin_summary_log drop index ukey_o_d_t
-------------------------------------------------------------------------------

二、唯一索引用法实战
如果插入的数据会导致UNIQUE索引或PRIMARY KEY发生冲突/重复，有三种方式避免数据异常
0、存在则先删除，后插入
添加唯一索引后写入数据(replace into方式)：

mysql> replace into tv_topic.tv_episode_checkin_summary_log values ('', '617749130020181218', 'wcb', 1,2,1552492800,2,2,'');
Query OK, 2 rows affected, 1 warning (0.24 sec)

mysql> select * from tv_episode_checkin_summary_log where oid = '617749130020181218' and dtime ='1552492800';
+-----+--------------------+-------+-----+------+------------+-------+-------+-----+
| id  | oid                | topic | num | type | dtime      | ctime | mtime | ext |
+-----+--------------------+-------+-----+------+------------+-------+-------+-----+
| 169 | 617749130020181218 | wcb   |   1 | 2    | 1552492800 |     2 |     2 |     |
+-----+--------------------+-------+-----+------+------------+-------+-------+-----+
1 row in set (0.00 sec)

1、存在则更新
添加唯一索引后写入数据(on duplicate key update方式)：

insert into tv_topic.tv_episode_checkin_summary_log values ('', '617749130020181218', 'wcb', 1,2,1552492800,2,2,'') on duplicate key update mtime = 3, ext = 1;
-------------------------------------------------------------------------------

2、存在则忽略
添加唯一索引后写入数据(insert ignore info方式)：

mysql> insert into tv_topic.tv_episode_checkin_summary_log values ('', '617749130020181218', 'wcb', 1,2,1552492800,2,2,'');
ERROR 1062 (23000): Duplicate entry '617749130020181218-1552492800-2' for key 2
mysql> insert ignore into tv_topic.tv_episode_checkin_summary_log values ('', '617749130020181218', 'wcb', 1,2,1552492800,2,2,'');
Query OK, 0 rows affected, 1 warning (0.10 sec)
-------------------------------------------------------------------------------

以上三种情况都是插入重复的情况，应该根据自身业务的具体情况，在数据插入的时候就加上

思考：如果更新的时候重复？