自己做了测试，个数对存储引擎不同而不同！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)
注意：错误不是“ERROR 1117 (HY000) at line 1: Too many columns”！
从Innodb的限制可以看到。
#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;
+-------+---------+------+-----+---------+----------------+
| 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
#!/bin/sh
x=1
while [ $x -le 3000 ]
do
mysql -uroot -Dtest > /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;
+-------+---------+------+-----+---------+-------+
| 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 个字段！
