1,添加excel对应的字段表,全部为varchar类型(navicat中新建表,粘贴复制字段)。
2,mysql表添加示例数据,navicat导出向导,导出数据(普通excel格式),复制数据到excel文件。
3,navicat导入向导导入excel数据。
4,创建视图:(需要格式转换)
SELECT
id,
NAME,
age,
birthday,
CASE sex WHEN '男' THEN 1 when '女' then 0 ELSE null END as sex
FROM
user_cp ;
5,创建过程:
begin
#从excel导入数据 v1.1 huoyuanshen
declare t_user_id int(11);
declare t_age varchar(1000);
declare t_name varchar(1000);
declare t_birthday varchar(1000);
declare t_sex varchar(1000);
declare _done TINYINT(1) default 0;
declare cur_user cursor for select name,age,birthday,sex from view_user_cp ;
declare continue handler for not found set _done = 1;
open cur_user;
loop_xxx:loop
fetch cur_user into t_name,t_age,t_birthday,t_sex ;
if _done=1 then
leave loop_xxx;
end if;
begin
#第一个主表
insert into user_cp1(id,name,age) values(null,t_name,t_age) ;
#取得主键id
select max(id) into t_user_id from user_cp1;
#插入副表(包含主键id)
insert into user_cp2(id,user_id,birthday,sex) values(null,t_user_id,t_birthday,t_sex) ;
end;
end loop;
close cur_user;
end
2,mysql表添加示例数据,navicat导出向导,导出数据(普通excel格式),复制数据到excel文件。
3,navicat导入向导导入excel数据。
4,创建视图:(需要格式转换)
SELECT
id,
NAME,
age,
birthday,
CASE sex WHEN '男' THEN 1 when '女' then 0 ELSE null END as sex
FROM
user_cp ;
5,创建过程:
begin
#从excel导入数据 v1.1 huoyuanshen
declare t_user_id int(11);
declare t_age varchar(1000);
declare t_name varchar(1000);
declare t_birthday varchar(1000);
declare t_sex varchar(1000);
declare _done TINYINT(1) default 0;
declare cur_user cursor for select name,age,birthday,sex from view_user_cp ;
declare continue handler for not found set _done = 1;
open cur_user;
loop_xxx:loop
fetch cur_user into t_name,t_age,t_birthday,t_sex ;
if _done=1 then
leave loop_xxx;
end if;
begin
#第一个主表
insert into user_cp1(id,name,age) values(null,t_name,t_age) ;
#取得主键id
select max(id) into t_user_id from user_cp1;
#插入副表(包含主键id)
insert into user_cp2(id,user_id,birthday,sex) values(null,t_user_id,t_birthday,t_sex) ;
end;
end loop;
close cur_user;
end