MySQL 5
MyISAM 最大可支持 256TB
myisam_data_pointer_size 這個值 (mysql 4 無此)
可以設 2 ~ 7
default 6 (256TB)
若是設為 4 就是 4G
查詢：
mysql> show variables like 'myisam_data%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| myisam_data_pointer_size | 6 |
+--------------------------+-------+
1 row in set (0.00 sec)
ref:
http://dev.mysql.com/doc/refman/5.0/en/table-size-limit.html
測試
MySQL 4 (32bits)
mysql> describe tbl_test2;
+-------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+----------------+
| id | int(4) unsigned | | PRI | NULL | auto_increment |
| txt1 | varchar(150) | | | | |
| note | varchar(100) | | | | |
+-------+-----------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> show table status like 'tbl_test2'\G
*************************** 1. row ***************************
Name: tbl_test2
Type: MyISAM
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 4294967295 最大 4G 的表格空間
Index_length: 1024
Data_free: 0
Auto_increment: 4
Create_time: 2013-05-14 07:09:06
Update_time: 2013-05-14 07:09:06
Check_time: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
CREATE TABLE `tbl_test3` (
`id` int(4) unsigned NOT NULL AUTO_INCREMENT,
`txt1` varchar(150) NOT NULL DEFAULT '',
`note` varchar(100) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 MAX_ROWS=500000000 AVG_ROW_LENGTH=200;
mysql> describe tbl_test3;
+-------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+----------------+
| id | int(4) unsigned | | PRI | NULL | auto_increment |
| txt1 | varchar(150) | | | | |
| note | varchar(100) | | | | |
+-------+-----------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> show table status like 'tbl_test3'\G
*************************** 1. row ***************************
Name: tbl_test3
Type: MyISAM
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 1099511627775 有 1T 的表格空間
Index_length: 1024
Data_free: 0
Auto_increment: 4
Create_time: 2013-05-14 07:10:22
Update_time: 2013-05-14 07:10:22
Check_time: NULL
Create_options: max_rows=500000000 avg_row_length=200
Comment:
1 row in set (0.00 sec)
MySQL 5 (64bits)
mysql> describe tbl_test2;
+-------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+----------------+
| id | int(4) unsigned | NO | PRI | NULL | auto_increment |
| txt1 | varchar(150) | NO | | | |
| note | varchar(100) | NO | | | |
+-------+-----------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> show table status like 'tbl_test2'\G
*************************** 1. row ***************************
Name: tbl_test2
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 281474976710655 有 256T 的表格空間可用
Index_length: 1024
Data_free: 0
Auto_increment: 4
Create_time: 2014-09-30 12:28:16
Update_time: 2014-09-30 12:35:27
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
如果加了 MAX_ROWS= xxx ...：
CREATE TABLE `tbl_test3` (
`id` int(4) unsigned NOT NULL AUTO_INCREMENT,
`txt1` varchar(150) NOT NULL DEFAULT '',
`note` varchar(100) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 MAX_ROWS=500000000 AVG_ROW_LENGTH=200;
mysql> describe tbl_test3;
+-------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+----------------+
| id | int(4) unsigned | NO | PRI | NULL | auto_increment |
| txt1 | varchar(150) | NO | | | |
| note | varchar(100) | NO | | | |
+-------+-----------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> show table status like 'tbl_test3'\G
*************************** 1. row ***************************
Name: tbl_test3
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 1099511627775 只剩最大 1T空間可用
Index_length: 1024
Data_free: 0
Auto_increment: 4
Create_time: 2014-09-30 12:39:50
Update_time: 2014-09-30 12:39:50
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: max_rows=500000000 avg_row_length=200
Comment:
1 row in set (0.02 sec)
