关于mysql字符串的存储之char,varchar类型
平时工作中有关char,varchar的疑问,今天总结下:
char的范围可以存放0到255个字符,注意哦,是确切的字符,
CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`a` char(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
向表中插入几个值,
insert into t1 values(1,'aaaaa'),(2,'我是谁'),(3,'(当我睁开眼睛,发现自己竟然什么也看不见,眼前一片黑暗时,我像被噩梦吓倒一样,全身惊恐,悲伤极了,那种感觉让我今生永远难以忘怀。) 1880年6月27日,我出生在美国的南部亚拉巴马州的塔斯甘比亚镇。 父系祖先来自瑞典,移民定居在美国的马里兰州。有件不可思议的事,我们的一位祖先竟然是聋哑教育专家。谁料得到,他竟然会有一个像我这样又盲又聋又哑的后人。每当我想到这里,心里就不禁大大地感慨一番,命运真是无法预知啊! 我的祖先自从在亚拉巴马州的塔斯甘比亚镇买了土地后,整个家族就在这里定居下来。据说,那时候由于地处偏僻,祖父每年都要特地从塔斯甘比亚镇骑马到760英里外的费城,购置家里和农场所需的用品、农具、肥料和种籽等。每次祖父在往赴费城的途中,总会写家书回来报平安,信中对西部沿途的景观,以及旅途中所遭遇的人、事、物都有清楚且生动的描述。直到今天,大家仍很喜欢一而再地翻看祖父留下的书信,就好像是在看一本历险小说,百读不厌。 我的父亲亚瑟?凯勒曾是南北战争时的南军上尉,我的母亲凯蒂?亚当斯是他的第二任妻子,母亲小父亲好几岁。 在我病发失去视觉、听觉以前,我们住的屋子很小,总共只有一间正方形的大房子和一间供仆人住的小房子。那时候,依照南方人的习惯,他们会在自己的家旁再加盖一间屋子,以备急需之用。南北战争之后,父亲也盖了这样一所屋子,他同我母亲结婚之后,住进了这个小屋。小屋被葡萄、爬藤蔷薇和金银花遮盖着,从园子里看去,像是一座用树枝搭成的凉亭。小阳台也藏在黄蔷薇和南方茯苓花的花丛里,成了多彩的蜂鸟和殷勤的蜜蜂的乐园. 祖父和祖母所住的老宅,离我们这个蔷薇凉亭不过几步。由于我们家被茂密的树木、绿藤所包围,所以邻居人都称我们家为“绿色家园”。这是童年时代的天堂。 在我的家庭老师——莎莉文小姐尚未到来之前,我经常独自一人,依着方型的黄杨木树篱,慢慢地走到庭园里,凭着自己的嗅觉,寻找初开的紫罗兰和百合花,深深地吸着那清新的芳香。有时候我也会在心情不好时,独自到这里来寻求慰藉,我总是把炙热的脸庞藏在凉气沁人的树叶和草丛之中,让烦躁不安的心情冷静下来。置身于这个绿色花园里,真是心旷神怡。这里有爬在地上的卷须藤和低垂的茉莉,还有一种叫做蝴蝶荷的十分罕见的花。因为它那容易掉落的花瓣很像蝴蝶的翅膀,所以名叫蝴蝶荷,这种花发出一阵阵甜丝丝的气味。但最美丽的还是那些蔷薇花。在北方的花房里,很少能够见到我南方家里的这种爬藤蔷薇。它到处攀爬,一长串一长串地倒挂在阳台上,散发着芳香,丝毫没有尘土之气。每当清晨,它身上朝露未干,摸上去是何等柔软、何等高洁,使人陶醉不已。我不由得时常想,上帝御花园里的曝光兰,也不过如此吧! 我生命的开始是简单而普通的,就像每个家庭迎接第一个孩子时一样,大家都充满喜悦。为了要给第一个孩子命名,大家都绞尽脑汁,你争我吵,每个人都认为自己想出来的名字才是最有意义的。父亲希望以他最尊敬的祖先的名字“米德尔?坎培儿”作我的名字,母亲则想用她母亲的名字“海伦?艾培丽特”来命名。大家再三讨论的结果,是依照母亲的希望,决定用外婆的名字。先是为了命名争吵不休,之后,为了要带我去教堂受洗,大家又手忙脚乱,以至于兴奋的父亲在前往教会途中,竟把这个名字忘了。当牧师问起“这个婴儿叫什么名字”时,紧张兴奋的父亲一时之间说出了“海伦?亚当斯”这个名字。因此,我的名字就不是沿用外祖母的名字“海伦?艾培丽特”,而变成了“海伦?亚当斯”。 家里的人告诉我说,我在婴儿时期就表现出了不服输的个性,对任何事物都充满了好奇心,个性非常倔强,常常想模仿大人们的一举一动。所以,6个月时已经能够发出“茶!茶!茶!”和“你好!”的声音,吸引了每个人的注意。甚至于“水”这个字,也是我在1岁以前学会的。直到我生病后,虽然忘掉了以前所学的字,但是对于“水”这个字却仍然记得。家人还告诉我,在我刚满周岁时就会走路了。我母亲把我从浴盆中抱起来,放在膝上,突然间,我发现树的影子在光滑的地板上闪动,就从母亲的膝上溜下来,自己一步一步地、摇摇摆摆地去踩踏那些影子。
春光里百鸟啁啾,歌声盈耳,夏天里到处是果子和蔷薇花,待到草黄叶红已是深秋来临。三个美好的季节匆匆而过,在一个活蹦乱跳、咿呀学语的孩子身上留下了美好的记忆。然而好景不长,幸福的时光总是结束得太早。一个充满知更鸟和百灵鸟的悦耳歌声且繁花盛开的春天,就在一场高烧的病痛中悄悄消失了。在次年可怕的2月里,我突然生病,高烧不退。医生们诊断的结果,是急性的胃充血以及脑充血,他们宣布无法挽救了。但在一个清晨,我的高烧突然退了,全家人对于这种奇迹的发生,当时惊喜得难以言喻。但是,这一场高烧已经让我失去了视力和听力,我又像婴儿一般蒙昧,而他们,我的家人和医生,却全然不知内容我们大家都读过一些令人激动的故事,这些故事里的主人公仅仅活在有限并且特定的时间内,有时长达一年,有时短到24小时。但我们总是有兴趣发现,那命中注定要死的是那些有选择自由的人,而不是那些活动范围被严格限定了的判了刑的犯人。 这样的故事让我们思考,在相似的情况下,我们该怎么办,作为终有一死的人,在那最终的几个小时内安排什么事件,什么经历,什么交往?在回顾往事时,我们该找到什么快乐?什么悔恨?
');
再查询下看看:
root@test 10:27:25>select char_length(a),length(a) from t1;
+----------------+-----------+
| char_length(a) | length(a) |
+----------------+-----------+
| 5 | 5 |
| 3 | 6 |
| 255 | 498 |
+----------------+-----------+
虽然定义的是0-255个字符,如果插入的字符串长度大于等于定义的长度,MySQL会自动截取能放下的字符,而且t1表定义的字符集是gbk,每个gbk占两个字节,但是255*2是510B呀,怎么会是498B呢?
首先推测是其中的标点占一个字节,验证下,
root@test 10:41:18>insert into t1 values(4,'我是,谁?');
Query OK, 1 row affected (0.00 sec)
root@test 10:43:41>insert into t1 values(5,'我是 谁');
Query OK, 1 row affected (0.00 sec)
root@test 10:44:13>insert into t1 values(6,'我是谁? ');
Query OK, 1 row affected (0.00 sec)
root@test 10:44:51>insert into t1 values(7,'我是谁? 呢');
Query OK, 1 row affected (0.00 sec)
再次查看:
root@test 10:45:24>select char_length(a),length(a) from t1;
+----------------+-----------+
| char_length(a) | length(a) |
+----------------+-----------+
| 5 | 5 |
| 3 | 6 |
| 255 | 498 |
| 5 | 8 |
| 4 | 7 |
| 4 | 7 |
| 6 | 10 |
+----------------+-----------+
7 rows in set (0.00 sec)
确实哦,是标点符号占的是1个Byte。
再看下varchar类型,手册上说的是能存放0-65535字节,并且是一个表中的列共享的,也就是说一个表中定义的varchar类型的长度的总和不要超过65535个字节,平时我定义的varchar类型定义40000个长度的时候,发现列定义自动转换为mediumtext类型了,就是由于这个原因,这在术语上叫做行溢出,验证下:
root@test 10:45:27>create table t2(a varchar(65535));
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@test 11:04:28>show warnings;
+-------+------+--------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------+
| Note | 1246 | Converting column 'a' from VARCHAR to TEXT |
+-------+------+--------------------------------------------+
1 row in set (0.00 sec)
root@test 11:05:49>show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`a` mediumtext
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
是不是一个表中varchar列的总和呢?
root@test 11:17:02>CREATE TABLE `t3` (
-> `a` varchar(20000),
-> b varchar(40000),
-> c varchar(10000)
-> ) ENGINE=InnoDB DEFAULT CHARSET=gbk;
Query OK, 0 rows affected, 1 warning (0.01 sec)
root@test 11:17:26>show warnings;
+-------+------+--------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------+
| Note | 1246 | Converting column 'b' from VARCHAR to TEXT |
+-------+------+--------------------------------------------+
1 row in set (0.00 sec)
root@test 11:17:31>show create table t3\G
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`a` varchar(20000) DEFAULT NULL,
`b` mediumtext,
`c` varchar(10000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
而innodb中一页是16KB,即16384个字节,远远小于65535个字节,而innodb表是索引组织的,即B+树的结构,每个页至少保留2条记录(否则就失去了B+树的意义,变成链表了),一般情况下,数据都是存放在B-tree Node的页类型中,其中前768个字节保存在B-tree Node页中,溢出的数据保存在Uncompressed BLOB Page中。
参考资料:
http://dev.mysql.com/doc/refman/5.1/en/char.html
MySQL技术内幕--InnoDB存储引擎