数值类型主要用来存储数字,不同的数值类型提供不同的取值范围,可以存储的值范围越大,所需要的存储空间也越大; 数值类型分为:①整数类型 ②浮点数类型 ③定点数类型。
1、整数类型如下:
示例:
mysql> create table t1(
-> m tinyint,
-> n smallint,
-> x mediumint,
-> y int,
-> z bigint unsigned # 默认是有符号的列,unsigned表示无符号列
-> );
查看表的详细信息如下(在创建表的时候没有指定其长度,但是每一列都有自己默认的长度):
2、浮点数类型和定点数类型
优化建议:
示例1:
#新建一个表,值的长度都为5,小数点后都是两位
mysql> create table tab2(
-> x float(5,2),
-> y double(5,2),
-> z decimal(5,2)
-> );
#插入一些正常符合要求的数据,并无报错
mysql> insert into tab2 values(123.45,123.45,123.45);
#插入一些不符合规定的数据,会返回1个warning信息
mysql> insert into tab2 values(123.456,123.456,123.456);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings; # 查看warning信息,提示z列有截断的数据
+-------+------+----------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------+
| Note | 1265 | Data truncated for column 'z' at row 1 |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)
上述示例插入的数据,实际显示如下(这里会有两个123.46,是我不小心多插入了一边数据,所以可忽略多出的一行):
通过实际插入的数据不难发现,如果插入不符合列规定的数据,那么最终会以四舍五入的方法处理。
需要注意的是,在上面的数值类型中,它只允许在小数点后面多一位,而不允许在小数点之前多一位,如插入1234.5或1234.35就会报错。
示例2:
mysql> create table tab3( # 创建多个列,长度都为10,小数点后面有两位
-> x float(10,2),
-> y double(10,2),
-> z decimal(10,2)
-> );
mysql> insert into tab3 values(12345678.123,12345678.123,12345678.123);
Query OK, 1 row affected, 1 warning (0.01 sec)
#同样会返回warning信息,提示z列有截断数据
最终插入到表中的数据如下:
在上面的表中,x列为float数值类型,其他两列的数值还是基于四舍五入的方法进行插入的,但是float数值类型的x列,插入的数据和实际输入的数据就有些出入了,并且会随着小数点位数的增加,这个浮动范围会更大。 3、日期和时间类型 表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性。
优化建议:
YEAR
格式1:以4位字符串格式表示的 YEAR ,范围为 '1901' ~ '2155'; 格式2:以4位数字格式表示的 YEAR ,范围为 1901 ~ 2155; 格式3:以2位字符串格式表示的 YEAR ,范围为 '00' ~ '99' ,其中,'00' ~ '69' 被转换为 2000 ~ 2069 ,'70' ~ '99' 被转换为 1970 ~ 1999; 格式4:以2位数字格式表示的 YEAR ,范围为 1 ~ 99 ,其中,1 ~ 69 被转换为 2001 ~ 2069 ,70 ~ 99 被转换为 1970 ~ 1999。
例:
mysql> create table tab4(x year); # 新建一个表,只有一列,数值类型为year
mysql> insert into tab4 values('2000'),(2000),('96'),(96); # 依次插入四位的字符、数值;两位的字符、数值
插入的数据如下:
mysql> delete from tab4; # 删除原有数据
mysql> insert into tab4 values('0'),(0),('00'),(00); # 插入一些不符合列规定的数值
当插入的年份不合法时,会用0000表示。 当插入的年份不合法时,会用0000表示。 当插入的年份不合法时,会用0000表示。
2)TIME
例:
mysql> create table tab5(date time);
mysql> insert into tab5 values('12:12:12'),(121212),(3),('3 10:2'),(14),('08:08');
上述插入的数据,基本可以对应SQL语句来看出来其规律,唯一需要解释的,应该就是“82:02:00”,对应的插入值是“3 10:2”,最中写入表中的时间是3天(3 X 24)+10小时,零2分钟,也就是82个小时零两分钟。 3)DATE
例:
mysql> create table tab6(t date); # 创建一个表,列的数据类型为date
mysql> insert into tab6 values('1999-09-09'),(990909),(19990909); # 插入不同格式的日期
插入的结果如下:
其实,对于date这一种数值类型,对于其格式并没有严格的要求,如2019-12-12这样的数值可以插入成功,2000!10:10这样的数值同样可以插入成功,如下:
4)DATETIME
例:
mysql> create table tab7(dt datetime);
mysql> insert into tab7 values('1996-09-19 12:24:56');
5)TIMESTAMP
字符串类型用来存储字符串数据,还可以存储比如图片和声音的二进制数据; MySQL 支持两种字符串类型:文本字符串和二进制字符串。
优化建议:
1、char和varchar
例:
#新建一个表,字符类型分别为char和varchar
mysql> create table tab8(c char(4),vc varchar(4));
#插入数据,每条数据都是两个字母加两个空格
mysql> insert into tab8 values('ab ','ab ');
调用length函数,查看插入的数据长度:
调用紧凑函数,查看其实际数据:
可以验证了,如果是char类型的列,尾部的空格会被删除掉,如果是varchar类型的列,空格不会被删除掉,而是一个空格占一个位。
需要注意的是,如果是char类型的列,假如定义数值的长度为4,那么就算插入的数值长度只有2,它还是会占4个长度的空间,而varchar则不会,因为前者属于不可变长度的数值类型,而后者是可变的。 2、TEXT
TINYTEXT 最大长度为 255 个字符; TEXT 最大长度为 65536 个字符; MEDIUMTEXT 最大长度为 16777215 个字符; LONGTEXT 最大长度为 4294967295 个字符。
3、ENUM 在基本的数据类型中,无外乎就是些数字和字符,但是某些事物是较难用数字和字符来准确地表示的。比如一周有七天,分别是Sunday、Monday、Tuesday、Wednesday、Thursday、Friday 和 Saturday。如果我们用整数 0、1、2、3、4、5、6 来表示这七天,那么多下来的那些整数该怎么办?而且这样的设置很容易让数据出错,即取值超出范围。我们能否自创一个数据类型,而数据的取值范围就是这七天呢?因此有了 ENUM 类型(Enumeration,枚举),它允许用户自己来定义一种数据类型,并且列出该数据类型的取值范围。ENUM 是一个字符串对象,其值为表创建时在列规定中枚举(即列举)的一列值,语法格式为:字段名 ENUM ('值1', '值2', ..... '值n') 字段名指将要定义的字段,值 n 指枚举列表中的第 n 个值,ENUM类型的字段在取值时,只能在指定的枚举列表中取,而且一次只能取一个。如果创建的成员中有空格时,其尾部的空格将自动删除。ENUM 值在内部用整数表示,每个枚举值均有一个索引值:列表值所允许的成员值从 1 开始编号,MySQL 存储的就是这个索引编号。枚举最多可以有 65535 个元素。 例1:
#创建一个表,类型为enum,默认值依次为first、second、third
mysql> create table tab9(
-> enm enum('first','second','third')
-> );
#插入数据
mysql> insert into tab9 values('first'),('third'),('second');
正常查看插入的数据如下:
通过下面的方法,查看出每个值所对应的枚举的值,如下:
例2:
#创建两个列的表,第二列为enum类型
mysql> create table tab10(
-> soc int,
-> level enum('excellent','good','bad')
-> );
#插入数据测试,第二列的值,可以直接写枚举中包含的值,也可以通过所在位数来调用
mysql> insert into tab10 values(70,'good'),(90,1),(75,2),(50,3);
#如果插入一个没有定义过的枚举值则会报错,如下:
mysql> insert into tab10 values(70,'best'),(90,1),(75,2),(50,4);
ERROR 1265 (01000): Data truncated for column 'level' at row 1
查看最终表中的值(只有第一个语句插入的值):
4、SET
例:
#创建一个表,数据类型为set,并且自定义set字段的值
mysql> create table tab11(s set('a','b','c','d'));
#只能插入自定义的set字段的值
mysql> insert into tab11 values('a'),('b,c,a'),('a,b,a');
#如果插入没有定义的值,则会报错
mysql> insert into tab11 values('d,g,s');
ERROR 1265 (01000): Data truncated for column 's' at row 1
查看表中的顺序,发现已经把值去重并且将顺序排列好了,如下:
5、BIT
例:
#创建一个表,数据类型为bit,宽度为4,也就是说,最多只能插入16以下的数据
mysql> create table tab12(b bit(4));
#插入正常的数据
mysql> insert into tab12 values(2),(9),(15);
#插入大于15以上的数据就会报错
mysql> insert into tab12 values(2),(9),(18);
ERROR 1406 (22001): Data too long for column 'b' at row 3
查看表中最终插入的数据(二进制类型的值,需要用以下语句查看,可以看到,只有第一条sql语句成功插入了):
6、BINARY 和 VARBINARY
例:
#创建一个表,数据类型分别为binary和varbinary
mysql> create table tab13(
-> b binary(3),
-> vb varbinary(30)
-> );
#插入数据测试
mysql> insert into tab13 values(5,5);
也可以通过以下语句进行查看对比:
mysql> select b,vb='0\0\5',b='5\0\0',vb='5',vb='5\0\0' from tab13;
7、BLOB