MySQL酒窖数据库插入随机数据遇Error 1062主键重复问题

错误原因分析 #

• 错误的codice获取逻辑：原存储过程试图通过update vino set vino.codice = ...获取随机codice，但Vino表初始为空时，这条update语句不会修改任何行，后续插入的vino.codice值要么是NULL，要么会重复选取已用过的codice，完全不符合主键唯一性要求。
• 未排除已插入的codice：每次通过ORDER BY rand() LIMIT 1随机选取Bottiglia的codice时，没有过滤掉已经插入到Vino表的记录，必然会出现主键重复冲突。
• 嵌套循环逻辑混乱：外层while c <= var和内层while c <=175的条件相互冲突，导致循环执行逻辑混乱，会重复插入相同数据或超出预期次数。
• 不必要关闭外键检查：关闭foreign_key_checks会破坏外键约束，可能导致Vino表插入Bottiglia中不存在的codice，违背数据库设计的完整性要求。

修正后的存储过程 #

正确思路是：先获取所有Bottiglia中类型为vino的codice，随机打乱后逐个插入Vino表，为每条记录分配随机的酒类型和葡萄品种，确保每个codice只插入一次。

drop procedure if exists insert_vino;
delimiter $$
create procedure insert_vino()
begin
    declare done int default 0;
    declare current_codice bigint(8) unsigned;
    declare tipo enum('rosso', 'bianco');
    declare viti varchar(40);
    
    -- 声明游标，获取所有类型为vino的Bottiglia codice并随机排序
    declare vino_codices cursor for
        select codice from bottiglia 
        where tipo_bottiglia = 'vino'
        order by rand();
    
    -- 游标结束处理
    declare continue handler for not found set done = 1;
    
    start transaction;
    
    -- 打开游标
    open vino_codices;
    
    -- 遍历所有符合条件的codice
    repeat
        fetch vino_codices into current_codice;
        if not done then
            -- 随机选择酒类型
            set tipo = if(rand() > 0.5, 'rosso', 'bianco');
            
            -- 根据类型选择对应的葡萄品种
            if tipo = 'rosso' then
                set viti = elt(floor(1 + rand() * 10), 
                    'barbera', 'dolcetto', 'nebbiolo',
                    'corvina', 'rondinella', 'sangiovese', 
                    'nero d avola', 'syrah', 'cerasuolo di vittoria', 'nerello mascalese');
            else
                set viti = elt(floor(1 + rand() * 9), 
                    'timorasso', 'pinot bianco', 'pinot grigio',
                    'soavignon blanc', 'friuliano', 'vermentino', 
                    'pecorino', 'malvasia del lazio', 'grillo');
            end if;
            
            -- 插入数据
            insert into vino(codice, tipo_vino, vitigno)
            values(current_codice, tipo, viti);
        end if;
    until done end repeat;
    
    -- 关闭游标
    close vino_codices;
    
    commit;
end $$
delimiter ;

修正说明 #

• 游标遍历避免重复：通过游标获取所有符合条件的codice并随机排序，确保每个codice只被处理一次，从根源杜绝主键重复问题。
• 随机分配类型：用rand() > 0.5随机分配红葡萄酒或白葡萄酒类型，无需固定数量的嵌套循环，逻辑更灵活。
• 保留外键约束：不关闭外键检查，确保插入的codice都存在于Bottiglia表中，维护数据库完整性。
• 逻辑简洁清晰：通过repeat-until循环遍历游标，避免原存储过程中嵌套while的逻辑混乱。

内容的提问来源于stack exchange，提问作者GB99