具有访问前端的mysql数据库结构。

I have a server that collects a lot of information. Right now there is a database for each month and a table for each day of the month. Usually there are about 200,000+ rows per day (about 150MB a table).

我有一个收集大量信息的服务器。现在每个月都有一个数据库，每个月都有一个表。通常每天大约有200,000多行(大约150MB一个表)。

On another post, someone suggested I just use one table per month to make it easier to manipulate the data. This would not be hard since all the entries have a date\time stamp.

在另一篇文章中，有人建议我每月只使用一个表，以便更容易地操作数据。这并不困难，因为所有的条目都有一个日期\时间戳。

The problem is that we have to use an MS Access front-end and the data is not linked, but imported each day for revision. This has worked well with a VBA script that automatically connects to the right database & table.

问题是，我们必须使用MS Access前端，并且数据没有链接，而是每天导入进行修订。这在VBA脚本中运行得很好，VBA脚本自动连接到正确的数据库和表。

sConnect = "ODBC;Driver={MySQL ODBC 5.1 Driver};" & _
  "SERVER=172.16.0.0;" & _
  "PORT=3306;" & _
  "DATABASE=" & "Mon" & dbMonth & ";" & _
  "USER=USER;" & _
  "PASSWORD=PASS;"

DoCmd.TransferDatabase acImport, "ODBC Database", _
  sConnect, acTable, "Data" & dbDay, "Revise", , True

Anyone have a suggestion for an quick & easy way to automatically import just the day's info from a database that has 6 million+ entries? Right now it takes about 20 seconds over the LAN to import one day's worth of data.

有人有什么建议，可以快速简单地从一个有600多万个条目的数据库中自动导入当天的信息吗?现在，在局域网中导入一天的数据需要大约20秒。

Thanks!

谢谢!

4 个解决方案

#1

One question that enters my mind is why are you separating your information off like this - why not simply time stamp the entries, and dump it all into one database to work against. Much easier to keep track of, maintain, backup, and restore - then just write your VBA to fetch the appropriate records for your front-end to revise, pushing them back to the table within that one database once your revisions are through - perhaps checking a 'Revised' checkbox to indicate that these should NOT be pulled next time (if I am understanding the purpose correctly).

我想到的一个问题是，为什么要像这样将信息分开——为什么不简单地对条目进行时间戳，并将其全部转储到一个数据库中进行处理呢?更容易跟踪、维护、备份、恢复,然后写出你的VBA获取适当的前端修改记录,把他们带回一个数据库的表内一旦你的修改是通过——也许检查“修订”复选框,表示这些不应该把下次(如果我理解正确的目的)。

Another is why would you not want to link to the database, or at least a view of the database?

另一个问题是，为什么不希望链接到数据库，或者至少不希望链接到数据库的视图?

I am still a little new here, so perhaps there is larger concerns I am not seeing with my questions - and if so, I'd love to be enlightened as well.

我在这里还是个新手，所以我的问题可能有更大的问题我看不到——如果有的话，我也很想被启发。

#2

I'd stick with one table in MySQL and add a timestamp field with a default of CURRENT_TIMESTAMP, so that MySQL will auto-populate this timestamp field.

我将使用MySQL中的一个表，并添加一个默认的CURRENT_TIMESTAMP字段，以便MySQL自动填充这个timestamp字段。

A simple query can then return one month's records, which you could append to a temp (?) table.

然后，一个简单的查询可以返回一个月的记录，您可以将其附加到temp(?)表中。

However...

然而……

we need to have the data imported into a table and not just linked

我们需要将数据导入到表中，而不仅仅是链接

Are you sure you really need to do that? If you are processing each record further via vba, can you not include any additional info as extra fields in the original MySQL table?

你确定你真的需要那样做吗?如果您正在通过vba进一步处理每个记录，是否可以在原始MySQL表中包含任何额外的信息?

#3

I would put all data into a single table, create a link instead of importing and generate your query from VBA. Yes, it may be a bit complicated up front, but, in my opinion, the easiest to continue working with and maintain.

我将把所有数据放在一个表中，创建一个链接而不是导入，并从VBA生成查询。是的，前面可能有点复杂，但是在我看来，最简单的方法是继续工作和维护。

We have mixins of MySql, Access database, Access forms, VB6 and VB.Net. It is a pain to work with all of these technologies. I have done enough with the apps to know that a single table and re-linking is your best bet.

我们有MySql、Access数据库、Access forms、VB6和VB.Net的混合。使用所有这些技术是一件痛苦的事情。我已经用这些应用程序做了足够多的工作，知道一个表和重新链接是你最好的选择。

#4

Well, I am going to restructure the database, because I found out that Access has a "hidden" way to populate the filtering list from linked tables. In database options you can tell it to use ODBC for filter lists too. It is was hard to find & I didn't even think it could be done. Now I have a lot more options because I can just link the tables.

我将重组数据库，因为我发现Access有一种“隐藏”的方式来从链接的表中填充过滤列表。在数据库选项中，您可以告诉它使用ODBC来过滤列表。这很难找到&我甚至不认为它能做到。现在我有更多的选项，因为我可以链接表。

Thank for all those who tried to help.

谢谢所有那些试图帮助我的人。

#1