У меня есть один идентификатор почтового хранилища и его теги:
Post_id | Tags -------------------------------------- 1 | keyword1,keyword2,keyword3 Я хочу, чтобы каждая петля за этой таблицей делала петлю:
поместите ключевое слово1, keyword2, keyword3 в новую таблицу:
word_id | word_value ------------------------- 1 | keyword1 2 | keyword2 3 | keyword3
get mysql_insert_id () foreach (или существует word_id, если word_value уже существует), а затем введите новую таблицу:
post_id | word_id ------------------ 1 | 1 1 | 2 1 | 3
Я использую php и mysql для выполнения этой задачи, но это происходит медленно. У кого-нибудь есть хорошая идея?
сделайте что-то вроде этого:
-- TABLES drop table if exists post_tags; create table post_tags ( post_id int unsigned not null auto_increment primary key, tags_csv varchar(1024) not null ) engine=innodb; drop table if exists keywords; create table keywords ( keyword_id mediumint unsigned not null auto_increment primary key, name varchar(255) unique not null ) engine=innodb; -- optimised for queries such as - select all posts that have keyword 3 drop table if exists post_keywords; create table post_keywords ( keyword_id mediumint unsigned not null, post_id int unsigned not null, primary key (keyword_id, post_id), -- clustered composite PK ! key (post_id) ) engine=innodb; -- STORED PROCEDURES drop procedure if exists normalise_post_tags; delimiter # create procedure normalise_post_tags() proc_main:begin declare v_cursor_done tinyint unsigned default 0; -- watch out for variable names that have the same names as fields !! declare v_post_id int unsigned; declare v_tags_csv varchar(1024); declare v_keyword varchar(255); declare v_keyword_id mediumint unsigned; declare v_tags_done tinyint unsigned; declare v_tags_idx int unsigned; declare v_cursor cursor for select post_id, tags_csv from post_tags order by post_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_csv; -- split the out the v_tags_csv and insert set v_tags_done = 0; set v_tags_idx = 1; while not v_tags_done do set v_keyword = substring(v_tags_csv, v_tags_idx, if(locate(',', v_tags_csv, v_tags_idx) > 0, locate(',', v_tags_csv, v_tags_idx) - v_tags_idx, length(v_tags_csv))); if length(v_keyword) > 0 then set v_tags_idx = v_tags_idx + length(v_keyword) + 1; set v_keyword = trim(v_keyword); -- add the keyword if it doesnt already exist insert ignore into keywords (name) values (v_keyword); select keyword_id into v_keyword_id from keywords where name = v_keyword; -- add the post_keywords insert ignore into post_keywords (keyword_id, post_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 ; -- TEST DATA insert into post_tags (tags_csv) values ('keyword1,keyword2,keyword3'), ('keyword1,keyword5'), ('keyword4,keyword3,keyword6,keyword1'); -- TESTING call normalise_post_tags(); select * from post_tags order by post_id; select * from keywords order by keyword_id; select * from post_keywords order by keyword_id, post_id;3-- TABLES drop table if exists post_tags; create table post_tags ( post_id int unsigned not null auto_increment primary key, tags_csv varchar(1024) not null ) engine=innodb; drop table if exists keywords; create table keywords ( keyword_id mediumint unsigned not null auto_increment primary key, name varchar(255) unique not null ) engine=innodb; -- optimised for queries such as - select all posts that have keyword 3 drop table if exists post_keywords; create table post_keywords ( keyword_id mediumint unsigned not null, post_id int unsigned not null, primary key (keyword_id, post_id), -- clustered composite PK ! key (post_id) ) engine=innodb; -- STORED PROCEDURES drop procedure if exists normalise_post_tags; delimiter # create procedure normalise_post_tags() proc_main:begin declare v_cursor_done tinyint unsigned default 0; -- watch out for variable names that have the same names as fields !! declare v_post_id int unsigned; declare v_tags_csv varchar(1024); declare v_keyword varchar(255); declare v_keyword_id mediumint unsigned; declare v_tags_done tinyint unsigned; declare v_tags_idx int unsigned; declare v_cursor cursor for select post_id, tags_csv from post_tags order by post_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_csv; -- split the out the v_tags_csv and insert set v_tags_done = 0; set v_tags_idx = 1; while not v_tags_done do set v_keyword = substring(v_tags_csv, v_tags_idx, if(locate(',', v_tags_csv, v_tags_idx) > 0, locate(',', v_tags_csv, v_tags_idx) - v_tags_idx, length(v_tags_csv))); if length(v_keyword) > 0 then set v_tags_idx = v_tags_idx + length(v_keyword) + 1; set v_keyword = trim(v_keyword); -- add the keyword if it doesnt already exist insert ignore into keywords (name) values (v_keyword); select keyword_id into v_keyword_id from keywords where name = v_keyword; -- add the post_keywords insert ignore into post_keywords (keyword_id, post_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 ; -- TEST DATA insert into post_tags (tags_csv) values ('keyword1,keyword2,keyword3'), ('keyword1,keyword5'), ('keyword4,keyword3,keyword6,keyword1'); -- TESTING call normalise_post_tags(); select * from post_tags order by post_id; select * from keywords order by keyword_id; select * from post_keywords order by keyword_id, post_id;3-- TABLES drop table if exists post_tags; create table post_tags ( post_id int unsigned not null auto_increment primary key, tags_csv varchar(1024) not null ) engine=innodb; drop table if exists keywords; create table keywords ( keyword_id mediumint unsigned not null auto_increment primary key, name varchar(255) unique not null ) engine=innodb; -- optimised for queries such as - select all posts that have keyword 3 drop table if exists post_keywords; create table post_keywords ( keyword_id mediumint unsigned not null, post_id int unsigned not null, primary key (keyword_id, post_id), -- clustered composite PK ! key (post_id) ) engine=innodb; -- STORED PROCEDURES drop procedure if exists normalise_post_tags; delimiter # create procedure normalise_post_tags() proc_main:begin declare v_cursor_done tinyint unsigned default 0; -- watch out for variable names that have the same names as fields !! declare v_post_id int unsigned; declare v_tags_csv varchar(1024); declare v_keyword varchar(255); declare v_keyword_id mediumint unsigned; declare v_tags_done tinyint unsigned; declare v_tags_idx int unsigned; declare v_cursor cursor for select post_id, tags_csv from post_tags order by post_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_csv; -- split the out the v_tags_csv and insert set v_tags_done = 0; set v_tags_idx = 1; while not v_tags_done do set v_keyword = substring(v_tags_csv, v_tags_idx, if(locate(',', v_tags_csv, v_tags_idx) > 0, locate(',', v_tags_csv, v_tags_idx) - v_tags_idx, length(v_tags_csv))); if length(v_keyword) > 0 then set v_tags_idx = v_tags_idx + length(v_keyword) + 1; set v_keyword = trim(v_keyword); -- add the keyword if it doesnt already exist insert ignore into keywords (name) values (v_keyword); select keyword_id into v_keyword_id from keywords where name = v_keyword; -- add the post_keywords insert ignore into post_keywords (keyword_id, post_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 ; -- TEST DATA insert into post_tags (tags_csv) values ('keyword1,keyword2,keyword3'), ('keyword1,keyword5'), ('keyword4,keyword3,keyword6,keyword1'); -- TESTING call normalise_post_tags(); select * from post_tags order by post_id; select * from keywords order by keyword_id; select * from post_keywords order by keyword_id, post_id;
для каждого ключевого слова
insert into newtable (id, keyword) select id, 'aKeyword' from oldtable where oldtable.keywords like '%aKeyword%'
если oldtable.keywords – это просто VARCHAR, или
insert into newtable (id, keyword) select id, 'aKeyword' from oldtable where FIND_IN_SET('aKeyword',keywords)>0
если это тип SET.