MySQL学习笔记进阶篇（八）之数据类型

MySQL数据类型非常的丰富，接下来介绍一下MySQL常用的数据类型。

数值类型

整型
   类型         字节     范围（有符号位）
   tinyint     1字节    -128 ~ 127      无符号位：0 ~ 255
   smallint    2字节    -32768 ~ 32767
   mediumint   3字节    -8388608 ~ 8388607
   int         4字节
   bigint      8字节
   int(M)  M表示总位数
   - 默认存在符号位，unsigned 属性修改
   - 显示宽度，如果某个数不够定义字段时设置的位数，则前面以0补填，zerofill 属性修改
       例：int(5)   插入一个数'123'，补填后为'00123'
   - 在满足要求的情况下，越小越好。
   - 1表示bool值真，0表示bool值假。MySQL没有布尔类型，通过整型0和1表示。常用tinyint(1)表示布尔型。

 

浮点型
   类型             字节
   float(单精度)     4字节
   double(双精度)    8字节
  
   定义浮点型时，需指定总位数和小数位数。
       如：float(M, D)     double(M, D)
       M表示总位数，D表示小数位数。
       M和D的大小会决定浮点数的范围。不同于整型的固定范围。
       M既表示总位数（不包括小数点和正负号），也表示显示宽度（所有显示符号均包括）。
       支持科学计数法表示。
       浮点数表示近似值。
 

定点数
   decimal -- 可变长度
   decimal(M, D)   M也表示总位数，D表示小数位数。
   保存一个精确的数值，不会发生数据的改变，不同于浮点数的四舍五入。
   将浮点数转换为字符串来保存，每9位数字保存为4个字节。

字符串类型

char, varchar 
   char    定长字符串，速度快，但浪费空间
   varchar 变长字符串，速度慢，但节省空间
   M表示能存储的最大长度，此长度是字符数，非字节数。
   不同的编码，所占用的空间不同。
   char,最多255个字符，与编码无关。
   varchar,最多65535字符，与编码有关。
   一条有效记录最大不能超过65535个字节。utf8 最大为21844个字符，gbk 最大为32766个字符，latin1 最大为65532个字符
   varchar 是变长的，需要利用存储空间保存 varchar 的长度，如果数据小于255个字节，则采用一个字节来保存长度，反之需要两个字节来保存。
   varchar 的最大有效长度由最大行大小和使用的字符集确定。
   最大有效长度是65532字节，因为在varchar存字符串时，第一个字节是空的，不存在任何数据，然后还需两个字节来存放字符串的长度，所以有效长度是64432-1-2=65532字节。

blob, text
   blob 二进制字符串（字节字符串）
       tinyblob, blob, mediumblob, longblob
   text 非二进制字符串（字符字符串）
       tinytext, text, mediumtext, longtext
   text 在定义时，不需要定义长度，也不会计算总长度。
   text 类型在定义时，不可给default值
   longtext可以存储几百兆的大小，如果很大可以使用这个数据类型。
binary, varbinary
   类似于char和varchar，用于保存二进制字符串，也就是保存字节字符串而非字符字符串。
   char, varchar, text 对应 binary, varbinary, blob.

日期时间类型

   datetime    8字节    日期及时间     1000-01-01 00:00:00 到 9999-12-31 23:59:59
   date        3字节    日期         1000-01-01 到 9999-12-31
   timestamp   4字节    时间戳        19700101000000 到 2038-01-19 03:14:07
   time        3字节    时间         -838:59:59 到 838:59:59
   year        1字节    年份         1901 - 2155
datetime    YYYY-MM-DD hh:mm:ss
timestamp  YY-MM-DD hh:mm:ss
                  YYYYMMDDhhmmss
                  YYMMDDhhmmss
                  YYYYMMDDhhmmss
                  YYMMDDhhmmss
date           YYYY-MM-DD
                  YY-MM-DD
                 YYYYMMDD
                 YYMMDD
                 YYYYMMDD
                 YYMMDD
time        hh:mm:ss
               hhmmss
               hhmmss
year        YYYY
               YY

集合

集合（set）

在创建表时，性别，民族，省份这样可以以集合形式存在的字段可以使用set。
set(val1, val2, val3...)
   create table tab ( gender set('男', '女', '无') );
   insert into tab values ('男, 女');
   最多可以有64个不同的成员。以bigint存储，共8个字节。采取位运算的形式。
   当创建表时，SET成员值的尾部空格将自动被删除。

在选择数据类型时，尽量选择存储空间小执行效率较高的，例如如果你要是存储性别，可以选择char类型的或者set来存储，因为性别只有男女或不详，在英文中只有'male'，'female'，’unknow'这三种值，没有必要存储在varchar中，由于char类型的速度要快于varchar，所以最好选用char(6)来存储性别。由于double和float会损失精度，不适合存储金融项目中的金钱之类的要求精度的数据。