mysql根据查询数据进行批量修改

女爷i 2023-07-18 12:45 17阅读 0赞

在工作时遇到了一个问题，需要通过查询一张表中的数据来修改另外一张表中的数据，这里不方便谈具体业务，简单通过下面两张表描述一下，这里不要纠结我建的表的合理性，只是简单说明一下。

teacher 表

![20200327111643175.png][]

student 表

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1lfdm9jZWZlbnJpcg_size_16_color_FFFFFF_t_70][]

业务是将student表中teacher\_name 一一对应的添加到teacher 表中 。

![2020032711233655.png][]

这里我提出两种方法供大家讨论。

1.通过mysql的存储过程来完成

DELIMITER $$
    
    USE `test`$$
    
    DROP PROCEDURE IF EXISTS `t3`$$
    
    CREATE DEFINER=`root`@`%` PROCEDURE `t3`()
    BEGIN
    	DECLARE q VARCHAR(200);
    	DECLARE s VARCHAR(200);
    	DECLARE p BIGINT;
    	DECLARE f BIGINT;
    	DECLARE e BIGINT;
    	DECLARE i INT DEFAULT 1;
    	SET q = (SELECT GROUP_CONCAT(teacher_name SEPARATOR ',') FROM student);
    	SET s = (SELECT GROUP_CONCAT(teacher_id SEPARATOR ',') FROM student);
    	SET p = (SELECT COUNT(1) FROM student);
    	WHILE i <= p DO
    		SET f=SUBSTRING_INDEX(SUBSTRING_INDEX(q,',',i),',',-1);
    		SET e=SUBSTRING_INDEX(SUBSTRING_INDEX(s,',',i),',',-1);
    		UPDATE teacher SET teacher_name =  f WHERE id =  e;
    		SET i= i+1;
    	END WHILE;
    	
        END$$
    
    DELIMITER ;

2.通过shell脚本完成

#!/bin/bash
    truncate -s 0 ab1.txt
    truncate -s 0 ab2.txt
    mysql -uroot -h192.168.2.100 -proot test -e "SELECT teacher_name FROM student "  >> ab1.txt
    mysql -uroot -h192.168.2.100 -proot test -e "SELECT teacher_id FROM student"  >> ab2.txt
    
    
    str=(`cat ./ab1.txt | awk -F ' ' '{print $1}'`)
    str2=(`cat ./ab2.txt | awk -F ' ' '{print $1}'`)
    for i in ${!str[@]}
    do
       if [ $i -gt 0 ]
       then
            mysql -uroot -h192.168.2.100 -proot test -e "UPDATE teacher SET teacher_name =  ${str[$i]} WHERE id = ${str2[$i]}"
       fi
    done

因为这些都是新学的，写的有可能不是特别合理，仅供大家开放思维。

[20200327111643175.png]: /images/20230528/22c074ebc5b04063a4e4768ba157260c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1lfdm9jZWZlbnJpcg_size_16_color_FFFFFF_t_70]: /images/20230528/82abfd5e8e4a4100b014b8d1e83318bf.png
[2020032711233655.png]: /images/20230528/a9dff43ad832465e8921e258f8e7418c.png