查询表结构 类型
select COLUMN_NAME,COLUMN_TYPE from information_schema.columns where table_name ='t_advice_core' order by `ORDINAL_POSITION` SHOW VARIABLES LIKE 'character_set%'
在mysql建表sql里,我们经常会有定义字符串类型的需求。
CREATE TABLE `user` ( `name` varchar(100) NOT NULL DEFAULT '' COMMENT '名字') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;
比方说user表里的名字,就是个字符串。mysql里有两个类型比较适合这个场景。
char和varchar。
声明它们都需要在字段边上加个数组,比如char(100)和varchar(100),这个100是指当前字段能放的最大字符数。
![](https://pics2.baidu.com/feed/77094b36acaf2eddd78f68046f970ce538019370.jpeg@f_auto?token=3e697191c1d84fac47e2b89a09ee4903)
那问题来了,声明varchar字段时,它的最大长度是多少呢?
相信大家应该听说过varchar字段的最大长度是65535吧。
没听过也没关系,你现在听到了。
但实际上是这样吗?我们慢慢分析。。。
虽然通常认为VARCHAR字段的最大长度是65535,但实际上这个说法是不完全准确的。这是因为,在MySQL中,VARCHAR字段的最大长度取决于多个因素,包括字符集、行格式和存储引擎等。下面我们将对这些因素进行详细说明。
![](https://pic.rmb.bdstatic.com/bjh/news/113861825555445f1e74af4da14b976d.gif)
一、行格式的影响
MySQL 中的行格式是指在存储数据时,将数据按照一定的方式组织成行的格式。常见的行格式有固定长度行格式(Fixed-length format)和动态行格式(Dynamic format)。
在固定长度行格式中,每一行都有固定的长度,每个字段的长度都是固定的。而在动态行格式中,每一行的长度都是根据数据的实际长度而定,字段长度也是根据数据的实际长度来动态调整的。因此,使用不同的行格式,对于 varchar 字段的最大长度有不同的影响。
在固定长度行格式中,varchar 类型字段的最大长度确实是 65535。因为在固定长度行格式中,每个字段都占用固定的空间,varchar 类型字段的长度也是固定的,所以其最大长度是受到限制的。
而在动态行格式中,varchar 类型字段的最大长度是根据实际数据长度而定的,不受到固定长度的限制。因此,在动态行格式中,varchar 类型字段的最大长度可以超过 65535。
示例:
假设我们有一个包含一个 varchar 类型字段的表,行格式为 Dynamic,字符集为 utf8mb4。我们插入一个长度为 80000 的字符串:
CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `value` varchar(80000) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;INSERT INTO `test` (`value`) VALUES (REPEAT('a', 80000));
在这个例子中,我们插入了一个长度为 80000 的字符串,而在 Dynamic 行格式下,这个字符串可以被正确地存储,没有出现错误。
二、字符集的影响
MySQL 支持多种字符集,包括 ASCII、ISO-8859、utf8mb4 等。不同的字符集对于 varchar 类型字段的最大长度也有不同的影响。
在 ASCII 和 ISO-8859 字符集中,一个字符只占用一个字节的空间,因此 varchar 类型字段的最大长度是 65535 个字节。而在 utf8mb4 字符集中,一个字符可能占用 1 到 4 个字节的空间,因此 varchar 类型字段的最大长度也就不再是固定的,而是根据字符集的不同而有所不同。
示例:
我们继续以 utf8mb4 字符集为例,考虑长度为 80000 的字符串在 varchar 类型字段中的最大长度。
在 utf8mb4 字符集下,每个字符可能占用的字节数不同,最大为 4 个字节。因此,如果我们使用 utf8mb4 字符集,那么 varchar 类型字段的最大长度是 65535/4=16383。
CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `value` varchar(16383) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;INSERT INTO `test` (`value`) VALUES (REPEAT('你', 80000));
在这个例子中,我们将 varchar 类型字段的最大长度设为 16383,在 utf8mb4 字符集下,一个字符最多占用 4 个字节,因此这个字段最大能存储的字符串长度为 16383。但是我们插入了一个长度为 80000 的字符串,MySQL 报错:
ERROR 1406 (22001): Data too long for column 'value' at row 1
这是因为插入的字符串长度超过了我们定义的最大长度。
![](https://pics6.baidu.com/feed/bd315c6034a85edf8787690cadd3042fdd54752d.jpeg@f_auto?token=ae39f21e9a737acfbd0388faa006fafd)
三、存储引擎的影响
MySQL 支持多种存储引擎,包括 InnoDB、MyISAM、Memory 等。不同的存储引擎对于 varchar 类型字段的最大长度也有不同的影响。
在 InnoDB 存储引擎中,varchar 类型字段的最大长度是受到限制的,最大长度为 65535。而在 MyISAM 存储引擎中,varchar 类型字段的最大长度可以达到 65535/2=32767。
示例:
我们以 InnoDB 和 MyISAM 存储引擎为例,分别考虑长度为 80000 的字符串在 varchar 类型字段中的最大长度。
在 InnoDB 存储引擎下,varchar 类型字段的最大长度是受到限制的,最大长度为 65535。因此,在 InnoDB 存储引擎下,我们需要将 varchar 类型字段的长度设为 65535 或更小。
`id` int(11) NOT NULL AUTO_INCREMENT, `value` varchar(65535) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;INSERT INTO `test_innodb` (`value`) VALUES (REPEAT('a', 65535));
在这个例子中,我们将 varchar 类型字段的长度设为 65535,在 InnoDB 存储引擎下,这个字段最大能存储的字符串长度就是 65535。
而在 MyISAM 存储引擎下,varchar 类型字段的最大长度可以达到 65535/2=32767。因此,在 MyISAM 存储引擎下,我们可以将 varchar 类型字段的长度设为 32767 或更小。
CREATE TABLE `test_myisam` ( `id` int(11) NOT NULL AUTO_INCREMENT, `value` varchar(32767) NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;INSERT INTO `test_myisam` (`value`) VALUES (REPEAT('a', 32767));
在这个例子中,我们将 varchar 类型字段的长度设为 32767,在 MyISAM 存储引擎下,这个字段最大能存储的字符串长度就是 32767。
需要注意的是,在 MySQL 5.7.5 之前的版本中,MyISAM 存储引擎也存在最大行长度的限制,因此即使在 MyISAM 存储引擎下,varchar 类型字段的最大长度也不一定能够达到 65535/2=32767。在 MySQL 5.7.5 之后,MyISAM 存储引擎的最大行长度限制被取消,因此 varchar 类型字段的最大长度可以达到 65535/2=32767。