PHP实现：检测MySQL数据库表行数是否发生变化

Got it, let's figure out how to track when the row count of your MySQL table changes. Since you already have code to query the current count, the key missing piece is saving that previous count somewhere so you can compare it on subsequent runs. Here are a few practical, easy-to-implement approaches:

1. Store Previous Count in a Local File #

This is the simplest option if you're running a script on a single machine. You can save the last row count to a text file, read it back on each run, compare, then update the file.

Example Code (Python) #

import mysql.connector

# Your existing function to get current row count
def get_current_row_count():
    conn = mysql.connector.connect(
        host="your_host",
        user="your_user",
        password="your_password",
        database="your_db"
    )
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) FROM your_table;")
    count = cursor.fetchone()[0]
    cursor.close()
    conn.close()
    return count

# Helper functions to read/write the history file
def get_previous_count(file_path="row_count_history.txt"):
    try:
        with open(file_path, "r") as f:
            return int(f.read().strip())
    except (FileNotFoundError, ValueError):
        # If file doesn't exist or has invalid data, initialize with current count
        current_count = get_current_row_count()
        save_count(current_count, file_path)
        return current_count

def save_count(count, file_path="row_count_history.txt"):
    with open(file_path, "w") as f:
        f.write(str(count))

# Main logic
current_count = get_current_row_count()
previous_count = get_previous_count()

if current_count != previous_count:
    print(f"Row count changed! Previous: {previous_count}, Current: {current_count}")
    # Add your alert/logic here (e.g., send email, write to a log file)
else:
    print(f"Row count unchanged: {current_count}")

# Update the history with the latest count
save_count(current_count)

Pros: No extra services needed, super straightforward.
Cons: Not ideal for distributed systems or if multiple processes might access the file at once.

2. Store Previous Count in a MySQL Table #

For more reliability (especially in production or multi-machine setups), create a dedicated table to track row counts for your target tables. This keeps the history persisted in your database where it's easy to access and maintain.

Step 1: Create the Tracking Table #

CREATE TABLE table_row_counts (
    table_name VARCHAR(255) PRIMARY KEY,
    last_count INT NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Step 2: Example Code (Python) #

import mysql.connector

def get_db_connection():
    return mysql.connector.connect(
        host="your_host",
        user="your_user",
        password="your_password",
        database="your_db"
    )

def get_current_row_count(table_name):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute(f"SELECT COUNT(*) FROM {table_name};")
    count = cursor.fetchone()[0]
    cursor.close()
    conn.close()
    return count

def get_previous_count(table_name):
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # Check if we have an existing record
    cursor.execute("SELECT last_count FROM table_row_counts WHERE table_name = %s;", (table_name,))
    result = cursor.fetchone()
    
    if result:
        previous_count = result[0]
    else:
        # Initialize with current count if no record exists
        previous_count = get_current_row_count(table_name)
        cursor.execute("INSERT INTO table_row_counts (table_name, last_count) VALUES (%s, %s);", 
                      (table_name, previous_count))
        conn.commit()
    
    cursor.close()
    conn.close()
    return previous_count

def update_previous_count(table_name, new_count):
    conn = get_db_connection()
    cursor = conn.cursor()
    # REPLACE will update existing or insert new
    cursor.execute("REPLACE INTO table_row_counts (table_name, last_count) VALUES (%s, %s);", 
                  (table_name, new_count))
    conn.commit()
    cursor.close()
    conn.close()

# Main logic
target_table = "your_table"
current_count = get_current_row_count(target_table)
previous_count = get_previous_count(target_table)

if current_count != previous_count:
    print(f"Row count for {target_table} changed! Previous: {previous_count}, Current: {current_count}")
    # Add your alert/logic here
else:
    print(f"Row count for {target_table} remains the same: {current_count}")

update_previous_count(target_table, current_count)

Pros: Persistent, works across multiple machines, tracks update timestamps.
Cons: Requires creating an extra table, slightly more setup.

3. Use a Cache (e.g., Redis) for Fast Access #

If you need high-performance reads/writes (like for frequent checks) or are working in a distributed system, using a cache like Redis is a great option.

Example Code (Python) #

import mysql.connector
import redis

def get_current_row_count():
    conn = mysql.connector.connect(
        host="your_host",
        user="your_user",
        password="your_password",
        database="your_db"
    )
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) FROM your_table;")
    count = cursor.fetchone()[0]
    cursor.close()
    conn.close()
    return count

# Initialize Redis connection
redis_client = redis.Redis(host='localhost', port=6379, db=0, decode_responses=True)

def get_previous_count(cache_key="your_table_row_count"):
    previous_count = redis_client.get(cache_key)
    if not previous_count:
        # Initialize cache with current count
        current_count = get_current_row_count()
        redis_client.set(cache_key, current_count)
        return current_count
    return int(previous_count)

def update_previous_count(cache_key="your_table_row_count", new_count=None):
    if new_count is None:
        new_count = get_current_row_count()
    redis_client.set(cache_key, new_count)

# Main logic
current_count = get_current_row_count()
previous_count = get_previous_count()

if current_count != previous_count:
    print(f"Row count changed! Previous: {previous_count}, Current: {current_count}")
    # Add your alert/logic here
else:
    print(f"Row count unchanged: {current_count}")

update_previous_count(new_count=current_count)

Pros: Blazing fast reads/writes, perfect for distributed systems.
Cons: Requires a Redis server, cache can be cleared (so handle initialization properly).

Bonus: Optimize for Large Tables #

If your table has millions of rows, SELECT COUNT(*) can be slow. Instead, maintain a real-time counter using triggers:

Step 1: Create a Counter Table #

CREATE TABLE table_counters (
    table_name VARCHAR(255) PRIMARY KEY,
    counter INT NOT NULL DEFAULT 0
);

-- Initialize with current row count
INSERT INTO table_counters (table_name, counter) VALUES ('your_table', (SELECT COUNT(*) FROM your_table));

Step 2: Add Triggers to Update the Counter #

-- Trigger for INSERTs
DELIMITER //
CREATE TRIGGER inc_counter_after_insert AFTER INSERT ON your_table
FOR EACH ROW
BEGIN
    UPDATE table_counters SET counter = counter + 1 WHERE table_name = 'your_table';
END //
DELIMITER ;

-- Trigger for DELETEs
DELIMITER //
CREATE TRIGGER dec_counter_after_delete AFTER DELETE ON your_table
FOR EACH ROW
BEGIN
    UPDATE table_counters SET counter = counter - 1 WHERE table_name = 'your_table';
END //
DELIMITER ;

Now you can get the row count in O(1) time with:

SELECT counter FROM table_counters WHERE table_name = 'your_table';

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