柯基笔记：MySQL导入大数据

• 作者：柯基大佬
• 日期：2021-10-20
• 类型：Java
• 说明：本文源于 柯基 手写摘要，如需转载请带上链接或注明出处！

大数据库导表

数据库引擎：

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

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

步骤

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

拆表

一、品牌表 car_brand

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'

二、子品牌表 car_sub_brand

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 '%进口%'

三、车系表 car_series

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 = ''

四、车型表 car_type

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. 平行进口

五、删除old_id临时字段