将文本文件传感器时序数据存入MySQL的表结构设计咨询

First, let's break down your initial design and then suggest a more efficient, maintainable approach for storing your sensor data.

What's Not Ideal About Your Current Plan #

• Redundant day table: Storing dates in a separate table is unnecessary because each reading's timestamp already includes the date. Joining three tables (sensor → day → dates) every time you query data adds complexity without any performance or storage benefits.
• Poorly named dates table: "dates" is a reserved keyword in MySQL, which can cause syntax errors. Additionally, storing only the time (e.g., 00:00) without the date means you can't uniquely identify which day that reading belongs to—critical for time-series data.
• Missing context for numeric values: Your three numeric fields don't have meaningful names, which will make queries and schema maintenance confusing long-term.

Recommended Schema Design #

Here's a normalized, practical schema that addresses these issues while maintaining data integrity and query performance:

1. sensor Table (Stores Sensor Metadata) #

This table keeps track of unique sensors to avoid repeating the sensor code (u901_radglobal) in every reading.

CREATE TABLE sensor (
    sensor_id INT AUTO_INCREMENT PRIMARY KEY,
    sensor_code VARCHAR(50) NOT NULL UNIQUE, -- e.g., 'u901_radglobal'
    sensor_description VARCHAR(255) DEFAULT NULL -- Optional: add details like sensor type/location
);

2. readings Table (Stores Time-Series Data) #

This table links directly to the sensor table and stores each reading with its full timestamp and metrics:

CREATE TABLE readings (
    reading_id INT AUTO_INCREMENT PRIMARY KEY,
    sensor_id INT NOT NULL,
    reading_datetime DATETIME NOT NULL, -- e.g., '2019-02-01 00:00'
    -- Replace these with meaningful metric names (adjust based on your data!)
    irradiance DECIMAL(10,6) NOT NULL, -- First value: -7.8047
    temperature DECIMAL(10,6) NOT NULL, -- Second value: 38.56
    some_metric DECIMAL(10,9) NOT NULL, -- Third value: 5.8193586538461
    -- Foreign key to ensure valid sensor references
    FOREIGN KEY (sensor_id) REFERENCES sensor(sensor_id),
    -- Index for fast time-series queries
    INDEX idx_sensor_datetime (sensor_id, reading_datetime)
);

Why This Works Better: #

• No redundant data: The full DATETIME field eliminates the need for a separate day table. You can still filter readings by date using DATE(reading_datetime) = '2019-02-01'.
• Data integrity: Foreign keys ensure you can't add readings for non-existent sensors.
• Faster queries: The idx_sensor_datetime index speeds up common queries like "get all readings for sensor X on date Y" or "fetch readings between two timestamps".
• Readability: Meaningful metric names make your schema self-documenting.

Loading Data from file.txt into MySQL #

Once your tables are set up, here are two reliable ways to import your data:

Option 1: Use LOAD DATA INFILE (Fastest for Large Files) #

This is MySQL's built-in tool for bulk importing data. First, clean up your file if it has multiple spaces between values (replace with single spaces using sed 's/ */ /g' file.txt > cleaned_file.txt).

-- 1. Insert your sensor if it doesn't already exist
INSERT INTO sensor (sensor_code) VALUES ('u901_radglobal') ON DUPLICATE KEY UPDATE sensor_code = sensor_code;

-- 2. Get the sensor ID to link readings
SET @sensor_id = (SELECT sensor_id FROM sensor WHERE sensor_code = 'u901_radglobal');

-- 3. Bulk load the data
LOAD DATA INFILE '/path/to/cleaned_file.txt'
INTO TABLE readings
FIELDS TERMINATED BY ' '
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(@sensor_code, @reading_datetime, @irradiance, @temperature, @some_metric)
SET sensor_id = @sensor_id,
    reading_datetime = STR_TO_DATE(@reading_datetime, '%Y-%m-%d %H:%i'),
    irradiance = @irradiance,
    temperature = @temperature,
    some_metric = @some_metric;

Option 2: Python Script (For Custom Parsing) #

If you need more control over parsing (e.g., handling edge cases), use a Python script with mysql-connector:

import mysql.connector
from mysql.connector import Error

# Connect to your MySQL database
try:
    conn = mysql.connector.connect(
        host='your_host',
        database='your_db',
        user='your_user',
        password='your_password'
    )
    if conn.is_connected():
        cursor = conn.cursor()

        # Insert sensor (if not exists)
        cursor.execute("INSERT INTO sensor (sensor_code) VALUES ('u901_radglobal') ON DUPLICATE KEY UPDATE sensor_code = sensor_code")
        conn.commit()

        # Get sensor ID
        cursor.execute("SELECT sensor_id FROM sensor WHERE sensor_code = 'u901_radglobal'")
        sensor_id = cursor.fetchone()[0]

        # Parse and insert readings
        with open('file.txt', 'r') as f:
            for line in f:
                # Split line using quotes to handle the datetime string
                parts = line.strip().split('"')
                reading_datetime = parts[3].strip()
                metrics = parts[5].strip().split()
                
                insert_query = """
                    INSERT INTO readings (sensor_id, reading_datetime, irradiance, temperature, some_metric)
                    VALUES (%s, %s, %s, %s, %s)
                """
                cursor.execute(insert_query, (sensor_id, reading_datetime, metrics[0], metrics[1], metrics[2]))
        
        conn.commit()
        print("Data imported successfully!")

except Error as e:
    print(f"Error: {e}")
finally:
    if conn.is_connected():
        cursor.close()
        conn.close()

Additional Tips #

• Adjust data types: If your metrics don't need high precision, you can use FLOAT instead of DECIMAL, but DECIMAL is better for scientific measurements where precision matters.
• Partitioning: For long-term storage (months/years of data), consider partitioning the readings table by date (e.g., monthly partitions) to improve query speed and manageability.
• Error handling: When importing, add checks for invalid timestamps or numeric values to avoid corrupting your database.

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