首页 > > 网络编程 > Php编程 >

sql语句之随机查询记录和批量插入--基于mysql

2018-06-22 05:11:04来源：未知阅读 ()

本周遇到了好几次数据库方面的问题，一个是上一篇文章提到的因为要修改数据结构引起的在表间复制字段的需求，另一个就是这篇文章要写的：1，从某个数据表中按照某个字段不重复的随机选取几百条记录；2，然后把这些记录和另外一些值组成完整的行记录插入到数据表中。

1，事出有因

佛说，凡事都有因果，不怨天尤人（其实我不信佛，我也忍不了不怨）。事情是这样的：我司开发了一个简单的投票系统，系统上线后一直运行的比较顺利直到上周末......话说那日我正端坐于禅座上闭目悟道，眼看就要入定了，突然间眼前一道白光闪过，白光中带着几个字：“S施主的作品在网页中突然丢失，请尽快查清问题。另注：此作品备受欢迎，已有八百位观众投过票”，那白光中隐隐约约的呈现出产品大人的微微发光的徽记，啊，那正是大人的微信头像。

我马上答道：“谨遵产品大人旨意，马上照办”。于是乎，ssh到服务器，mysql到数据库客户端，按照S施主的姓名顺藤摸瓜的一顿摸索，结果发现：S施主只有用户注册记录、而作品记录却是空空的。我马上回复产品大人：“S施主在我系统中注册过，但其未有作品录入”。

产品大人回复道：“系统是不是遭到了黑客攻击，请务必查清是否有系统漏洞会被邪恶之人利用远程法术盗窃、毁坏数据，并查找数据库的备份，务必恢复S施主的作品信息”。

我思索了下，答道：”照办“。

于是，立马打开代码，查看逻辑，然后意识到我司开发的这套系统是基于微信身份认证的，作品的上传、编辑、删除、投票都需要经过微信身份认证才可以，否则登录都没发登录，也就谈不上发起什么恶意请求了。然后，我又查看了下作品删除的逻辑，删除动作只允许POST请求，且此处结合了session机制，必须为作品拥有者才能做删除动作。然后，我又查看了几个数据库备份，发现在这几个时间点上都没有发现S施主的作品痕迹。

于是，我向产品大人回复了检查结果。

产品大人沉吟了一段时间后道：“我与S施主商议下，让其重新上传作品，然后你将投票数量给他恢复下吧”。

我顺口答道：“OK”。

2，见招拆招

作为一名刚入互联网江湖不久的小僧，知道江湖广大无边，莫说修炼方法千万，即便是某一门派的经书、技艺也是繁琐复杂的很。比如要完成上面产品大人的任务，那就涉及到了mysql这门技艺。补上S施主的投票记录，若是十条、八条的也就罢了，大不了打一通少林长拳，在mysql客户端将下面的命令敲它个十遍、八遍不就可以了么。

insert into values(作品id, 投票者id, 投票时间);

问题是800条记录，那敲起来也是很费体力的。况且，对于S施主的这一作品，投票记录中作者id应为在系统中注册过的且不能重复、投票时间不能全都是一个时间才对。看来少林长拳虽勇猛，遇上这个对手，却是占了下风。那么只能这么办了：从已有的投票记录表中随机抽出800条不重复的用户记录，然后将这些记录与相应字段构成完整的记录行再插入数据表。

3，破招

招一，少林拈花指--sql随机抓取。

SELECT distinct 投票者id
FROM 投票表
WHERE 投票者id >=
(
    (SELECT MAX(投票者id) FROM 投票表)
    -(SELECT MIN(投票者id) FROM 投票表)) * RAND()
    + (SELECT MIN(投票者id) FROM 投票表
)
LIMIT 800;

招二，自动化少林长拳--php构造sql插入语句

 1 // 数据库连接过程及$sql1省略
 2 
 3 $stmt1 = $db->prepare($sql1);
 4 $e = $stmt1->setFetchMode(PDO::FETCH_NUM);
 5 $stmt1->execute();
 6 $r1 = $stmt1->fetchAll();
 7 
 8 $time = strtotime('2017-02-20 08:00:00') + 60;
 9 
10 // 拼接插入sql语句
11 $sql2 = 'insert into 投票表 values';
12 foreach ($r1 as $row) {
13     $sql2 .= '(' . S施主作品id . ',' . $row[0] . ',' . "'" . date('Y-m-d H:i:s', $time) . "'" . '),';
14     $time += 60;
15 }
16 $sql2 = rtrim($sql2, ',') . ';';
17 
18 $stmt2 = $db->prepare($sql2);
19 $r2 = $stmt2->execute();