Mysql的字段类型详解

一、正数类型

首先介绍正数类型，正数类型分为inyint，smallint，mediumint，int 与 bigint，他们的区别如下：

整数类型     名称              字节                取值范围
tinyint(m)   迷你型        占 1 个字节              128~127
samllint(m)  小整型        占 2 个字节             32768~32767
mediumint(m) 中整型        占 3 个字节           8388608~8388607
int(m)       标准整型      占 4 个字节         2147483648~2147483647
bigint(m)    大整型        占 8 个字节    -9223372036854775808~9223372036854775807

区别：就是在磁盘中占的字节数不同

A、容纳数据范围不同

1、tinyint(M) M默认为4，存储占1个字节

     带符号的范围是-128到127。无符号的范围是0到255。

2、smallint(M) M默认为6，存储占2个字节

     带符号的范围是-32768到32767；无符号的范围是0到65535。

3、mediumint(M) M默认为11，存储占3个字节

     带符号的范围是-8388608到8388607；无符号的范围是0到16777215。

4、int(M) M默认为11，存储占4个字节

     带符号的范围是-2147483648到2147483647；无符号的范围是0到4294967295。

5、bigint(M) M默认为20，存储占8个字节

      带符号的范围是-9223372036854775808到9223372036854775807；无符号的范围是0到18446744073709551615。

B、存储大小不同

       smallint 存储占2 个字节，int 存储占4字节，所以在考虑最大数据范围的前提下，为了节省磁盘空间，优先选用smallint类型。

二、小数类型

其次看下小数类型区别，分别是Float、Double 、Decimal

小数类型
Float                  Double                    Decimal
Float(m,n)             Double(m,n)               Decimal(m,n)
7个有效位              15个有效位                28个有效位
Float(5,2)999.99       Decimal(5,2)999.99        Decimal(5,2)999.99 

1、Float与Double区别 

float  单精度 存储4x8=32位的长度 内存中占 4 个字节 
double 双精度 存储8x8=64位的长度 内存中占 8 个字节 

float  用 32 位二进制描述 小数点后有效位数7位 
double 用 64 位二进制描述 小数点后有效位数16位 



Float： 需要小数部分并且对精度的要求不高时，选择float。
Double：需要保持多次反复迭代的计算精确性时，或在操作值很大的数字时，选择double，
        双精度用来进行高速数学计算、科学计算、卫星定位计算等处理器上双精度型实际
		上比单精度的快

2、Decimal类型

decimal：数据存储形式是，将每9位十进制数存储为4个字节
decimal：要存储金额（3888.00元）的字段，需要用到数据类型decimal

3、范围

double(M,D)、float(M,D)、decimal(M,D)
m代表数字位数，d代表保留小数个数。

三、字符串类型

CHAR(m)        0-255               定长字符串
VARCHAR(m)     0-65535             变长字符串
TEXT           0-65535             长文本数据
LONGTEXT       0-4294967295        极大文本数据
BLOB           0-65535             二进制形式的长文本数据
LONGBLOB       0-4294967295        二进制形式的极大文本数据

A、CHAR与VARCHAR的区别

1. CHAR是定长的，范围是0~255，char会开辟固定的长度，值超出范围将被截短，值小于范围的用空格填充。
2. VARCHAR是可变长度，范围0~65535，varchar是数据占多长就多长，不会来填补，但是会多一个字节来存储其字符的长度
3. 它们之间的不同处在于MySQL数据库处理指示器的方式，CHAR把这个大小视为值的大小，在长度不足的时用空格补足。而VARCHAR类型把它是为最大值，并且只存储字符串实际需要的长度（增加一个额外字节来存储字符串本身的长度）来存储值，所以短于指示器长度的VARCHAR类型不会被空格填补，但长于指示器的值仍然会被截短。
4. VARCHAR类型可以根据实际内容动态改变存储值的长度，因此在不能确定字段需要多少字符时使用VARCHAR类型可以有效地节约磁盘空间、提高存储效率。
5. CHAR的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节；
6. VARCHAR的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节，两者的存储数据都非unicode的字符数据。
    

B、TEXT类型与BLOB类型相同点

1. 在TEXT或BLOB列的存储或检索过程中不存在大小写转换，当未运行在严格模式下时，若为TEXT或BOLB列分配一个超过该列类型的最大长度值时，则值会被截取。若截取的字符不是空格，将提示一条警告信息。
2. TEXT和BOLB列不能有默认值。
3. 在保存或检索TEXT和BLOB列的值时不删除尾部空格。
4. 对于TEXT和BLOB列的索引，必须指定索引前缀的长度。

C、TEXT类型与BLOB类型的不同点

1. TEXT值对大小写不敏感，而BLOB对大小写敏感。
2. TEXT被视为非二进制字符串，而BLOB被视为二进制字符串。
3. TEXT根据字符集的校对规则对值进行排序和比较，BLOB列没有字符集。
4. 可将TEXT列视为 VARCHAR列，将BLOB列视为VARBINARY列。
5. BLOB可以存储图片，而TEXT只能存储纯文本文件。

四、日期类型 

类型       字节        范围                                格式                 用途
DATE      3 字节  1000-01-01/9999-12-31                    YYYY-MM-DD           日期值
TIME      3 字节  ’-838:59:59’/838:59:59’                  HH:MM:SS             时间值
YEAR      1 字节  1901/2155                                YYYY                 年份值
DATETIME  8 字节  1000-01-01 00:00:00/9999-12-31 23:59:59  YYYY-MM-DD HH:MM:SS  混合日期和时间值
TIMESTAMP 4 字节  1970-01-01 00:00:00                      YYYYMMDD HHMMSS      混合日期和时间值，时间戳

1、Year类型

       用于存储年，存储时用一个字节，插入数据时可以使用各种格式指定YEAR值（非常节约内存，只需要年的时候，此字段合适）。

       常见插入格式:

1. 四位字符串或者数字，范围为"1901"~"2155"，写多少即为多少（推荐甚至强制要求必须）；
2. 两位字符串格式:范围为"00"-"99"，"00"-"69"被转化为20**（例如:"16"转化为"2016"），"70"-"99"被转化为19**（下同）；
3. 两位数字格式，范围为199，169被转化为20**（例如:1转化为2001），70~99被转化为19**。

2、Time类型

     time用于存储时间信息，存储时需要三个字节。

     常见插入格式:

1. 字符串格式:'HH:MM:SS','HH:MM','D HH:MM','D HH','SS'等形式。举个例子，输入'30'，Time类型会自动转换为00:00:30（推荐甚至强制要求必须）。
2. HHMMSS'格式的字符串或HHMMSS格式的数值表示，例如，输入'123456'，Time类型会转换成12:34:56；输入123456，如果输入0或者'0'，那么TIME类型会转换为0000:00:00。
3. 'D HH:MM:SS'格式的字符串表示。其中，D表示天数，取值范围是0~~34。保存时，小时的值等于（D*24+HH）。举个例子，输入'2 11:30:50'，Time类型会转换为59:30:50。

     使用current_time或者current_time()或者now()输入当前系统时间。（一般用于默认值）

3、Date类型

     date用于存储日期，没有时间部分，存储时需要三个字节。

     常见插入格式：

1. 'YYYY-MM-DD'或'YYYYMMDD'格式的字符串表示，这种方式可以表达的范围是'1000-01-01'~'9999-12-31'（推荐甚至强制要求必须）；
2. 如'YYYY/MM/DD'，'YYYY@MM@DD'，'YYYY.MM.DD'等分隔形式。举个例子，输入'2011.3.8'，date类型将转换为2011-03-08；
3. 'YY-MM-DD'或者'YYMMDD'格式的字符串表示，其中'YY'的取值，'00'-'69'转换为20002069，'70'-'99'转换为19701999。与year类型类似。

     使用current_date或now()来输入当前系统时间。（一般用于默认值）

4、Datetime类型

     datetime类型使用8个字节来表示日期和时间。  

     常见插入格式：

1. 'YYYY-MM-DD HH:MM:SS'或'YYYYMMDDHHMMSS'格式的字符串表示。范围是'1000-01-01 00:00:00'~'9999-12-31 23:59:59'（推荐甚至强制要求必须）；
2. 如'YYYY/MM/DD'，'YYYY@MM@DD'，'YYYY.MM.DD'等分隔形式。情况与date类型相同，且时间部分也可使用任意的分隔符隔开(注意：Time类型只能用':'隔开)。

    使用now()来输入当前系统日期和时间。

5、timestamp类型

      timestamp类型使用4个字节来表示日期和时间。

1、timestamp与dateTime区别

• timestamp存储需要四个字节，时间是根据时区来显示，取值范围为"1970-01-01 00:00:01" UTC ~ "2038-01-19 03:14:07" 
• datetim  存储需要八个字节，时间不根据时区来显示，取值范围为"1000-01-01 00:00:00" ~ "9999-12-31 23:59:59"

• timestamp和时区有关
• datetime 和时区无关，怎么存入怎么返回，对程序员友好

• timestamp类型的范围太小，最大时间为2038-01-19 11:14:07，插入时间的比这个大，将会数据库插入0000-00-00 00:00:00
• datetime 类型的范围较大，选择dateTime类型比较安全。

MySQL中表示当前时间的其他方式

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP()
NOW()
LOCALTIME
LOCALTIME()
LOCALTIMESTAMP
LOCALTIMESTAMP()