Mysql之数据类型

一、整形的显示宽度

　　在mysql的数据类型中有如下定义，

整型类型(显示宽度)

　　注意，正确的理解，其实是显示宽度，因为整型的显示宽度，对数值大小无影响，只是当设置了zerofill的时候，在显示的时候补0而已。

　　

从上面的显示我们看到，设置了宽度为5，并且zerofill了，这样在查询的时候，会在前面自动补0。宽度可以不设置，因为整数数据类型都有默认的显示宽度，不设就采用默认的。

 　　另外，即使设置了显示宽度，也能赋超过显示宽度的值，整型的显示宽度仅仅用于显示补0。

　

二、浮点型与定点型

　　在mysql的数据类型中浮点型分为两种，float()与double()类型，定点型为decimal()。

数据类型(M,D)

• M：精度，数据的总长度；
• D：标度，小数点后的长度；

　　其区别在于：

1. 当不指定精度时，Float、Double默认会保存实际精度，而Decimal默认是整数；
2. 当标度不够时，都会四舍五入，但Decimal会警告信息。

　　

unsigned   既为非负数，用此类型可以增加数据长度!

例如如果    tinyint最大是127，那    tinyint    unsigned    最大   就可以到    127 * 2

unsigned 属性只针对整型，而binary属性只用于char 和varchar。

类型	说明
tinyint	非常小的整数
smallint	较小整数
mediumint	中等大小整数
int	标准整数
bigint	较大整数
float	单精度浮点数
double	双精度浮点数
decimal	一个串的浮点数

每种数值类型的名称和取值范围如表2所示。

类型说明	取值范围
tinyint[(m)]	有符号值：-128 到127（- 27 到27 - 1） 无符号值：0到255（0 到28 - 1） 1个字节
smallint[(m)]	有符号值：-32768 到32767（- 215 到215 - 1） 无符号值：0到65535（0 到21 6 - 1） 2个字节
mediumint[(m)]	有符号值：-8388608 到8388607（- 22 3 到22 3 - 1 ） 无符号值：0到16777215（0 到22 4 - 1） 3个字节
int[(m)]	有符号值：-2147683648 到2147683647（- 231 到231- 1） 无符号值：0到4294967295（0 到232 - 1） 4个字节
bigint[(m)]	有符号值：-9223372036854775808 到9223373036854775807（- 263到263-1） 无符号值：0到18446744073709551615（0到264 – 1） 8个字节
float[(m, d)]	最小非零值：±1.175494351e - 38
double[(m,d)]	最小非零值：±2.2250738585072014e - 308
decimal (m, d)	可变；其值的范围依赖于m 和d

表2：数值列类型的取值范围

各种类型值所需的存储量如表3所示。

类型说明	存储需求
tinyint[(m)]	1字节
smallint[(m)]	2字节
mediumint[(m)]	3字节
int[(m)]	4字节
bigint[(m)]	8字节
float[(m, d)]	4字节
double[(m, d)]	8字节
decimal (m, d)	m字节（mysql < 3.23），m+2字节（mysql > 3.23 ）

表3：数值列类型的存储需求

mysql提供了五种整型： tinyint、smallint、mediumint、int和bigint。int为integer的缩写。这些类型在可表示的取值范围上是不同的。 整数列可定义为unsigned从而禁用负值；这使列的取值范围为0以上。各种类型的存储量需求也是不同的。取值范围较大的类型所需的存储量较大。

mysql 提供三种浮点类型： float、double和decimal。与整型不同，浮点类型不能是unsigned的，其取值范围也与整型不同，这种不同不仅在于这些类型有最大 值，而且还有最小非零值。最小值提供了相应类型精度的一种度量，这对于记录科学数据来说是非常重要的（当然，也有负的最大和最小值）。

三、日期与时间类型

　　MySQL日期时间所有种类如下：

类型	字节数	取值范围	零值
YEAR	1	1901~2155	0000
DATE	4	1000-01-01~9999-12-31	0000:00:00
TIME	3	-838:59:59~838:59:59	00:00:00
DATETIME	8	1000-01-01 00:00:00~9999-12-31 23:59:59	0000-00-00 00:00:00
TIMESTAMP	4	1970-01-01 08:00:01~2038-01-19 11:14:07	00000000000000

　　DATE类型前面可以加个D，D表示天即24小时，如往DATE类型插入 '2 15:20:20'；

　　

另外TimeStamp类型与DateTime在无输入的情况下要注意下：

1. TimeStamp使用Current_TimeStamp()而DateTime使用NOW(来获取当前时间)；
2. 输入NULL时，系统会输入系统当前日期与时间；
3. 无任何输入时，系统会输入系统当前日期与时间；

四、字符串类型

　　字符串类型包括CHAR、VARCHAR、BLOB、TEXT、ENUM、SET。

　　1、CHAR与VARCHAR

　　其定义方式为：

字符串类型(M)

　　这个M是长度的意思，插入该列的字符串长度将不允许超过M所指定的长度。另外，由于MySQL在建库时就指定了字符集，因此就不存在nchar、nvarchar、ntext这种数据类型了。

• char最长可取255；
• varchar最长可取65535；

　　2、TEXT

　　TEXT分为4种，与SQLServer不同：

类型	允许的长度	存储空间
TINYTEXT	0~255字节	值的长度+2字节
TEXT	0~65535字节	值的长度+2字节
MEDIUMTEXT	0~167772150字节	值的长度+3字节
LONGTEXT	0~4294967295字节	值的长度+4字节

 　3、ENUM类型

　　ENUM类型(枚举类型)，与C#的概念一样，在定义时指定取值范围。

属性名 ENUM('值1','值2','值3'...'值n')

• ENUM有NOT NULL属性，其默认值为取值列表的第一个元素；
• ENUM无NOT NULL，则ENUM类型将允许插入NULL，并且NULL为默认值；

　　

　　CREATE TABLE Test4(Sex ENUM('男','女'));
　　INSERT INTO Test4 VALUES('男');
　　INSERT INTO Test4 VALUES('爷');    --这行报错
　　SELECT * FROM Test4;

　　4、SET类型

　　在创建表时，就指定SET类型的取值范围。

属性名 SET('值1','值2','值3'...,'值n')

　　它与ENUM的区别在什么地方呢？

　　

　　基本上就是多选的ENUM。

　　5、二进制类型

　　二进制类型是在数据库中存储二进制数据的数据类型。二进制类型包括BINARY、VARBINARY、BIT、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。

类型	取值范围
BINARY(M)	字节数为M，允许长度为0~M的定长二进制字符串
VARBINARY(M)	允许长度为0~M的变长二进制字符串，字节数为值的长度加1
BIT(M)	M位二进制数据，M最大值为64
TINYBLOB	可变长二进制数据，最多255个字节
BLOB	可变长二进制数据，最多2的16次方-1个字节
MEDIUMBLOB	可变长二进制数据，最多2的24次方-1个字节
LONGBLOB	可变长二进制数据，最多2的32次方-1个字节

　　1、BINARY和VARBINARY

　　两者唯一的差别在于BINARY当长度不够时会补。

　　2、BIT类型

　　BIT类型与SQLServer里的就差别大了。 其定义方式为：

BIT(M)

　　其中"M"指定了该二进制的最大字节长度为M，M的最大值为64。如BIT(4)就是数据类型为BIT类型，长度为4。其能够存储的值为0-15。因为变成二进制后，15的值为1111。

　　在查询BIT类型的数据时，要用BIN(字段名+0)来将值转换为二进制显示。

　　

CREATE TABLE Test6(BB BIT(4));
INSERT INTO Test6 VALUES(12);
SELECT BIN(BB+0) FROM Test6;

　　上面的结果以二进制显示，最大的长度为4。

　　3、BLOB类型

　　BLOB类型是一种特殊的二进制类型。BLOB可以存储数据量很大的二进制数据，如图片，视频等。BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们之间的区别也只是最大长度不同。

五、数据类型的选择

1. 整型：根据要显示的最大值决定；
2. 浮点型：要显示小数。如果要精确到小数点后10位，就选择DOUBLE，而不应该选择FLOAT。DECIMAL精度较高，浮点数会出现误差，如果精度较高，则应选择定点数DECIMAL；
3. 字符串型：定长与变长的区别，CHAR类型占用空间比较大，但是处理速度比VARCHAR快，如果长度变化不大，如身份证号码那种，最好选择CHAR类型。而对于评论字符串，最好选择VARCHAR；
4. 时间：根据需要显示的类型咯，特别是TIMESTAMP,如果需要显示的时间与时区对应，就应该选择TIMESTAMP；
5. ENUM类型和SET类型：长度不同，ENUM类型最多可以由65535个成员，而SET类型最多只能包含64个成员。且ENUM只能单选，而SET类型可以多选；
6. TEXT类型和BLOB类型：TEXT只能存储字符数据，而BLOB可以存储二进制数据。如果是纯文本，适合TEXT。如果是图片等适合存二进制； 

六、问题总结

　　1、存储路径的问题

　　MySQL中，如果路径中使用""符号时，这个符号会被过滤。解决的办法是路径中用"/"或"\"来代替""。这样MySQL就不会自动过滤路径中的分隔符。

　　2、MySQL中的布尔类型

　　MySQL中没有Bool或Boolean类型，但是为了支持SQL标准，也可以定义Bool或Boolean类型的，但是Bool或Boolean类型最后转换成的是TinyInt(1)，也就是说,在MySQL中，布尔类型实际上是TinyInt(1)。

　　3、MySQL中如何存储JPG图片或MP3音乐

　　一般情况下，数据库中不直接存储图片和音频文件，而是存储图片或音频文件的路径，如果在特殊情况下需要在MySQL数据库中存储图片和音频文件，可以选择BLOB类型；