mysql最大字段数量及 varchar类型总结
mysql最大字段数
一直对mysql最大字段数不明确有人说是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，即一个字符一个字节
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，即一个字符三个字节 65535-1-2 结果除以3 ==21844
-1表示第一个字节不存数据，-2表示两个字节存放varchar的长度，除以3是utf8字符特性，一个字符三个字节。
varchar 字段是将实际内容单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度（长度超过255时需要2个字节），因此最大长度不能超过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:在任何编码下, 不管汉字还是数字或者是字母都算是一个字符
length: 是计算字段的长度, utf8编码下,一个汉字是算三个字符,一个数字或字母算一个字符。其他编码下,一个汉字算两个字符, 一个数字或字母算一个字符。 CHARACTER_LENGTH(str) CHARACTER_LENGTH()是CHAR_LENGTH()的同义词。 BIT_LENGTH(str) 返回2进制长度
MySQL数据类型（留作备忘）
|
类 型 |
大 小 |
描 述 |
|
CAHR(Length) |
Length字节 |
定长字段，长度为0~255个字符 |
|
VARCHAR(Length) |
String长度+1字节或String长度+2字节 |
变长字段，长度为0~65 535个字符 |
|
TINYTEXT |
String长度+1字节 |
字符串，最大长度为255个字符 |
|
TEXT |
String长度+2字节 |
字符串，最大长度为65 535个字符 |
|
MEDIUMINT |
String长度+3字节 |
字符串，最大长度为16 777 215个字符 |
|
LONGTEXT |
String长度+4字节 |
字符串，最大长度为4 294 967 295个字符 |
|
TINYINT(Length) |
1字节 |
范围：-128~127，或者0~255（无符号） |
|
SMALLINT(Length) |
2字节 |
范围：-32 768~32 767，或者0~65 535（无符号） |
|
MEDIUMINT(Length) |
3字节 |
范围：-8 388 608~8 388 607，或者0~16 777 215（无符号） |
|
INT(Length) |
4字节 |
范围：-2 147 483 648~2 147 483 647，或者0~4 294 967 295（无符号） |
|
BIGINT(Length) |
8字节 |
范围：-9 223 372 036 854 775 808~9 223 372 036 854 775 807，或者0~18 446 744 073 709 551 615（无符号） |
|
FLOAT(Length, Decimals) |
4字节 |
具有浮动小数点的较小的数 |
|
DOUBLE(Length, Decimals) |
8字节 |
具有浮动小数点的较大的数 |
|
DECIMAL(Length, Decimals) |
Length+1字节或Length+2字节 |
存储为字符串的DOUBLE，允许固定的小数点 |
|
DATE |
3字节 |
采用YYYY-MM-DD格式 |
|
DATETIME |
8字节 |
采用YYYY-MM-DD HH:MM:SS格式 |
|
TIMESTAMP |
4字节 |
采用YYYYMMDDHHMMSS格式；可接受的范围终止于2037年 |
|
TIME |
3字节 |
采用HH:MM:SS格式 |
|
ENUM |
1或2字节 |
Enumeration(枚举)的简写，这意味着每一列都可以具有多个可能的值之一 |
|
SET |
1、2、3、4或8字节 |
与ENUM一样，只不过每一列都可以具有多个可能的值 |