MySQL 归档 - 菜鸟公园

2020-09-05 作者：菜鸟DJ 0

一个方便的数据库操作shell脚本

用法说明

这个脚本可以方便的备份和恢复数据库，而且通过show参数可以用来查看该实例中有多少个数据库。
唯一需要手动操作的是到脚本里配置账号密码。账号密码放在脚本的目的是为了避免每次输入。

导出文件格式是tar.gz。导入的文件格式是zip。这部分跟我的习惯有关系，我备份的数据库都会在本地进行处理压缩成zip上传，就是为了防止没有经过处理的数据库直接拿去导入。

#!/bin/bash

# 这是一个自动化导出MySQL数据库的脚本

# 修改此处
user="root"
passwd="123456"
port=3306
host="127.0.0.1"
dblist=(
"test"
)
root=`pwd`"/"
function printUsage {
    echo "Usage:"
    echo "$0 [-t dump|import|show] [-f file]"
    echo "导出/导入数据库脚本 v1.1"
    echo "导出的sql以数据库命名。"
    echo "导入的文件同样是提取sql文件名！"
    echo "show 参数可以打印数据库名，用于替换dblist"
    echo "dump文件是tar.gz的压缩包，import的文件是zip的压缩包"
}
function printParaErr {
    echo "参数错误！"
}

function printFileNotExist {
    echo "文件不存在！"
}

function printFileNotSupport {
    echo "仅支持压缩文件！(zip)"
}

function cleanOutDir {
    if [ -d $outDir ]; then 
        echo "清理临时目录:" $outDir
        rm -rf $outDir
    fi
}

function addNote {
    echo "是否添加备注文件？[回车跳过]"
    read note

    if [ ${#note} -gt 1 ];then 
        cd $root$outDir
        echo $note > note.txt
    fi
}

if [ $# == 0 ]; then 
    printUsage
    exit 0
fi

GETOPTOUT=`getopt t:f: "$@"`
    set -- $GETOPTOUT
    while [ -n "$1" ]
    do
    case $1 in
        -t)
            type=$2
            shift
            ;;
        -f)
            inputfile=$2
            shift
            ;;
        --)
            shift
            break
            ;;
        *)
            printParaErr
            printUsage;
            exit 1;
            ;;
    esac
    shift
    done
if [ $type == 'dump' ]; then
    echo "目标数据库为 $user@$host:$port"
elif [ $type == 'import' ]; then
    if [ ! -f "$inputfile" ]; then
        printFileNotExist
        printUsage
        exit 0
    fi

    ext_name="${inputfile##*.}"

    if [ $ext_name != 'zip' ]; then
        printFileNotSupport
        printUsage
        exit 0
    fi


    echo "导入源数据为: $inputfile"
    echo "目标数据库为: $user@$host:$port"
elif [ $type == 'show' ]; then
    echo "当前实例存在的数据库:(已排除系统数据库)"
else
    printParaErr
    printUsage;
    exit 1;
fi

# echo "请输入数据库密码，按回车键结束"
# stty -echo # 设置输入字符不回显
# read passwd
# stty echo  # 取消不回显状态

runTime=`date '+%Y%m%d%H%M'`
outDir="./sqlHandler_$runTime/"

cleanOutDir

if [ $type == 'dump' ]; then
    echo "创建导出目录:" $outDir
    mkdir -p $outDir

    for db_name in ${dblist[*]}; do
        cmd="mysqldump -h $host -u $user -P $port -p$passwd $db_name"
        echo $cmd | sed "s/-p$passwd/-p****/g";
        $cmd > "$outDir$db_name.sql";
    done
    cd $outDir
    tar cvzf "dbbak_$runTime.tar.gz" *
    rm -rf *.sql
    addNote
    echo "导出文件为："
    echo $root$outDir"/"`ls|grep tar`|sed -e "s/\/\//\//g" -e "s/\/\.\//\//g"

elif [ $type == 'import' ]; then
    echo "创建临时目录:" $outDir
    mkdir -p $outDir
    unzip -d $outDir $inputfile
    files=$(ls $outDir)
    cd $outDir
    echo "mysql command:"
    for file in ${files[*]}; do
        db_name="${file%.*}"
        # ext_name="${file##*.}"
        cmd="mysql -h $host -u $user -P $port -p$passwd --default-character-set=utf8 $db_name"
        echo -e "\t"$cmd " < $file" | sed "s/-p$passwd/-p****/g";
        $cmd < $file
    done
    cd $root
    cleanOutDir
    # $cmd < $inputfile
elif [ $type == 'show' ]; then
    rm -rf $outDir
    cmd="mysql -h $host -u $user -P $port -p$passwd "
    $cmd -e "show databases;" |grep -v -E 'Database|mysql|information_schema|performance_schema'|sed -e 's/^/"/g' -e 's/$/"/g'
    # echo $result
fi

if [[ $? == 0 ]]; then
    echo "操作完成！";
else
    echo "┗━━ 操作失败！";
fi

bash MySQL mysqldump shell

2019-08-23 作者：菜鸟DJ 0

PHP

MySQL 分组之后如何取Top(N)?

最近碰到一个有意思的问题，因为MySQL里没有top n的用法，所以如果要实现取数据的前几操作只能通过排序之后加limit限制数量，但是这种用法又跟group 冲突。这篇文章就是来分析下分组取topN的解题思路。

现在创建一个测试表。用户的商品消费数据（测试表就不建立索引了）

CREATE TABLE `tb_user_consume` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned DEFAULT NULL,
  `goods_id` int(11) unsigned DEFAULT NULL,
  `goods_name` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL,
  `price` decimal(10,2) unsigned DEFAULT NULL,
  `num` int(10) unsigned DEFAULT NULL,
  `total` decimal(10,2) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tb_user_consume`(`user_id`,`goods_id`,`goods_name`,`price`,`num`,`total`)
VALUES
(100,1,"1号商品",1.3,10, price*num),
(100,2,"2号商品",1.15,12, price*num),
(100,3,"3号商品",3.4,5, price*num),
(100,4,"4号商品",18.99,2, price*num),
(100,5,"5号商品",7.4,9, price*num),'
(101,1,"1号商品",1.3,13, price*num),
(101,2,"2号商品",1.15,12, price*num),
(101,3,"3号商品",3.4,20, price*num),
(101,4,"4号商品",18.99,8, price*num),
(101,5,"5号商品",7.4,7, price*num),'
(102,1,"1号商品",1.3,21, price*num),
(102,2,"2号商品",1.15,3, price*num),
(102,3,"3号商品",3.4,51, price*num),
(102,4,"4号商品",18.99,23, price*num),
(102,5,"5号商品",7.4,22, price*num),'
(103,1,"1号商品",1.3,2, price*num),
(103,2,"2号商品",1.15,7, price*num),
(103,3,"3号商品",3.4,9, price*num),
(103,4,"4号商品",18.99,22, price*num),
(103,5,"5号商品",7.4,99, price*num),'
(104,1,"1号商品",1.3,77, price*num),
(104,2,"2号商品",1.15,54, price*num),
(104,3,"3号商品",3.4,23, price*num),
(104,4,"4号商品",18.99,23, price*num),
(104,5,"5号商品",7.4,44, price*num)
;

假如现在有一个需求是，筛选出用户消费商品总价最高的前三个商品。

粗一看，这个需求也没有什么实现上的难度，就是根据用户分组，取出表里total最高的三行记录就可以了。
对没有错，需求就是这么简单，解题思路也不难，那么我们开始着手编码了。

第一步，做一个子查询，

取出表里total最高的三行记录

sql写起来也很简单，如下所示

SELECT * FROM `tb_user_consume` WHERE user_id = 100 ORDER BY total DESC LIMIT 3;

第二步，按照用户分组
取出所有用户

SELECT * FROM `tb_user_consume` ORDER BY total DESC LIMIT 3 GROUP BY user_id;

看这个好像是满足了需求，别急，我们运行一下。

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘GROUP BY user_id’ at line 1

报错了，很明显上面的sql有语法错误。limit 只能用在查询语句的最后面。
那么我们要怎么去实现这个需求呢？用单一的子查询好像都没法直接的按照用户分组来取数据。
一般到这种时候，我们很可能就直接用代码来解决了。

先取出所有用户列表。 SELECT DISTINCT(user_id) AS uid FROM tb_user_consume;

然后遍历用户列表，按照上面的查询语句查出所有的用户前三total信息 SELECT * FROM tb_user_consume WHERE user_id = 100 ORDER BY total DESC LIMIT 3;

这种方法不是不可取，在表里的数据不多的时候，用这个也能完成需求，抛去执行效率不说，我们就说开发效率，又是写代码，又是写sql。还要去联调，是不是很费时费力？
那么到底能不能通过sql语句直接查询出来呢？

首先我们要想，上面不能实现的痛点在哪里？没有办法先limit 3，对不对？那我们能不能通过排序筛选的方式来实现，排序后达到三个的数量我们就停止。
按照机器的思维应该是，先order by user_id, 然后 order by total desc。

SELECT * FROM tb_user_consume ORDER BY user_id ,total DESC;

在这个结果集里当user_id 输出3个记录行就停止。 本文的重点来了 怎么实现这个呢？
通过谷歌（其实是百度）发现mysql里有一个 case when的条件判断。正好满足我们的需求。【在这个结果集里当user_id 输出3个记录行就停止】 美哉！开撸。

SELECT @rnd :=
    CASE
    WHEN @userid = `user_id` THEN
    @rnd := @rnd+1
    ELSE 1
    END rnd, @userid := `user_id`, user_id,total,goods_id,goods_name,price
FROM `tb_user_consume`,
    (SELECT @rnd := 1,
         @userid:=0) b
    ORDER BY  `user_id` ,`total` DESC

像这样我们就可以以rnd变量来标记我们的结果集排序结果了。这样我们把它作为一个子查询在外面加上限制条件就拿到指定的行数。
最终的sql如下：

SELECT *
FROM 
    (SELECT @rnd :=
        CASE
        WHEN @userid = `user_id` THEN
        @rnd := @rnd+1
        ELSE 1
        END rnd, @userid := `user_id`, user_id,total,goods_id,goods_name,price
    FROM `tb_user_consume`,
        (SELECT @rnd := 1,
         @userid:=0) b
        ORDER BY  `user_id` ,`total` DESC) aa
    WHERE rnd <=3;

最终的查询展示结果：

+------+----------------------+---------+--------+----------+------------+-------+
| rnd  | @userid := `user_id` | user_id | total  | goods_id | goods_name | price |
+------+----------------------+---------+--------+----------+------------+-------+
|    1 |                  100 |     100 |  66.60 |        5 | 5号商品    |  7.40 |
|    2 |                  100 |     100 |  37.98 |        4 | 4号商品    | 18.99 |
|    3 |                  100 |     100 |  17.00 |        3 | 3号商品    |  3.40 |
|    1 |                  101 |     101 | 151.92 |        4 | 4号商品    | 18.99 |
|    2 |                  101 |     101 |  68.00 |        3 | 3号商品    |  3.40 |
|    3 |                  101 |     101 |  51.80 |        5 | 5号商品    |  7.40 |
|    1 |                  102 |     102 | 436.77 |        4 | 4号商品    | 18.99 |
|    2 |                  102 |     102 | 173.40 |        3 | 3号商品    |  3.40 |
|    3 |                  102 |     102 | 162.80 |        5 | 5号商品    |  7.40 |
|    1 |                  103 |     103 | 732.60 |        5 | 5号商品    |  7.40 |
|    2 |                  103 |     103 | 417.78 |        4 | 4号商品    | 18.99 |
|    3 |                  103 |     103 |  30.60 |        3 | 3号商品    |  3.40 |
|    1 |                  104 |     104 | 436.77 |        4 | 4号商品    | 18.99 |
|    2 |                  104 |     104 | 325.60 |        5 | 5号商品    |  7.40 |
|    3 |                  104 |     104 | 100.10 |        1 | 1号商品    |  1.30 |
+------+----------------------+---------+--------+----------+------------+-------+

参考文章: 我的mysql如何分组取top10？

<?php
$host = '127.0.0.1';
$dbname = 'yang';
$port = 3306;

$db = new PDO("mysql:host=$host;dbname=$dbname;port=$port", 'root', '12345');

$goods_info = [
    ['id' => 1, 'name' => '1号商品', 'price' => 1.30],
    ['id' => 2, 'name' => '2号商品', 'price' => 1.15],
    ['id' => 3, 'name' => '3号商品', 'price' => 3.40],
    ['id' => 4, 'name' => '4号商品', 'price' => 18.99],
    ['id' => 5, 'name' => '5号商品', 'price' => 7.40],
];

function insert($goods_info, PDO &$db, $start)
{
    $sql = 'insert into tb_user_consume(user_id,goods_id,goods_name,price,num,total) values';
    for ($i = $start; $i < $start + 50000; $i++) {
        foreach ($goods_info as $info) {
            $num = mt_rand(0, 1000);
            $total = $num * $info['price'];
            $sql .= sprintf("(%d,%d,\"%s\",%f,%d,%f),", $i, $info['id'], $info['name'], $info['price'], $num, $total);
        }
    }
    $sql = substr($sql, 0, -1);
    //echo $sql;

    $db->prepare($sql)->execute();
}

// 批量添加测试数据
//for ($j = 1000000; $j < 2000000; $j += 50000) {
//    insert($goods_info,$db,$j);
//}

// 执行时间
$start = time();
select($db);
echo "cost:".(time()-$start)."\n";

function select(PDO &$db){
    for ($i = 1000000; $i < 2000000; $i++) {
        $sql = 'SELECT * FROM `tb_user_consume` WHERE user_id = '.$i.' ORDER BY total DESC LIMIT 3; ';
        $ret = $db->query($sql)->fetchAll();
        //var_dump($ret);
    }
}

MySQL php

2/2