万答#11,MySQL中char与varchar有什么区别
GreatSQL 8.0.25 InnoDB
参数 | char | varchar |
---|---|---|
长度是否可变 | 定长 | 变长 |
存储容量 | 0 ~ 255 | 0 ~ 65,535 |
CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
字段V、C都写入一个4+空格的字符
[root@GreatSQL][test]> INSERT INTO vc VALUES ('4 ', '4 '); [root@GreatSQL][test]> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc; +---------------------+---------------------+ | CONCAT('(', v, ')') | CONCAT('(', c, ')') | +---------------------+---------------------+ | (4 ) | (4) | +---------------------+---------------------+ 1 rows in set (0.00 sec)
测试结果,char的长度维持不变,占了2个字符,varchar空格长度变了,占了一个字符。
当写入长度大于设定长度时候,出现报错
[root@GreatSQL][test]>INSERT INTO vc VALUES ('123456', '123456'); ERROR 1406 (22001): Data too long for column 'v' at row 1
调整sql_mode,再写入的时候,自动截取限制容量内的内容
[root@GreatSQL][test]>set session sql_mode="ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION" [root@GreatSQL][test]>INSERT INTO vc VALUES ('123456', '123456'); Query OK, 1 row affected, 2 warnings (0.02 sec) [root@GreatSQL][test]>select * from vc; +------+------+ | v | c | +------+------+ | 1234 | 1234 | +------+------+ 1 row in set (0.00 sec)
创建表,设置CHAR长度为256, 结果提示错误
[root@GreatSQL][test]>CREATE TABLE vc (v VARCHAR(255), c CHAR(256)); ERROR 1074 (42000): Column length too big for column 'c' (max = 255); use BLOB or TEXT instead