[其他 mysql限制]MySQL表有记录条数的最大限制吗？ - Chinaunix

1.4.4. How Big MySQL Tables Can Be
MySQL 3.22 had a 4GB (4 gigabyte) limit on table size. With the MyISAM storage engine in MySQL 3.23, the maximum table size was increased to 65536 terabytes (2567 – 1 bytes). With this larger allowed table size, the maximum effective table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits.

The InnoDB storage engine maintains InnoDB tables within a tablespace that can be created from several files. This allows a table to exceed the maximum individual file size. The tablespace can include raw disk partitions, which allows extremely large tables. The maximum tablespace size is 64TB.

The following table lists some examples of operating system file-size limits. This is only a rough guide and is not intended to be definitive. For the most up-to-date information, be sure to check the documentation specific to your operating system.

Operating System File-size Limit
Linux 2.2-Intel 32-bit 2GB (LFS: 4GB)
Linux 2.4+ (using ext3 filesystem) 4TB
Solaris 9/10 16TB
NetWare w/NSS filesystem 8TB
win32 w/ FAT/FAT32 2GB/4GB
win32 w/ NTFS 2TB (possibly larger)
MacOS X w/ HFS+ 2TB

On Linux 2.2, you can get MyISAM tables larger than 2GB in size by using the Large File Support (LFS) patch for the ext2 filesystem. On Linux 2.4, patches also exist for ReiserFS to get support for big files (up to 2TB). Most current Linux distributions are based on kernel 2.4 and include all the required LFS patches. With JFS and XFS, petabyte and larger files are possible on Linux. However, the maximum available file size still depends on several factors, one of them being the filesystem used to store MySQL tables.

For a detailed overview about LFS in Linux, have a look at Andreas Jaeger's Large File Support in Linux page at http://www.suse.de/~aj/linux_lfs.html.

Windows users please note: FAT and VFAT (FAT32) are not considered suitable for production use with MySQL. Use NTFS instead.

By default, MySQL creates MyISAM tables with an internal structure that allows a maximum size of about 4GB. You can check the maximum table size for a table with the SHOW TABLE STATUS statement or with myisamchk -dv tbl_name. See Section 13.5.4, “SHOW Syntax”.

If you need a MyISAM table that is larger than 4GB in size (and your operating system supports large files), the CREATE TABLE statement allows AVG_ROW_LENGTH and MAX_ROWS options. See Section 13.1.5, “CREATE TABLE Syntax”. You can also change these options with ALTER TABLE after the table has been created, to increase the table's maximum allowable size. See Section 13.1.2, “ALTER TABLE Syntax”.

Other ways to work around file-size limits for MyISAM tables are as follows:

If your large table is read-only, you can use myisampack to compress it. myisampack usually compresses a table by at least 50%, so you can have, in effect, much bigger tables. myisampack also can merge multiple tables into a single table. See Section 8.2, “myisampack — Generate Compressed, Read-Only MyISAM Tables”.

MySQL includes a MERGE library that allows you to handle a collection of MyISAM tables that have identical structure as a single MERGE table. See Section 14.3, “The MERGE Storage Engine”.

Storage Requirements for String Types

Column Type Storage Required
CHAR(M) M bytes, 0 <= M <= 255
VARCHAR(M) Prior to MySQL 5.0.3: L + 1 bytes, where L <= M and 0 <= M <= 255. MySQL 5.0.3 and later: L + 1 bytes, where L <= M and 0 <= M <= 256 or L + 2 bytes, where L <= M and 256 < M <= 65535 (see note below).
BINARY(M) M bytes, 0 <= M <= 255
VARBINARY(M) L+1 bytes, where L <= M and 0 <= M <= 255
TINYBLOB, TINYTEXT L+1 byte, where L < 28
BLOB, TEXT L+2 bytes, where L < 216
MEDIUMBLOB, MEDIUMTEXT L+3 bytes, where L < 224
LONGBLOB, LONGTEXT L+4 bytes, where L < 232
ENUM('value1','value2',...) 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
SET('value1','value2',...) 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)

VARCHAR and the BLOB and TEXT types are variable-length types. For each, the storage requirements depend on the actual length of column values (represented by L in the preceding table), rather than on the type's maximum possible size. For example, a VARCHAR(10) column can hold a string with a maximum length of 10. The actual storage required is the length of the string (L), plus 1 byte to record the length of the string. For the string 'abcd', L is 4 and the storage requirement is 5 bytes.

For the CHAR, VARCHAR, and TEXT types, the values L and M in the preceding table should be interpreted as number of characters, and lengths for these types in column specifications indicate the number of characters. For example, to store a TINYTEXT value requires L characters + 1 byte.

To calculate the number of bytes used to store a particular CHAR, VARCHAR, or TEXT column value, you will need to take into account the character set in use for that column. In particular, when using Unicode, you must keep in mind that not all Unicode characters use the same number of bytes. For a breakdown of the storage used for different categories of Unicode characters, see Section 10.5, “Unicode Support”. You can also obtain this information from the Maxlen column in the output of SHOW CHARSET.

Note: In MySQL 5.0.3 and later, the effective maximum length for a VARCHAR column is 65,532 characters.

As of MySQL 5.0.3, the NDBCLUSTER engine supports only fixed-width columns. This means that a VARCHAR column from a table in a MySQL Cluster will behave as follows:

If the size of the column is 256 characters or fewer, then the column requires one extra byte of storage per row.

If the size of the column is geater than 256 characters, then the column requires 2 bytes extra storage per row.

Note that the number of bytes required per character varies according to the character set used. For example, if a VARCHAR(100) column in a Cluster table uses the utf-8 character set, then each character requires 3 bytes storage. This means that each record in such a column takes up 100 × 3 + 1 = 301 bytes for storage, regardless of the length of the string actually stored in any given record. For a VARCHAR(1000) column in a table using the NDBCLUSTER storage engine with the utf-8 character set, each record will use 1000 × 3 + 2 = 3002 bytes storage; that is, the column is 1,000 characters wide, each character requires 3 bytes storage, and each record has a 2-byte overhead because 1,000 > 256.

The BLOB and TEXT types require 1, 2, 3, or 4 bytes to record the length of the column value, depending on the maximum possible length of the type. See Section 11.4.3, “The BLOB and TEXT Types”.

TEXT and BLOB columns are implemented differently in the NDB Cluster storage engine, wherein each record in a TEXT column is made up of two separate parts. One of these is of fixed size (256 bytes), and is actually stored in the original table. The other consists of any data in excess of 256 bytes, which stored in a hidden table. The records in this second table are always 2,000 bytes long. This means that the size of a TEXT column is 256 if size <= 256 (where size represents the size of the record); otherwise, the size is 256 + size + (2000 – (size – 256) % 2000).

The size of an ENUM object is determined by the number of different enumeration values. One byte is used for enumerations with up to 255 possible values. Two bytes are used for enumerations having between 256 and 65,535 possible values. See Section 11.4.4, “The ENUM Type”.

The size of a SET object is determined by the number of different set members. If the set size is N, the object occupies (N+7)/8 bytes, rounded up to 1, 2, 3, 4, or 8 bytes. A SET can have a maximum of 64 members. See Section 11.4.5, “The SET Type”.

原帖由 likuku 于 2006-2-21 13:09 发表
Mysql 相比商业数据库，支持的数据类型少。

假如非要使用在严肃场合或者数据量巨大，需要长久使用，且没多少资金买商业数据库，推荐使用PostgreSQL。

PostgreSQL历史，特性参考<<ostgreSQL 的昨天今 ...

原帖由 likuku 于 2006-2-21 13:09 发表
Mysql 相比商业数据库，支持的数据类型少。

假如非要使用在严肃场合或者数据量巨大，需要长久使用，且没多少资金买商业数据库，推荐使用PostgreSQL。

PostgreSQL历史，特性参考<<PostgreSQL 的昨天今 ...

原帖由 北京野狼 于 2006-2-21 21:16 发表

mysql支持的数据类型少一些, 但是绝对不存在百八十万数据就不能应付的问题 .

PostgreSQL,没有任何地方明显高于mysql,当然mysql也未必强过PostgreSQL。

原帖由 dancebear 于 2006-2-22 00:48 发表

错~~postgresql至少不会因为磁盘格式的问题影响表的限制~！

原帖由 likuku 于 2006-2-21 13:09 发表
Mysql 相比商业数据库，支持的数据类型少。

假如非要使用在严肃场合或者数据量巨大，需要长久使用，且没多少资金买商业数据库，推荐使用PostgreSQL。

PostgreSQL历史，特性参考<<ostgreSQL 的昨天今 ...

Server version 3.23.58-max-debug
Protocol version 10