В моей базе данных есть ненормализованная таблица с именем 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 - Вставить запятую разделенный список на нормализованные таблицы с помощью хранимой процедуры в соответствии с моими потребностями, но мне это не удалось.
Пожалуйста, помогите мне, моя таблица БД превратилась в беспорядок, и в ней около 500 000 строк, из-за чего мне очень сложно взорвать и управлять огромными массивами в каждом проекте (последний проект с ~ 200 000 строк).
