如何在MariaDB/MySQL中存储实验x-y时间序列浮点数据？

优先推荐：规范化关系型设计 #

这是最适配时间序列测量数据的存储方式，完全规避长度限制问题，同时支持灵活的查询与分析操作。

表结构设计 #

1. 测试元数据表（tests）
   存储每个测试的唯一标识和基础信息：

   字段名	类型	说明
   test_id	BIGINT	主键，自增，唯一标识测试
   test_name	VARCHAR(255)	测试名称/编号
   created_at	DATETIME	测试创建时间
   conditions	TEXT	实验条件（可选）

2. 测量数据表（measurements）
   存储每一组x、y测量值，通过test_id关联到对应测试：

   字段名	类型	说明
   measurement_id	BIGINT	主键，自增
   test_id	BIGINT	外键，关联tests.test_id
   x_value	DOUBLE	x轴浮点测量值
   y_value	DOUBLE	y轴浮点测量值

优势 #

• 无存储长度限制：无论单测试有多少组x/y值，都可以逐行插入
• 查询灵活：可快速筛选特定测试的所有数据、指定x范围的y值，或进行统计计算（如平均值、极值）
• 性能稳定：MariaDB/MySQL对百万级甚至千万级行的处理效率足够满足实验室数据需求

Python批量插入示例 #

import mysql.connector
import csv

# 数据库连接配置
db_config = {
    "host": "localhost",
    "user": "your_username",
    "password": "your_password",
    "database": "lab_data_db"
}

# 初始化连接与游标
conn = mysql.connector.connect(**db_config)
cursor = conn.cursor()

# 1. 插入测试元数据，获取当前测试ID
cursor.execute("INSERT INTO tests (test_name) VALUES (%s)", ("20240520_温度实验",))
test_id = cursor.lastrowid

# 2. 读取空格分隔的CSV文件
measurement_data = []
with open("test_data.csv", "r") as f:
    csv_reader = csv.reader(f, delimiter=" ")
    for row in csv_reader:
        # 跳过空行
        if not row:
            continue
        x = float(row[0])
        y = float(row[1])
        measurement_data.append((test_id, x, y))

# 3. 批量插入测量数据（效率远高于单条插入）
cursor.executemany(
    "INSERT INTO measurements (test_id, x_value, y_value) VALUES (%s, %s, %s)",
    measurement_data
)

# 提交事务并关闭连接
conn.commit()
cursor.close()
conn.close()

备选方案：JSON类型存储 #

如果仅需整体存储/导出数据，不需要频繁查询局部x/y值，可以使用MariaDB 10.2+支持的JSON类型，将单测试的所有x/y对存为JSON数组：

表结构调整 #

在tests表中新增measurement_data字段：

ALTER TABLE tests ADD COLUMN measurement_data JSON;

存储格式示例：

[{"x": 0.1, "y": 2.3}, {"x": 0.2, "y": 2.5}, {"x": 0.3, "y": 2.7}]

注意事项 #

• JSON类型无硬性长度限制，但过大的JSON数据会降低查询和解析效率
• 无法直接对单个x/y值进行SQL层面的统计或筛选操作，需先取出JSON再在应用层处理

不推荐方案：分隔字符串存储 #

不要将x值用逗号/空格拼接成字符串存在单列（同理y值），这种方式会导致：

• 查询时需拆分字符串，效率极低
• 无法直接进行数值运算
• 容易出现数据格式错误

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