MySQL 数据库表中的最大记录数

Maximum number of records in a MySQL database table

提问by xpepermint

What is the upper limit of records for MySQL database table. I'm wondering about autoincrement field. What would happen if I add milions of records? How to handle this kind of situations? Thx!

MySQL数据库表的记录上限是多少。我想知道自动增量字段。如果我添加数百万条记录会发生什么？遇到这种情况怎么办？谢谢！

采纳答案by KM.

mysql int types can do quite a few rows: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

mysql int 类型可以做很多行：http: //dev.mysql.com/doc/refman/5.0/en/numeric-types.html

unsigned intlargest value is 4,294,967,295
unsigned bigintlargest value is 18,446,744,073,709,551,615

无符号int最大值是4,294,967,295
无符号bigint最大值18,446,744,073,709,551,615

回答by Bill Karwin

The greatest value of an integer has little to do with the maximum number of rows you can store in a table.

整数的最大值与可以存储在表中的最大行数几乎没有关系。

It's true that if you use an int or bigint as your primary key, you can only have as many rows as the number of unique values in the data type of your primary key, but you don't have to make your primary key an integer, you could make it a CHAR(100). You could also declare the primary key over more than one column.

确实，如果您使用 int 或 bigint 作为主键，则行数只能与主键数据类型中唯一值的数量一样多，但不必将主键设为整数，您可以将其设为 CHAR(100)。您还可以在不止一列上声明主键。

There are other constraints on table size besides number of rows. For instance you could use an operating system that has a file size limitation. Or you could have a 300GB hard drive that can store only 300 million rows if each row is 1KB in size.

除了行数之外，表大小还有其他限制。例如，您可以使用具有文件大小限制的操作系统。或者，如果每行的大小为 1KB，您可以拥有一个 300GB 的硬盘驱动器，它只能存储 3 亿行。

The limits of database size is really high:

数据库大小的限制真的很高：

http://dev.mysql.com/doc/refman/5.1/en/source-configuration-options.html

http://dev.mysql.com/doc/refman/5.1/en/source-configuration-options.html

The MyISAM storage engine supports 2³²rows per table, but you can build MySQL with the --with-big-tablesoption to make it support up to 2⁶⁴rows per table.

MyISAM 存储引擎支持每个表2 ³²行，但您可以使用--with-big-tables选项构建 MySQL，使其最多支持每个表2 ⁶⁴行。

http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html

http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html

The InnoDB storage engine doesn't seem to have a limit on the number of rows, but it has a limit on table size of 64 terabytes. How many rows fits into this depends on the size of each row.

InnoDB 存储引擎似乎对行数没有限制，但它对表大小有 64 TB 的限制。适合多少行取决于每行的大小。

回答by PHPGuru

I suggest, never delete data. Don't say if the tables is longer than 1000 truncate the end of the table. There needs to be real business logic in your plan like how long has this user been inactive. For example, if it is longer than 1 year then put them in a different table. You would have this happen weekly or monthly in a maintenance script in the middle of a slow time.

我建议，永远不要删除数据。不要说如果表的长度超过 1000 则截断表的末尾。您的计划中需要有真正的业务逻辑，例如此用户不活动的时间。例如，如果超过 1 年，则将它们放在不同的表中。您会在缓慢的时间中每周或每月在维护脚本中发生这种情况。

When you run into to many rows in your table then you should start sharding the tables or partitioning and put old data in old tables by year such as users_2011_jan, users_2011_feb or use numbers for the month. Then change your programming to work with this model. Maybe make a new table with less information to summarize the data in less columns and then only refer to the bigger partitioned tables when you need more information such as when the user is viewing their profile. All of this should be considered very carefully so in the future it isn't too expensive to re-factor. You could also put only the users which comes to your site all the time in one table and the users that never come in an archived set of tables.

当您在表中遇到许多行时，您应该开始对表进行分片或分区，并将旧数据按年份放入旧表中，例如 users_2011_jan、users_2011_feb 或使用月份的数字。然后更改您的编程以使用此模型。也许创建一个包含较少信息的新表来汇总较少列中的数据，然后仅在您需要更多信息（例如用户查看其个人资料时）时才引用更大的分区表。所有这些都应该非常仔细地考虑，以便将来重新考虑因素不会太昂贵。您还可以只将一直访问您站点的用户放在一个表中，而将从未访问过的用户放在一组归档的表中。

回答by Xylo

In InnoDB, with a limit on table size of 64 terabytes and a MySQL row-size limit of 65,535 there can be 1,073,741,824 rows. That would be minimum number of records utilizing maximum row-size limit. However, more records can be added if the row size is smaller .

在 InnoDB 中，表大小限制为 64 TB，MySQL 行大小限制为 65,535，可以有 1,073,741,824 行。这将是使用最大行大小限制的最小记录数。但是，如果行大小较小，则可以添加更多记录。

回答by Data

According to Scalability and Limits section in http://dev.mysql.com/doc/refman/5.6/en/features.html, MySQL support for large databases. They use MySQL Server with databases that contain 50 million records. Some users use MySQL Server with 200,000 tables and about 5,000,000,000 rows.

根据http://dev.mysql.com/doc/refman/5.6/en/features.html 中的可扩展性和限制部分，MySQL 支持大型数据库。他们将 MySQL Server 与包含 5000 万条记录的数据库一起使用。一些用户使用 MySQL Server 的 200,000 个表和大约 5,000,000,000 行。

回答by Saurabh Chandra Patel

Row Size Limits

行大小限制

The maximum row size for a given table is determined by several factors:

The maximum row size for a given table is determined by several factors:

• The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, even if the storage engine is capable of supporting larger rows. BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row.

• The maximum row size for an InnoDB table, which applies to data stored locally within a database page, is slightly less than half a page. For example, the maximum row size is slightly less than 8KB for the default 16KB InnoDB page size, which is defined by the innodb_page_size configuration option. “Limits on InnoDB Tables”.

• If a row containing variable-length columns exceeds the InnoDB maximum row size, InnoDB selects variable-length columns for external off-page storage until the row fits within the InnoDB row size limit. The amount of data stored locally for variable-length columns that are stored off-page differs by row format. For more information, see “InnoDB Row Storage and Row Formats”.
• Different storage formats use different amounts of page header and trailer data, which affects the amount of storage available for rows.

• MySQL 表的内部表示具有 65,535 字节的最大行大小限制，即使存储引擎能够支持更大的行。BLOB 和 TEXT 列仅对行大小限制贡献 9 到 12 个字节，因为它们的内容与行的其余部分分开存储。

• InnoDB 表的最大行大小（适用于本地存储在数据库页中的数据）略小于半页。例如，对于默认的 16KB InnoDB 页大小，最大行大小略小于 8KB，这是由 innodb_page_size 配置选项定义的。“ InnoDB 表的限制”。

• 如果包含可变长度列的行超过 InnoDB 最大行大小，则 InnoDB 为外部页外存储选择可变长度列，直到该行适合 InnoDB 行大小限制。对于页外存储的可变长度列，本地存储的数据量因行格式而异。有关更多信息，请参阅“ InnoDB 行存储和行格式”。
• 不同的存储格式使用不同数量的页头和尾部数据，这会影响行可用的存储量。

回答by Md Nazrul Islam

Link http://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html

链接http://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html

Row Size Limits

行大小限制

The maximum row size for a given table is determined by several factors:

给定表的最大行大小由几个因素决定：

The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, even if the storage engine is capable of supporting larger rows. BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row.

MySQL 表的内部表示具有 65,535 字节的最大行大小限制，即使存储引擎能够支持更大的行。BLOB 和 TEXT 列仅对行大小限制贡献 9 到 12 个字节，因为它们的内容与行的其余部分分开存储。

The maximum row size for an InnoDB table, which applies to data stored locally within a database page, is slightly less than half a page for 4KB, 8KB, 16KB, and 32KB innodb_page_size settings. For example, the maximum row size is slightly less than 8KB for the default 16KB InnoDB page size. For 64KB pages, the maximum row size is slightly less than 16KB. See Section 15.8.8, “Limits on InnoDB Tables”.

InnoDB 表的最大行大小适用于本地存储在数据库页面中的数据，对于 4KB、8KB、16KB 和 32KB innodb_page_size 设置，其最大行大小略小于页面的一半。例如，对于默认的 16KB InnoDB 页大小，最大行大小略小于 8KB。对于 64KB 页面，最大行大小略小于 16KB。请参阅第 15.8.8 节，“InnoDB 表的限制”。

If a row containing variable-length columns exceeds the InnoDB maximum row size, InnoDB selects variable-length columns for external off-page storage until the row fits within the InnoDB row size limit. The amount of data stored locally for variable-length columns that are stored off-page differs by row format. For more information, see Section 15.11, “InnoDB Row Storage and Row Formats”.

如果包含可变长度列的行超过 InnoDB 最大行大小，则 InnoDB 为外部页外存储选择可变长度列，直到该行适合 InnoDB 行大小限制。存储在页外的可变长度列的本地存储数据量因行格式而异。有关更多信息，请参阅第 15.11 节，“InnoDB 行存储和行格式”。

Different storage formats use different amounts of page header and trailer data, which affects the amount of storage available for rows.

不同的存储格式使用不同数量的页头和尾部数据，这会影响行可用的存储量。

For information about InnoDB row formats, see Section 15.11, “InnoDB Row Storage and Row Formats”, and Section 15.8.3, “Physical Row Structure of InnoDB Tables”.

有关 InnoDB 行格式的信息，请参阅第 15.11 节“InnoDB 行存储和行格式”和第 15.8.3 节“InnoDB 表的物理行结构”。

For information about MyISAM storage formats, see Section 16.2.3, “MyISAM Table Storage Formats”.

有关 MyISAM 存储格式的信息，请参阅第 16.2.3 节，“MyISAM 表存储格式”。

http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

回答by 9jamkt

There is no limit. It only depends on your free memory and system maximum file size. But that doesn't mean you shouldn't take precautionary measure in tackling memory usage in your database. Always create a script that can delete rows that are out of use or that will keep total no of rows within a particular figure, say a thousand.

没有限制。它仅取决于您的可用内存和系统最大文件大小。但这并不意味着您不应该采取预防措施来解决数据库中的内存使用问题。始终创建一个脚本，该脚本可以删除不使用的行或将保留特定数字中的总行数，例如一千。