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()


版权声明:本文为qq_41946216原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。