mysql删除表中的唯一索引吗_mysql删除唯一索引

在项目中用spring data jpa指定了一个唯一索引：

@Entity

@Table(name= "t_product")

@Getter

@Setter

@AllArgsConstructor

@NoArgsConstructorpublic classProductItem {

@Id

@GeneratedValue(strategy=GenerationType.IDENTITY)privateLong id;//订单Id

@Column(nullable = false,length = 32,unique = true)private String orderId;

结果它自动在建表时就指定了订单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.14sec)

Records:0 Duplicates: 0 Warnings: 0mysql> show index fromt_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了。