小知识,大挑战!本文正在参与“程序员必备小知识”创作活动。
针对UTF-8编码规则
1个英文=1个字节(1Byte) 1个汉字=3个字节(3Byte)
Mysql的数据类型
大致可以分为三类
- 数值
- 时间
- 字符
数值
定长的,即创建表结构时无法修改这个类型的数据存储上限(字符类型大部分都是变长的可以指定存储的数据最大支持多少字节数据)
类型 | 大小 | 范围(无符号) | 备注 |
---|---|---|---|
TINYINT | 1 Bytes | 有符号:(-128,127)无符号(0,255) 化成二进制就可以理解,因为有符号会使用一位来存储符号 所以有符号比无符号要小一倍 | 状态值,例如status |
SMALLINT | 2 Bytes | (0,65 535) | |
MEDIUMINT | 3 Bytes | (0,16 777 215) | |
INT或INTEGER | 4 Bytes | (0,4 294 967 295) | |
BIGINT | 8 Bytes | (0,18 446 744 073 709 551 615) | 以上几个数值都可以作为一些业务id(orderId)来使用, 具体看使用的场景上限是什么样的 |
FLOAT | 4 Bytes | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度浮点数 |
DOUBLE | 8 Bytes | 0,(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为小数点后的长度) |
### 时间 |
类型 | 大小 | 范围 | 格式 | 备注 |
---|---|---|---|---|
DATE | 3 Bytes | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期 |
TIME | 3 Bytes | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间 |
YEAR | 1 Bytes | 1901/2155 | YYYY | 年份 |
DATETIME | 8 Bytes | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 日期和时间 |
TIMESTAMP | 4 Bytes | 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒, 北京时间 2038-1-19 11:14:07, 格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 时间戳 |
字符类型
类型 | 大小 | 备注 |
---|---|---|
CHAR | 0-255 bytes | |
VARCHAR | 0-65535 bytes | |
TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 1. 不设置长度(设置也无效),当不知道属性业务长度时可以使用 2. 二进制形式的长文本数据 3. 无法设置默认值 |
TEXT | 0-65 535 bytes | 1.不设置长度(设置也无效),当不知道属性业务长度时可以使用 2.长文本数据 3. 无法设置默认值 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
-
CHAR(n)和VARCHAR(n)中的n表示的是字符数量不是字节数,比如CHAR(4)可以存储“abcd”(4Bytes)也可以存储“一二三四”(12Bytes)
-
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
-
VARCHAR当指定default null时,需要1bit来标识这个约束
-
VARCHAR当数据长度小于255时,在数据库底层计算该字符长度的字段只需要一个,超过255需要两个字符至少需要一位存储所以所需字段特别小的时候可以选择使用CHAR来存储省下这个字符空间
-
TEXT与BLOB区别:BLOB保存二进制数据,TEXT保存字符数据.例如图片的存储,其实就是将图片的信息保存为二进制(Blob)的形式来存储(当然现实是一般将图片保存在服务器的某个文件夹下,直接可以使用路径和图片名称获取)
-
char、varchar、text的取舍
- 知道长度的用char
- 长度变化的用varchar
- 一般不使用text,可以使用es或者oss代替
为什么设计mysql要优化存储的数据结构
- 空间上占用更小的空间
- 在极限条件下,每行数据更小表明mysql的每页数据能存储更多的行数据,在一定条件下可以增加查询效率
- 人为条件下也可以使用不同的数据类型来表示不同类型的存储情况,从而规范协作开发中的开发规范性问题
为什么变长的varchar要尽可能小的符合业务需要
- 在进行排序(需要临时空间)和创建临时表时申请临时空间,会使用指定的n来申请内存
为什么字段尽量不要默认为null
- 该列数据只要有一个是null,就会导致该列复合索引失效
- null值干扰排序、分组、去重等结果
- null值在进行复合函数的计算时会有问题
- null不等同于空,在实际业务开发中会多出来一种逻辑