mysql常用字段类型简介

##mysql字段类型

1、tinyint、smallint、mediumint、int、integer、bigint详解

1)tinyint:存储所占一个字节,一个字节等于8bit,根据1bit可以存储0到1两种可能性,因此tinyint类型可以存储2的8次方,也就是256种可能性,从0开始计数,无符号也就是可以 存储0 ~ 255,如果是有符号,那就是-128 ~ 127。 即   − 2 2 3 × 1 − 2 2 3 × 1 − 1 \ -2^{2^{3}×1} - 2^{2^{3}×1} -1  223×1223×11

注:
tinyint(1) 和 tinyint(3) 没什么区别,占用字节都是一位,存储范围都是一样的(都是无符号:0~255, 有符号:-128~127)

tinyint(3) zerofill ,当插入的数据少于3位的时候,左边自动补零,这才是限制显示长度
004

2)smallint:存储所占两个字节,同上也就是可以存储2的16次方,也就是可以存储65536种可能性,无符号从0开始则是可以存储0 ~ 65535,有符号则是-32768 ~ 32767。
  − 2 2 3 × 2 − ( 2 2 3 × 2 − 1 ) \ -2^{2^{3}×2} - (2^{2^{3}×2} -1)  223×2(223×21)

3)mediumint:存储所占三个字节,也就是2的24次方,可以存储16777216种可能性,无符号可以存储0 ~ 16777215,有符号可以存储-8388608 ~ 8388607。
  − 2 2 3 × 3 − ( 2 2 3 × 3 − 1 ) \ -2^{2^{3}×3} - (2^{2^{3}×3} -1)  223×3(223×31)

4)int:存储所占四个字节,也就是2的32次方,可以存储4294967296种可能性,无符号可以存储0 ~ 4294967295,有符号则是-2147483648 ~ 2147483647。
  − 2 2 3 × 4 − ( 2 2 3 × 4 − 1 ) \ -2^{2^{3}×4} - (2^{2^{3}×4} -1)  223×4(223×41)
5)bigint:存储所占8个字节,也就是2的64次方,可以存储2的64次种可能性,无符号可以存储0 ~ ((2³²×²)-1),有符号则是-(2³²×²)/2 ~ (2³²×²)/2-1。
  − 2 2 3 × 8 − ( 2 2 3 × 8 − 1 ) \ -2^{2^{3}×8} - (2^{2^{3}×8} -1)  223×8(223×81)

6)MySQL 数据类型中的 integer types 有点奇怪。你可能会见到诸如:int(3)、int(4)、int(8) 之类的 int 数据类型。刚接触 MySQL 的时候,你可能以为 int(3) 占用的存储空间比 int(4) 要小, int(4) 占用的存储空间比 int(8) 小。
MySQL 手册是这么说的 int(M): M indicates the maximum display width for integer types. 在 integer 数据类型中,M 表示最大显示宽度。

即:

int(2) => 10
int(3) => 010
int(5) => 00010

拓展: 我们可以从 以上的 信息中可总结出 整型的 数据类型存储大小与字节数的 公式

有符号的:(m=存储大小,n=字节数)
  − 2 2 3 × n − ( 2 2 3 × n − 1 ) \ -2^{2^{3}×n} - (2^{2^{3}×n} -1)  223×n(223×n1)

无符号
  m = ( 2 2 3 × n − 1 ) − ( − 2 2 3 × n ) = 2 2 3 × n + 1 − 1 \ m= (2^{2^{3}×n}-1) - (-2^{2^{3}×n}) = 2^{2^{3}×n+1}-1  m=(223×n1)(223×n)=223×n+11

2、float、double、decimal区别

float类型表示单精度浮点数值,double类型表示双精度浮点数值,float和double都是浮点型,而decimal是定点型;

  1. float: 浮点型,含字节数为4,32bit,数值范围为(7个有效):
-3.4E^{38} - 3.4E^{38} 

float(M,S) M为全长,S为小数点后长度

  1. double: 双精度实型,含字节数为8,64bit数值范围为(15个有效位):
      − 1.7 E 308 − 1.7 E 308 \ -1.7E^{308} - 1.7E^{308}  1.7E3081.7E308

  2. decimal:数字型,128bit,不存在精度损失,常用于银行帐目计算。(28个有效位)

怎么样才能存储一个准确的数据
如果采用float或者double类型的话,数据有时候完全准确的,有时候是不准确的,怎么才能存储一个准确的数字,完全看你需要存什么样的数据,假如存储一个8.25这样的数字,那永远都是准确的。但是如果存储0.9这样的数字,则永远存不准确。

所以如果一个实数在MySQL中存储准确的话,会出现以下三种情况

数据真的准确,数据能在有限的存储空间里完全存储起来

数据存储被截断,但是通过四舍五入依然能够将数据显示准确

数据存储被截断,通过四舍五入不能将数字正确显示

关于decimal类型

MySQL中支持浮点数的类型有FLOAT、DOUBLE和DECIMAL类型,DECIMAL 类型不同于FLOAT和DOUBLE,DECIMAL 实际是以串存放的。DECIMAL 可能的最大取值范围与DOUBLE 一样,但是其有效的取值范围由M 和D 的值决定。如果改变M 而固定D,则其取值范围将随M 的变大而变大。

对于精度比较高的东西,比如money,建议使用decimal类型,不要考虑float,double, 因为他们容易产生误差,numeric和decimal同义,numeric将自动转成decimal。

DECIMAL从MySQL 5.1引入,列的声明语法是DECIMAL(M,D)。在MySQL 5.1中,参量的取值范围如下:

~ M是数字的最大数(精度)。其范围为1~65(在较旧的MySQL版本中,允许的范围是1~254),M 的默认值是10。

~ D是小数点右侧数字的数目(标度)。其范围是0~30,但不得超过M。

说明:float占4个字节,double占8个字节,decimail(M,D)占M+2个字节。

如DECIMAL(5,2) 的最大值为9999.99,因为有7 个字节可用。

所以M 与D 是影响DECIMAL(M, D) 取值范围的关键。

类型说明取值范围(MySQL < 3.23)取值范围(MySQL >= 3.23)
DECIMAL(4,1)-9.9 到 99.9-999.9 到 9999.9
DECIMAL(5,1)-99.9 到 999.9-9999.9 到 99999.9
DECIMAL(6,1)-999.9 到 9999.9-99999.9 到 999999.9
DECIMAL(6,2)-99.99 到 999.99-9999.99 到 99999.99
DECIMAL(6,3)-9.999 到 99.999-999.999 到 9999.999
如何选择float,double,decimal

结论总是放在最后,根据上面的分析:可以得出以下结论

1、 如果你要表示的浮点型数据转成二进制之后能被32位float存储,或者可以容忍截断,则使用float,这个范围大概为要精确保存6位数字左右的浮点型数据

比如10分制的店铺积分可以用float存储,小商品零售价格(1000块之内)
2、 如果你要表示的浮点型数据转成二进制之后能被64位double存储,或者可以容忍截断,这个范围大致要精确到保存13位数字左右的浮点型数据

比如汽车价格,几千万的工程造价
3、 相比double,已经满足我们大部分浮点型数据的存储精度要求,如果还要精益求精,则使用decimal定点型存储
比如一些科学数据,精度要求很高的金钱

数据库中float转decimal类型时需要注意:

1、float转decimal时,decimal数值范围要设置的大于原来的float值。否则转换失败或者数据丢失。
2、decimal小范围转大范围没问题,但大范围转小范围会丢失数据。
3、float小范围转大范围没问题,大范围转小范围数据虽然不会丢失。但最好还是别用。
4、最好配置mysql的sql_mode为严格模式。能起到保护的作用。

mysql中char,varchar、text类型的区别和选用

关于char,varchar与text平时没有太在意,一般来说,可能现在大家都是用varchar。但是当要存储的内容比较大时,究竟是选择varchar还是text呢?不知道。。。。。。

text 、 char、varchar 是数据在数据库中的存放策略问题,为了,合理
应用存储空间,是数据库服务器数据类型划分的方式。对于应用程序,把它们
和string对应就可以了。

于是去查阅了一些资料,顺便将这三种类型做个比较:

(1)char: char不用多说了,它是定长格式的,但是长度范围是0~255. 当你想要储存一个长度不足255的字符时,MySQL会用空格来填充剩下的字符。因此在读取数据时,char类型的数据要进行处理,把后面的空格去除。
1)可以有默认值,
2)尾部有空格会被截断
3)不管汉字、英文,还是其他编码,都可以存255字符

(2)varchar: 关于varchar,有的说最大长度是255,也有的说是65535,查阅很多资料后发现是这样的:varchar类型在5.0.3以下的版本中的最大长度限制为255,而在5.0.3及以上的版本中,varchar数据类型的长度支持到了65535,也就是说可以存放65532个字节(注意是字节而不是字符!!!)的数据(起始位和结束位占去了3个字节),也就是说,在5.0.3以下版本中需要使用固定的TEXT或BLOB格式存放的数据可以在高版本中使用可变长的varchar来存放,这样就能有效的减少数据库文件的大小。

1)65535并不是一个很精确的上限,可以继续缩小这个上限,65535个字节包括所有字段的长度,变长字段的长度标识(每个变长字段额外使用1或者2个字节记录实际数据长度)、NULL标识位的累计NULL标识位,如果varchar字段定义中带有default null允许列空,则需要需要1bit来标识,每8个bits的标识组成一个字段一张表中存在N个varchar字段,那么需要(N+7)/8 (取整)bytes存储所有的NULL标识位
。如果数据表只有一个varchar字段且该字段DEFAULT NULL,那么该varchar字段的最大长度为65532个字节,即65535-2-1=65532 bytes,
在物理存储上,varchar使用1到2个额外的字节表示实际存储的字符串长度(bytes)。如果列的最大长度小于256个字节,用一个字节表示(标识)。如果最大长度大于等于256,使用两个字节。当选择的字符集为latin1,一个字符占用一个bytevarchar(255)存储一个字符,一共使用2个bytes物理空间存储数据实际数据长度和数据值。varchar(256)存储一个字符,使用2 bytes表示实际数据长度,一共需要3 bytes物理存储空间。
2)可以有默认值
3)尾部有空格不会截断

(3)text:与char和varchar不同的是,text不可以有默认值,其最大长度是
  2 16 − 1 \ 2^{16}-1  2161
1)text和varchar基本相同
2)text会忽略指定的大小这和varchar有所不同,text不能有默认值
3)尾部有空格不会被截断

4)text使用额 外的2个字节来存储数据的大小,varchar根据存储数据的大小选择用几个字节来存储

5)text的65535字节全部用来存储数据,varchar则会 占用1-3个字节去存储数据大小

总结起来,有几点:

经常变化的字段用varchar

知道固定长度的用char

尽量用varchar

超过255字符的只能用varchar或者text

能用varchar的地方不用text

类型大小用途
CHAR0-255字节定长字符串
VARCHAR0-65535 字节变长字符串
TINYBLOB0-255字节不超过 255 个字符的二进制字符串
TINYTEXT0-255字节短文本字符串
BLOB0-65 535字节二进制形式的长文本数据
TEXT0-65 535字节长文本数据
MEDIUMBLOB0-16 777 215字节二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215字节中等长度文本数据
LONGBLOB0-4 294 967 295字节二进制形式的极大文本数据
LONGTEXT0-4 294 967 295字节极大文本数据

3、date、time、year、datetime、 TIMESTAMP区别

类型大小范围格式用途
DATE3字节1000-01-01
/
9999-12-31
YYYY-MM-DD日期值
TIME3字节‘-838:59:59’
/
‘838:59:59’
HH:MM:SS时间值或持续时间
YEAR1字节1901/2155YYYY年份值
DATETIME8字节1000-01-01 00:00:00
/
9999-12-31 23:59:59
YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP4字节1970-01-01 00:00:00/2038YYYYMMDD HHMMSS结束时间是第 2147483647 秒,
北京时间 2038-1-19 11:14:07,
格林尼治时间 2038年1月19日
凌晨 03:14:07 混合日期和时间值,时间戳

4、bit

类型大小用途
bit1个二进制的位bit(8) 表示8个二进制的位
性别可以定义为0,1,
而不使用male或female字符串

数据逻辑删除

某辆车在车库中停放的状态

所有基于两种状态的数据都可以使用0,1来存储.

  • 1
    点赞
  • 10
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

漏墨小子

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值