В моята база данни има ненормализирана таблица със структура на име details и примерни данни, както е показано по-долу (извинения за изображението, просто реших, че ще бъде по-разбираемо):
Моето предизвикателство е да разделя колони - assignee, inventor and ipcsubclass с помощта на разделител | в нови таблици {detail_inv и inventors}, {detail_asg и assignees} и {detail_ipc и ipcsubclasses}.
И в трите случая схемите на таблиците са подобни. Например колони в таблицата на изобретателите - id и name и в таблицата detail_inv - detail_id и inventor_id. Трябва да има само едно име на ред с всички имена, уникални в таблицата на изобретателите и идентификатори, за да поддържа връзка в таблицата detail_inv.
Опитах съхранена процедура с код по-долу за изобретатели - направих 3 процедури за 3 колони:(
drop procedure if exists normalise_details;
delimiter #
create procedure normalise_details()
proc_main:begin
declare v_cursor_done int unsigned default 0;
declare v_post_id int unsigned;
declare v_tags varchar(2048);
declare v_keyword varchar(50);
declare v_keyword_id mediumint unsigned;
declare v_tags_done int unsigned;
declare v_tags_idx int unsigned;
declare v_cursor cursor for select id, inventor from details order by id;
declare continue handler for not found set v_cursor_done = 1;
set autocommit = 0;
open v_cursor;
repeat
fetch v_cursor into v_post_id, v_tags;
set v_tags_done = 0;
set v_tags_idx = 1;
while not v_tags_done do
set v_keyword = substring(v_tags, v_tags_idx,
if(locate('|', v_tags, v_tags_idx) > 0,
locate('|', v_tags, v_tags_idx) - v_tags_idx,
length(v_tags)));
if length(v_keyword) > 0 then
set v_tags_idx = v_tags_idx + length(v_keyword) + 1;
set v_keyword = trim(v_keyword);
insert into inventors (name) values (v_keyword);
select id into v_keyword_id from inventors where name = v_keyword;
insert into details_inv (inventor_id, detail_id) values (v_keyword_id, v_post_id);
else
set v_tags_done = 1;
end if;
end while;
until v_cursor_done end repeat;
close v_cursor;
commit;
end proc_main #
delimiter ;
Когато опитам това върху някои произволни тестови данни, работи добре. когато правя това на действителна маса, не работи добре. Вмъкват се само частични данни. SQL не извежда грешки (с изключение на някои случаи: "#1172 - Резултатът се състои от повече от един ред" или "колоната inventor_id не може да бъде нула")
Опитах се да променя кода в MySQL - Вмъкване на запетая разделен списък в нормализирани таблици чрез съхранена процедура, за да отговаря на нуждите ми, но не успях.
Моля, помогнете ми, моята DB таблица се е превърнала в бъркотия и има около 500 000 реда, което прави наистина трудно за мен да експлодирам и управлявам огромни масиви за всеки проект (скорошен проект с ~ 200 000 реда).
