mysql根据一个表的内容匹配到另一个表效率问题

zch11230 发表于 2022-1-11 19:48

哇，看到论坛还可以请教数据库问题，正好手里有个问题一直没有解决，就是像下面有两个表，test1是我要操作的表，有学员的成绩，但你猜怎么的，正好就没有班级，姓名，只有个学号，test2相当于是基础信息表，有学号，有姓名有班级，现在就是需要根据学号这个唯一的字段，在test2中去搜索，把匹配到的班级，姓名，给插入到test1中，就相当于excel的vlookup函数，我也通过百度，写出了一个下面样子的函数，但是效率太慢了，也就3000多条数据，就要执行半分钟。求大佬赐教。

mariadb> select * from test1;+------+------+------+------+------+
| 学号 | 语文 | 数学 | 班级 | 姓名 |
+------+------+------+------+------+
| 11 | 80 | 90 |    |    |
| 21 | 60 | 70 |    |    |
| 68 | 99 | 88 |    |    |
+------+------+------+------+------+
3 rows in set (0.09 sec)

mariadb> select * from test2;
+------+--------+------+
| 学号 | 班级 | 姓名 |
+------+--------+------+
| 11 | 一年级 | 老李 |
| 21 | 三年级 | 小王 |
| 68 | 二年级 | 阿珍 |
+------+--------+------+
3 rows in set (0.29 sec)

mariadb> call test_fx();
Query OK, 3 rows affected (0.10 sec)

mariadb> select * from test1;
+------+------+------+--------+------+
| 学号 | 语文 | 数学 | 班级 | 姓名 |
+------+------+------+--------+------+
| 11 | 80 | 90 | 一年级 | 老李 |
| 21 | 60 | 70 | 三年级 | 小王 |
| 68 | 99 | 88 | 二年级 | 阿珍 |
+------+------+------+--------+------+
3 rows in set (0.09 sec)

CREATE DEFINER=`root`@`%` PROCEDURE `test_fx`()
BEGIN
DECLARE isdone INT DEFAULT 1;
DECLARE sn int;

DECLARE search CURSOR FOR SELECT DISTINCT
my_db.test1.`学号`
FROM
my_db.test1
WHERE
my_db.test1.`姓名`='';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET isdone = 0;

OPEN search;

FETCH search INTO sn;

WHILE isdone DO
   UPDATE my_db.test1,my_db.test2
SET
   my_db.test1.`姓名` = my_db.test2.`姓名`,my_db.test1.`班级` = my_db.test2.`班级`
WHERE
   my_db.test1.`学号` = my_db.test2.`学号` AND my_db.test1.`学号` = sn AND my_db.test1.`姓名` = '';

FETCH search INTO sn;
END WHILE;

CLOSE search;

END

reetin 发表于 2022-1-11 20:14

能不能join一下，然后复制到一张新表？{:1_896:}

dleo 发表于 2022-1-11 20:16

join 不就是一句话就更新了么

dunxp 发表于 2022-1-11 20:23

看到"你猜怎么的"这句有种莫名的喜感:lol

你猜怎么着，数据库中有一条很重要的思想就是不同类型的数据放在不同表里，通过主键/外键连接查询

sxsy 发表于 2022-1-11 20:40

select * from test1 as a left join test2 as b on a.学号=b.学号
大概这个意思吧

15593600257 发表于 2022-1-11 21:55

创建个视图不就好了嘛

xyl52p 发表于 2022-1-11 22:16

sxsy 发表于 2022-1-11 20:40
select * from test1 as a left join test2 as b on a.学号=b.学号
大概这个意思吧

嗯，我也觉的楼主的需求就是left join能解决的事情，楼主把它复杂化了。

/bq 发表于 2022-1-11 22:22

就一表连接操作，速度慢加上主键索引就快了

Goldrepo 发表于 2022-1-11 23:48

数据量不多的话可以使用多表关联更新：
update test1 a, test2 b set a.班级 = b.班级, a.姓名 = b.姓名 where a.学号 = b.学号;
数据量多的话可以用以下方式：
查询最终想要的数据插入到一张新表里面，把test1改回其他表名，把新表改回test1。
insert into 新表
select a.学号, a.语文, a.数学, b.班级, b.姓名
from test1 a left join test2 b on a.学号 = b.学号;

zch11230 发表于 2022-1-11 23:50

本帖最后由 zch11230 于 2022-1-12 00:12 编辑

感谢大家帮忙就啥也不变加了索引快多了 join也可以直接操作两个表，不用去插入数据了，这种之前要30秒到现在只需要1秒的感觉真爽，更高级的应用方法还需要再理解学习。

页: [1] 2

吾爱破解 - 52pojie.cn's Archiver

mysql根据一个表的内容匹配到另一个表效率问题