Home Tags Posts tagged with "Varchar"

Varchar

0 53

MySQL 数据类型选择

MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL 支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。


数值类型

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC),以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值,并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

作为 SQL 标准的扩展,MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

img

无论在什么数据库中,数值和字符串这两种数据字段类型都用的最多,并且往往量级非常大,在这种背景下,选择的字段类型的优劣对性能影响非常大,因此我们有必要熟知上表中各种类型的使用及差别。

一、TINYINT

  1. 表示范围 :(-128,127) or (0,255)

  2. 占用字节:1字节

  3. 【强制】表达是与否概念的字段,必须使用is_xxx的方式命名,数据类型是unsigned tinyint

  4. 最小显示长度(TINYINT(1) 和 TINYINT(2)的区别)

    TINYINT(m)

 m表示的是显示数据宽度,不同中数据类型它的数据宽度也是有差别的。数据宽度和数据类型的取值范围彼此之间是相互独立的,没有任何联系。 bigint(m)默认宽度为20,如果创建时表中设置了zerofill关键字(默认是用0填充的)。zerofill含义是:往表中插入的数值比定义的长度小的时候,会在数 值前进行补值。 img

  1. TINYINT和ENUM的区别

  TINYINT(1)或ENUM(’真’,’假’)?
        用ENUM枚举当存储只有2个值时只占用一个位的宽度,0或1,但会花更多的时间去寻找枚举查询的开始。
        用TINYINT(1)默认就会占用4个位的宽度(0000)
        得出结论:比如要存储一个介于0-9之间的值,为了查询获取这个值,建议用TINYINT(1)会更快,但如果你是为了大量记录枚举(“真”,“假”),那么用ENUM( ‘true’ , ‘false’) 搜索会更快。一般的,咱们如果存的是纯数字的话,建议用tinyint,如果是字符串,且是固定长度的,建议用char,而enum的枚举字段,使用的使用需要慎重考虑,避免带来不必要的麻烦

       注:TINYINT(1)在使用Mybatis代码生成器时,会自动转为Boolean类型!!!

二、INT或INTEGER

  1. 表示范围 :(-2 147 483 648,2 147 483 647) or (0,4 294 967 295)

  2. 占用字节:4字节

  3. The keyword INT is a synonym for INTEGER

  4. 如果不需要存取负值,最好加上unsigned

三、BIGINT

1.Mysql里有个数据类型bigint在java转换成实体对象时,处理不当容易出现以下异常:

java.lang.ClassCastException: java.lang.Long cannot be cast to java.math.BigInteger

只需要注意以下情况,就可避免此类异常:当数据库中该属性添加unsigned,则在对象中对应的属性类型应该为BigInteger; ​ 当数据库中该属性未添加unsigned,则在对象中对应的属性类型应该为Long。可以成功映射为Long的表用的是BIGINT(20),但是出问题的表使用的是BIGINT(20) UNSIGNED。如果不是无符号类型,BIGINT(20)的取值范围为-9223372036854775808~9223372036854775807。与Java.lang.Long的取值范围完全一致,mybatis会将其映射为Long;而BIGINT(20) UNSIGNED的取值范围是0 ~ 18446744073709551615,其中一半的数据超出了Long的取值范围,Mybatis将其映射为BigInteger。mysql数据库字段bigint使用 – 一心二念 – 博客园 (cnblogs.com)

2.存储手机号码用bigint(11)还是varchar(11)

空间:BIGINT——8字节CHAR——11字节
效率:BIGINT效率更高
使用:32位某些应用中bigint太大会溢出,要进行各种转换,麻烦
结论:使用char(11)更好

3.用 BIGINT 做主键,而不是 INT;

在真实业务场景中,整型类型最常见的就是在业务中用来表示某件物品的数量。例如上述表的销售数量,或电商中的库存数量、购买次数等。在业务中,整型类型的另一个常见且重要的使用用法是作为表的主键,即用来唯一标识一行数据。

整型结合属性 auto_increment,可以实现自增功能,但在表结构设计时用自增做主键,希望你特别要注意以下两点,若不注意,可能会对业务造成灾难性的打击:

用 BIGINT 做主键,而不是 INT;自增值并不持久化,可能会有回溯现象(MySQL 8.0 版本前)。 从表 1 可以发现,INT 的范围最大在 42 亿的级别,在真实的互联网业务场景的应用中,很容易达到最大值。例如一些流水表、日志表,每天 1000W 数据量,420 天后,INT 类型的上限即可达到。可以看到,当达到 INT 上限后,再次进行自增插入时,会报重复错误,MySQL 数据库并不会自动将其重置为 1。

第二个特别要注意的问题是,MySQL 8.0 版本前,自增不持久化,自增值可能会存在回溯问题!(23条消息) MySQL-数字类型自增是真的坑_Five在努力的博客-CSDN博客

其实,在海量互联网架构设计过程中,为了之后更好的分布式架构扩展性,不建议使用整型类型做主键,更为推荐的是字符串类型。合适的字符存储长度,不但节约数据库表空间、节约索引存储,更重要的是提升检索速度。

正例:人的年龄用unsigned tinyint(表示范围0-255,人的寿命不会超过255岁);海龟就必须是smallint,但如果是太阳的年龄,就必须是int;如果是所有恒星的年龄都加起来,那么就必须使用bigint。

四、FLOAT,DOUBLE,DECIMAL

  1. 表示范围 :较大

  2. 占用字节:4字节,8字节

  3. MySQL 中使用浮点数和定点数来表示小数。

浮点类型有两种,分别是单精度浮点数FLOAT)和双精度浮点数DOUBLE);定点类型只有一种,就是 DECIMAL。浮点类型和定点类型都可以用(M, D)来表示,其中M称为精度,表示总共的位数;D称为标度,表示小数的位数。浮点数类型的取值范围为 M(1~255)和 D(1~30,且不能大于 M-2),分别表示显示宽度和小数位数。M 和 D 在 FLOAT 和DOUBLE 中是可选的,FLOAT 和 DOUBLE 类型将被保存为硬件所支持的最大精度。DECIMAL 的默认 D 值为 0、M 值为 10。

  1. DOUBLE 实际上是以字符串的形式存放的(精准)

  2. 长度一定下,浮点数表示范围更大,缺点则是精度问题

在 MySQL 中,定点数以字符串形式存储,在对精度要求比较高的时候(如货币、科学数据),使用 DECIMAL 的类型比较好,另外两个浮点数进行减法和比较运算时也容易出问题,所以在使用浮点数时需要注意,并尽量避免做浮点数比较。

  1. 单精度float比双精度效率更高

  2. 浮点数判断相等不能用== 或者equals,应当使用

  3. 【强制】小数类型为decimal,禁止使用float和double。

说明:float和double在存储的时候,存在精度损失的问题,很可能在值的比较时,得到不正确的结果。如果存储的数据范围超过decimal的范围,建议将数据拆成整数和小数分开存储。


日期类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个”零”值,当指定不合法的MySQL不能表示的值时使用”零”值。

TIMESTAMP类型有专有的自动更新特性,将在后面描述。

img


字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。img

一、CHAR和VARCHAR

CHAR数据类型是MySQL中的固定长度的字符类型。我们经常声明CHAR类型的长度,指定我们要存储的最大字符数。例如,CHAR(20)最多可以容纳20个字符。

如果要存储的数据是固定大小,则应使用CHAR数据类型。与VARCHAR这种情况相比,您将获得更好的性能。

CHAR数据类型的长度可以是0到255之间的任何值。当您存储CHAR值时,MySQL会将其值用空格填充到您声明的长度。

当您查询CHAR值时,MySQL将删除尾随空格。

请注意,如果启用PAD_CHAR_TO_FULL_LENGTH SQL模式,MySQL将不会删除尾随空格。

区别:

  1. CHAR的长度是不可变的,而VARCHAR的长度是可变的

  2. CHAR的存取速度要比VARCHAR快得多

  3. 存储方式不同

varchar比char节省空间,但在效率上比char稍微差一些。varchar比char节省空间,是因为varchar是可变字符串,比如:用varchar(5)存储字符串“abc”,只占用3个字节的存储空间,而用char(5)存储,则占用5个字节(“abc ”)。varchar比char效率稍差,是因为,当修改varchar数据时,可能因为数据长度不同,导致数据迁移(即:多余I/O)。其中,oracle对此多余I/O描述的表达是:“行迁移”(Row Migration)。

  1. “行迁移”(Row Migration)

    *“当一行的记录初始插入时是可以存储在一个block中的,由于更新操作导致行增加了,而block的自由空间已经完全满了,这个时候就产生了行迁移。在这种情况下,oracle将会把整行数据迁移到一个新的block中,oracle会保留被迁移的行的原始指针指向新的存放行数据的block,这就意味着被迁移行的ROW ID是不会改变的。"*
    
      其中要解释一下:block是oracle中最小的数据组织与管理单位,是数据文件磁盘储存空间单位,也是**数据库I/O最小单位(****也就是说,读和写都是一个block的大小,所以如果block没满时,更新内容长度变更的varchar字段,和更新内容长度没变的varchar字段,I/O次数是一样,不存在额外消耗,只有在block满时,才会出现额外I/O,所以char和varchar性能之间的性能差异,是相当细微的,绝大多数情况下可以忽略不计,所以上文描述的“稍”差的含义)**。
    
      所以,我的开发经验是:“**用varchar代替char的效率有所下降,但不大**”。
  2. 占用空间不同

CHAR的存储方式是,一个英文字符(ASCII)占用1个字节,一个汉字占用两个字节;而VARCHAR的存储方式是,一个英文字符占用2个字节,一个汉字也占用2个字节。

varchar是可变长字符串,不预先分配存储空间(这里说的是存储时不预先分配存储空间,但是在查询需要创建临时表时,会按照设置的长度预先分配内存空间,因此这个设置长度时不可以随意,详情见下varchar的长度设计),长度不要超过5000,如果存储长度大于此值,定义字段类型为TEXT,独立出来一张表,用主键来对应,避免影响其它字段索引效率。

     6.VARCHAR类型的实际长度是它的值的实际长度+1。为什么”+1″呢?

这一个字节用于保存实际使用了多大的长度。


二、MySQL中的varchar长度设计

 之前看到一篇博文,讲的是在定义varchar(n)时,需要用多大就设置多大,而我当时在分析时,对数据量估算错误了,设置了varchar(5000),被leader和导师批评了一番,因此特意学习varchar的长度设计:
尽管varchar(n)是存储可变长字符串的,但是其n也不是越大越好,理论如下
当MySql在查询需要创建临时表的时候(union,order by、group by,子查询),在MySql读取数据之前,是只知道varchar的长度n,不知道实际数据的长度的,但是读取数据之前需要预分配内存空间,MySql是根据varchar(n)中的n来进行分配内存的,这样也是最合理的方式,不可能分配小于n个字符的空间,因此针对varchar(1000)设置就会预先分配1000个字符空间,很显然这个是十分不靠谱的设计。

三、varchar(50)和varchar(255)有性能上的差别么?

对于INNODB,varchar(50)varchar(255)这两者在存放方式上完全一样:1-2 byte保存长度,实际的字符串存放在另外的位置,每个字符1 byte到4 byte不定(视编码和实际存储的字符而定)。所以将一个字段从varchar(50)长度改成varchar(100)长度不会导致表的重建。但如果把长度从varchar(50)改成varchar(256)就不一样了,表示长度会需要用到2 byte或更多。既然255长度以下对INNODB都一样,而且我们平时基本上也不太会使用到MYISAM,那么是不是为了省心,我们就可以把255长度以下的字段的类型都设置成varchar(255)了呢?
非也。
因为内存表(临时表)介意。
虽然我们不会明文创建内存表,但所有的中间结果都会被数据库引擎存放在内存表(MySQL在有些查询情况下需要创建内存表)。我们可以通过EXPLAIN或者SHOW STATUS可以查看MYSQL是否使用了内存表用来帮助完成某个操作。
而内存表会按照定义的varchar(n)的n来分配内存。以utf-8编码为例,对于varchar(255),每一行所占用的内存就是长度的2 byte + 3 * 255 byte。对于100条数据,光一个varchar字段就占约1GB内存。如果我们该用varchar(50),就可以剩下来约80%的内存空间。
除此之外,255长度也可能会对索引造成坑。MySQL在5.6版本及之前的最大长度是767 byte。但MySQL 5.5版本后开始支持4个byte的字符集utf8mb4(沙雕表情用到的字符太多,长度不够用)。255 * 4 > 767,所以索引就放不下varchar(255)长度的字段了。虽然MySQL在5.7版本后将限制改成了3072 byte,但如果是多字段的联合索引还是有可能会超过这个限制

所以我们的结论就是:在长度够用的情况下,越短越好。

四、varchar(255)和varchar(256)的区别

在varchar长度接近256时,varchar长度设置成255的好处:

1、方便InnoDB建索引,对于 MyISAM,可以对前 1000 个字节做索引,对于 InnoDB,则只有 767 字节。(来源依据)。255X3=765

2、少申请一个字节,记录字符创长度,一个8位的tinyint,可以表示的无符号数值的范围是,0-255,如果长度超过了255,需要在申请个字节

五、varchar(n)长度定义知识总结

1.在长度够用的情况下,越短越好(因为长了会导致查询生成临时表时降低性能且占用内存)。

2.在varchar长度接近256时,varchar长度设置成255更好

六、参考文章