mysql删除唯一索引

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql删除唯一索引，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5175字，纯文字阅读大概需要8分钟。

内容图文

　　结果它自动在建表时就指定了订单ID作为唯一索引了：

mysql> show create table t_product;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                      |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_product | CREATE TABLE `t_product` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(32) NOT NULL,
  `product_id` varchar(32) DEFAULT NULL,
  `product_name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_g2ylk6wqo7apfrsa7o0bvq4s7` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

　　或这样查：

mysql> show index from t_product;
+-----------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name                     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_product |          0 | PRIMARY                      |            1 | id          | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| t_product |          0 | UK_g2ylk6wqo7apfrsa7o0bvq4s7 |            1 | order_id    | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

　　然后我插入多条相同订单ID的产品就挂了：

2019-12-19 11:32:11.746  WARN 6702 --- [nio-9988-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1062, SQLState: 23000
2019-12-19 11:32:11.747 ERROR 6702 --- [nio-9988-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : Duplicate entry ‘123456‘ for key ‘UK_g2ylk6wqo7apfrsa7o0bvq4s7‘
2019-12-19 11:32:11.748 ERROR 6702 --- [nio-9988-exec-2] c.wlf.order.prize.service.OrderService   : db error : could not execute statement; SQL [n/a]; constraint [UK_g2ylk6wqo7apfrsa7o0bvq4s7]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

　　代码里去掉

,unique = true

　　但还不够，因为表已生成，需要手动干掉这个唯一索引：

mysql> alter table `t_product` drop index `UK_g2ylk6wqo7apfrsa7o0bvq4s7`;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t_product;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_product |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

　　现在可以重复插入多个相同订单ID了。

mysql删除唯一索引

标签：nio   for   private   jdbc   name   const   vat   lstat   utf8   

本文系统来源：https://www.cnblogs.com/wuxun1997/p/12066898.html

内容总结

以上是互联网集市为您收集整理的mysql删除唯一索引全部内容，希望文章能够帮你解决mysql删除唯一索引所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端