MySQL數據庫使用技巧三例

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

編者按：在日常網絡運維工作中，我們經常要在mysql數據庫中進行諸如設備日志記錄的查詢、不達標ONU對應用戶的查詢等操作，在這個過程中有一些特定的需求，需要解決，通過對MySQL數據庫的系統學習，可以得到解決。

下面將具體過程介紹如下。

刪除指定日期內的日志文件

我們在局域網中建立了數臺日志服務器，用于記錄網絡設備產生的日志文件，這些日志文件主要是作為網絡故障排查的一個依據，但由于每天產生的日專文件數據巨大，占用數據庫的存儲空間，降低了查詢的效率。最近這個問題越來越明顯了，執行相應的查詢語句時越來越慢。

通過對日志文件的結構進行分件，我們發現有一個“ReceivedAt”字段，里面是存儲的日志信息產生的時間，那么就可以依據這個字段，進行刪除操作，當然進行刪除操作之前，我們需要先用select語句看一下這某個指定的時間段內有多少條數據文件，語句如下：

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月份產生的日志文件，一共有34.6118萬條，這些信息由于時期久遠，可以刪除掉了，語句如下：

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';

從執行完畢后出現的信息看

受影響的行: 346118

時間: 50.541s

用了不到一分鐘的時間，就將34.6118萬行的數據刪除掉了，可以再用select語句驗證一下。從出現的提示信息看，一條信息也沒有了，證明確實是從MySQL數據庫中刪除了。

其實在MySQL數據庫執行刪除指定的日期間的數據信息的方法有很多種，但是通過實踐驗證，證明這種方法效率最高，理解起來也很容易。

通過視圖來優化查詢操作

在我們的網絡優化工作當中，需要定期整理各縣公司（營業部）不達標ONU的設備信息發給對應縣公司（營業部）。具體來說就是要從OLT設備的網管平臺中導出光功率不達標的ONU設備信息、從OLT的命令行界面取出ONU下面所帶上網設備的MAC地址，從RAIDUS系統的日志文件中取出用戶的PPPOE帳號和設備的MAC地址，最后在BOSS系統中取出諸如用戶的姓名、PPPOE帳號以及家庭住址等的信息，然后將這些信息導入MySQL數據，進行關聯操作，逐步取出所需的信息。經過實踐發一，通過視圖功能可以很方便的比對出我們需要的用戶信息，具體過程如下：

比如在數據庫中我們需要對四個表格進行比對，分別為：

“liangshan_user_mac20190411”

“l i a n g s h a n-o l tpppoe-20190411”

“raidus20181030”

“boss20180611”

把這四個表格另存為csv格式的文件，然后導入數據庫。

導入成功后，打開菜單欄的“視圖”，“新建視圖”選項。

然后點擊“視圖創建工具”，可以鼠標左鍵點擊需要的表格依次拖動到右側空白區域內我們最后想要得到的數據就是“boss20180611”中的全部數據 和“raidus20181030”中的用戶MAC，這個是需要通過WHERE語句進行過濾之后得出的數據，比對條件可以點擊WHERE后邊的<-->的“列表”進行添加。

添加完所有的過濾條件之后，可以點擊預覽查看相關的用戶信息，如果想導出這些數據的話，可以點擊“SQL預覽”，會出現創建這個視圖的相關腳本文件，可以把這個腳本文件復制到“查詢”界面，創建一個新的表格，運行腳本之后可以導出相關數據。

在“查詢”界面下點擊“新建查詢”，把剛才的腳本文件粘貼進去，這里需要創建一個新表，命令是：“CREATE TABLE liangshan測試 AS”。

點擊“運行”選項，運行完畢后，刷新左側列表可以看到“liangshan測試”的表格已經生成，可以點擊右側“導出”選項，將Excel表格進行導出。

視圖的特性可以和基本表一樣進行增刪改查操作，而且它方便操作，特別是查詢操作，將原來復雜的sql語句編輯轉變為圖型化的拖拽和點擊操作，極大的降低了操作的難度，也避免了無謂的輸入錯誤，經過實踐驗證是一個方便高效的進行關聯查詢的方法。

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

在我們進行mysq操作時，發現有一個表中的MAC地址字段，里面包含的“.”需要去掉，那么使用這個sql語句就可以實現：

UPDATE

`liangsan光功率不達標ONU設備及下掛設備的MAC地址`

SET

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

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