博客

##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×1−223×1−1

注：
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×2−1)

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×3−1)

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×4−1)
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×8−1)

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×n−1)

无符号
即

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×n−1)−(−223×n)=223×n+1−1

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为小数点后长度

2. double: 双精度实型，含字节数为8，64bit数值范围为(15个有效位):
   即

    

   −

   1.7

   E

   308

   −

   1.7

   E

   308

   \ -1.7E^{308} – 1.7E^{308}

    −1.7E308−1.7E308

3. 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) 取值范围的关键。

如何选择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

 216−1
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

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

4、bit

数据逻辑删除

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

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