时间戳

首页 > 解决方案 > 使用mysql选择随机记录

mysql - 使用mysql选择随机记录

问题描述

我的 MySql 数据库中有两个表：

员工表：

   id   |   name   |   projectcount   
---------------------------------
     5    |   john    |     1
     7    |   mike    |     1
     8    |   jane    |     0

项目表：

     id   |   name   |   employeeId   
---------------------------------
     1    |   pro1    |     5
     2    |   pro2    |     7
     3    |   pro3    |     8

CREATE TABLE EmployeeTable (
  id INT,
  name VARCHAR(30),
  projectcount int
  );
  
CREATE TABLE ProjectTable (
  id INT,
  name VARCHAR(30),
  employeeId INT
  );
  
INSERT INTO EmployeeTable (id, name, projectcount) VALUES
(5, "john", 1),
(7, "mike", 1),
(8, "jane", 0);

INSERT INTO ProjectTable (id, name, employeeId) VALUES
(1, "pro1", 5),
(2, "pro2", 7),
(3, "pro3", 8);

我想从项目表中选择 3 个随机记录并更新员工表中的项目计数并使用选择查询返回这些记录。

我使用存储过程的方法：

DECLARE userId1 int DEFAULT 0;
DECLARE userId2 int DEFAULT 0;
DECLARE userId3 int DEFAULT 0;

DECLARE projectId1 int DEFAULT 0;
DECLARE projectId2 int DEFAULT 0;
DECLARE projectId3 int DEFAULT 0;

select id, employeeid into projectId1, userId1 from project order by RAND() LIMIT 1;
select id, employeeid into projectId2, userId2 from project order by RAND() LIMIT 1;
select id, employeeid into projectId3, userId3 from project order by RAND() LIMIT 1;

update employee set projectcount = projectcount + 1 where id = userId1;
update employee set projectcount = projectcount + 1 where id = userId2;
update employee set projectcount = projectcount + 1 where id = userId3;

select * from project where id in (projectId1, projectId2, projectId3);

上面的代码有效，但以更静态的方式编写。是否对此进行了任何改进以看起来更清洁。谢谢。

标签： mysql

解决方案

计划A

“是否对此进行了任何改进以看起来更清洁。” 一次只做一个请求。

由于这可能不满足您对“更清洁”的预期含义，我将继续：

B计划

翻转东西。并使用事务。

它的运行速度提高了 3 倍，并修复了一个潜在的错误。这算不算“清洁工”？

BEGIN;

SELECT @ids := GROUP_CONCAT(id)
    -- I assume that `id` is the PRIMARY KEY of `project`?
    from project
    order by RAND() LIMIT 3;
-- That will run 3 times as fast as 3 separate SELECTs.
-- It avoids tallying the same id 3 times
-- but it may hit the same employee 3 times

SET @sql = CONCAT("UPDATE employee
      SET projectcount = projectcount + 1
      WHERE id IN (", @ids, ")");
PREPARE _sql FROM @sql;
EXECUTE _sql;
DEALLOCATE PREPARE _sql;

...  -- similar execute to get the `SELECT *`

COMMIT;

如果您有数百万行要从中挑选，那么请注意，这ORDER BY RAND()始终需要全表扫描和排序。这里有一些加快速度的方法：http: //mysql.rjweb.org/doc.php/random

C计划

收紧规范（没有重复项目，双重分配用户，定义“更清洁”等）然后我们可以寻找其他方法。

推荐阅读