mysql 开发基础系列12 选择合适的数据类型(上)

一. char 与varchar比较

  mysql 开发基础系列12 选择合适的数据类型(上)

  在上图的最后一行的值只适用在"非严格模式",关于严格模式后面讲到。在“开发基础系列4“ 中讲到CHAR 列删除了尾部的空格。
由于char是固定长度,所以字的处理速度比varchar快,但也浪费存储空间,随着mysql 的不断升级,varchar数据类型的性能也在不断改进提高,varchar的字节是L+1字节,1字节是用来记录其长度的字节。

  char长度可选范围在0-255之间,也就是char最大能存储255个字符,varchar的长度范围为0-65535个字节。

  下面重点区别下mysql与sql server中对varchar(n) n的区别

  -- 在sql server中varchar(10) 10代表字节数,而不是字符数, 一个汉字二个字节所以最多放五个汉字
INSERT INTO table_1 values('你好中国人民')

mysql 开发基础系列12 选择合适的数据类型(上)

  --  在mysql中varchar(10) 10代表字符数,而不是字节数,一个汉字是1个字符所以最多可放10个汉字
INSERT INTO Myisam_char VALUES('你好中国人民','你好中国人民')

mysql 开发基础系列12 选择合适的数据类型(上)

在mysql中,不同的存储引擎对char和varchar的使用原则有所不同,这里简单概括下:
  myisam  存储引擎:建议使用固定长度的数据列代替可变长度的数据列。
  memory  存储引擎:目前都使用固定长度的数据行存储,因此无论使用char或varchar列都没有关系,两者都是作为char类型处理。
  innodb    存储引擎:建议使用varchar类型,在innodb内部行存储格式没有区分固定长度和可变长度。

二.  text与blob 

  二者通常用来保存较大文本,如文章或日记, 主要差别是BloB能用来保存二进制数据如照片,text只能保存字符数据。
  这里介绍blob与text存在的一些常见问题:

  1. "空洞"性能问题

      在大量删除操作时,数据表中会留下很大的"空洞",以后填入这些空洞的记录在插入的性能上会有影响,为了提高性能,建议定期使用optimize table功能对表来进行碎片整理。下面来验证下

-- 创建表t
CREATE TABLE t(id VARCHAR(1000),context TEXT);
-- 往里面插入大量数据
INSERT INTO t VALUES(1,REPEAT('haha',100));
INSERT INTO t VALUES(2,REPEAT('haha',100));
INSERT INTO t VALUES(3,REPEAT('haha',100));
INSERT INTO t SELECT * FROM t;
-- ...
INSERT INTO t SELECT * FROM t;
--  获取表的空间大小 176.70 MB
SELECT (data_length/1024.0/1024.0) 'data_MB',
index_length FROM information_schema.tables
WHERE table_schema='test' AND table_name = 't';

mysql 开发基础系列12 选择合适的数据类型(上)

-- 从表中删除ID为1的数据,占1/3 (共 131072 行受到影响)
DELETE FROM t WHERE id=1;
-- 再次获取表的空间大小, 是176.68 MB,并没有因为数据删除而减少

mysql 开发基础系列12 选择合适的数据类型(上)

-- 使用optimize table 优化
OPTIMIZE TABLE t;

mysql 开发基础系列12 选择合适的数据类型(上)

--  再次获取表的空间大小, 是134.65MB 发现表的数据大大缩小了,"空洞"被回收了 

mysql 开发基础系列12 选择合适的数据类型(上)

上一篇:python-数据


下一篇:mysql 开发基础系列22 SQL Model