設為首頁

收藏本站

導覽首頁 | 新登場    ◇聯盟溫泉 | 民宿 | 人力銀行 | 女性 |

類型:mysql_article

把MySQL容量由4G擴大為1TB
使用 SHOW TABLE STATUS 這個指令檢查了一下資料庫,發現 MySQL 將每個資料表的容量上限都配置為 4294967295 (4G)

而在官方網站上找到的資料似乎說,我們也可以藉由 ALTER TABLE ..... 之類的語法來變更資料表的最大容量,相關敘述如下:

If you need a MyISAM table that is larger than 4GB and your operating system supports large files, the CREATE TABLE statement supports AVG_ROW_LENGTH and MAX_ROWS options. See Section 13.1.7, “CREATE TABLE Syntax”. You can also change these options with ALTER TABLE to increase a table's maximum allowable size after the table has been created. See Section 13.1.2, “ALTER TABLE Syntax”.

很難懂,但看看這個範例就會懂。
可以開測試用的咨暸表先踹一下。

mysql> show table status like 'weather' \G
*************************** 1. row ***************************
Name: weather
Type: MyISAM
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 4294967295
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2003-03-03 00:43:43
Update_time: 2003-03-03 00:43:43
Check_time: 2003-06-14 15:11:21
Create_options:
Comment:
1 row in set (0.00 sec)

看到 Max_data_length: 4294967295 ( 4GB ) 是 default 值

mysql> alter table weather max_rows = 200000000000 avg_row_length = 50;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show table status like 'weather' \G
*************************** 1. row ***************************
Name: weather
Type: MyISAM
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 1099511627775
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2003-06-17 13:12:49
Update_time: 2003-06-17 13:12:49
Check_time: NULL
Create_options: max_rows=4294967295 avg_row_length=50
Comment:
1 row in set (0.00 sec)

做完上面的 alter table 之後,可以看到
Create_options: max_rows=4294967295 avg_row_length=50
Max_data_length: 1099511627775 ( 1TB ),也就是 200000000000 * 50
這樣,這個 table 就可以使用到 1TB 的大小囉!



主旨:

內容:




104休閒信箱 2.3.0 © 104mm.com 2001 - 2017. 您尚未登錄
Page generated in 0.00808001 seconds with 3 Queries