MySQL的数据类型详解_zerofill 具体位置

数据类型概览

约束条件

约束条件就是在给字段加一些约束，使该字段存储的值更加符合我们的预期。

常用约束条件有以下这些：

UNSIGNED ：无符号，值从0开始，无负数
ZEROFILL：零填充，当数据的显示长度不够的时候可以使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED
NOT NULL：非空约束，表示该字段的值不能为空
DEFAULT：表示如果插入数据时没有给该字段赋值，那么就使用默认值
PRIMARY KEY：主键约束，表示唯一标识，不能为空，且一个表只能有一个主键。一般都是用来约束id
AUTO_INCREMENT：自增长，只能用于数值列，而且配合索引使用,默认起始值从1开始，每次增长1
UNIQUE KEY：唯一值，表示该字段下的值不能重复，null除外。比如身份证号是一人一号的，一般都会用这个进行约束
FOREIGN KEY：外键约束，目的是为了保证数据的完成性和唯一性，以及实现一对一或一对多关系

数值类型

数值类型包括整数型、浮点型、定点型

整数型（精确值）

M： 表示默认的显示长度，有符号和无符号的默认显示长度是不一致的，比如: INT 有符号默认显示长度为 4，无符号默认显示长度为3

1. TINYINT[(M)] [UNSIGNED] [ZEROFILL] 范围非常小的整数，有符号的范围是 -128到127，无符号的范围是0到 255
2. SMALLINT[(M)] [UNSIGNED] [ZEROFILL] 范围较小的整数，有符号的范围是 -32768到32767，无符号的范围是0到 65535
3. MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] 中等大小的整数，有符号的范围是 -8388608到8388607，无符号的范围是0到 16777215。
4. INT[(M)] [UNSIGNED] [ZEROFILL] 正常大小的整数，有符号的范围是 -2147483648到 2147483647。无符号的范围是 0到4294967295。
5. BIGINT[(M)] [UNSIGNED] [ZEROFILL] 大整数，有符号的范围是 -9223372036854775808到 9223372036854775807，无符号的范围是0到 18446744073709551615。
   
   有符号和无符号（UNSIGNED）：一个字节有8个二进制位，有符号的时候最高位用来表示符号，比如TINYINT有8个二进制位，最左边的位表示符号，1表示正，0表示负，剩下的7位来表示数值的大小；
   可以区分正负的类型，称为有符号类型。无正负的类型，称为无符号类型。
   简单的理解为就是
   有符号值可以表示负数，0以及正数
   无符号值只能为0或正数
   如果不手动指定UNSIGNED，那么默认就是有符号的

整数类型实践

CREATE TABLE int_db(
a TINYINT,
b SMALLINT,
c MIDDLEINT,
d INT,
e BIGINT
);

1
2
3
4
5
6
7
8

表结构如下（desc int_db）

解释： 我们来看看type这一列，可以看到，每个字段类型后面都有一个括号，括号里面的有个数值，这个数值实际上就是字段的显示宽度，也就是M的值，M表示整数类型的最大显示宽度。最大显示宽度为255.显示宽度与类型可包含的值范围无关
我们在创建表的时候并没有指定字段类型的显示宽度，那么，默认的显示宽度则是该字段类型最大的显示宽度
例如字段a的显示宽度为4，是因为TINYINT有符号值的范围是-128到127，
-128的长度为4（负号、1、2、8共四位），所以默认的显示宽度最大为4，其他的以此类推

 CREATE TABLE int_db( a TINYINT, b SMALLINT, c MIDDLEINT, d INT, e BIGINT );
1

可以看到，默认显宽度就变成3了，因为无符号的TINYINT的值范围为0-255，没有负号，所以最多是3位。

ZEROFILL

使用该约束后当数据的长度比我们指定的显示宽度小的时候会使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED

CREATE TABLE int_db2(
a TINYINT(8) ZEROFILL,
b TINYINT(5) UNSIGNED);

INSERT int_db2() VALUES(12,12);
1
2
3
4
5

解释： 可以看到，12变成了00000012，自动在前面补了0，这是因为指定的显示宽度是8，但是12只有两位，所以在前面补0，使长度为8。这就是ZEROFILL的效果

浮点型

1. FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
   一个小的（单精度）浮点数。允许值是-3.402823466E+38 到-1.175494351E-38， 0以1.175494351E-38 到3.402823466E+38,M是总位数，D是小数点后面的位数
2. DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
   正常大小（双精度）浮点数。允许值是 -1.7976931348623157E+308到-2.2250738585072014E-308，0以及 2.2250738585072014E-308到 1.7976931348623157E+308。M是总位数，D是小数点后面的位数

实践举例：
我们指定a字段为FLOAT类型，总长度为3，小数点后两位为2,b字段总长度为5，小数点后两位长度为3

 CREATE TABLE float_db(
    a FLOAT(3,2),
    b DOUBLE(5,3)
  ) ;
1
2
3
4

解释： 可以看到，我们给a字段的值是1.111，但是只存进去了1.11； 浮点数存在精度丢失的问题，如果涉及到小数运算，尽量不要用浮点型

定点型

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

1. M是总位数，范围是1到65； 如果M省略，则默认值为10； 小数点和（负数)-符号不计入 M。
2. D是小数点后的位数；D范围为0到30；D为0，则值没有小数点或小数部分；如果D省略，则默认值为0，且不得大于M。

CREATE TABLE decimal_db(a DECIMAL);
desc decimal_db;
insert into descimal_db values(30.6783);

create table descimal_db2(a decimal(5,3));
desc descimal_db2;
 insert into descimal_db values(30.6783);
1
2
3
4
5
6
7

解释：可以看到，存进去的数值被四舍五入阶段了，也就是说，DECIMAL也在存储时存在精度丢失的问题。

超出范围和溢出处理
当MySQL将值存储在超出列数据类型允许范围的数值列中时，结果取决于当时生效的SQL模式：
如果启用了严格的SQL模式，则MySQL会根据SQL标准拒绝带有错误的超出范围的值，并且插入失败。
如果未启用限制模式，MySQL会将值截断到列数据类型范围的相应端点，并存储结果值，并产生一个警告;
在我们的配置文件中可以看到SQL模式的配置，关于SQL模式详情请看SQL模式官方文档

字符串类型

1. CHAR[(M)] 一个固定长度的字符串，在存储时始终用空格填充指定长度。 M表示以字符为单位的列长度。M的范围为0到255.如果M省略，则长度为1，存储时占用M个字节
2. VARCHAR(M)可变长度的字符串，M 表示字符的最大列长度，M的范围是0到65,535，存储时占用**L+1（L<=M,L为实际字符的长度）**个字节
3. TINYTEXT[(M)] 不能有默认值，占用L+1个字节，L<2^8
4. TEXT[(M)] 不能有默认值，占用L+2个字节，L<2^16
5. MEDIUMTEXT[(M)] 不能有默认值，占用L+3个字节，L<2^24
6. LONGTEXT[(M)] 不能有默认值，占用L+4个字节，L<2^32
7. ENUM(‘value1’,‘value2’,…) ENUM是一个字符串对象，其值从允许值列表中选择，它只能有一个值，从值列表中选择,最多可包含65,535个不同的元素
8. SET(‘value1’,‘value2’,…) 字符串对象，该对象可以有零个或多个值，最多可包含64个不同的成员

char和varchar

CREATE TABLE str_db(
a CHAR(4),
b VARCHAR(4));

INSERT str_db() VALUES("","");
INSERT str_db() VALUES("ab","ab");
INSERT str_db() VALUES("abcd","abcd");
INSERT str_db() VALUES("abcdefg","abcdefg");//在严格模式下，该条数据会插入失败，非严格模式则会对数据进行截取

1
2
3
4
5
6
7
8
9
10

解释： 我们看到查询的结果是一样的，但实际上他们存储时占用的长度是不一样的。
CHAR类型不管存储的值的长度是多少，都会占用M个字节，而VARCHAR则占用实际长度+1个字节。

建议： 但是CHAR的查询效果要高于VARCHAR，所以说，如果字段的长度能够确定的话，比如手机号，身份证号之类的字段，可以用CHAR类型，像地址，邮箱之类的就用VARCHAR

先了解一下，Mysql中length()、char_length()的区别。
　　1）、length()：mysql里面的length()函数是一个用来获取字符串长度的内置函数。
　　2）、char_length()：在mysql内置函数里面查看字符串长度的还有一个函数是char_length()。
　　3）、这两个函数的区别是：
　　 a）、length()： 单位是字节，utf8编码下,一个汉字三个字节，一个数字或字母一个字节。gbk编码下,一个汉字两个字节，一个数字或字母一个字节。
　　b）、char_length()：单位为字符，不管汉字还是数字或者是字母都算是一个字符。

提示： varchar 单独需要一个或者两个字节来记录该列真实占用的字节数； 真实数据占用的字节数小于等于127，用一个字节记录真实占用的字节数； 真实数据占用的字节数大于127用2个字节记录真实占用的字节数

TEXT系列

TEXT系列的存储范围比VARCHAR要大，当VARCHAR不满足时可以用TEXT系列中的类型。需要注意的是TEXT系列类型的字段不能有默认值，在检索的时候不存在大小写转换，没有CHAR和VARCHAR的效率高

枚举类型

enum

CREATE TABLE enum_db(gender ENUM("男","女"));

INSERT enum_db() VALUES("男");
INSERT enum_db() VALUES(1); 也可以使用编号插入值，等同于"男"，序号从1开始
INSERT enum_db() VALUES("女");
INSERT enum_db() VALUES(2);等同于"女"

1
2
3
4
5
6
7

下面我们插入一条不是枚举集合中的数据试一下

set

在ENUM中我们只能从允许值列表中给字段插入一个值，而在SET类型中可以给字段插入多个值

日期时间类型

1. TIME 范围是’-838:59:59.000000’ 到’838:59:59.000000’
2. DATE 支持的范围是 '1000-01-01’到 ‘9999-12-31’
3. DATETIME 日期和时间组合。支持的范围是 '1000-01-01 00:00:00.000000’到 ‘9999-12-31 23:59:59.999999’。
4. TIMESTAMP 时间戳。范围是’1970-01-01 00:00:01.000000’UTC到’2038-01-19 03:14:07.999999’UTC。
5. YEAR 范围是 1901到2155

TIME

我们可以看到TIME的存储范围是’-838:59:59’到 ‘838:59:59’，因为TIME类型不仅可以用于表示一天中的时间 ，还可以用于表示两个事件之间的经过时间或时间间隔
格式： D HH:MM:SS
D：表示天数，当指定该值时，存储时小时（24）会先乘以该值
HH：表示小时
MM：表示分钟
SS:表示秒

CREATE TABLE time_db(
a TIME
)


INSERT time_db() VALUES('22:14:16');
--   -2表示间隔了2两天
INSERT time_db() VALUES('-2 22:14:16');
-- 有冒号从小时开始
INSERT time_db() VALUES('14:16');
-- 没有冒号且没有天数则数据从秒开始
INSERT time_db() VALUES('30');
-- 有天数也从小时开始
INSERT time_db() VALUES('3 10');
-- 直接使用数字代替也可以
INSERT time_db() VALUES(253621);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

DATE

CREATE TABLE date_db(
a DATE)

INSERT date_db() VALUES(20180813);
INSERT date_db() VALUES("2018-06-1");
INSERT date_db() VALUES("2018-4-1");
INSERT date_db() VALUES("2018-04-07");

1
2
3
4
5
6
7
8

DATETIME

CREATE TABLE datetime_db(
a DATETIME
)
INSERT datetime_db() VALUES(20180102235432);
INSERT datetime_db() VALUES("2015-04-21 21:14:32");
INSERT datetime_db() VALUES("2015-04-23");
1
2
3
4
5
6

TIMESTAMP

datetime timestamp date？
datetime 更像日历上面的时间和你手表的时间的结合，就是指具体某个时间，精确到毫，8个字节，
timestamp 更适合来记录时间，比如我在东八区时间现在是 2016-08-02 10:35:52， 你在日本（东九区此时时间为 2016-08-02 11:35:52），我和你在聊天，数据库记录了时间，取出来之后，对于我来说时间是 2016-08-02 10:35:52，对于日本的你来说就是 2016-08-02 11:35:52， 精确到秒 4个字节存储，1970-2038
date: 可以直接使用函数进行比较， 3个字节

CREATE TABLE timestamp_db(
a TIMESTAMP
)
INSERT timestamp_db() VALUES(20020121);
INSERT timestamp_db() VALUES(20020121142554);
INSERT timestamp_db() VALUES("2015-12-16 21:14:15");
INSERT timestamp_db() VALUES("2015-12-17");
INSERT timestamp_db() VALUES(NULL);
INSERT timestamp_db() VALUES(CURRENT_TIMESTAMP);
INSERT timestamp_db() VALUES();

1
2
3
4
5
6
7
8
9
10
11

YEAR

CREATE TABLE year_db(
a YEAR
)

INSERT year_db() VALUES("1993");
INSERT year_db() VALUES(1993);

1
2
3
4
5
6
7

提问： ip: varchar(15)还是32位无符号整数来保存这个ip地址？

create table ip_db(ip int(12) );

 insert into ip_db values(INET_ATON('127.127.117.163'));
1
2
3

INET_ATON(‘127.127.117.163’) : 把IP转换为数字存储；
INET_ntoa(2139059619) : 把数字转换为IP；