如何用Chart.js实现MySQL数据的无刷新动态图表更新？

问题描述 #

本人从周一开始学习JS和PHP，现有按固定时间间隔存储数据的MySQL数据库，已用Chart.js实现数据静态可视化，希望通过简单步骤让图表每秒动态更新，认为<meta http-equiv="refresh" content="5">这类页面刷新方案因动画问题不理想，特此咨询可行方法。

原代码：

<?php

$username = "root";
$password = "_!Stud3nt&";
$database = "datalog";

try {
    $pdo = new PDO("mysql:host=localhost;database=$database", $username, $password);
    //SET the PDO error mode to exception
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}   catch(PDOException $e){
    die("ERROR: could not connect. " . $e->getMessage());
}
?>

<!DOCTYPE html>
<html lang="de">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="style/style.css">
    
    <title>ems</title>
    <style type="text/css">
        .chartBox {
            width: 700px;
        }
    </style>
</head>
<body>

    <canvas id="myChart"></canvas>
    <?php
    //Attempt select query execution
    try{
        $sql = "SELECT * FROM datalog.day_avg ORDER BY dateavg DESC LIMIT 7";
        $result = $pdo->query($sql);
        if($result->rowCount() > 0){
            $druck = array();
            $volumenstrom = array();
            $spannung = array();
            while($row=$result->fetch()){
                $druck[] = $row ["avg_druck"];
                $volumenstrom[] = $row ["avg_volumenstrom"];
                $spannung[] = $row ["avg_spannung_quelle"];
            }
        unset($result);
        }else{
            echo "No records matching your query were found.";
        }
    }catch(PDOException $e){
        die("ERROR: Not able to execute $sql.".$e->getMessage());
    }   
    //Close connection
    unset($pdo);
    ?>

    <div class="chartBox">
        <canvas id="myChart" width="400" height="400"></canvas>
    </div>
    <script> </script>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <script>

    //Setup Block

    const druck = <?php echo json_encode ($druck); ?>;
    const volumenstrom = <?php echo json_encode ($volumenstrom); ?>;
    const spannung = <?php echo json_encode ($spannung); ?>;
    const data = {
    labels: ['t-8', 't-7', 't-6', 't-5', 't-4', 't-3', 't-2', 't-1'], 
            datasets: [{
                label: 'Druck in bar',
                data: druck,
                backgroundColor: 'rgba(255, 99, 132, 0.2)',
                borderColor: 'rgba(255, 99, 132, 1)',
                borderWidth: 1
            },{
                label: 'Volumenstrom in l/min',
                data: volumenstrom,
                backgroundColor: 'rgba(54, 162, 235, 0.2)',
                borderColor: 'rgba(54, 162, 235, 1)',
                borderWidth: 1
            },{
            label: 'Spannung in V',
                data: spannung,
                backgroundColor: 'rgba(255, 206, 86, 0.2)',
                borderColor: 'rgba(255, 206, 86, 1)',
                borderWidth: 1
            }]
        };
    //Config Block
    const config = {

        type: 'line',
        data,
        options: {
            scales: {
                y: {
                    beginAtZero: true
                }
            }
        }
    };
    //Render Block
    const myChart = new Chart(
        document.getElementById('myChart'),
        config 
    );
    </script>
</body>
</html>

实现步骤（简单版） #

1. 新建数据接口文件 get-data.php

把数据库查询逻辑单独抽出来，返回JSON格式的最新数据：

<?php
$username = "root";
$password = "_!Stud3nt&";
$database = "datalog";

try {
    $pdo = new PDO("mysql:host=localhost;database=$database", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $sql = "SELECT * FROM datalog.day_avg ORDER BY dateavg DESC LIMIT 7";
    $result = $pdo->query($sql);
    
    if($result->rowCount() > 0){
        $data = [
            'druck' => [],
            'volumenstrom' => [],
            'spannung' => []
        ];
        while($row = $result->fetch()){
            $data['druck'][] = $row["avg_druck"];
            $data['volumenstrom'][] = $row["avg_volumenstrom"];
            $data['spannung'][] = $row["avg_spannung_quelle"];
        }
        echo json_encode($data);
    } else {
        echo json_encode(['error' => 'No records found']);
    }
} catch(PDOException $e){
    echo json_encode(['error' => 'Database error: ' . $e->getMessage()]);
}
?>

2. 修改前端HTML文件

去掉PHP直接输出数据的逻辑，改用AJAX定时获取数据并更新图表：

<!DOCTYPE html>
<html lang="de">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="style/style.css">
    
    <title>ems</title>
    <style type="text/css">
        .chartBox {
            width: 700px;
        }
    </style>
</head>
<body>
    <!-- 移除重复的canvas元素 -->
    <div class="chartBox">
        <canvas id="myChart" width="400" height="400"></canvas>
    </div>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <script>
        // 初始化图表结构
        const labels = ['t-8', 't-7', 't-6', 't-5', 't-4', 't-3', 't-2', 't-1'];
        const data = {
            labels: labels,
            datasets: [{
                label: 'Druck in bar',
                data: [],
                backgroundColor: 'rgba(255, 99, 132, 0.2)',
                borderColor: 'rgba(255, 99, 132, 1)',
                borderWidth: 1
            },{
                label: 'Volumenstrom in l/min',
                data: [],
                backgroundColor: 'rgba(54, 162, 235, 0.2)',
                borderColor: 'rgba(54, 162, 235, 1)',
                borderWidth: 1
            },{
                label: 'Spannung in V',
                data: [],
                backgroundColor: 'rgba(255, 206, 86, 0.2)',
                borderColor: 'rgba(255, 206, 86, 1)',
                borderWidth: 1
            }]
        };

        const config = {
            type: 'line',
            data: data,
            options: {
                scales: {
                    y: {
                        beginAtZero: true
                    }
                }
            }
        };

        const myChart = new Chart(
            document.getElementById('myChart'),
            config 
        );

        // 定义更新图表的函数
        async function updateChart() {
            try {
                // 请求后端接口获取最新数据
                const response = await fetch('get-data.php');
                const result = await response.json();
                
                if (result.error) {
                    console.error(result.error);
                    return;
                }
                
                // 更新图表数据
                myChart.data.datasets[0].data = result.druck;
                myChart.data.datasets[1].data = result.volumenstrom;
                myChart.data.datasets[2].data = result.spannung;
                
                // 通知Chart.js更新视图（平滑过渡）
                myChart.update();
            } catch (error) {
                console.error('获取数据失败:', error);
            }
        }

        // 页面加载时先获取一次数据
        updateChart();

        // 每秒执行一次更新（1000毫秒=1秒）
        setInterval(updateChart, 1000);
    </script>
</body>
</html>

3. 关键说明

• 移除了原代码中重复的<canvas id="myChart">，避免Chart.js渲染冲突。
• 使用fetch API异步请求数据，无需刷新页面。
• 调用myChart.update()方法，Chart.js会自动以平滑动画更新图表，不会出现闪烁。
• 用setInterval定时触发更新，实现每秒刷新一次数据。

内容的提问来源于stack exchange，提问作者TXmahoo