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

zch11230

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

[Shell] 纯文本查看 复制代码

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)

[SQL] 纯文本查看 复制代码

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

能不能join一下，然后复制到一张新表？

dleo

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

dunxp

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

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

sxsy

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

15593600257

创建个视图  不就好了嘛  

xyl52p

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

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

/bq

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

Goldrepo

数据量不多的话可以使用多表关联更新：
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

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