Mysql实际开发中注意点以及设计表结构的数据类型

·  阅读 219

「这是我参与11月更文挑战的第12天,活动详情查看:2021最后一次更文挑战

针对UTF-8编码规则

1个英文=1个字节(1Byte) 1个汉字=3个字节(3Byte)

Mysql的数据类型

大致可以分为三类

  • 数值
  • 时间
  • 字符

数值

定长的,即创建表结构时无法修改这个类型的数据存储上限(字符类型大部分都是变长的可以指定存储的数据最大支持多少字节数据)

类型大小范围(无符号)备注
TINYINT1 Bytes有符号:(-128,127)无符号(0,255)
化成二进制就可以理解,因为有符号会使用一位来存储符号
所以有符号比无符号要小一倍
状态值,例如status
SMALLINT2 Bytes(0,65 535)
MEDIUMINT3 Bytes(0,16 777 215)
INT或INTEGER4 Bytes(0,4 294 967 295)
BIGINT8 Bytes(0,18 446 744 073 709 551 615)以上几个数值都可以作为一些业务id(orderId)来使用,
具体看使用的场景上限是什么样的
FLOAT4 Bytes0,(1.175 494 351 E-38,3.402 823 466 E+38)单精度浮点数
DOUBLE8 Bytes0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)双精度浮点数
(相对于单精度也就相当于更能存小数点后数字可以更多)
DECIMAL对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2依赖于M和D的值类似保存的类似字符串的形式
(不会存储在数据库之后因为数据结构修改原始数据)
(M为全长,D为小数点后的长度)

时间

类型大小范围格式备注
DATE3 Bytes1000-01-01/9999-12-31YYYY-MM-DD日期
TIME3 Bytes'-838:59:59'/'838:59:59'HH:MM:SS时间
YEAR1 Bytes1901/2155YYYY年份
DATETIME8 Bytes1000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS日期和时间
TIMESTAMP4 Bytes1970-01-01 00:00:00/2038
结束时间是第 2147483647 秒,
北京时间 2038-1-19 11:14:07
格林尼治时间 2038年1月19日 凌晨 03:14:07
YYYYMMDD HHMMSS时间戳

字符类型

类型大小备注
CHAR0-255 bytes
VARCHAR0-65535 bytes
TINYBLOB0-255 bytes不超过 255 个字符的二进制字符串
TINYTEXT0-255 bytes短文本字符串
BLOB0-65 535 bytes1. 不设置长度(设置也无效),当不知道属性业务长度时可以使用
2. 二进制形式的长文本数据
3. 无法设置默认值
TEXT0-65 535 bytes1.不设置长度(设置也无效),当不知道属性业务长度时可以使用
2.长文本数据
3. 无法设置默认值
MEDIUMBLOB0-16 777 215 bytes二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215 bytes中等长度文本数据
LONGBLOB0-4 294 967 295 bytes二进制形式的极大文本数据
LONGTEXT0-4 294 967 295 bytes极大文本数据
  1. CHAR(n)和VARCHAR(n)中的n表示的是字符数量不是字节数,比如CHAR(4)可以存储“abcd”(4Bytes)也可以存储“一二三四”(12Bytes)

  2. CHAR(n)和VARCHAR(n)最大的区别在于,指定CHAR的大小之后,这个地方就会预留这么大的空间供存储数据,即使存储的不够这么大空间也需要消耗这么大空间,VARCHAR(n)指定的只是指定了一个存储上限,具体占用内存还是根据存储的数据大小决定的

    CREATE TABLE `test` (
      `char_str` char(4) DEFAULT NULL,
      `varchar_str` varchar(4) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    insert into test char_str='12',varchar_str='12';
    
    此时这条数据的char_str占用4Bytes,varchar_str占用2Bytes
    复制代码
  3. VARCHAR当指定default null时,需要1bit来标识这个约束

  4. VARCHAR当数据长度小于255时,在数据库底层计算该字符长度的字段只需要一个,超过255需要两个字符至少需要一位存储所以所需字段特别小的时候可以选择使用CHAR来存储省下这个字符空间

  5. TEXT与BLOB区别:BLOB保存二进制数据,TEXT保存字符数据.例如图片的存储,其实就是将图片的信息保存为二进制(Blob)的形式来存储(当然现实是一般将图片保存在服务器的某个文件夹下,直接可以使用路径和图片名称获取)

  6. char、varchar、text的取舍

    • 知道长度的用char
    • 长度变化的用varchar
    • 一般不使用text,可以使用es或者oss代替

为什么设计mysql要优化存储的数据结构

  • 空间上占用更小的空间
  • 在极限条件下,每行数据更小表明mysql的每页数据能存储更多的行数据,在一定条件下可以增加查询效率
  • 人为条件下也可以使用不同的数据类型来表示不同类型的存储情况,从而规范协作开发中的开发规范性问题

为什么变长的varchar要尽可能小的符合业务需要

  • 在进行排序(需要临时空间)和创建临时表时申请临时空间,会使用指定的n来申请内存

为什么字段尽量不要默认为null

  1. 该列数据只要有一个是null,就会导致该列复合索引失效
  2. null值干扰排序、分组、去重等结果
  3. null值在进行复合函数的计算时会有问题
  4. null不等同于空,在实际业务开发中会多出来一种逻辑
分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改