MySQL：Insert into select group by count on duplicate update数据同步

Got it, let's walk through how to build this daily aggregation system step by step. You need a PHP script that runs via cron each night to pull data from your analytika table, group it by frequency for each column, and sync those stats to separate tables using MySQL's INSERT ... SELECT ... GROUP BY ... ON DUPLICATE KEY UPDATE pattern. Here's everything you need:

1. First, Create Your Target Stats Tables #

First, you'll need separate tables for each column you want to track. Each table will store the unique value from the main table and its cumulative frequency. We'll set the value column as the primary key to enable the ON DUPLICATE KEY UPDATE behavior.

-- Table for aa_current frequency stats
CREATE TABLE IF NOT EXISTS aa_current_stats (
    value VARCHAR(255) NOT NULL PRIMARY KEY,
    frequency INT UNSIGNED NOT NULL DEFAULT 0
);

-- Table for aa_referrer frequency stats
CREATE TABLE IF NOT EXISTS aa_referrer_stats (
    value VARCHAR(255) NOT NULL PRIMARY KEY,
    frequency INT UNSIGNED NOT NULL DEFAULT 0
);

-- Table for aa_mobile frequency stats (boolean type matches your data)
CREATE TABLE IF NOT EXISTS aa_mobile_stats (
    value BOOLEAN NOT NULL PRIMARY KEY,
    frequency INT UNSIGNED NOT NULL DEFAULT 0
);

2. PHP Script for Daily Aggregation #

This script will connect to your database, run the aggregation queries for each column, and log activity for debugging. We'll use PDO for robust error handling and security.

<?php
// Update these with your database credentials
$dbConfig = [
    'host' => 'localhost',
    'dbname' => 'your_database_name',
    'user' => 'your_db_username',
    'pass' => 'your_db_password'
];

// Path to log file (make sure the directory is writable by the cron user)
$logPath = '/var/log/analytika_aggregation.log';

// Helper function to write log messages
function writeLog($message) {
    global $logPath;
    $timestamp = date('[Y-m-d H:i:s]');
    file_put_contents($logPath, "$timestamp $message\n", FILE_APPEND);
}

try {
    // Connect to database
    $pdo = new PDO(
        "mysql:host={$dbConfig['host']};dbname={$dbConfig['dbname']};charset=utf8mb4",
        $dbConfig['user'],
        $dbConfig['pass'],
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]
    );
    writeLog("Successfully connected to database");

    // Aggregate aa_current data
    $currentQuery = "
        INSERT INTO aa_current_stats (value, frequency)
        SELECT aa_current, COUNT(*)
        FROM analytika
        WHERE aa_current IS NOT NULL  -- Skip NULL values (adjust if needed)
        GROUP BY aa_current
        ON DUPLICATE KEY UPDATE frequency = frequency + VALUES(frequency);
    ";
    $pdo->exec($currentQuery);
    writeLog("Finished aggregating aa_current data");

    // Aggregate aa_referrer data
    $referrerQuery = "
        INSERT INTO aa_referrer_stats (value, frequency)
        SELECT aa_referrer, COUNT(*)
        FROM analytika
        WHERE aa_referrer IS NOT NULL
        GROUP BY aa_referrer
        ON DUPLICATE KEY UPDATE frequency = frequency + VALUES(frequency);
    ";
    $pdo->exec($referrerQuery);
    writeLog("Finished aggregating aa_referrer data");

    // Aggregate aa_mobile data
    $mobileQuery = "
        INSERT INTO aa_mobile_stats (value, frequency)
        SELECT aa_mobile, COUNT(*)
        FROM analytika
        GROUP BY aa_mobile
        ON DUPLICATE KEY UPDATE frequency = frequency + VALUES(frequency);
    ";
    $pdo->exec($mobileQuery);
    writeLog("Finished aggregating aa_mobile data");

    writeLog("All aggregation tasks completed successfully\n");

} catch (PDOException $e) {
    $errorMsg = "Database error occurred: " . $e->getMessage();
    writeLog($errorMsg);
    exit(1); // Exit with non-zero code to alert cron of failure
}
?>

3. Set Up Cron to Run Daily #

To schedule this script to run nightly (e.g., at 2 AM), open your crontab editor:

crontab -e

Add this line (replace the path with your actual script location):

0 2 * * * /usr/bin/php /path/to/your/aggregation_script.php

• 0 2 * * * means "run at 2:00 AM every day"
• Use the full path to PHP (check with which php) to avoid cron path issues

4. Key Optimizations & Notes #

• Index Your Main Table: For large analytika tables, add indexes to the columns you're aggregating to speed up the GROUP BY queries:

  CREATE INDEX idx_analytika_current ON analytika(aa_current);
  CREATE INDEX idx_analytika_referrer ON analytika(aa_referrer);
  CREATE INDEX idx_analytika_mobile ON analytika(aa_mobile);
  

• Incremental Aggregation: If your main table has a timestamp column (e.g., created_at), modify the queries to only process the previous day's data instead of the entire table:

  WHERE DATE(created_at) = CURDATE() - INTERVAL 1 DAY
  

• Handle NULLs: Adjust the WHERE ... IS NOT NULL clauses if you want to include NULL values in your frequency stats.
• Permissions: Ensure the cron user has read access to the script and write access to the log file, and your database user has SELECT on analytika and INSERT/UPDATE on the stats tables.

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