MySQL數(shù)據(jù)庫(kù)使用技巧三例

■ 山東 趙秀芹 劉楊青 李瑞祥

編者按：在日常網(wǎng)絡(luò)運(yùn)維工作中，我們經(jīng)常要在mysql數(shù)據(jù)庫(kù)中進(jìn)行諸如設(shè)備日志記錄的查詢、不達(dá)標(biāo)ONU對(duì)應(yīng)用戶的查詢等操作，在這個(gè)過(guò)程中有一些特定的需求，需要解決，通過(guò)對(duì)MySQL數(shù)據(jù)庫(kù)的系統(tǒng)學(xué)習(xí)，可以得到解決。

下面將具體過(guò)程介紹如下。

刪除指定日期內(nèi)的日志文件

我們?cè)诰钟蚓W(wǎng)中建立了數(shù)臺(tái)日志服務(wù)器，用于記錄網(wǎng)絡(luò)設(shè)備產(chǎn)生的日志文件，這些日志文件主要是作為網(wǎng)絡(luò)故障排查的一個(gè)依據(jù)，但由于每天產(chǎn)生的日專文件數(shù)據(jù)巨大，占用數(shù)據(jù)庫(kù)的存儲(chǔ)空間，降低了查詢的效率。最近這個(gè)問(wèn)題越來(lái)越明顯了，執(zhí)行相應(yīng)的查詢語(yǔ)句時(shí)越來(lái)越慢。

通過(guò)對(duì)日志文件的結(jié)構(gòu)進(jìn)行分件，我們發(fā)現(xiàn)有一個(gè)“ReceivedAt”字段，里面是存儲(chǔ)的日志信息產(chǎn)生的時(shí)間，那么就可以依據(jù)這個(gè)字段，進(jìn)行刪除操作，當(dāng)然進(jìn)行刪除操作之前，我們需要先用select語(yǔ)句看一下這某個(gè)指定的時(shí)間段內(nèi)有多少條數(shù)據(jù)文件，語(yǔ)句如下：

S E L E C T * F R O M SystemEvents

WHERE

S y s t e m E v e n t s.ReceivedAt

BETWEEN

'2018-08-01 00:00:00'

AND

'2 0 1 8-0 9-0 1 00:00:00';

這是查詢的2018年8月份產(chǎn)生的日志文件，一共有34.6118萬(wàn)條，這些信息由于時(shí)期久遠(yuǎn)，可以刪除掉了，語(yǔ)句如下：

D E L E T E F R O M SystemEvents

WHERE

S y s t e m E v e n t s.ReceivedAt

BETWEEN

'2018-08-01 00:00:00'

AND

'2 0 1 8-0 9-0 1 00:00:00';

從執(zhí)行完畢后出現(xiàn)的信息看

受影響的行: 346118

時(shí)間: 50.541s

用了不到一分鐘的時(shí)間，就將34.6118萬(wàn)行的數(shù)據(jù)刪除掉了，可以再用select語(yǔ)句驗(yàn)證一下。從出現(xiàn)的提示信息看，一條信息也沒(méi)有了，證明確實(shí)是從MySQL數(shù)據(jù)庫(kù)中刪除了。

其實(shí)在MySQL數(shù)據(jù)庫(kù)執(zhí)行刪除指定的日期間的數(shù)據(jù)信息的方法有很多種，但是通過(guò)實(shí)踐驗(yàn)證，證明這種方法效率最高，理解起來(lái)也很容易。

通過(guò)視圖來(lái)優(yōu)化查詢操作

在我們的網(wǎng)絡(luò)優(yōu)化工作當(dāng)中，需要定期整理各縣公司（營(yíng)業(yè)部）不達(dá)標(biāo)ONU的設(shè)備信息發(fā)給對(duì)應(yīng)縣公司（營(yíng)業(yè)部）。具體來(lái)說(shuō)就是要從OLT設(shè)備的網(wǎng)管平臺(tái)中導(dǎo)出光功率不達(dá)標(biāo)的ONU設(shè)備信息、從OLT的命令行界面取出ONU下面所帶上網(wǎng)設(shè)備的MAC地址，從RAIDUS系統(tǒng)的日志文件中取出用戶的PPPOE帳號(hào)和設(shè)備的MAC地址，最后在BOSS系統(tǒng)中取出諸如用戶的姓名、PPPOE帳號(hào)以及家庭住址等的信息，然后將這些信息導(dǎo)入MySQL數(shù)據(jù)，進(jìn)行關(guān)聯(lián)操作，逐步取出所需的信息。經(jīng)過(guò)實(shí)踐發(fā)一，通過(guò)視圖功能可以很方便的比對(duì)出我們需要的用戶信息，具體過(guò)程如下：

比如在數(shù)據(jù)庫(kù)中我們需要對(duì)四個(gè)表格進(jìn)行比對(duì)，分別為：

“l(fā)iangshan_user_mac20190411”

“l(fā) i a n g s h a n-o l tpppoe-20190411”

“raidus20181030”

“boss20180611”

把這四個(gè)表格另存為csv格式的文件，然后導(dǎo)入數(shù)據(jù)庫(kù)。

導(dǎo)入成功后，打開(kāi)菜單欄的“視圖”，“新建視圖”選項(xiàng)。

然后點(diǎn)擊“視圖創(chuàng)建工具”，可以鼠標(biāo)左鍵點(diǎn)擊需要的表格依次拖動(dòng)到右側(cè)空白區(qū)域內(nèi)我們最后想要得到的數(shù)據(jù)就是“boss20180611”中的全部數(shù)據(jù) 和“raidus20181030”中的用戶MAC，這個(gè)是需要通過(guò)WHERE語(yǔ)句進(jìn)行過(guò)濾之后得出的數(shù)據(jù)，比對(duì)條件可以點(diǎn)擊WHERE后邊的<-->的“列表”進(jìn)行添加。

添加完所有的過(guò)濾條件之后，可以點(diǎn)擊預(yù)覽查看相關(guān)的用戶信息，如果想導(dǎo)出這些數(shù)據(jù)的話，可以點(diǎn)擊“SQL預(yù)覽”，會(huì)出現(xiàn)創(chuàng)建這個(gè)視圖的相關(guān)腳本文件，可以把這個(gè)腳本文件復(fù)制到“查詢”界面，創(chuàng)建一個(gè)新的表格，運(yùn)行腳本之后可以導(dǎo)出相關(guān)數(shù)據(jù)。

在“查詢”界面下點(diǎn)擊“新建查詢”，把剛才的腳本文件粘貼進(jìn)去，這里需要?jiǎng)?chuàng)建一個(gè)新表，命令是：“CREATE TABLE liangshan測(cè)試 AS”。

點(diǎn)擊“運(yùn)行”選項(xiàng)，運(yùn)行完畢后，刷新左側(cè)列表可以看到“l(fā)iangshan測(cè)試”的表格已經(jīng)生成，可以點(diǎn)擊右側(cè)“導(dǎo)出”選項(xiàng)，將Excel表格進(jìn)行導(dǎo)出。

視圖的特性可以和基本表一樣進(jìn)行增刪改查操作，而且它方便操作，特別是查詢操作，將原來(lái)復(fù)雜的sql語(yǔ)句編輯轉(zhuǎn)變?yōu)閳D型化的拖拽和點(diǎn)擊操作，極大的降低了操作的難度，也避免了無(wú)謂的輸入錯(cuò)誤，經(jīng)過(guò)實(shí)踐驗(yàn)證是一個(gè)方便高效的進(jìn)行關(guān)聯(lián)查詢的方法。

替換表中特定字段中的某些字符

在我們進(jìn)行mysq操作時(shí)，發(fā)現(xiàn)有一個(gè)表中的MAC地址字段，里面包含的“.”需要去掉，那么使用這個(gè)sql語(yǔ)句就可以實(shí)現(xiàn)：

UPDATE

`liangsan光功率不達(dá)標(biāo)ONU設(shè)備及下掛設(shè)備的MAC地址`

SET

`MAC地 址`=replace(`MAC地 址`,'.','');

這種替換操作本來(lái)在EXCEL或者WPS里面很容易操作，但是由于這個(gè)表中還有一列是IP地址，IP地址中也有“.”，如果直接執(zhí)行替換操作的話，會(huì)把IP地址中的“.”也替換掉，而這是我們不希望看到，而在sql里面卻可以方便的對(duì)指定的字段進(jìn)行某些字符的替換。