MS Access 数据库引擎表中的最大行数? [英] Maximum number of rows in an MS Access database engine table?

问题描述

我们知道 MS Access 数据库引擎被节流"以允许最大文件大小为 2GB(或者可能内部连接限制为小于 4KB 数据页的 2 的某个幂).但这在实际中意味着什么?

We know the MS Access database engine is 'throttled' to allow a maximum file size of 2GB (or perhaps internally wired to be limited to fewer than some power of 2 of 4KB data pages). But what does this mean in practical terms?

为了帮助我衡量这一点，您能告诉我可以插入 MS Access 数据库引擎表的最大行数吗?

To help me measure this, can you tell me the maximum number of rows that can be inserted into a MS Access database engine table?

为了满足表的定义，所有行必须是唯一的，因此是唯一约束(例如PRIMARY KEY、UNIQUE、CHECK、数据宏等)是一项要求.

To satisfy the definition of a table, all rows must be unique, therefore a unique constraint (e.g. PRIMARY KEY, UNIQUE, CHECK, Data Macro, etc) is a requirement.

我意识到有一个理论上的限制，但我感兴趣的是实际的(不一定可行)，现实生活的限制.

I realize there is a theoretical limit but what I am interested in is the practical (and not necessarily practicable), real life limit.

推荐答案

这是我的尝试:

我创建了一个没有键的单列 (INTEGER) 表:

I created a single-column (INTEGER) table with no key:

CREATE TABLE a (a INTEGER NOT NULL);

从 1 开始按顺序插入整数.

Inserted integers in sequence starting at 1.

当它插入 65,632,875 行时，我停止了它(几个小时后任意地).文件大小为 1,029,772 KB.

I stopped it (arbitrarily after many hours) when it had inserted 65,632,875 rows. The file size was 1,029,772 KB.

我压缩了文件，将其略微减少到 1,029,704 KB.

I compacted the file which reduced it very slightly to 1,029,704 KB.

我添加了一个PK:

ALTER TABLE a ADD CONSTRAINT p PRIMARY KEY (a);

将文件大小增加到 1,467,708 KB.

which increased the file size to 1,467,708 KB.

这表明最大值在 8000 万左右.

This suggests the maximum is somewhere around the 80 million mark.

这篇关于MS Access 数据库引擎表中的最大行数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！