Skip to content

Latest commit

 

History

History
294 lines (241 loc) · 20.1 KB

【1】常用的数据类型.md

File metadata and controls

294 lines (241 loc) · 20.1 KB

1 常用的数据类型

   数据类型 我们接触过不同的编程语言，其中也会有各种数据的类型，用来操作不同类型的数据。自然数据库是用来存储数据的，所以在类型上，MySQL也提供了很多丰富的类型来供我们使用，所以我们首先要清楚每种类型的特点，选择合适的类型存储数据，来优化数据库📕。

-***Tacks [^NAME]***

[^NAME]: This is my Sign! My name is `A Tao`, email is `[email protected]`
If you find any mistakes , you can contact me!

2019/09/15 Sunday

目录

【1】数值类型

整数(int)

  1. 关于整数的有一个有无符号的 unsigned 属性选择,默认是负数和正数各一半。如果使用后，可以扩大正数范围，在设置主键(primary key)的时候常用，因为我们通常主键都是正整数开始的。
  2. 在类型后面可以跟上小括号 (M) 指定宽度,默认是(11)。这个不会影响到数据存储，M只是显示宽度上。不指定的话，宽度则默认为 int(11) 。一般配合 zerofill 使用。
  3. 列可以配合 zerofill 使用,如果数字位数不够可以使用0填充，看来其比较宽度整齐。如果你指定了 zerofill那么MySQL会为你默认指定 unsigned 也就是非负数。
  4. 关于范围说明。 举个例子，例如 tinyint 是一个字节的，而一个字节8位，计算机内部都是二进制存储，那么第一位是作为符号位的，所以只有剩下的7位表示所以范围也就是2^7，设置无符号后，可以不用符号位，从而扩大正数范围。
类型 字节 范围 说明
tinyint 1 有符号(-128，127) 、 无符号 (0，255) 2^8=256 小整数值
smallint 2 有符号(-32768，32767)、无符号(0，65535) 2^16=65536
mediumint 3 有符号(-8388608，8388607)、无符号(0, 16777215) 2^24=16 777 216
int 4 有符号(-2147483648，2147483647)、无符号 (0，4294967295) 2^32=4294967296 常用
bigint 8 有符号(-2^63，2^63-1)、无符号 (0，2^64-1) 2^64 反正数字超级大啦

小数(float decimal)

  1. 小数
    • 浮点数
      • 单精度 float
      • 双精度 double
    • 定点数 decimal
  2. 类型后面可以跟上 (M,D) 表示一共**M(精度)**个数字,**D(标度)**个小数位。
  3. 对于 float 和 double 的精度取决于尾数,表示的范围取决于指数。
    • float最大精度6位 double最大精度17位。 （MySQL5.7）
  4. 对于浮点数 float和 double
    • 如果指定了对应的(M,D),那么超出的数据会四舍五入截断,但是系统不会报错。
    • 如果不指定(M,D)，但是没超过浮点数的最大精度(float 6 / double 17)， 会按照实际插入的数据精度显示。
  5. 对于定点数decimal 如果系统不指定则按照默认值 (10,0) 来存储，如果超出,系统会报错，但是也会四舍五入插入进去。
  6. 如果插入数据后出现warning。可以输入(show warnings 进行查看上一个命令的报错)。
类型 字节 精度 说明
float(M,D) 4 6位 4*8=32位(1位符号位 8位指数位 23位尾数)
double(M,D) 8 17位 8*8=64位(1位符号位 11位指数位 52位尾数)
decimal(M,D) M+2 定点数 里面实际上使用字符串存储，比较准确，常用于货币等很精准

位

  • 对于位数值,直接select看不到结果,需要用bin() 或者 hex()函数 进行显示二进制或者16进制.
  • 这个类型，不太常用，目前我也没怎么使用过。
类型 字节 说明
bit(M) 1~8字节 默认M=1

【2】字符串类型

简单字符串(char 、varchar 、text)

  1. 不同的字符集 对应不同的编码 字符与字节之间的关系也会略有差异，一般来说UTF8一个中文字符占3字节。
  2. char 与 varchar 在检索数据时候，char会删除字符尾部的空格，varchar则会保留这些空格。
  3. 其他扩展类型
    • tinytext tinyblob 0~255字节(2^8-1)2MB
    • mediumtext mediumblob 0~16777215字节(2^24-1)16MB
    • longtext longblob 0~4294967295字节(2^32-1)1GB

特殊的字符串类型(enum 、 set)

  1. enum 枚举类型。
    • 例如create table string_enum (sex enum('M','F')); 创建表的sex的字段设置为枚举类型。
    • enum类型，插入的时候，只能从允许的类型中选择，或者插入为NULL
    • 在创建的时候通过枚举显示指定 存储最多2字节。
      • 对于(1~255)个成员，需要使用1个字节，
      • 对于(255~65535)个成员，需要使用2个字节存储。
    • 根据你枚举的类型来存储，如果你插入的字符没有(mysql5.7)，报错Data truncated for column
      • 忽略大小写的。
      • 插入值可以为空。
      • 插入值一次只能选择一个。
  2. set 字符串对象。
    • 例如 create table string_set (hobby set ('a','b','c','d','e')); 创建hobby的集合有a,b,c,d,e五个成员。
    • set类型，可以允许从值的集合中选择任意 1 个 或者 多个元素进行组合，所以插入的范围只要是集合内的就可以。
    • 插入的有重复的例如('a,b,a')这种只会写入('a,b')保留一个
    • 可以包含有0~64个成员。不同成员的占用字节也不同，最大为8字节。
      • 1~8个成员的集合 占用1个字节
      • 9~16个成员的集合 占用2个字节
      • 17~24个成员的集合占用3个字节
      • 25~32个成员的集合占用4个字节
      • 33~64个成员的集合占用8个字节
类型 字节 说明
char(M) M个字节 0~255字节 定长字符串
varchar(M) 0~65535字节 变长字符串
text 0~65535字节 长文本数据
enum 1~2字节 枚举类型
set 1~8字节 字符串对象
binary(M) 类似与char 存放的是二进制数据 在末尾填充0字节 达到定长M的长度
varbinary(M) 类似与varchar 存放的是二进制数据
blob 0~65535字节 类似text，存储二进制形式的长文本数据

【3】日期时间类型

datetime

datetime 是 date 和 time 的组合。

  • datetime 的范围比较大 支持的范围为 '0001-01-01 00:00:00' 到 9999-12-31 23:59:59'
  • 显示宽度固定在19字符，并且格式为YYYY-MM-DD HH:MM:SS

timestamp

timestamp 系统会自动创建默认值CURRENT_TIMESTAMP当前系统日期(该类型默认不会是空);

  • 一个表中只能有一个timestamp的类型(mysql5.7)
    • 如果创建两个字段都是这个类型，那么后一个字段会报错invalid default value；
    • 另外这个类型你不需要手动指定默认值，系统会自动给你加上；
    • 当然你要是手动指定的话，也必须指定是CURRENT_TIMESTAMP；
    • 所以一般要么直接用这个timestamp就行，不需要再指定默认值；
      • 你只需要指定 timestamp 系统自动给你添加为下面的这句话。
      • now timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  • timestamp 与时区也有关系
    • 插入日期的时候，会转化为本地时区后存放；
    • 从数据库取出来的时候，也会讲日期时间转化为本地时区后显示;
  • timestamp 的取值范围为 1970-01-01 08:00:01 到 2038年某一天
    • 范围比较小，因此不适合存储太久远的日期。
    • 如果超出，会存入0000-00-00 00:00:00
类型 字节 说明
datetime 8 字节 年-月-日 时:分:秒(0000-00-00 00:00:00)
timestamp 4 字节 (00000000000000)
year 1 字节 年(0000) 取值范围在1901~2155
date 3 字节 年月日(0000-00-00) 取值范围在 1000-01-01 到 9999-12-31
time 3 字节 时分秒(00:00:00)

【4】选择合适的数据类型

  1. char 与 varchar
    • 数据检索的时候，删除了char尾部的空格 ，保留了varchar的尾部空格；
    • char 固定长度，处理快；缺点 浪费存储空间；适用于 长度变化不大的，对于查询速度有一定要求的；
    • varchar 变长长度
      • Myisam：建议使用固定长度char
      • Innodb：建议使用变长长度varchar，内存存储有一个头指针指向数据列，数据行存储的是实际长度
  2. text 与 blob
    • 对于大文本存储 text存储文本格式，blob存储二进制；
    • 如果遇到大量删除，可能会在数据表中留下空洞，可以定期执行optimize table 表名来进行碎片整理；
    • 优化大文本检索
      • 采用前缀索引，对字段的前n字符创建索引；
      • 避免在查询的时候，直接用select * 对大文本进行查询，比较耗费网络输出结果；
      • 将大文本的列分出去一个表，本身的表采用固定长度数据格式，减少主表的碎片处理；
  3. float 与 decimal
    • 浮点型(float double real)与定点型(deciaml)
    • 浮点型，如果插入超过长度，会被四舍五入，到实际的精度值来进行插入。
      • 浮点型，容易出现误差，可以用定点类型来存储货币等精度要求高的值。避免浮点数直接运算
    • 定点型，是采用字符串存储的，更可以精准保证数据。
  4. datetime 与 timestamp
    • 如果存储时间久远，可以使用datetime范围更大一些。
    • 如果对不同时区，要显示一样，可以使用timestamp。

【5】字符集(character)

  **字符集(Character)**刚才讲到了字符类型的，例如char或者varchar等，我们也提及到了后面跟的小括号里面写的M是表示字节的意思，那么字符 与字节的之间对应关系，就是字符集的体现，简单来说，一个字母a需要几个字节去表示它。

  再深入来说一下就是，字符存入到计算机的要进行编码(⼀个字符映射成⼀个⼆进制数据的过程叫做编码)，读取出来的时候要进行解码(⼀个 ⼆进制数据映射到⼀个字符的过程叫做解码)。而字符集就是这样字符与字节之间的映射关系。例如常见支持中文的有，UTF8 、GBK等。最早奠基性的字符集是Ascii，包含128个字符，一个字符对应1个字节。

  校对规则(Collation) 或者叫做比较规则，用来定义比较字符串的方法。字符集和校对规则是一对多的关系。MySQL支持40多种字符集和200多种校对规则。每个字符集至少对应一种校对规则。例如常见的utf8对应utf8_general_ci。

  1. utf8字符集 收录地球上能想到的所有字符 变⻓编码⽅式 1～4个字节编码⼀个字符；

    • utf8mb3：阉割过的utf8字符集，只使⽤1～3个字节表示字符。(在MySQL中utf8是utf8mb3的别名)
    • utf8mb4：正宗的utf8字符集，使⽤1～4个字节表示字符。 ⽐如存储⼀些 emoji表情啥的，那请使⽤utf8mb4
    • 所以MySQL里面utf8的默认是utf8 一个字符占用3个字节。

  2. utf8_general_ci 通用的比较规则

    • _ci（ case insensitive ）不区分⼤⼩写
    • _cs （ case sensitive ） 区分⼤⼩写

  3. 查看字符集 与 校对规则

    • show charset; 或者 show character set;
    • show collation like 'utf8\_%';

  4. mysql的字符集和校对规则的四个级别

    • 服务器级别
      • 查看当前设置 show variables like '%_server';
      • 修改默认值
        • 方法1 my.cnf配置文件 添加k-v [mysqld] character-set-server=utf8 重启永久生效。
        • 方法2 服务器启动的时候指定启动选项 mysqld --character-set-server=utf8
    • 数据库级别
      • 查看当前设置 show variables like '%_database';
      • 修改默认值
        • 方法1 创建数据库的时候手动指定 create database xxx charset yyy collate zzz;
        • 方法2 利用alter进行修改 alter database 数据库名 charset xxx collate yyy;
      • 默认值：如果不设置对应的数据字符集，校对规则，那么就会使用当前的服务器级别的默认字符集和校对规则。
    • 数据表级别
      • 查看当前设置 show create table article;\G
      • 修改的话,可以利用alter进行修改当前的表
      • 默认值：是使用的数据库级别的默认值;
    • 行级别
      • 一般很少设置这个，只是一个灵活选项。
      • 同⼀个表中的不同的列也可以 有不同的字符集和⽐较规则
      • 默认值：将使⽤该列所在表的字符集和⽐较规则作为该列的字符集和⽐较规则

  5. 客户端与服务器连接字符集和校对规则

    • character_set_client客户端、 character_set_connection 连接 、character_set_results 结果集
    • 客户端请求服务器
      • 客户端发往服务器的请求 一个字符串；
      • 服务器解码：从character_set_client 转为 character_set_connection ；
      • 服务器向客户端返回的结果 字符集character_set_results ；
      • 如果某个列使⽤的字符集和character_set_connection代 表的字符集不⼀致的话，还需要进⾏⼀次字符集转换 ；
    • 黑窗中文乱码
      • 可以使用一条命令对其进行统一设置 set names utf8;
      • 也可以在配置文件中加入[mysql] default-character-set=utf8 保存 重启服务器。

打个总结

  1. 类型选择

    • 整数型
      • 例如性别 就可以选用tinyint 或者 enum。但实际上我们还是建议使用tinyint
      • 非负的值，可以使用unsigned 默认范围都是从负到正，各一半，但是例如主键id，我们可能不需要负数，就可以加上使其在正数范围扩大一倍。
      • 一般没啥特别说明，我主键一般使用的**int(11)**来进行存储。
    • 浮点数
      • 浮点数float在储存空间及运行效率上要优于精度数值类型decimal。
      • decimal可以提供更加准确的小数级精确运算不会有错误产生计算更精确，适用于金融类型数据的存储。
      • 综合考虑一般建议使用**decimal**定点型。
    • 字符串
      • 对于char(M) 比指定长度M大的值将被截短，而比指定长度小的值将会用空格作填补。如果MySQL表使用列格式row_format=fixed创建的话，会使用定长。这里如果看不懂的话，之后还会说明的。
      • 对于varchar(M) 只使用存储字符串实际需要的字节（增加一个额外字节来存储字符串本身的长度）来存储值，也即是M+1。如果超过指定的M个字节，那么也会截断。
      • 对于innoDB数据表,内部的行存储格式没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针)因此,主要的性能因素是数据行使用的存储总量。所以最好使用varchar，这个地方涉及到内部列的存储格式。
      • 例如姓名 一般姓名都是两个或者三个汉字，最多是四个汉字。这样我们可以使用 char(12) 这样，采用固定定长，可以减少碎片，提高存储效率。
      • text的类型，一般用于存储大文本的内容，例如文章内容等。删除操作会在数据表中留下很大的"空洞",为了提高性能,建议定期使用 optimize table 功能对这类表进行碎片整理。
      • 对于其他特殊的类型，enum set，我们提倡使用**tinyint**来替代，因为处理更快的时间、更省的空间以及更容易扩展。
    • 日期时间
      • 如果没有特别需求，一般我使用的**timestamp**，一方面存储比datetime占用字节小一倍，只占用4个字节；另外，MySQL还会默认为你的timestamp字段设置一个默认值属性current_timestamp并且为非空not null 和 on update CURRENT_TIMESTAMP；同时注意，当前修改这个记录的其他字段的时候，这个时间会更新。
      • 但是注意了，一个表里面只能有一个字段为timestamp。否则会报错Invalid default value
      • timestamp是UTC时间戳，与时区相关。存储范围较小。
      • MySQL的日期时间类型，最小支持秒级别的，所以如果想要微妙等，可以用int来保存时间戳microtime()。

  2. 选择类型的几个注意点
    选择恰当的数据类型，节省一定的存储空间，提高性能，有效的利用。但是你要真实的理解到你的字段的一个存储范围。

    • 尽量避免使用 NULL
      • 也就是说，最好在定义字段的时候，加上一个not null,在之后索引建立上，会性能更好，避免的判断null的过程。因为可能为NULL列使得索引，索引统计和值比较都更复杂
    • 尽量选用最小的占用空间
      • 一方面减少占用磁盘空间，也降低磁盘I/O的读写开销。减少内存占用。
    • 尽量选择用比较简单的数据类型
      • 最简单的也就是整数tinyint。整型比字符操作代价要小得多，因为字符集和校对规则(排序规则)使字符比整型比较更加复杂。

  3. 几种类型后面的(M)

    • 整数，例如 int(M) 这个M表示显示宽度范围，默认11。通常与zerofill配合使用，宽度不足可以补充0，使其在显示宽度上一致。 不影响存储。
    • 浮点数，例如 decimal(M,D) 这个M表示精度是多少个位，例如decimal(2,1)表示精度位是2个，小数位占一个，那么整数部分也是占1个
    • 字符类型，必带M，例如char(M) 这个M表示要存储多少字节，跟字符的关系是有一个字符集的概念，中文的utf8默认一个字符占用3个字节。