php - 查询 SQL 并格式化为 CSV 的有效方法

Question

我有一个 MySQL 数据库，用于保存来自多个不同传感器的温度读数。我最初想到使用三个不同的表来存储我的数据:

mysql> select * from sensor_info;
+----+------------------+------+--------+
| id | address          | name | active |
+----+------------------+------+--------+
|  1 | 28D684CD02000057 | NULL |      1 |
|  2 | 28099B49030000D8 | NULL |      1 |
|  3 | 28339ACD0200004B | NULL |      1 |
+----+------------------+------+--------+

mysql> select * from data_period limit 4;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  1 | 2012-06-30 09:35:02 |
|  2 | 2012-06-30 09:36:22 |
|  3 | 2012-06-30 09:37:46 |
|  4 | 2012-06-30 09:40:36 |
+----+---------------------+

mysql> select * from data_points limit 4;
+----+-------------+-----------+-------+
| id | data_period | sensor_id | data  |
+----+-------------+-----------+-------+
|  1 |           1 |         1 | 77.90 |
|  2 |           1 |         2 | 77.34 |
|  3 |           1 |         3 | 77.56 |
|  4 |           2 |         1 | 78.01 |
+----+-------------+-----------+-------+

我想做的是获取我存储的数据并将其放入 CSV 文件中，以便我可以使用 dygraphs Javascript library 显示它。我需要将数据转换为如下格式:

date,temp1,temp2,temp3
2012-06-30 09:35:02,77.90,77.34,77.56
2012-06-30 09:36:22,78.01,77.36,77.59
....

我开始这样做的每一种方式(使用 PHP)，我似乎都让它变得过于复杂，并且必须将查询放在循环中的循环中。我是否让自己变得比我需要的更加困难？

大部分工作是使用查询还是使用 PHP 来完成？今后，我还想添加代码，如果特定时间戳中缺少温度读数，则将 NULL 放入 CSV 中。

我并不是在寻找非常具体的答案，我只是想知道我应该朝哪个方向发展。我什至不知道如何开始格式化数据库中的数据，或者我是否应该尝试寻找不同的格式来将我的信息存储在数据库中。

Answer 1

我会运行一个选择查询来将批处理连接在一起。您可以在可能没有数据的地方使用外连接。

SELECT data_period.ts AS date, dp1.data AS temp1, dp2.data AS temp2, dp3.data AS temp3
FROM data_period
LEFT OUTER JOIN data_points AS dp1 ON dp1.data_period=data_period.id AND dp1.sensor_id=1
LEFT OUTER JOIN data_points AS dp2 ON dp2.data_period=data_period.id AND dp2.sensor_id=2
LEFT OUTER JOIN data_points AS dp3 ON dp3.data_period=data_period.id AND dp3.sensor_id=3

(参见:SQL Fiddle Example)

这应该为您提供一组可以循环访问的结果。

如果你真的希望 MySQL 完成大部分工作，你可以将第一行更改为(我认为)SELECT data_period.ts +','+ IFNULL(dp1.data,'NULL') +','+ IFNULL(dp2.data,'NULL') +','+ IFNULL(dp3.data,'NULL')

综合评论和答案并将其保存到文件中:

SELECT data_period.ts AS date, dp1.data AS temp1, dp2.data AS temp2, dp3.data AS temp3
INTO OUTFILE '/home/user/output.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM data_period LEFT OUTER JOIN data_points AS dp1 ON dp1.data_period=data_period.id      
AND dp1.sensor_id=1 LEFT OUTER JOIN data_points AS dp2 ON  
dp2.data_period=data_period.id   AND dp2.sensor_id=2 LEFT OUTER JOIN data_points AS dp3 
ON dp3.data_period=data_period.id AND dp3.sensor_id=3;