Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How to set the size of single table by mysql

2025-01-29 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

This article is about how to set the size of a single table in MySQL. Xiao Bian thinks it is quite practical, so share it for everyone to make a reference. Let's follow the editor and have a look.

mysql Methods for setting the size of a single table: 1. Modification method when creating a table;2. Method for modifying an existing table. The code is [ALTER TABLE tbl_name MAX_ROWS= 10000000 AVG_ROW_LENGTH=15000].

mysql Set the size of a single table:

The MyISAM storage engine's single table size limit for MySQL databases is no longer determined by the MySQL database itself (the limit is expanded to 64pb), but by the file system on the host's OS.

Before mysql 5.0, myisam storage engine default table size was 4Gb, which can be viewed with one command:

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

Datafile length: The size of the current data file

Keyfile length: Size of the index file

Max datafile length: Maximum data file size

Max keyfile length: Maximum index file size

If you need MyISAM tables larger than 4 GB (and your operating system supports large files), use CREATE TABLE with AVG_ROW_LENGTH and MAX_ROWS options. Once the table is created, you can also change these options using ALTER TABLE to increase the maximum allowable size 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;

Two: Innodb storage engine is divided into two types, one is shared table space storage mode, and the other is exclusive table space storage mode.

1) When sharing tablespaces, all data of 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 files. A table can exist across multiple files, so its size limit is no longer a limit of file size, but its own limit.

As you can see from the official documentation of Innodb, the maximum limit of its table space is 64TB, which means that Innodb's single table limit is basically around 64TB. Of course, this size includes all indexes and other related data of this table.

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

Operating system size limit

win32 w/ FAT/FAT32 2GB/4GBwin32 w/ NTFS 2TB (possibly larger) Linux 2.2-Intel 32-bit 2GB (LFS: 4GB)Linux 2.4+ 4TB(ext3)Solaris 9/10 16 TBIntranet w/NSS filesystem 8TBMacOS X w/ HFS+ 2TB Thank you for reading! About mysql how to set the size of a single table to share here, I hope the above content can be of some help to everyone, so that everyone can learn more knowledge. If you think the article is good, you can share it so that more people can see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report