在项目中用spring data jpa指定了一个唯一索引：
@Entity
@Table(name = "t_product")
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class ProductItem { @Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long 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, rows affected (0.14 sec)
Records: Duplicates: Warnings: 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 | | PRIMARY | | id | A | | NULL | NULL | | BTREE | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
row in set (0.00 sec)
现在可以重复插入多个相同订单ID了。