时间戳

首页 > 解决方案 > MySQL Array 基于索引位置的 SELECT 和 UPDATE 语句

mysql - MySQL Array 基于索引位置的 SELECT 和 UPDATE 语句

问题描述

请考虑下面我的表格图像，我的问题涉及在列上执行UPDATE和。SELECTsensor_values

在此处输入图像描述

sensor_valuescolumn 是一个长度为 86,400 的数组，对应于一天中每一秒的传感器值。我现在将其设置为JSON数据类型。

我的问题

如何UPDATE在特定索引上执行？

我如何SELECT度过一天的前 300 秒？下面的伪代码

SELECT sensor_values[0:299] FROM facts WHERE d_date = '2020-03-10' AND dim_tag = 'Water_Temp'

这是一个指向 PostGresql 的 DBFiddle 的链接，它做的事情与我在 MariaDB 中尝试完成的事情非常相似。 https://dbfiddle.uk/?rdbms=postgres_12&fiddle=6999d2607c48a809ff995c8844c7f7bb

编辑：下面我的系统的高级概述

我有一个 Python 数据管道，可以读取数千个传感器的值并执行计算。它负责存储在其他表中的所有数据汇总。

我需要第二级的数据可以轻松访问以进行分析，我正试图以这个问题中所说的方式来做。我的想法是在 Python 中获取当天的第二天，并将其用作要更新的数组索引，如下所示：

now = datetime.datetime.now()
midnight = now.replace(hour=0, minute=0, second=0, microsecond=0)
secondsSinceMidnight = (now - midnight).seconds

sensorValue =  300.25
sensorTag = "Water_Temp"

sqlQuery = 
"""
UPDATE facts
SET sensor_values[%s] = %s
WHERE d_date = %s AND dim_tag = %s
"""
sqlValues = secondsSinceMidnight, sensorValue, datetime.datetime.today().strftime('%Y-%m-%d'), sensorTag

dbOps.update(sqlQuery, sqlValues)

我不想整天跟踪 Python 中的值然后执行INSERT，因为如果程序崩溃我会丢失所有数据，并且使用预先形成的数组可以让我快速以编程方式确定数据丢失的位置我程序的其他领域（即一天中sensor_value的0一组秒）。

我使用术语预制阵列，因为我计划让我的程序定期检查以确保当前和第二天的每个传感器始终有一行可用，确保在午夜无缝过渡，因为颗粒非常小。

正如比尔卡尔文的回答所建议的那样，这是我的第一种方法 - 但是有数千个传感器，每个传感器都会每秒发布一次。数据库将呈指数增长：1000 * 86400 = 86.4M每天输入的行数。

所以，它不必是JSON，只是一个可以这种格式修改的数组。我认为这是正确的方法MySQL。更新了帖子的标题以更好地反映问题。

标签： mysqljsonmariadb

解决方案

如果您想使用 SQL 处理一组传感器值的各个元素，请不要将其存储在 JSON 中。将其作为行存储在普通表中。

CREATE TABLE facts (
 d_date DATE NOT NULL,
 second INT NOT NULL,
 dim_tag VARCHAR(...) NOT NULL,
 sensor_value NUMERIC(5,2) NOT NULL,
 PRIMARY KEY (d_date, second)
);

然后你想要的查询在 SQL 中更直接：

UPDATE facts SET sensor_value = 300.25 WHERE d_date = '2020-03-10' AND second = 4;

SELECT sensor_value FROM facts WHERE d_date = '2020-03-10' AND second < 300;

如果您坚持将传感器值存储为 JSON 数组，则没有使用 SQL 操作单个元素的简单方法。您应该只将整个数组提取到您的应用程序中，然后将 JSON 解压缩到可以使用代码读取或更改的数组中。

这是使用 JSON 的弱点：很容易将一组值插入到单个列中，并且很容易将整个集合作为一个单元获取。但是，如果您想使用 SQL 逻辑来处理该集合的各个元素，那就很尴尬了。

有人可以发布一个答案，为您提供您在上述问题中要求的结果吗？可能，但开发时间太长，下次你想用这些数据做任何其他类型的任务时，你必须再次寻求帮助。

当你真的想要离散的数据元素时使用 JSON 不是一个可持续的设计，因为它会导致软件开发成本增加太高。

重新提供您的附加信息：

MySQL 不支持数组数据类型或任何使用数组下标的语法，就像你想象的那样。

我同意每天 8640 万行是一个快速的增长速度。您将在不到两周的时间内达到超过 10 亿行。

可能是您需要时间序列数据库而不是关系数据库。时间序列数据库针对类似数据的长流进行优化，并在列上运行聚合查询。他们接受这些数据流的快速摄取，也比 InnoDB 等数据库引擎更紧凑地存储数据。

你应该看看其中之一：

推荐阅读