MySQL数据库表中的最大记录数
MySQL数据库表的记录上限是多少？我想知道自动增量字段。如果我添加数百万条记录会发生什么？遇到这种情况该如何处理呢？ 谢谢！
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!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
整数的最大值与表中可以存储的最大行数关系不大。
确实，如果您使用 int 或 bigint 作为主键，则只能拥有与主键数据类型中唯一值的数量一样多的行，但不必将主键设置为整数，您可以将其设为 CHAR(100)。您还可以在多个列上声明主键。
除了行数之外，表大小还有其他限制。例如，您可以使用有文件大小限制的操作系统。或者，您可能拥有一个 300GB 硬盘，如果每行大小为 1KB，则只能存储 3 亿行。
数据库大小的限制确实很高。
https://dev.mysql.com/doc/refman/ en/innodb-restrictions-limitations.html
InnoDB 存储引擎每个表都有一个内部 6 字节行 ID，因此最大行数等于 248 或 281,474,976,710,656。
InnoDB 表空间的表大小也有 64 TB 的限制。其中包含多少行取决于每行的大小。
64TB 限制假定默认页面大小为 16KB。您可以增加页面大小，从而将表空间增加到 256TB。但我认为，在您将表增长到该大小之前，您会发现其他性能因素使这种做法变得不可取。
对于 MyISAM 表（尽管我不建议使用 MyISAM），旧版本的 MySQL默认情况下，每个表的限制为 232 行。在当前版本中，限制为 (232)2。请参阅 https://dev.mysql.com/doc/refman /en/myisam-storage-engine.html
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.
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.
The limits of database size is really high.
https://dev.mysql.com/doc/refman/en/innodb-restrictions-limitations.html
The InnoDB storage engine has an internal 6-byte row ID per table, so there are a maximum number of rows equal to 248 or 281,474,976,710,656.
An InnoDB tablespace also has a limit on table size of 64 terabytes. How many rows fits into this depends on the size of each row.
The 64TB limit assumes the default page size of 16KB. You can increase the page size, and therefore increase the tablespace up to 256TB. But I think you'd find other performance factors make this inadvisable long before you grow a table to that size.
For MyISAM tables (though I don't recommend using MyISAM), older versions of MySQL had a limit of 232 rows per table by default. In current versions, the limit is (232)2. See https://dev.mysql.com/doc/refman/en/myisam-storage-engine.html
mysql int 类型可以执行相当多的行： http://dev .mysql.com/doc/refman/5.0/en/numeric-types.html
无符号
int最大值为4,294,967,295无符号
bigint最大值为18,446,744,073,709,551,615mysql int types can do quite a few rows: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
unsigned
intlargest value is4,294,967,295unsigned
bigintlargest value is18,446,744,073,709,551,615我建议，永远不要删除数据。不要说如果表长度超过 1000 则截断表末尾。您的计划中需要有真正的业务逻辑，例如该用户处于非活动状态多久了。例如，如果超过 1 年，则将它们放在不同的表中。您可能会在维护脚本中每周或每月在缓慢的时间内发生这种情况。
当您在表中遇到很多行时，您应该开始对表进行分片或分区，并按年份将旧数据放入旧表中，例如 users_2011_jan、users_2011_feb 或使用月份的数字。然后更改您的编程以使用该模型。也许创建一个包含较少信息的新表，以将数据汇总到较少的列中，然后仅在需要更多信息（例如用户查看其个人资料时）时才引用较大的分区表。所有这些都应该非常仔细地考虑，这样将来重构的成本就不会太高。您还可以仅将始终访问您网站的用户放入一个表中，而将从未出现过的用户放入一组已存档的表中。
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.
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.
在 InnoDB 中，表大小限制为 64 TB，MySQL 行大小限制为 65,535，可以有 1,073,741,824 行。这将是利用最大行大小限制的最小记录数。但是，如果行大小较小，则可以添加更多记录。
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 .
根据 http://dev.mysql.com/ 中的可扩展性和限制部分doc/refman/5.6/en/features.html,
MySQL 对大型数据库的支持。他们使用 MySQL Server 以及包含 5000 万条记录的数据库。一些用户使用具有 200,000 个表和大约 5,000,000,000 行的 MySQL Server。
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.
行大小限制
MySQL 表的内部表示有最大行大小
限制为 65,535 字节，即使存储引擎能够
支持更大的行。 BLOB 和 TEXT 列仅贡献 9 到 12
字节数接近行大小限制，因为它们的内容已存储
与行的其余部分分开。
InnoDB表的最大行大小，适用于数据
本地存储在数据库页内，略小于半页。例如，对于默认的 16KB InnoDB 页大小，最大行大小略小于 8KB，这是由 innodb_page_size 配置选项定义的。 “InnoDB 表的限制”。
本地存储的可变长度列的数据量
存储在页外的内容因行格式而异。有关更多信息，请参阅
“InnoDB 行存储和行格式” 。
Row Size Limits
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”.
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”.
链接 http://dev.mysql.com/doc /refman/5.7/en/column-count-limit.html
行大小限制
给定表的最大行大小由几个因素决定：
MySQL 表的内部表示的最大行大小限制为 65,535字节，即使存储引擎能够支持更大的行。 BLOB 和 TEXT 列仅对行大小限制贡献 9 到 12 个字节，因为它们的内容与行的其余部分分开存储。
InnoDB 表的最大行大小（适用于数据库页中本地存储的数据）对于 4KB、8KB、16KB 和 32KB innodb_page_size 设置略小于半页。例如，对于默认的 16KB InnoDB 页大小，最大行大小略小于 8KB。对于 64KB 页面，最大行大小略小于 16KB。请参见第 15.8.8 节“InnoDB 表的限制”。
如果包含可变长度列的行超过了 InnoDB 最大行大小，InnoDB 会选择可变长度列进行外部页外存储，直到该行符合 InnoDB 行大小限制。对于存储在页外的可变长度列，本地存储的数据量因行格式而异。有关更多信息，请参见第 15.11 节 “InnoDB 行存储和行格式”。
不同的存储格式使用不同数量的页眉和页尾数据，这会影响行的可用存储量。
有关 InnoDB 行格式的信息，请参阅第 15.11 节“InnoDB 行存储和行格式”和第 15.8.3 节“InnoDB 表的物理行结构”。
有关 MyISAM 存储格式的信息，请参见第 16.2.3 节 “MyISAM 表存储格式”。
http://dev.mysql.com/doc/refman/ 5.7/en/innodb-restrictions.html
Link 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.
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”.
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”.
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”.
For information about MyISAM storage formats, see Section 16.2.3, “MyISAM Table Storage Formats”.
http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html
没有限制。它仅取决于您的可用内存和系统最大文件大小。但这并不意味着您不应该采取预防措施来解决数据库中的内存使用问题。始终创建一个脚本，该脚本可以删除不再使用的行，或者将行总数保留在特定数字（例如一千）内。
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.