MySQL-数据修复-课程的人员的userId大小写问题-大小写使用md5判断-list转临时表

edu_course_associated_user 大写 
edu_course_learning_analysis_t 大写 


edu_user_learning_course_record_t 小写 以这为准
edu_user_learning_course_record_o 小写

查询1sql
SELECT t.container_id, t.id as '分析表id', t.user_id as "分析表userId", u.id "关联表id",u.user_id "关联表userId",
t2.user_id as "学习记录表userId",  md5(t.user_id), md5(u.user_id) 
from edu_course_learning_analysis_t t 
INNER JOIN edu_course_associated_user u on t.user_id = u.user_id and t.course_id = u.course_id 
INNER JOIN edu_user_learning_course_record_t t2 on t.user_id = t2.user_id  and t.course_id = t2.course_id 
and t.container_id = t2.container_id where md5(t.user_id) <> md5(t2.user_id);
+----------------------+-------------+-----------------+-------------+-----------------+-----------------------+----------------------------------+----------------------------------+
| container_id         | 分析表id    | 分析表userId    | 关联表id    | 关联表userId    | 学习记录表userId      | md5(t.user_id)                   | md5(u.user_id)                   |
+----------------------+-------------+-----------------+-------------+-----------------+-----------------------+----------------------------------+----------------------------------+
| tianjinjingjikaifaqu |      117953 | JASON150213     |      346119 | JASON150213     | jason150213           | dae0c6e56a109dce5ff276527fe241bf | dae0c6e56a109dce5ff276527fe241bf |
| tianjintianyaoyaoye  |      651983 | TY0112          |      391735 | TY0112          | ty0112                | a5673dc012db682c7f79fcb8a321c8d8 | a5673dc012db682c7f79fcb8a321c8d8 |
| tianjintianyaoyaoye  |      650607 | TY0112          |      385803 | TY0112          | ty0112                | a5673dc012db682c7f79fcb8a321c8d8 | a5673dc012db682c7f79fcb8a321c8d8 |
| tianjintianyaoyaoye  |      651982 | TY0111          |      391734 | TY0111          | ty0111                | 5eff723f3c45ad609918b74c7b986675 | 5eff723f3c45ad609918b74c7b986675 |
| tianjintianyaoyaoye  |      651981 | TY0110          |      391733 | TY0110          | ty0110                | 0a1be5d9ce0cb336aca1fcf42ba566eb | 0a1be5d9ce0cb336aca1fcf42ba566eb |
| tianjintianyaoyaoye  |      651984 | TY0113          |      391736 | TY0113          | ty0113                | 3fa65a815dbbf52f1322010ce8ebf00a | 3fa65a815dbbf52f1322010ce8ebf00a |
| tianjintianyaoyaoye  |      653145 | TY8303          |      392821 | TY8303          | ty8303                | 7fd2e72fa0cc5e3c3193f32cd3f044db | 7fd2e72fa0cc5e3c3193f32cd3f044db |
| tianjintianyaoyaoye  |      643313 | TY0111          |      335614 | TY0111          | ty0111                | 5eff723f3c45ad609918b74c7b986675 | 5eff723f3c45ad609918b74c7b986675 |
| tianjintianyaoyaoye  |      684155 | TY0115          |      579648 | TY0115          | ty0115                | a6ee0df500733e02c71f2b602b1ac4ab | a6ee0df500733e02c71f2b602b1ac4ab |
| tianjintianyaoyaoye  |      684153 | TY0108          |      579646 | TY0108          | ty0108                | b0255c58527c5c132d4a3abef03fc218 | b0255c58527c5c132d4a3abef03fc218 |
| tianjintianyaoyaoye  |      684154 | TY0109          |      579647 | TY0109          | ty0109                | be8b674f17fd50f2625c917335692a62 | be8b674f17fd50f2625c917335692a62 |
| tianjintianyaoyaoye  |      684160 | TY0122          |      579653 | TY0122          | ty0122                | cb0c4af2d66aff1d6dcac368e7944d0a | cb0c4af2d66aff1d6dcac368e7944d0a |

+----------------------+-------------+-----------------+-------------+-----------------+-----------------------+----------------------------------+----------------------------------+
174 rows in set (1.70 sec)


-- 以edu_user_learning_course_record_t为准，更新edu_course_learning_analysis_t、edu_course_associated_user成小写

执行2sql：
UPDATE  edu_course_learning_analysis_t t 
INNER JOIN edu_course_associated_user u on t.user_id = u.user_id and t.course_id = u.course_id 
INNER JOIN edu_user_learning_course_record_t t2 on t.user_id = t2.user_id  and t.course_id = t2.course_id 
and t.container_id = t2.container_id set t.user_id = t2.user_id,u.user_id=t2.user_id where md5(t.user_id) <> md5(t2.user_id) and t.container_id = 'tianjintianyaoyaoye';


-- 以上方案不可行（edu_user_learning_course_record_t 不一定有值好像）
-- 测试
DROP TABLE if exists user_id_change;
create temporary table user_id_change 
(
 user_id varchar(255)
);

-- 这个需要拼接一下
INSERT into user_id_change(user_id) VALUES ("admin_51"),("yangkaiwen");

select * from user_id_change;

SELECT u.user_id,t.user_id from edu_course_associated_user u left JOIN edu_course_learning_analysis_t t on t.user_id = u.user_id and t.container_id = u.container_id 
inner join user_id_change a on a.user_id = u.user_id 
where u.container_id  = "BaoXianFuWu" and u.user_id in ("admin_51","yangkaiwen") GROUP BY md5(CONCAT(u.user_id,t.user_id)) ;

UPDATE  edu_course_associated_user u 
left JOIN edu_course_learning_analysis_t t on t.user_id = u.user_id and t.container_id = u.container_id 
inner join user_id_change a on a.user_id = u.user_id 
set t.user_id = a.user_id,u.user_id=a.user_id where u.container_id  = "BaoXianFuWu" and u.user_id in ("admin_51","yangkaiwen") ;

DROP TABLE if exists user_id_change;

select * from user_id_change;



-------------------尝试修复 edu_user_intermediate-放弃------
 DROP TABLE if exists user_id_change;
create temporary table user_id_change 
(
 user_id varchar(255)
);

-- 这个需要拼接一下
INSERT into user_id_change(user_id) VALUES ('zhengfuyizu'),('luyike'),('admin_51');

select * from user_id_change;

SELECT  * from edu_user_intermediate WHERE id <=2;
 -- 适合全大写，或者全小写的情况
SELECT u.id as intermediate_id , u.handled_user_id as intermediat_user_id,REPLACE(REPLACE(u.handled_user_id,LOWER(a.user_id),a.user_id),UPPER(a.user_id),a.user_id) from edu_user_intermediate u 
left join user_id_change a on  LOWER(u.handled_user_id) like CONCAT("%" ,LOWER(a.user_id),"%") 
where 
 u.container_id = "BaoXianFuWu" and a.user_id in ('zhengfuyizu','luyike','admin_51') and u.id <=2;

UPDATE  edu_user_intermediate u left join user_id_change a on LOWER(u.handled_user_id) like CONCAT("%" ,LOWER(a.user_id),"%") 
set u.handled_user_id=REPLACE(REPLACE(u.handled_user_id,LOWER(a.user_id),a.user_id),UPPER(a.user_id),a.user_id)
where 
  u.container_id  = "BaoXianFuWu" and a.user_id in ('zhengfuyizu','luyike','admin_51') and u.id <=2;

-- 测试数据
update edu_user_intermediate set handled_user_id  = "ZHENGFUYIZU,LUYIKE" WHERE id = 1;
update edu_user_intermediate set handled_user_id  = "817010,ADMIN_51" WHERE id = 2;

不行：
    如果一行数据里有2个人需要修改，那上面的只能改一个人的

    所以需要每个人去执行 
         String userIdListStr = "'ty8431','ty7980','ty8303','ty8266','ty8425','ty5653','ty6209','ty4550','ty4410','ty4397','ty8324','ty8432','ty8301','ty8343','ty5100','ty6002','ty1010001','ty8380','ty3935','ty8342','ty1034640','ty8337','ty1050071','ty8442','ty1070001','ty8344','ty8332','ty8323','ty6442','ty8405','ty5702','ty5703','ty5697','ty1085728','ty1080041','ty108004','ty0249','ty0247','ty0248','ty5518','ty2138','ty3551','ty4444','ty4227','ty0017','tywsz0015','ty0020','ty0031','ty0028','ty0025','ty0008','ty0032','ty0042','ty3470','ty4592','ty8260','ty8279','ty8309','ty8310','ty8330','ty8401','ty8404','ty6659','ty8418','ty8406','ty8421','ty0108','ty0109','ty0001','ty0116','ty0118','ty0115','ty0117','tyzk8078','ty0123','ty0122','ty0121','ty8464','ty0151'";

        String[] userIdListArr  = userIdListStr.split(",");

        String containerId = "tianjintianyaoyaoye";
        String sqlFormmat = "update edu_user_intermediate set handled_user_id  = REPLACE(REPLACE(handled_user_id,LOWER('%s'),'%s'),UPPER('%s'),'%s')  " +
                "WHERE  container_id  = '%s' and LOWER(handled_user_id) like CONCAT('%%',LOWER('%s'), '%%');"; 
         // 适合全大写，或者全小写的情况
        for (String userId : userIdListArr) {
            userId = userId.replaceAll("'","");
            String executeSql = String.format(sqlFormmat,userId,userId,userId,userId,containerId,userId);
            System.out.println(executeSql);
        }


-------------------线上待执行,容器id要换--------------------------

每次按容器来

DROP TABLE if exists user_id_change;
create temporary table user_id_change 
(
 user_id varchar(255),
 UNIQUE KEY `idx_user` (`user_id`)
);

-- 这个需要拼接一下
INSERT into user_id_change(user_id) VALUES ('ty8431'),('ty7980'),('ty8303'),('ty8266'),('ty8425'),('ty5653'),('ty6209'),('ty4550'),('ty4410'),('ty4397'),('ty8324'),('ty8432'),('ty8301'),('ty8343'),('ty5100'),('ty6002'),('ty1010001'),('ty8380'),('ty3935'),('ty8342'),('ty1034640'),('ty8337'),('ty1050071'),('ty8442'),('ty1070001'),('ty8344'),('ty8332'),('ty8323'),('ty6442'),('ty8405'),('ty5702'),('ty5703'),('ty5697'),('ty1085728'),('ty1080041'),('ty108004'),('ty0249'),('ty0247'),('ty0248'),('ty5518'),('ty2138'),('ty3551'),('ty4444'),('ty4227'),('ty0017'),('tywsz0015'),('ty0020'),('ty0031'),('ty0028'),('ty0025'),('ty0008'),('ty0032'),('ty0042'),('ty3470'),('ty4592'),('ty8260'),('ty8279'),('ty8309'),('ty8310'),('ty8330'),('ty8401'),('ty8404'),('ty6659'),('ty8418'),('ty8406'),('ty8421'),('ty0108'),('ty0109'),('ty0001'),('ty0116'),('ty0118'),('ty0115'),('ty0117'),('tyzk8078'),('ty0123'),('ty0122'),('ty0121'),('ty8464'),('ty0151');

select * from user_id_change;

-- 这一步收藏一下修改到的数据
SELECT a.user_id as newUserId, u.id as associated_id , u.user_id as associated_user_id, t.id as analysis_id,t.user_id analysis_user_id from edu_course_associated_user u left JOIN edu_course_learning_analysis_t t on t.user_id = u.user_id and t.container_id = u.container_id 
inner join user_id_change a on a.user_id = u.user_id 
where md5(u.user_id) <> md5(a.user_id) and u.container_id = "tianjintianyaoyaoye" and u.user_id in ('ty8431','ty7980','ty8303','ty8266','ty8425','ty5653','ty6209','ty4550','ty4410','ty4397','ty8324','ty8432','ty8301','ty8343','ty5100','ty6002','ty1010001','ty8380','ty3935','ty8342','ty1034640','ty8337','ty1050071','ty8442','ty1070001','ty8344','ty8332','ty8323','ty6442','ty8405','ty5702','ty5703','ty5697','ty1085728','ty1080041','ty108004','ty0249','ty0247','ty0248','ty5518','ty2138','ty3551','ty4444','ty4227','ty0017','tywsz0015','ty0020','ty0031','ty0028','ty0025','ty0008','ty0032','ty0042','ty3470','ty4592','ty8260','ty8279','ty8309','ty8310','ty8330','ty8401','ty8404','ty6659','ty8418','ty8406','ty8421','ty0108','ty0109','ty0001','ty0116','ty0118','ty0115','ty0117','tyzk8078','ty0123','ty0122','ty0121','ty8464','ty0151') GROUP BY md5(CONCAT(u.user_id,t.user_id)) ;

UPDATE  edu_course_associated_user u 
left JOIN edu_course_learning_analysis_t t on t.user_id = u.user_id and t.container_id = u.container_id 
inner join user_id_change a on a.user_id = u.user_id 
set t.user_id = a.user_id,u.user_id=a.user_id where  md5(u.user_id) <> md5(a.user_id) and u.container_id  = "tianjintianyaoyaoye" and u.user_id in ('ty8431','ty7980','ty8303','ty8266','ty8425','ty5653','ty6209','ty4550','ty4410','ty4397','ty8324','ty8432','ty8301','ty8343','ty5100','ty6002','ty1010001','ty8380','ty3935','ty8342','ty1034640','ty8337','ty1050071','ty8442','ty1070001','ty8344','ty8332','ty8323','ty6442','ty8405','ty5702','ty5703','ty5697','ty1085728','ty1080041','ty108004','ty0249','ty0247','ty0248','ty5518','ty2138','ty3551','ty4444','ty4227','ty0017','tywsz0015','ty0020','ty0031','ty0028','ty0025','ty0008','ty0032','ty0042','ty3470','ty4592','ty8260','ty8279','ty8309','ty8310','ty8330','ty8401','ty8404','ty6659','ty8418','ty8406','ty8421','ty0108','ty0109','ty0001','ty0116','ty0118','ty0115','ty0117','tyzk8078','ty0123','ty0122','ty0121','ty8464','ty0151') ;

Query OK, 626 rows affected (1 min 24.55 sec)
Rows matched: 2422  Changed: 626  Warnings: 0

再查询 “查询1sql”
 SELECT t.container_id, t.id as '分析表id', t.user_id as "分析表userId", u.id "关联表id",u.user_id "关联表userId",
    -> t2.user_id as "学习记录表userId",  md5(t.user_id), md5(u.user_id)
    -> from edu_course_learning_analysis_t t
    -> INNER JOIN edu_course_associated_user u on t.user_id = u.user_id and t.course_id = u.course_id
    -> INNER JOIN edu_user_learning_course_record_t t2 on t.user_id = t2.user_id  and t.course_id = t2.course_id
    -> and t.container_id = t2.container_id where md5(t.user_id) <> md5(t2.user_id);
+----------------------+-------------+------------------+-------------+------------------+-----------------------+----------------------------------+----------------------------------+
| container_id         | 分析表id    | 分析表userId     | 关联表id    | 关联表userId     | 学习记录表userId      | md5(t.user_id)                   | md5(u.user_id)                   |
+----------------------+-------------+------------------+-------------+------------------+-----------------------+----------------------------------+----------------------------------+
| tianjinjingjikaifaqu |      117953 | JASON150213      |      346119 | JASON150213      | jason150213           | dae0c6e56a109dce5ff276527fe241bf | dae0c6e56a109dce5ff276527fe241bf |
| tianjintianyaoyaoye  |      651983 | TY0112           |      391735 | TY0112           | ty0112                | a5673dc012db682c7f79fcb8a321c8d8 | a5673dc012db682c7f79fcb8a321c8d8 |
| tianjintianyaoyaoye  |      650607 | TY0112           |      385803 | TY0112           | ty0112                | a5673dc012db682c7f79fcb8a321c8d8 | a5673dc012db682c7f79fcb8a321c8d8 |
| tianjintianyaoyaoye  |      651982 | TY0111           |      391734 | TY0111           | ty0111                | 5eff723f3c45ad609918b74c7b986675 | 5eff723f3c45ad609918b74c7b986675 |
| tianjintianyaoyaoye  |      651981 | TY0110           |      391733 | TY0110           | ty0110                | 0a1be5d9ce0cb336aca1fcf42ba566eb | 0a1be5d9ce0cb336aca1fcf42ba566eb |
| tianjintianyaoyaoye  |      651984 | TY0113           |      391736 | TY0113           | ty0113                | 3fa65a815dbbf52f1322010ce8ebf00a | 3fa65a815dbbf52f1322010ce8ebf00a |
| tianjintianyaoyaoye  |      643313 | TY0111           |      335614 | TY0111           | ty0111                | 5eff723f3c45ad609918b74c7b986675 | 5eff723f3c45ad609918b74c7b986675 |
| tianjintianyaoyaoye  |      688270 | TY1050070        |      588319 | TY1050070        | ty1050070             | 8078eea4842e347162035b937fd0d158 | 8078eea4842e347162035b937fd0d158 |
| tianjintianyaoyaoye  |      688252 | TY0071           |      588346 | TY0071           | ty0071                | ed6de5555e624c1316e09bed88e007f8 | ed6de5555e624c1316e09bed88e007f8 |
| guangxiyufengshuinij |      715548 | YFSN_14797720614 |      646220 | YFSN_14797720614 | yfsn_14797720614      | f786cd546e927193c0ca09b7f6447a0e | f786cd546e927193c0ca09b7f6447a0e |
+----------------------+-------------+------------------+-------------+------------------+-----------------------+----------------------------------+----------------------------------+
10 rows in set (2.28 sec)

确认平台的这些人，到底是大小写？查询 tianjintianyaoyaoye下的各个数据，但是组织架构中搜索不到了。去掉 tianjinjingjikaifaqu ，guangxiyufengshuinij 
执行2sql：


DROP TABLE if exists user_id_change;

select * from user_id_change;

-- 这个不能执行吧 获取查询下 当前容器下的所有数据，似乎不能执行，这个表也有的其他表业务也会来查询
update edu_user_intermediate set handled_user_id  = REPLACE(REPLACE(handled_user_id,LOWER('ty8431'),'ty8431'),UPPER('ty8431'),'ty8431')  WHERE  container_id  = 'tianjintianyaoyaoye' and LOWER(handled_user_id) like CONCAT('%',LOWER('ty8431'), '%');
update edu_user_intermediate set handled_user_id  = REPLACE(REPLACE(handled_user_id,LOWER('ty7980'),'ty7980'),UPPER('ty7980'),'ty7980')  WHERE  container_id  = 'tianjintianyaoyaoye' and LOWER(handled_user_id) like CONCAT('%',LOWER('ty7980'), '%');
update edu_user_intermediate set handled_user_id  = REPLACE(REPLACE(handled_user_id,LOWER('ty8303'),'ty8303'),UPPER('ty8303'),'ty8303')  WHERE  container_id  = 'tianjintianyaoyaoye' and LOWER(handled_user_id) like CONCAT('%',LOWER('ty8303'), '%');
update edu_user_intermediate set handled_user_id  = REPLACE(REPLACE(handled_user_id,LOWER('ty8266'),'ty8266'),UPPER('ty8266'),'ty8266')  WHERE  container_id  = 'tianjintianyaoyaoye' and LOWER(handled_user_id) like CONCAT('%',LOWER('ty8266'), '%');