How to set the size of a single table in mysql

Mysql method to set the size of a single table: 1. Modification method when creating a table; 2. Modification method of an existing table, the code is [ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=15000].

Mysql method to set the size of a single table:

1. Single table size limit of the MyISAM storage engine of the MySQL database It is no longer determined by the MySQL database itself (the limit is expanded to 64pb), but by the file system on the OS of the host.

Before the mysql5.0 version, the default table size of the myisam storage engine was 4Gb. You can use the following command to view it:

[root@robert test]# cd /data/mysql/mysql_3306/data/test
[root@robert test]# myisamchk -dv t2
MyISAM file: t2
Record format: Fixed length
Character set: latin1_swedish_ci (8)
File-version: 1
Creation time: 2014-12-29 14:13:17
Status: checked,analyzed,optimized keys,sorted index pages
Data records: 0 Deleted blocks: 0
Datafile parts: 0 Deleted data: 0
Datafile pointer (bytes): 6 Keyfile pointer (bytes): 3
Datafile length: 0 Keyfile length: 1024
Max datafile length: 3096224743817214 Max keyfile length: 17179868159
Recordlength: 11
table description:
Key Start Len Index Type Rec/key Root Blocksize

• Datafile length: the size of the current data file

• Keyfile length: The size of the index file

• Max datafile length: The size of the maximum data file

• Max keyfile length: The size of the maximum index file

If you need to use a MyISAM table larger than 4GB (and your operating system supports large files), you can use the AVG_ROW_LENGTH and MAX_ROWS options. CREATE TABLE statement. After the table is created, you can also use ALTER TABLE to change these options to increase the maximum allowed capacity of the table.

How to modify when creating a table

CREATE TABLE tbl_name (
a INTEGER NOT NULL PRIMARY KEY,
b CHAR(18) NOT NULL
) MAX_ROWS = 1000000000 AVG_ROW_LENGTH = 32;

How to modify an existing table

ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=15000;

2: Innodb storage engine points There are two types, one is the shared table space storage method, and the other is the exclusive table space storage method.

1) When sharing table space storage, all data in Innodb is stored in a separate table space (but each table has a .frm table structure file), and this table space can be composed of many Composed of files, a table can exist across multiple files, so its size limit is no longer a limit on the file size, but a limit on itself.

As can be seen from Innodb’s official documentation, the maximum limit of its table space is 64TB. In other words, Innodb’s single table limit is basically around 64TB. Of course, this size includes this table. All indexes and other related data.

2) When using exclusive table space to store Innodb tables, the data of each table is stored in a separate file. At this time, the single table limit becomes the size limit of the file system.

Operating system size limit

win32 w/ FAT/FAT32 2GB/4GB
win32 w/ NTFS 2TB（可能更大）
Linux 2.2-Intel 32-bit 2GB (LFS: 4GB)
Linux 2.4+ 4TB(ext3)
Solaris 9/10 16TB
NetWare w/NSS filesystem 8TB
MacOS X w/ HFS+ 2TB

More related free learning recommendations: mysql tutorial (video)

The above is the detailed content of How to set the size of a single table in mysql. For more information, please follow other related articles on the PHP Chinese website!