1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
| drop table if exists img;
create table img
(
user_id int(8) unsigned not null auto_increment primary key,
next_table_id smallint(5) unsigned not null default 1
)engine=innodb;
drop table if exists images;
create table images
(
img_id smallint(5) unsigned not null,
user_id int(8) unsigned not null default 0,
primary key (user_id, img_id) -- composite clustered index
)engine=innodb;
delimiter #
create trigger images_before_ins_trig before insert on images
for each row
begin
declare v_id int unsigned default 1;
select next_table_id + 1 into v_id from img where user_id = new.user_id;
set new.img_id = v_id;
replace into img (user_id, next_table_id) VALUES (new.user_id, v_id);
end
delimiter ;
insert into images (user_id) values (100001),(100001),(100002),(100002); |
Partager