看到itpub 論壇上有關於mysql資料庫表中欄位個數的討論，討論mysql欄位個數限制在2559個！

自己做了測試，個數對儲存引擎不同而不同！innodb 建立到第1001個時會報 Can't create table 'yang.#sql-c6d_421' (errno: 139)的錯誤！

下面是測試過程~

mysql> select version();

+------------+

| version() |

+------------+

| 5.5.18-log |

+------------+

1 row in set (0.01 sec)

mysql> use test;

Database changed

情景一：innodb 儲存引擎的表！

mysql> show create table yql2 \G;

*************************** 1. row ***************************

Table: yql2

Create Table: CREATE TABLE `yql2` (

`id` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> EXIT

Bye

使用指令碼如下：

[root@rac3 sh]# cat check_mysql_tab_col_num.sh

#!/bin/sh

x=1

while [ $x -le 3000 ]

do

mysql -uroot -Dtest << EOF >> /root/sh/add_col_log_innodb.log

alter table yql2 add column f$x char(1);

EOF

x=`expr $x + 1`

echo "第 $x 個欄位"

done

~

[root@rac3 sh]# sh mysql_tab_col_num.sh

第 2 個欄位

第 3 個欄位

第 4 個欄位

...

第 999 個欄位

第 1000 個欄位

ERROR 1005 (HY000) at line 1: Can't create table 'yang.#sql-c6d_421' (errno: 139)

執行到第 1000個欄位出錯！Can't create table 'yang.#sql-c6d_421' (errno: 139)

注意：錯誤不是“ERROR 1117 (HY000) at line 1: Too many columns”！

從可以看到。

#A table cannot contain more than 1000 columns.
#The internal maximum key length is 3500 bytes, but MySQL itself restricts this to 1024 bytes.
#The maximum row length, except for VARCHAR, BLOB and TEXT columns, is slightly less than half of a database page. That is, the maximum row length is about 8000 bytes.LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including also BLOB and TEXT columns, must be less than 4GB. InnoDB stores the first 768 bytes of a VARCHAR, BLOB, or TEXT column in the row, and the rest into separate pages.
#Although InnoDB supports row sizes larger than 65535 internally, you cannot define a row containing VARCHAR columns with a combined size larger than 65535:

二 myisam 儲存引擎的表

mysql> show create table tab2 \G;

*************************** 1. row ***************************

Table: tab2

Create Table: CREATE TABLE `tab1` (

`tid` int(10) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql> desc tab2;

+-------+---------+------+-----+---------+----------------+

+-------+---------+------+-----+---------+----------------+

| tid | int(10) | NO | PRI | NULL | |

+-------+---------+------+-----+---------+----------------+

1 rows in set (0.00 sec)

[root@rac3 sh]# vim check_mysql_tab_col_num.sh

#!/bin/sh

x=1

while [ $x -le 3000 ]

do

mysql -uroot -Dtest << EOF >> /root/sh/add_col_log_myisam.log

alter table tab2 add column f$x char(1);

EOF

x=`expr $x + 1`

echo "第 $x 個欄位"

done

[root@rac3 sh]# sh check_mysql_tab_col_num.sh

第 2 個欄位

第 3 個欄位

......

第 2598 個欄位

第 2599 個欄位

ERROR 1117 (HY000) at line 1: Too many columns

檢視錶結構：

mysql> desc yql19;

+-------+---------+------+-----+---------+-------+

+-------+---------+------+-----+---------+-------+

| tid | int(10) | NO | | NULL | |

| f1 | char(1) | YES | | NULL | |

| f2 | char(1) | YES | | NULL | |

| f3 | char(1) | YES | | NULL | |

| f4 | char(1) | YES | | NULL | |

| f5 | char(1) | YES | | NULL | |

.....

| f2596 | char(1) | YES | | NULL | |

| f2597 | char(1) | YES | | NULL | |

| f2598 | char(1) | YES | | NULL | |

+-------+---------+------+-----+---------+-------+

2599 rows in set (0.08 sec)

共2599 個欄位！

可以參考關於innodb 表物理上儲存知識的另一篇文章：

【MySql】mysql 欄位個數的限制

相關文章