MySQL数据库使用技巧三例

902019.08

基础设施与数据管理

责任编辑：季莹 投稿信箱：netadmin@

Infrastructu re Mgmt. & Data Mg mt.

MySQL 数据库使用技巧三例

■ 山东 赵秀芹 刘杨青 李瑞祥

下面将具体过程介绍如下。

删除指定日期内的日志文件

我们在局域网中建立了数台日志服务器，用于记录网络设备产生的日志文件，这些日志文件主要是作为网络故障排查的一个依据，但由于每天产生的日专文件数据巨大，占用数据库的存储空间，降低了查询的效率。最近这个问题越来越明显了，执行相应的查询语句时越来越慢。

通过对日志文件的结构进行分件，我们发现有一个“ReceivedAt”字段，里面是存储的日志信息产生的时间，那么就可以依据这个字段，进行删除操作，当然进行删除操作之前，我们需要先用select 语句看一下这某个指定的时间段内有多少条数据文件，语句如下：

S E L E C T * F R O M

编者按：在日常网络运维工作中，我们经常要在mysql 数据库中进行诸如设备日志记录的查询、不达标ONU 对应用户的查询等操作，在这个过程中有一些特定的需求，需要解决，通过对MySQL 数据库的系统学习，可以得到解决。

SystemEvents

WHERE

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

BETWEEN

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

'2018-09-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

'2018-09-0 1

00:00:00';

从执行完毕后出现的信息看

受影响

的行: 346118

时间: 50.541s

用了不到一分钟的时间，就将34.6118万行的数据删除掉了，可以再用select 语句验证一下。从出现的提示信息看，一条信息也没有了，证明确实是从MySQL 数据库中删除了。

其实在MySQL 数据库执行删除指定的日期间的数据信息的方法有很多种，但是通过实践验证，证明这种方法效率最高，理解起来也很容易。

通过视图来优化查询操作

在我们的网络优化工作当中，需要定期整理各县公司（营业部）不达标ONU 的设备信息发给对应县公司（营业部）。具体来说就是要从OLT 设备的网管平台中导出

91

2019.08

基础设施与数据管理责任编辑：季莹 投稿信箱：netadmin@

Infrastru ctu re Mg mt. & Data Mg mt.

光功率不达标的ONU 设备信息、从OLT 的命令行界面取出ONU 下面所带上网设备的MAC 地址，从RAIDUS 系统的日志文件中取出用户的PPPOE 帐号和设备的MAC 地址，最后在BOSS 系统中取出诸如用户的姓名、PPPOE 帐号以及家庭住址等的信息，然后将这些信息导入MySQL 数据，进行关联操作，逐步取出所需的信息。经过实践发一，通过视图功能可以很方便的比对出我们需要的用户信息，具体过程如下：

比如在数据库中我们需要对四个表格进行比对，分别为：

“l i a n g s h a n _u s e r _mac20190411”

“l i a n g s h a n -o l t -pppoe-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 `

M

A

C

地

址`=r e p l a c e (`M A C 地址

`,'.','');

这种替换操作本来在EXCEL 或者WPS 里面很容易操作，但是由于这个表中还有一列是IP 地址，IP 地址中也有“.”，如果直接执行替换操作的话，会把IP 地址中的“.”也替换掉，而这是我们不希望看到，而在sql 里面却可以方便的对指定的字段进行某些字符的替换。N