在PHP中,我从多个SQL表中获取时间戳列表,并创建一个列出唯一时间戳的数组。然而,表之间的时间戳并不相同,对于同一事件,时间戳可能会相差一秒钟。因此,例如,我希望1374531523.343和1374531524.012被视为同一事件,而不是1374531520.342。
我一直在使用这个函数来获取最近事件的时间间隔:
function findNearest($number, $array, $index = false) {
$min = abs($number - $array[0]);
$min_i = 0;
foreach ($array as $ind => $value) {
$mint = abs($number - $value);
if ($mint < $min) {
$min = $mint;
$min_i = $ind;
}
}
return ($index ? $min_i : $min);
}
(添加索引部分是因为有时我需要最近时间的索引,但这可以移动到一个单独的函数)
因此,基本上我为每个表运行一个简单的SELECT ...
查询,并检查每个时间戳:
while ($g = $q->fetch_object())
if (findNearest($g->timestamp, $timestamps) > 1) $timestamps[] = $g->timestamp;
}
这是我想要的,但我正在研究大约100000个不同的时间戳,未来将研究多达500000个。随着$timestamps
变得越来越大,它变得越来越慢。我知道这是不可避免的,但也许有一种更好的方法可以通过MySQL或PHP来减少时间?
这些步骤可以更快地缩短处理时间,但可能会花费比预期更多的精力。
假设:
- 变量$qry包含将结果存储到变量$q的查询
- 下面有三张桌子
- 您执行查询,$q在您的示例中使用该查询,从表1、表2开始,最后从表3开始
表1:
id | col_timestamp | parent_table | parent_id
---------------------------------------------
1 | 1374531523.343| NULL | NULL
表2:
id | col_timestamp | parent_table | parent_id
---------------------------------------------
1 | 1374531520.444| NULL | NULL
2 | 1374531524.012| table_1 | 1
3 | 1374531556.012| NULL | 1
4 | 1374531556.512| table_2 | 3
表3:
id | col_timestamp | parent_table | parent_id
---------------------------------------------
1 | 1374531521.111| table_2 | 1
2 | 1374531523.111| table_1 | 1
说明:字段parent_table是一个varchar,表示当前行是指table_1、table_2或table_3。字段parent_id是指表中字段parent_table所指向的字段id。
现在,每次用户插入所有表时,我们都需要检查数据库中是否已经存在类似的事件。我们可以通过使用触发器来做到这一点。每次将一行插入表_2:时都会触发此触发器
DELIMITER $$
USE `your_database`$$
DROP TRIGGER /*!50032 IF EXISTS */ `before_insert_table_2`$$
CREATE TRIGGER `before_insert_table_2` BEFORE INSERT
ON `table_2` FOR EACH ROW
BEGIN
DECLARE var_id INTEGER ;
DECLARE var_table VARCHAR (10) ;
SELECT id, parent_table INTO var_id, var_table
FROM
( SELECT id, 'table_1' AS parent_table, col_timestamp
FROM table_1
WHERE parent_id IS NULL
AND col_timestamp BETWEEN NEW.col_timestamp - 1 AND NEW.col_timestamp + 1
UNION
SELECT id, 'table_2' AS parent_table, col_timestamp
FROM table_2
WHERE parent_id IS NULL
AND col_timestamp BETWEEN NEW.col_timestamp - 1 AND NEW.col_timestamp + 1
)
ORDER BY ABS(col_timestamp - NEW.col_timestamp), parent_table
LIMIT 1 ;
SET NEW.parent_id = var_id ;
SET NEW.parent_table = var_table ;
END ;
$$
DELIMITER ;
执行与表_1和表_3类似的步骤。
下一步是将parent_table和parent_id设置到现有数据中。您可以修改$qry以获取表名及其id,并更新到关联行中。此步骤只需要运行一次。
下一步是修改查询以获取事件。这是一个例子:
SELECT 'table_1' original_table, id
FROM table_1
WHERE parent_id IS NULL
UNION
SELECT 'table_2' original_table, id
FROM table_2
WHERE parent_id IS NULL
UNION
SELECT 'table_3' original_table, id
FROM table_3
WHERE parent_id IS NULL
最后一步是修改程序以实现数据库中的更改。
希望这能有所帮助。
是否可以为时间戳和类似的id创建另一个MySQL表
表:时间戳
time_id | timestamp
--------+---------------
1 | 1374531523.343
然后使用id与使用相同时间戳的所有其他表关联,类似于:
其他表格:
table_id | timestamps_time_id | etc...
---------+--------------------+-------
1 | 1 | etc...
也许您可以编辑查询,而不是使用PHP:处理结果
SELECT time FROM table GROUP BY time DIV 1