时间戳

首页 > 解决方案 > Mysql通过读取txt文件更新表？

mysql - Mysql通过读取txt文件更新表？

问题描述

/var/lib/mysql-files/myfile.txt 文件包含

23/08/2020 mycluster1 192.168.0.10 1515G 22G 1493G 2 15 17

我想通过阅读 /var/lib/mysql-files/myfile.txt 来更新 servercheck 表 我尝试了很多组合但找不到合适的解决方案 我也搜索导入命令但找不到更新

mysqlimport -u myuser -pxxXxX1xF databasename UPDATE 'servercheck' (`id`, `date`, `cluster`, `ip`, `totalsize`, `used`, `available`, `-10MBfiles`, `+10MBfiles`, `totalfiles`) VALUES LOAD_FILE('/var/lib/mysql-files/myfile.txt') Where ip='192.168.0.10';

如何更新此表？

谢谢堆

标签： mysqlsqlmysqlimport

解决方案

如果有人需要，这是脚本。

srvstat.txt

a=Australia b=04/09/2020 15:40 c=strg1-au.myserver.com d=192.168.0.15 e=/etc/mydata/ f=147G g=1G h=1% i= 147G j=99% k=0 l=0 m=0 n=OK o=OK p=32547320 KB r=27402 MB s=500G t=494G

这个 php 代码读取和更新表。

<?php
        $input = "/etc/system/srvstats/storage/srvstats.txt";
        $dbHost = "localhost";
        $dbUser = "myuser";
        $dbPass = "xxxxxxxxxxF";
        $db = "admin_my2";
        $table = "storagecheck";
        $id = "2";
        $data = explode('=', file_get_contents($input));

        $location = trim(substr($data[1], 0, -2));
        $date = trim(substr($data[2], 0, -2));
        $cluster = trim(substr($data[3], 0, -2));
        $ip = trim(substr($data[4], 0, -2));
        $mounted = trim(substr($data[5], 0, -2));
        $totalsize = trim(substr($data[6], 0, -2));
        $used = trim(substr($data[7], 0, -2));
        $usedPercent = trim(substr($data[8], 0, -2));
        $free = trim(substr($data[9], 0, -2));
        $freePercent = trim(substr($data[10], 0, -2));
        $minus50MBFiles = trim(substr($data[11], 0, -2));
        $plus50MBFiles = trim(substr($data[12], 0, -2));
        $totalFiles = trim(substr($data[13], 0, -2));
        $pinggateway = trim(substr($data[14], 0, -2));
        $raidhealth = trim(substr($data[15], 0, -2));
        $memorysize = trim(substr($data[16], 0, -2));
        $freememory = trim(substr($data[17], 0, -2));
        $backupsize = trim(substr($data[18], 0, -2));
        $backupspaceremaining = trim($data[19]);

        $conn = new mysqli($dbHost, $dbUser, $dbPass, $db);

        !$conn->connect_error OR die("Connection failed: " . $conn->connect_error);

        $sql = "UPDATE `$table` SET `location` = '$location', `date` = '$date', `cluster` = '$cluster', `ip` = '$ip', `mounted` = '$mounted', `totalsize` = '$totalsize', `used` = '$used', `used%` = '$usedPercent', `available` = '$free' , `free%` = '$freePercent', `-50MBfiles` = '$minus50MBFiles',`+50MBfiles` = '$plus50MBFiles',`totalfiles` = '$totalFiles',`pinggateway` = '$pinggateway', `raidhealth` = '$raidhealth', `memorysize` = '$memorysize', `freememory` = '$freememory', `backupsize` = '$backupsize', `backupspaceremaining%` = '$backupspaceremaining' WHERE `id` = $id";
        $conn->query($sql);

        echo "Entry ID $id updated succesfully.";

推荐阅读