MariaDB 層常用業務

前言  -  簡單準備一下前戲

　　前面寫過幾篇mariadb 數據的隨筆, 多數偏C/C++層面. 這次分享一下平時開發中, 處理的一些數據層面的業務.

對於MariaDB, 不做過多介紹. 如果你有Ubuntu 系統, 可以通過下麵來個環境玩玩

# 先搜索要的包, 再去安裝
apt-cache search mariadb
sudo apt-get install mariadb-server
sudo apt-get install mariadb-client

# 進入mariadb 開始操作
sudo mysql -uroot
status

這裡扯一下, 假如你複製mysql 腳本到 mariadb中執行, 出現下麵字元串

Display all 475 possibilities? (y or n)

造成原因是MariaDB中對Tab處理的問題, 你需要將腳本串中Tab替換成空格. 如果你用的是notepad++ 可以做下麵操作

-> 編程語言多了, 什麼扯坑都有. 多習慣就成經驗了, 一北韓吃遍天~~ 只能開心就好~~.

在好戲出現之前, 我們需要一些測試的基礎數據.執行下麵的構建腳本

-- 1.1 先構建實驗前戲
create database test;
use test;
create table t_rand (
    -- 推薦 設計主鍵的時候 id 為 bigint, int 為歷史原因
    id int unsigned not null primary key
);

-- 4.1 先構建數據, 我們就以 t_rand 表為例
drop table if exists t_score;
create table t_score(score int);
insert into t_score value(1);
insert into t_score value(2);
insert into t_score value(10);
insert into t_score value(10);
insert into t_score value(10);
insert into t_score value(3);
insert into t_score value(4);
insert into t_score value(6);
insert into t_score value(5);
select * from t_score;

 -> 到這基本的儲備工作已經完成了, 那我們開始吧~

正文  -  從實際業務出發

　　扯個談吧, 這篇文章挺不錯的 你為什麼會離開游戲行業？

1. 生成8位隨機數業務

　　有時候按照產品需求希望生成int 8位的隨機id. 常見做法是單獨搞一個隨機數表,這樣做有點小噁心.

後面我弄了投機取巧的辦法.八位隨機數範圍[10000000, 99999999] 我把它切分為

[10000000, 89999999] and [90000000, 99999999]兩部分生成,

前半分採用rand and check. 後半部分採用 max + 1. 總的思路如下

-- 1.2 開始構建存儲過程
drop procedure if exists p_rand_insert;
delimiter $
create procedure p_rand_insert(out oid int unsigned)
begin
    declare mi int unsigned;
    declare si int unsigned default 10000000;
    declare ei int unsigned default 70000000;
    
    declare i tinyint default 3;
    declare f tinyint default 0;
    
    while i > 0 && f = 0 do
        set f = 1;
        set mi = floor(si + ei * rand());
        select 0 into f from t_rand where id = mi limit 1;
        set i = i - 1;
    end while;
    
    if f = 0 then
        select max(id) into mi from t_rand;
        if mi < ei + si then
            set mi = ei + si;
        end if;
        set mi = mi + 1;
    end if;
    
    insert into t_rand value(mi);
    set oid = mi;
end
$
delimiter ;

不妨測試一下,

-- 1.3 開始構建測試數據
truncate table t_rand;

call p_rand_insert(@oid);
select @oid;
call p_rand_insert(@oid);
call p_rand_insert(@oid);
call p_rand_insert(@oid);

select * from t_rand;

得到的結果如下, 扯一點這個需求前期在於保護游戲內部一些隱私數據. 哈哈, 其實對於隱私數據就可以不顯示才是最好.

2. 清除db上面所有數據

　　經常需要清除數據, 這裡寫了個腳本直接清除指定DB上所有數據. 大家可以嘗試用一下. 

-- 2. 清除db上面所有數據
drop procedure if exists p_truncate;
delimiter $
create procedure p_truncate(dbname varchar(64))
begin
    declare tname varchar(64);
    declare lop tinyint default 1;
    
    -- 聲明游標
    declare getnames cursor for select table_name 
        from information_schema.tables 
        where table_type = 'BASE TABLE' and table_schema = dbname;
    -- 聲明handler 必須在游標聲明之後, 當游標數據讀取完畢會觸發下麵set
    declare continue handler for not found set lop = 0;
    
    -- 打開游標
    open getnames;
    
    -- 操作游標, 讀取第一行數據
    fetch getnames into tname;
    while lop = 1 do
        set @tsql = concat('truncate table ', dbname, '.', tname);
        prepare stmt from @tsql;
        execute stmt;
        deallocate prepare stmt;
            
        -- 讀取下一個行數據
        fetch getnames into tname;
    end while;
            
    -- 關閉游標
    close getnames;
end
$
delimiter ;

使用也很簡單, 看下麵小例子, 後面也有數據圖演示

-- 2.1 測試清除所有數據
select * from test.t_rand;
call p_truncate('test');
select * from test.t_rand;

對於實現的細節部分, 查查幫助手冊就明白了. 再補充一點, truncate 和 delete 區別. 直觀上前者特別快.

後者慢在 刪除的過程是每次從表中刪除一行，並且會將該行的刪除操作作為事務記錄到日誌中.

當然了truncate一個小細節, 它會幹掉 auto_increment 當前的種子值, 讓其變成0.

3. 導出資料庫表結構和表數據

　　這個在項目移植的時候會用上就一句話 

sudo mysqldump -uroot -d test > test_d.sql

-h -p 自己什麼環境自己補上, 我就本地測試沒有密碼. -d 表示不導出表數據. 如果只是為了導出特定的表. 可以寫成 test t_rand

寫到這裡讓我想起了那時候剛工作的第二年, 看見運維大拿寫出這段話. 當時 心裡 就 1萬個敬佩, wow 好厲害~

n年過去了, 不知道那些人還好嗎, 哈哈, 估計菜雞的我沒機會再敬他們一杯了 ~

4. 後臺統計需要排序

　　這個業務很普遍, 無外乎下麵兩種. 用 t_score表做測試. 直接看圖吧.

-- 4.2 夠好排序
select t.score,
	(select count(s.score) + 1 from t_score s where s.score > t.score) rank
    from t_score t order by t.score desc;

 另外一種, 1->2->3...這種來回搞

-- 4.3 都好排序
select t.score,
    (select count(s.score) + 1 
        from (select score from t_score group by score) s 
            where s.score > t.score) rank
    from t_score t order by t.score desc;

到這裡基本上資料庫(mariadb or mysql) 開發層面的業務也介紹了一些了.哈哈, 下次有機會再補充.

突然毫無徵兆想起一句話, 優化是毒藥.

後記  -  一切如舊的結束

 　　錯誤是難免的歡迎指正, O(∩_∩)O哈哈~  人生路很長, 已在脫貧路上奮勇向前了  (๑╹◡╹)ﾉ""" 不能給黨丟飯~

　　 似水年華  http://music.163.com/#/song?id=399954010