mysql最大字段數(shù)

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

還有人說

Max columns per row 4096
InnoDB is limited to 1000 columns  

實(shí)踐是檢驗(yàn)真理的唯一方法

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，即一個(gè)字符一個(gè)字節(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，即一個(gè)字符三個(gè)字節(jié) 65535-1-2 結(jié)果除以3 ==21844

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

varchar 字段是將實(shí)際內(nèi)容單獨(dú)存儲(chǔ)在聚簇索引之外，內(nèi)容開頭用1到2個(gè)字節(jié)表示實(shí)際長度（長度超過255時(shí)需要2個(gè)字節(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ù)字或者是字母都算是一個(gè)字符

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

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