一 介绍
存储引擎决定了表的类型,而表内存放的数据也要有不同的类型,每种数据类型都有自己的宽度,但宽度是可选的
详细参考:
mysql常用数据类型概览
- 数字
整型:tinyinit int bigint
小数:float double - 字符串
char(10):简单粗暴,浪费空间,存区速度快
varchar:精准,节省空间,存区速度慢
sql优化:创建表时,定长的类型往前放,变长的往后放
> 255个字符,超了就把文件路径存放到数据库中。 - 时间类型
datatime - 枚举类型与集合类型
enum set
二 数值类型
1.整数类型
整数类型:TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT
作用:存储年龄,等级,id,各种号码等
zerofill属性的作用
1、插入数据时,当该字段的值的长度小于定义的长度时,会在该值的前面补上相应的0
2、zerofill默认为int(10)
3、当使用zerofill 时,默认会自动加unsigned(无符号)属性,使用unsigned属性后,数值范围是原值的2倍,例如,有符号为-128 ~ +127,无符号为0~256。
tinyint[(m)] [unsigned] [zerofill]
小整数,数据类型用于保存一些范围的整数数值范围
有符号: -128 ~ 127
无符号:0 ~ 255
int[(m)] [unsigned] [zerofill]
整数,数据类型用于保存一些范围的整数数值范围:
有符号:-2147483648 ~ 2147483647
无符号:0 ~ 4294967295
bigint[(m)] [unsigned] [zerofill]
大整数,数据类型用于保存一些范围的整数数值类型
有符号:-9223372036854775808 ~ 9223372036854775807
无符号: 0 ~ 18446744073709551615
有符号和无符号tinyint
1.tinyint默认为有符号
mysql> create table t1(x tinyint); #默认为有符号,即数字前有正负号
mysql> desc t1;
mysql> insert into t1 values
-> (-129),
-> (-128),
-> (127),
-> (128);
mysql> select * from t1;
+------+
| x |
+------+
| -128 | #-129存成了-128
| -128 | #有符号,最小值为-128
| 127 | #有符号,最大值127
| 127 | #128存成了127
+------+
2.设置无符号tinyint
mysql> create table t2(x tinyint unsigned);
mysql> insert into t2 values
-> (-1),
-> (0),
-> (255),
-> (256);
mysql> select * from t2;
+------+
| x |
+------+
| 0 | -1存成了0
| 0 | #无符号,最小值为0
| 255 | #无符号,最大值为255
| 255 | #256存成了255
+------+
有符号和无符号int
1.int默认为有符号
mysql> create table t3(x int); #默认为有符号整数
mysql> insert into t3 values
-> (-2147483649),
-> (-2147483648),
-> (2147483647),
-> (2147483648);
mysql> select * from t3;
+-------------+
| x |
+-------------+
| -2147483648 | #-2147483649存成了-2147483648
| -2147483648 | #有符号,最小值为-2147483648
| 2147483647 | #有符号,最大值为2147483647
| 2147483647 | #2147483648存成了2147483647
+-------------+
2.设置无符号int
mysql> create table t4(x int unsigned);
mysql> insert into t4 values
-> (-1),
-> (0),
-> (4294967295),
-> (4294967296);
mysql> select * from t4;
+------------+
| x |
+------------+
| 0 | #-1存成了0
| 0 | #无符号,最小值为0
| 4294967295 | #无符号,最大值为4294967295
| 4294967295 | #4294967296存成了4294967295
+------------+
有符号和无符号bigint
1.有符号bigint
mysql> create table t6(x bigint);
mysql> insert into t5 values
-> (-9223372036854775809),
-> (-9223372036854775808),
-> (9223372036854775807),
-> (9223372036854775808);
mysql> select * from t5;
+----------------------+
| x |
+----------------------+
| -9223372036854775808 |
| -9223372036854775808 |
| 9223372036854775807 |
| 9223372036854775807 |
+----------------------+
2.无符号bigint
mysql> create table t6(x bigint unsigned);
mysql> insert into t6 values
-> (-1),
-> (0),
-> (18446744073709551615),
-> (18446744073709551616);
mysql> select * from t6;
+----------------------+
| x |
+----------------------+
| 0 |
| 0 |
| 18446744073709551615 |
| 18446744073709551615 |
+----------------------+
用zerofill测试整数类型的显示宽度
mysql> create table t7(x int(3) zerofill);
mysql> insert into t7 values
-> (1),
-> (11),
-> (111),
-> (1111);
mysql> select * from t7;
+------+
| x |
+------+
| 001 |
| 011 |
| 111 |
| 1111 | #超过宽度限制仍然可以存
+------+
注意:对于整型来说,数据类型后面的宽度并不是存储长度限制,而是显示限制,假如:int(8),那么显示时不够8位则用0来填充,够8位则正常显示,通过zerofill来测试,存储长度还是int的4个字节长度。默认的显示宽度就是能够存储的最大的数据的长度,比如:int无符号类型,那么默认的显示宽度就是int(10),有符号的就是int(11),因为多了一个符号,所以我们没有必要指定整数类型的数据,没必要指定宽度,因为默认的就能够将你存的原始数据完全显示。
int的存储宽度是4个Bytes,即32个bit,即2**32
无符号最大值为:4294967296-1
有符号最大值:2147483648-1
有符号和无符号的最大数字需要的显示宽度均为10,而针对有符号的最小值则需要11位才能显示完全,所以int类型默认的显示宽度为11是非常合理的
最后:整形类型,其实没有必要指定显示宽度,使用默认的就ok
MySQL的mode设置,博客,看完这篇博客应该就能理解MySQL的mode了。
2.浮点类型
定点数类型 DEC,等同于DECIMAL
浮点类型:FLOAT DOUBLE
作用:存储薪资、身高、温度、体重、体质参数等
FLOAT [(M,D)] [UNSIGNED] [ZEROFILL]
定义:单精度浮点数(非准确小数值),m是整数部分+小数部分的总个数,d是小数点后个数。m最大值为255,d最大值为30,例如:float(255,30)
有符号:
- -3.402823466E+38 to -1.175494351E-38,
- 1.175494351E-38 to 3.402823466E+38
无符号:
- 1.175494351E-38 to 3.402823466E+38
精确度:
**** 随着小数的增多,精度变得不准确 ****
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
定义:双精度浮点数(非准确小数值),m是整数部分+小数部分的总个数,d是小数点后个数。m最大值也为255,d最大值也为30
有符号:
- 1.7976931348623157E+308 to -2.2250738585072014E-308
- 2.2250738585072014E-308 to 1.7976931348623157E+308
无符号:
- 2.2250738585072014E-308 to 1.7976931348623157E+308
精确度:
****随着小数的增多,精度比float要高,但也会变得不准确 ****
decimal[(m[,d])] [unsigned] [zerofill]
定义:
准确的小数值,m是整数部分+小数部分的总个数(负号不算),d是小数点后个数。 m最大值为65,d最大值为30。比float和double的整数个数少,但是小数位数都是30位
精确度:
**** 随着小数的增多,精度始终准确 ****
对于精确数值计算时需要用此类型
decimal能够存储精确值的原因在于其内部按照字符串存储。
精度从高到低:decimal、double、float
–decimal精度高,但是整数位数少
–float和double精度低,但是整数位数多
–float已经满足绝大多数的场景了,但是什么导弹、航线等要求精度非常高,所以还是需要按照业务场景自行选择,如果又要精度高又要整数位数多,那么你可以直接用字符串来存。
字符串测试
mysql> create table t1(x float(256,31));
ERROR 1425 (42000): Too big scale 31 specified for column 'x'. Maximum is 30.
mysql> create table t1(x float(256,30));
ERROR 1439 (42000): Display width out of range for column 'x' (max = 255)
mysql> create table t1(x float(255,30)); #建表成功
Query OK, 0 rows affected (0.02 sec)
mysql> create table t2(x double(255,30)); #建表成功
Query OK, 0 rows affected (0.02 sec)
mysql> create table t3(x decimal(66,31));
ERROR 1425 (42000): Too big scale 31 specified for column 'x'. Maximum is 30.
mysql> create table t3(x decimal(66,30));
ERROR 1426 (42000): Too-big precision 66 specified for 'x'. Maximum is 65.
mysql> create table t3(x decimal(65,30)); #建表成功
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
| t2 |
| t3 |
+---------------+
3 rows in set (0.00 sec)
mysql> insert into t1 values(1.1111111111111111111111111111111); #小数点后31个1
Query OK, 1 row affected (0.01 sec)
mysql> insert into t2 values(1.1111111111111111111111111111111);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 values(1.1111111111111111111111111111111);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from t1; #随着小数的增多,精度开始不准确
+----------------------------------+
| x |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select * from t2; #精度比float要准确点,但随着小数的增多,同样变得不准确
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select * from t3; #精度始终准确,d为30,于是只留了30位小数
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
1 row in set (0.00 sec)
3.日期类型
类型:DATE,TIME,DETETIME,IMESTAMP,YEAR
作用:存储用户注册时间,文章发布时间,员工入职时间,出生时间,过期时间等
YEAR
YYYY(范围:1901/2155)2018
DATE
YYYY-MM-DD(范围:1000-01-01/9999-12-31)例:2018-01-01
TIME
HH:MM:SS(范围:’-838:59:59’/‘838:59:59’)例:12:09:32
DATETIME
YYYY-MM-DD HH:MM:SS(范围:1000-01-01 00:00:00/9999-12-31 23:59:59 Y)例: 2018-01-01 12:09:32
TIMESTAMP
YYYYMMDD HHMMSS(范围:1970-01-01 00:00:00/2037 年某时)
日期类型测试实例
year:
mysql> create table t10(born_year year); #无论year指定何种宽度,最后都默认是year(4)
mysql> insert into t10 values
-> (1900),
-> (1901),
-> (2155),
-> (2156);
mysql> select * from t10;
+-----------+
| born_year |
+-----------+
| 0000 |
| 1901 |
| 2155 |
| 0000 |
+-----------+
date,time,datetime:
mysql> create table t11(d date,t time,dt datetime);
mysql> desc t11;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d | date | YES | | NULL | |
| t | time | YES | | NULL | |
| dt | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
mysql> insert into t11 values(now(),now(),now());
mysql> select * from t11;
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2017-07-25 | 16:26:54 | 2017-07-25 16:26:54 |
+------------+----------+---------------------+
timestamp:
mysql> create table t12(time timestamp);
mysql> insert into t12 values();
mysql> insert into t12 values(null);
mysql> select * from t12;
+---------------------+
| time |
+---------------------+
| 2017-07-25 16:29:17 |
| 2017-07-25 16:30:01 |
+---------------------+
============注意啦,注意啦,注意啦===========
1. 单独插入时间时,需要以字符串的形式,按照对应的格式插入
2. 插入年份时,尽量使用4位值
3. 插入两位年份时,<=69,以20开头,比如50, 结果2050
>=70,以19开头,比如71,结果1971
mysql> create table t12(y year);
mysql> insert into t12 values
-> (50),
-> (71);
mysql> select * from t12;
+------+
| y |
+------+
| 2050 |
| 1971 |
+------+
============综合练习===========
mysql> create table student(
-> id int,
-> name varchar(20),
-> born_year year,
-> birth date,
-> class_time time,
-> reg_time datetime);
mysql> insert into student values
-> (1,'sb1',"1995","1995-11-11","11:11:11","2017-11-11 11:11:11"),
-> (2,'sb2',"1997","1997-12-12","12:12:12","2017-12-12 12:12:12"),
-> (3,'sb3',"1998","1998-01-01","13:13:13","2017-01-01 13:13:13");
mysql> select * from student;
+------+------+-----------+------------+------------+---------------------+
| id | name | born_year | birth | class_time | reg_time |
+------+------+-----------+------------+------------+---------------------+
| 1 | sb1 | 1995 | 1995-11-11 | 11:11:11 | 2017-11-11 11:11:11 |
| 2 | sb2 | 1997 | 1997-12-12 | 12:12:12 | 2017-12-12 12:12:12 |
| 3 | sb3 | 1998 | 1998-01-01 | 13:13:13 | 2017-01-01 13:13:13 |
+------+------+-----------+------------+------------+---------------------+
在实际应用的很多场景中,MySQL的这两种日期类型都能够满足我们的需要,存储精度都为秒,但在某些情况下,会展现出他们各自的优劣。下面就来总结一下两种日期类型的区别。
1.DATETIME的日期范围是1001——9999年,TIMESTAMP的时间范围是1970——2038年。
2.DATETIME存储时间与时区无关,TIMESTAMP存储时间与时区有关,显示的值也依赖于时区。在mysql服务器,操作系统以及客户端连接都有时区的设置。
3.DATETIME使用8字节的存储空间,TIMESTAMP的存储空间为4字节。因此,TIMESTAMP比DATETIME的空间利用率更高。
4.DATETIME的默认值为null;TIMESTAMP的字段默认不为空(not null),默认值为当前时间(CURRENT_TIMESTAMP),如果不做特殊处理,并且update语句中没有指定该列的更新值,则默认更新为当前时间。
4 字符串类型
类型:char,varchar
作用:名字,信息等等
#官网:https://dev.mysql.com/doc/refman/5.7/en/char.html
#注意:char和varchar括号内的参数指的都是字符的长度
char类型:定长,简单粗暴,浪费空间,存取速度快
字符长度范围:0-255(一个中文是一个字符,是utf8编码的3个字节)
存储:
存储char类型的值时,会往右填充空格来满足长度
例如:指定长度为10,存>10个字符则报错(严格模式下),存<10个字符则用空格填充直到凑够10个字符存储
检索:
在检索或者说查询时,查出的结果会自动删除尾部的空格,如果你想看到它补全空格之后的内容,除非我们打开pad_char_to_full_length SQL模式(SET sql_mode = 'strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH';)
varchar类型:变长,精准,节省空间,存取速度慢
字符长度范围:0-65535(如果大于21845会提示用其他类型 。mysql行最大限制为65535字节,字符编码为utf-8:https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html)
存储:
varchar类型存储数据的真实内容,不会用空格填充,如果'ab ',尾部的空格也会被存起来
强调:varchar类型会在真实数据前加1-2Bytes的前缀,该前缀用来表示真实数据的bytes字节数(1-2Bytes最大表示65535个数字,正好符合mysql对row的最大字节限制,即已经足够使用)
如果真实的数据<255bytes则需要1Bytes的前缀(1Bytes=8bit 2**8最大表示的数字为255)
如果真实的数据>255bytes则需要2Bytes的前缀(2Bytes=16bit 2**16最大表示的数字为65535)
检索:
尾部有空格会保存下来,在检索或者说查询时,也会正常显示包含空格在内的内容
char和varchar测试
创建一个t1表,包含一个char类型的字段
create table t1(id int,name char(4));
超过长度:
严格模式下(报错):
mysql> insert into t1 values('xiaoshabi');
ERROR 1406 (22001): Data too long for column 'name' at row 1
非严格模式下(警告):
mysql> set sql_mode='NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
mysql> create table t1(id int,name char(4));
Query OK, 0 rows affected (0.40 sec)
mysql> insert into t2 values('xiaoshabi');
Query OK, 1 row affected, 1 warning (0.11 sec)
查看一下结果:
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | xiao | #只有一个xiao
+------+------+
1 row in set (0.00 sec)
varchar类型和上面的效果是一样的,严格模式下也会报错。
如果没有超过长度,那么char类型时mysql会使用空格来补全自己规定的char(4)的4个字符,varchar不会,我们来做个对比
例如:
#再创建一个含有varchar类型的表t2
然后插入几条和t1里面相同的数据
mysql>insert into t1 values(2,'a'),(3,'bb'),(4,'ccc'),(5,'d');
mysql>create table t2(id int,name varchar(4));
mysql> insert into t2 values(1,'xiao'),(2,'a'),(3,'bb'),(4,'ccc'),(5,'d');
查看一下t1表和t2表的内容
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | xiao |
| 2 | a |
| 3 | bb |
| 4 | ccc |
| 5 | d |
+------+------+
5 rows in set (0.00 sec)
mysql> select * from t2;
+------+------+
| id | name |
+------+------+
| 1 | xiao |
| 2 | a |
| 3 | bb |
| 4 | ccc |
| 5 | d |
+------+------+
5 rows in set (0.00 sec)
好,两个表里面数据是一样的,每一项的数据长度也是一样的,那么我们来验证一下char的自动空格在后面补全的存储方式和varchar的不同
通过mysql提供的一个char_length()方法来查看一下所有数据的长度
mysql> select char_length(name) from t1;
+-------------------+
| char_length(name) |
+-------------------+
| 4 |
| 1 |
| 2 |
| 3 |
| 1 |
+-------------------+
5 rows in set (0.00 sec)
mysql> select char_length(name) from t2;
+-------------------+
| char_length(name) |
+-------------------+
| 4 |
| 1 |
| 2 |
| 3 |
| 1 |
+-------------------+
5 rows in set (0.00 sec)
通过查看结果可以看到,两者显示的数据长度是一样的,不是说好的char会补全吗,我设置的字段是char(4),那么长度应该都是4才对啊?这是因为mysql在你查询的时候自动帮你把结果里面的空格去掉了,如果我们想看到它存储数据的真实长度,需要设置mysql的模式,通过一个叫做PAD_CHAR_TO_FULL_LENGTH的模式,就可以看到了,所以我们把这个模式加到sql_mode里面:
mysql> set sql_mode='PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)
然后我们在查看一下t1和t2数据的长度:
mysql> select char_length(name) from t1;
+-------------------+
| char_length(name) |
+-------------------+
| 4 |
| 4 |
| 4 |
| 4 |
| 4 |
+-------------------+
5 rows in set (0.00 sec)
mysql> select char_length(name) from t2;
+-------------------+
| char_length(name) |
+-------------------+
| 4 |
| 1 |
| 2 |
| 3 |
| 1 |
+-------------------+
5 rows in set (0.00 sec)
通过结果可以看到,char类型的数据长度都是4,这下看到了两者的不同了吧,至于为什么mysql会这样搞,我们后面有解释的,先看现象就可以啦。
现在我们再来看一个问题,就是当你设置的类型为char的时候,我们通过where条件来查询的时候会有一个什么现象:
mysql> select * from t1 where name='a';
+------+------+
| id | name |
+------+------+
| 2 | a |
+------+------+
1 row in set (0.00 sec)
ok,结果没问题,我们在where后面的a后面加一下空格再来试试:
mysql> select * from t1 where name='a ';
+------+------+
| id | name |
+------+------+
| 2 | a |
+------+------+
1 row in set (0.00 sec)
ok,能查到,再多加一些空格试试,加6个空格,超过了设置的char(4)的4:
mysql> select * from t1 where name='a ';
+------+------+
| id | name |
+------+------+
| 2 | a |
+------+------+
1 row in set (0.00 sec)
ok,也是没问题的
总结:通过>,=,>=,<,<=作为where的查询条件的时候,char类型字段的查询是没问题的。
但是,当我们将where后面的比较符号改为like的时候,(like是模糊匹配的意思,我们前面见过,show variables like '%char%';来查看mysql字符集的时候用过)
其中%的意思是匹配任意字符(0到多个字符都可以匹配到),还有一个符号是_(匹配1个字符),这两个字符其实就像我们学的正则匹配里面的通配符,那么我们通过这些符号进行一下模糊查询,看一下,char类型进行模糊匹配的时候,是否还能行,看例子:
mysql> select * from t1 where name like 'a';
Empty set (0.00 sec)
发现啥也没查到,因为char存储的数据是4个字符长度的,不满4个是以空格来补全的,你在like后面就只写了一个'a',是无法查到的。
我们试一下上面的通配符来查询:
mysql> select * from t1 where name like 'a%';
+------+------+
| id | name |
+------+------+
| 2 | a |
+------+------+
1 row in set (0.00 sec)
这样就能看到查询结果了
试一下_是不是匹配1个字符:
mysql> select * from t1 where name like 'a_';
Empty set (0.00 sec)
发现一个_果然不行,我们试试三个_。
mysql> select * from t1 where name like 'a___';
+------+------+
| id | name |
+------+------+
| 2 | a |
+------+------+
1 row in set (0.00 sec)
发现果然能行,一个_最多匹配1个任意字符。
如果多写了几个_呢?
mysql> select * from t1 where name like 'a_____';
Empty set (0.00 sec)
查不到结果,说明_匹配的是1个字符,但不是0-1个字符。
复制代码
测试结果总结:
针对char类型,mysql在存储的时候会将不足规定长度的数据使用后面(右边补全)补充空格的形式进行补全,然后存放到硬盘中,但是在读取或者使用的时候会自动去掉它给你补全的空格内容,因为这些空格并不是我们自己存储的数据,所以对我们使用者来说是无用的。
5 枚举类型
字段的值只能在给定范围中选择,如单选框,多选框,如果你在应用程序或者前端不做选项限制,在MySQL的字段里面也能做限制
enum 单选 只能在给定的范围内选一个值,如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值(爱好1,爱好2,爱好3…)
枚举类型(enum)
An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
示例:
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
集合类型(set)
A SET column can have a maximum of 64 distinct members.
示例:
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
枚举和集合测试
mysql> create table consumer(
-> name varchar(50),
-> sex enum('male','female'),
-> level enum('vip1','vip2','vip3','vip4','vip5'), #在指定范围内,多选一
-> hobby set('play','music','read','study') #在指定范围内,多选多
-> );
mysql> insert into consumer values
-> ('xiaogui','male','vip5','read,study'),
-> ('taibai','female','vip1','girl');
mysql> select * from consumer;
+------+--------+-------+------------+
| name | sex | level | hobby |
+------+--------+-------+------------+
| xiaogui | male | vip5 | read,study |
| taibai | female | vip1 | |
+------+--------+-------+------------+