Java 3-3、大数据库导表

数据库引擎：

• InnoDB
  支持事务、外键等高级数据库功能，适用于业务

• MyISAM
  不支持事务，强调高性能

1.打开.sql文件，搜索“ENGINE=InnoDB”，把所有数据库引擎修改为“MyISAM”
2.命令行启动MySQL
3.show databases;
4.use xxx(数据库名);
5.source xxx.sql;

1. 查询表结构与自增长ID：show create table xxx

2. 清空表

3. 重置自增长ID：truncate table xxx

4. 新建一条数据，检查自增长id是否从1开始

5. 新建临时字段源id

6. 读表插入新表：将读取的表字段下的数据，插入另一长表的字段下

INSERT INTO tableName(field...) SELECt field... FROM tableName

7. 将表创建人数据填上
   update tableName set field = 'xxx'

sql

show create table car_type

TRUNCATE TABLE car_brand

insert into car_brand(name, letter, icon, old_id)
select p_pinpai, p_shouzimu, p_pinpai_logo, p_pinpai_id from yf_brand

update car_brand set creater_name = 'bzb'

1. 数据分组插入

insert into tableName_new(field1, field2)
select field1, field2 from tableName group field1, field2

2. 修改车系表，关联品牌id， 子品牌表brand_id = 品牌表old_id

update tabName1, tabName2
set tabName1.xx = tabName2.xx
where tabName1.xx = tabName2.xx

3. 将表创建人数据填上
4. 将所有来源设置为国产：3
5. 模糊匹配所有带“进口”标识的数据，将来源改为进口：2

sql

insert into car_sub_brand(name, brand_id)
select p_changshang, p_pinpai_id from yf_series group by p_changshang, p_pinpai_id

update car_sub_brand csb, car_brand cb 
set csb.brand_id = cb.id 
where csb.brand_id = cb.old_id

update car_sub_brand set creater_name = 'bzb'

update car_sub_brand set source_type = 3

update car_sub_brand set source_type = 2 where name like '%进口%'

1. 根据子品牌表中的 厂商+品牌id 分组

车系表新建临时字段：old_id, brand_id, changshang

注意：

• brand_id、changshang 两个字段用于关联子品牌表

• old_id 源车系id，用于后续的车型表关联

2. 读表插入新表

insert into car_series(name, old_id, brand_id, changshang)
select p_chexi, p_chexi_id, p_pinpai_id, p_changshang 
from yf_series

3. 子品牌表和车系表关联（非主键）

update car_series cs, car_sub_brand csb 
set cs.sub_brand_id = csb.id
where cs.brand_id = csb.old_id
and cs.changshang = csb.name 

4. 填充创建人字段

update car_series set creater_name = 'bzb'

5. 检查遗漏关联

select * from car_series where sub_brand_id is NULL or sub_brand_id = ''

1. 从yc_car中拆出需要的字段及数据（name是必须的，不再需要old_id）

insert into car_type(
name,
price,
price_str,
year_type,
sale_state,
effluent_standard,
fadongji,
qigangshu,
biansuxiang,
jinqixingshi,
qudongfangshi,
pailiang,
ranyouxinghao,
youxiangrongji,
zuigaochesu,
zuidamali,
zuidagonglv,
chang_kuan_gao,
zuoweishu,
xinglixiangrongji,
cheshenjiegou,
size_type,
series_id
)

select 
p_chexingmingcheng,
p_changshangzhidaojia_search,
p_changshangzhidaojia_yuan,
p_niankuan,
p_xiaoshouzhuangtai,
p_huanbaobiaozhun,
p_fadongji,
p_qigangshu_ge,
p_biansuxiang,
p_jinqixingshi,
p_qudongfangshi,
p_pailiang_l,
p_ranyoubiaohao,
p_youxiangrongji_l,
p_zuigaochesu_km_h,
p_zuidamali_ps,
p_zuidagongshuai_kw,
p_chang_kuan_gao_mm,
p_zuoweishu_ge,
p_hanglixiangrongji_l,
p_cheshenjiegou,
p_jibie,
p_chexing_id
from yf_car

2. 关联车系表

update car_type ct, car_series cs
set ct.series_id = cs.id
where ct.series_id = cs.old_id

-- 先将所有数据设置为 1：国产
update car_type set specification_id = 1

-- 把包含“进口”内容的数据修改为 2：进口
update car_type set specification_id = 2 
where name like '%进口%'

-- 填充创建人字段
update car_series set creater_name = 'bzb'

-- 检查遗漏关联
select * from car_type where series_id is NULL or series_id = ''

3. 删除脏数据

delete from car_type where name is NULL or name = ''

4. 下架过时车型(先下架全部车型，再上架需要的)

update car_type set enable = 0

update car_type set enable = 1 where year_type = '2015款'
...
update car_type set enable = 1 where year_type = '即将销售'

5. 平行进口