mysql最大字段數(shù)

一直對mysql最大字段數(shù)不明確有人說是1024

還有人說

Max columns per row 4096
InnoDB is limited to 1000 columns  

實踐是檢驗真理的唯一方法

mysql> use test;

mysql> create table t0008(id int) engine=innodb DEFAULT CHARSET=latin1;

[root@localhost ~]# vim add.sh 
#/bin/bash
 num=2
while((num<2000))
do
 echo $num
 mysql -p123456 -D test -e "alter table t0008 add column(col$num char(1))"
num=$(($num+1))
done

[root@localhost ~]# ./add.sh

Warning: Using a password on the command line interface can be insecure.
1017
Warning: Using a password on the command line interface can be insecure.
1018
Warning: Using a password on the command line interface can be insecure.
ERROR 1117 (HY000) at line 1: Too many columns

mysql> desc t0008;
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| id      | int(11)    | YES  |     | NULL    |       |
| col2    | varchar(1) | YES  |     | NULL    |       |
| col3    | varchar(1) | YES  |     | NULL    |       |

...............................................................................

...............................................................................

| col1014 | varchar(1) | YES  |     | NULL    |       |
| col1015 | varchar(1) | YES  |     | NULL    |       |
| col1016 | varchar(1) | YES  |     | NULL    |       |
| col1017 | varchar(1) | YES  |     | NULL    |       |
+---------+------------+------+-----+---------+-------+
1017 rows in set (0.01 sec)

mysql innodb引擎支持最大字段上線為1017

mysql> create table t0011(col1 char(1)) engine=myisam DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

[root@localhost ~]# ./add.sh 

Warning: Using a password on the command line interface can be insecure.
2410
Warning: Using a password on the command line interface can be insecure.
2411
Warning: Using a password on the command line interface can be insecure.
ERROR 1117 (HY000) at line 1: Too many columns
2412
Warning: Using a password on the command line interface can be insecure.
ERROR 1117 (HY000) at line 1: Too many columns

mysql> desc t0011;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| col1    | char(1) | YES  |     | NULL    |       |
| col2    | char(1) | YES  |     | NULL    |       |
| col3    | char(1) | YES  |     | NULL    |       |

.........................................................................

........................................................................

| col2408 | char(1) | YES  |     | NULL    |       |
| col2409 | char(1) | YES  |     | NULL    |       |
| col2410 | char(1) | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
2410 rows in set (0.04 sec)

mysql myisam引擎最大字段上限為2410   

-----------------------------------------------------------------------------------------------------------------

varchar字段的長度

mysql> create table t0008(col1 varchar(65535))charset=latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> create table t0008(col1 varchar(65534))charset=latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> create table t0008(col1 varchar(65533))charset=latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> create table t0008(col1 varchar(65532))charset=latin1;
Query OK, 0 rows affected (0.02 sec)

latin1字符集下的表varchar上限為65532，即一個字符一個字節(jié)

mysql> create table t0009(col1 varchar(65533))charset=utf8;
ERROR 1074 (42000): Column length too big for column 'col1' (max = 21845); use BLOB or TEXT instead
mysql> create table t0009(col1 varchar(21845))charset=utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> create table t0009(col1 varchar(21844))charset=utf8;
Query OK, 0 rows affected (0.00 sec)

utf8字符集下的表varchar上限為21844，即一個字符三個字節(jié) 65535-1-2 結果除以3 ==21844

 -1表示第一個字節(jié)不存數(shù)據(jù)，-2表示兩個字節(jié)存放varchar的長度，除以3是utf8字符特性，一個字符三個字節(jié)。

varchar 字段是將實際內容單獨存儲在聚簇索引之外，內容開頭用1到2個字節(jié)表示實際長度（長度超過255時需要2個字節(jié)），因此最大長度不能超過65535即 2的16次方（0-65535）

mysql> create table t0012(id int,name char(20),col3 varchar(N))chaset=utf8;

N的值為：(65535-1-2-4-20*3)/3=21822

mysql> create table t0012(id int,name char(20),col3 varchar(N))charset=latin1;

N的值為：65535-1-2-4-20=65508

char_length:在任何編碼下, 不管漢字還是數(shù)字或者是字母都算是一個字符

length: 是計算字段的長度, utf8編碼下,一個漢字是算三個字符,一個數(shù)字或字母算一個字符。其他編碼下,一個漢字算兩個字符, 一個數(shù)字或字母算一個字符。 CHARACTER_LENGTH(str) CHARACTER_LENGTH()是CHAR_LENGTH()的同義詞。 BIT_LENGTH(str) 返回2進制長度

MySQL數(shù)據(jù)類型（留作備忘）