使用php附加无限的sqlite数据库 [英] Attach unlimited sqlite databases using php

问题描述

一台设备每10分钟生成一次带有不同行的.db文件.所有的sqlite文件都有一个称为"wifi"的表.

One devices generates .db files every 10 minutes with different rows. All the sqlite files have the same table called "wifi".

我正在编写一个php应用程序，以从所有文件中读取所有数据.我正在使用ATTACH方法来合并数据库.喜欢:

I'm writing a php application to read all data from all the files. I am using ATTACH method to combine databases. Like:

try {
$file_db = new PDO('sqlite::memory:');

$file_db->setAttribute(PDO::ATTR_ERRMODE,
                        PDO::ERRMODE_EXCEPTION);

$file_db->exec("ATTACH DATABASE 'wifi_16-09-02_09_44_06.db' AS db1");
//many more attached files here, like 1000 or 2000

$result = $file_db->query('SELECT * FROM db1.wifi UNION ALL SELECT * FROM db2.wifi, ...');

$file_db = null;
}
catch(PDOException $e) {
echo $e->getMessage();

错误:

SQLSTATE [HY000]:常规错误:1个附加数据库过多-最多10个

SQLSTATE[HY000]: General error: 1 too many attached databases - max 10

在编写此代码后，我发现sqlite附件有一个限制，默认情况下为10，仅根据

After I wrote this code, I found out the sqlite attach has a limit, by default it is 10, and can be increased up to 125 only according to this.

如何附加数千个数据库?如果我的方法不正确，解决此问题的最佳方法是什么?

How can I attach thousands of databases? if my method is wrong, what is the best way to solve this?

推荐答案

只需将每个新数据库作为新的PDO实例打开.

Just open each new database as a new PDO instance.

$file_db = new PDO('sqlite::wifi_16-09-02_09_44_06.db');
$file_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$result = $file_db->query('SELECT * FROM db1.wifi UNION ALL SELECT * FROM db2.wifi, ...');
// do whatever
$file_db = new PDO('sqlite::wifi_whatever.db');
$file_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$result = $file_db->query('SELECT * FROM db1.wifi UNION ALL SELECT * FROM 

以此类推.

就这么简单.

这篇关于使用php附加无限的sqlite数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！