Mysql迁移数据生成随机主键的几种方式

冷不防 2023-10-10 17:41 15阅读 0赞

方式一：采用数据行号

mysql中没有rownum的概念，需要使用(select @rownum:=0) b 来定义一个行号的变量，再在select 的后面引用即可

SELECT @rownum:=@rownum+1 as rownum,unit_id,unit_name,unit_parent_id,sub_center_code,sub_center_name,enterprise_model,enterprise_model_name,unit_class,unit_class_name,unit_point_x,unit_point_y,assessment_method,total_score,
    case when score_level = '优秀' then '1' when score_level = '良好' then '2' when score_level = '及格' then '3' when score_level = '危险' then '4' end  score_level ,
    defeat_rate,create_time,optimize_total_score,
    case when optimize_score_level = '优秀' then '1' when optimize_score_level = '良好' then '2' when optimize_score_level = '及格' then '3' when optimize_score_level = '危险' then '4' end  optimize_score_level 
    FROM bw_smart_risk.risk_result_record a,(select @rownum:=0) b
    where create_time >= '2023-04-01 00:00:00' and create_time <= '2023-04-30 23:59:59'
    and unit_id = '340111YZDW201912130001';

方式二：采用MD5加密uuid生成随机主键（推荐）

核心：select MD5(uuid()) as id from dual ，具体使用如下

SELECT MD5(uuid()) as id,unit_id,unit_name,unit_parent_id,sub_center_code,sub_center_name,enterprise_model,enterprise_model_name,unit_class,unit_class_name,unit_point_x,unit_point_y,assessment_method,total_score,
    case when score_level = '优秀' then '1' when score_level = '良好' then '2' when score_level = '及格' then '3' when score_level = '危险' then '4' end  score_level ,
    defeat_rate,create_time,optimize_total_score,
    case when optimize_score_level = '优秀' then '1' when optimize_score_level = '良好' then '2' when optimize_score_level = '及格' then '3' when optimize_score_level = '危险' then '4' end  optimize_score_level 
    FROM bw_smart_risk.risk_result_record a
    where create_time >= '2023-04-01 00:00:00' and create_time <= '2023-04-30 23:59:59'
    and unit_id = '340111YZDW201912130001';