mysql - 错误1062(23000):键“PRIMARY”的条目“DP1”重复

我有一个表，它有一个自动递增的coulmndistributor_warehouse。我不能在插入第一行后插入行。我也见过类似的查询，但是每个地方都得到了答案，列需要自动递增，这已经在我的数据库中完成了。

mysql> desc distributor_warehouse;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| dpID       | varchar(12)   | NO   | PRI |         |       |
| cpID       | varchar(12)   | YES  |     | NULL    |       |
| QTY        | int(6)        | YES  |     | NULL    |       |
| COST_PRICE | decimal(10,2) | YES  |     | NULL    |       |
| SELL_PRICE | decimal(10,2) | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> select * from distributor_warehouse;
+------+------+------+------------+------------+
| dpID | cpID | QTY  | COST_PRICE | SELL_PRICE |
+------+------+------+------------+------------+
| DP1  | CP5  |   10 |    3000.00 |    3100.00 |
+------+------+------+------------+------------+
1 row in set (0.00 sec)

mysql> INSERT INTO distributor_warehouse(cpID,QTY,COST_PRICE,SELL_PRICE) VALUES ('CP6',150,999,1500);
ERROR 1062 (23000): Duplicate entry 'DP1' for key 'PRIMARY'

dpID通过使用触发器自动递增。
这是我的桌子。我成功地插入了第1行，然后在插入第2行时，问题出现了。

mysql> desc autoid;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| ID    | int(10) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.00 sec)

| tg_distributor_warehouse_id | INSERT | distributor_warehouse | BEGIN
INSERT INTO autoid VALUES(NULL);
SET NEW.dpID = CONCAT('DP',LPAD(LAST_INSERT_ID(),1,''));
END |

最佳答案

你的代码看起来很好

drop table if exists t,t1;
create table t(dpid varchar(12), cpid varchar(12), qty int,cost_price decimal(10,2),sell_price decimal(10,2));
create table t1(id int auto_increment primary key);

drop trigger if exists t;
delimiter$$
create trigger t before insert on t
for each row
begin
    insert into t1 values (null);
    SET NEW.dpID = CONCAT('DP',LPAD(LAST_INSERT_ID(),1,''));
end$$
delimiter;

INSERT INTO t(cpID,QTY,COST_PRICE,SELL_PRICE) VALUES ('CP6',150,999,1500);


INSERT INTO t(cpID,QTY,COST_PRICE,SELL_PRICE) VALUES ('CP6',150,999,1500);
select * from t;

+------+------+------+------------+------------+
| dpid | cpid | qty  | cost_price | sell_price |
+------+------+------+------------+------------+
| DP1  | CP6  |  150 |     999.00 |    1500.00 |
| DP2  | CP6  |  150 |     999.00 |    1500.00 |
+------+------+------+------------+------------+
2 rows in set (0.00 sec)

关于mysql - 错误1062(23000):键“PRIMARY”的条目“DP1”重复，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58281025/