【MySql】mysql 字段個數的限制

自己做了測試，個數對存儲引擎不同而不同！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 > /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) 

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

#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:

<b>二 myisam 存儲引擎的表</b>

mysql&gt; show create table tab2 \G;

       Table: tab2

Create Table: CREATE TABLE `tab1` (

  `tid` int(10) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8

mysql&gt; desc tab2;

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

| Field | Type    | Null | Key | Default | Extra          |

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

1 rows in set (0.00 sec)

[root@rac3 sh]# vim check_mysql_tab_col_num.sh 

mysql -uroot  -Dtest &gt; /root/sh/add_col_log_myisam.log

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

[root@rac3 sh]# sh check_mysql_tab_col_num.sh

......

第 2598 個字段

第 2599 個字段

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

檢視表結構：

mysql&gt; desc yql19;

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

| Field | Type    | Null | Key | Default | Extra |

| 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 個字段！