Mysql基本知识系列三数据类型
一、数据类型简介
MySQL支持多种类型的SQL数据类型：数值，日期和时间类型，字符串（字符和字节）类型，空间类型和 JSON数据类型等数据类型，描述使用以下约定：
-
M表示整数类型的最大显示宽度。M表示整数类型的最大显示宽度。对于浮点和定点类型， M是可以存储的总位数（精度）。对于字符串类型， M是最大长度。允许的最大值M取决于数据类型。
-
D适用于浮点和定点类型，并指示小数点后面的位数。最大可能值为30，但不应大于 M-2。
-
**[ ]**表示类型定义的可选部分。
在MySQL中常用数据类型主要分为以下几类
-
数值类型
-
字符串类型
-
日期时间类型
二、字段约束
约束条件就是在给字段加一些约束，使该字段存储的值更加符合我们的预期。
常用约束条件有以下这些
-
UNSIGNED ：无符号，值从0开始，无负数
-
ZEROFILL：零填充，当数据的显示长度不够的时候可以使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED
-
NOT NULL：非空约束，表示该字段的值不能为空
-
DEFAULT：表示如果插入数据时没有给该字段赋值，那么就使用默认值
-
PRIMARY KEY：主键约束，表示唯一标识，不能为空，且一个表只能有一个主键。一般都是用来约束id
-
AUTO_INCREMENT：自增长，只能用于数值列，而且配合索引使用,默认起始值从1开始，每次增长1
-
UNIQUE KEY：唯一值，表示该字段下的值不能重复，null除外。比如身份证号是一人一号的，一般都会用这个进行约束
-
FOREIGN KEY：外键约束，目的是为了保证数据的完成性和唯一性，以及实现一对一或一对多关系
三、数值类型
3.1 整数型
整数型（精确值）
TINYINT[(M)] [UNSIGNED] [ZEROFILL] 范围非常小的整数，有符号的范围是 -128到127，无符号的范围是0到 255
SMALLINT[(M)] [UNSIGNED] [ZEROFILL] 范围较小的整数，有符号的范围是 -32768到32767，无符号的范围是0到 65535
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] 中等大小的整数，有符号的范围是 -8388608到8388607，无符号的范围是0到 16777215。
INT[(M)] [UNSIGNED] [ZEROFILL] 正常大小的整数，有符号的范围是 -2147483648到 2147483647。无符号的范围是 0到4294967295。
BIGINT[(M)] [UNSIGNED] [ZEROFILL] 大整数，有符号的范围是 -9223372036854775808到 9223372036854775807，无符号的范围是0到 18446744073709551615。
3.2 浮点型
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
一个小的（单精度）浮点数。允许值是-3.402823466E+38 到-1.175494351E-38， 0以及1.175494351E-38 到3.402823466E+38,M是总位数，D是小数点后面的位数。
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
正常大小（双精度）浮点数。允许值是 -1.7976931348623157E+308到-2.2250738585072014E-308，0以及 2.2250738585072014E-308到 1.7976931348623157E+308。M是总位数，D是小数点后面的位数
3.3 定点型
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
常用于存储精确的小数，M是总位数，D是小数点后的位数。小数点和（负数） -符号不计入 M。如果 D为0，则值没有小数点或小数部分。最大位数（M）为 65， 最大支持小数（D）为30，如果D省略，则默认值为0.如果M省略，则默认值为10。M的范围是1到65。D范围为0到30，且不得大于M。
4.注意事项
超出范围和溢出处理
当MySQL将值存储在超出列数据类型允许范围的数值列中时，结果取决于当时生效的SQL模式：
如果启用了严格的SQL模式，则MySQL会根据SQL标准拒绝带有错误的超出范围的值，并且插入失败。
如果未启用限制模式，MySQL会将值截断到列数据类型范围的相应端点，并存储结果值，并产生一个警告
在我们的配置文件中可以看到SQL模式的配置，关于SQL模式详情请看SQL模式官方文档
sql_mode="STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION"
四、字符串类型
4.1 字符串类型
CHAR[(M)] 一个固定长度的字符串，在存储时始终用空格填充指定长度。 M表示以字符为单位的列长度。M的范围为0到255.如果M省略，则长度为1，存储时占用M个字节
VARCHAR(M)可变长度的字符串，M 表示字符的最大列长度，M的范围是0到65,535，存储时占用L+1（L<=M,L为实际字符的长度）个字节
TINYTEXT[(M)] 不能有默认值，占用L+1个字节，L<2^8
TEXT[(M)] 不能有默认值，占用L+2个字节，L<2^16
MEDIUMTEXT[(M)] 不能有默认值，占用L+3个字节，L<2^24
LONGTEXT[(M)] 不能有默认值，占用L+4个字节，L<2^32
ENUM('value1','value2',...) ENUM是一个字符串对象，其值从允许值列表中选择，它只能有一个值，从值列表中选择,最多可包含65,535个不同的元素
SET('value1','value2',...) 字符串对象，该对象可以有零个或多个值，最多可包含64个不同的成员
4.2 CHAR和VARCHAR
CHAR类型不管存储的值的长度是多少，都会占用M个字节，而VARCHAR则占用实际长度+1个字节。
但是CHAR的查询效果要高于VARCHAR，所以说，如果字段的长度能够确定的话，比如手机号，身份证号之类的字段，可以用CHAR类型，像地址，邮箱之类的就用VARCHAR
4.3 TEXT类型
TEXT系列的存储范围比VARCHAR要大，当VARCHAR不满足时可以用TEXT系列中的类型。需要注意的是TEXT系列类型的字段不能有默认值，在检索的时候不存在大小写转换，没有CHAR和VARCHAR的效率高
4.4 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);等同于"女"
下面我们插入一条不是枚举集合中的数据试一下，可以看到是插入失败的
五、时间日期类型
TIME 范围是’-838:59:59.000000’ 到’838:59:59.000000’
DATE 支持的范围是 ‘1000-01-01’到 ‘9999-12-31’
DATETIME 日期和时间组合。支持的范围是 ‘1000-01-01 00:00:00.000000’到 ‘9999-12-31 23:59:59.999999’。
TIMESTAMP 时间戳。范围是’1970-01-01 00:00:01.000000’UTC到’2038-01-19 03:14:07.999999’UTC。
YEAR 范围是 1901到2155
5.1 TIME
我们可以看到TIME的存储范围是’-838:59:59’到 ‘838:59:59’，因为TIME类型不仅可以用于表示一天中的时间，还可以用于表示两个事件之间的经过时间或时间间隔，
TIME的完整的显示为 D HH:MM:SS
D：表示天数，当指定该值时，存储时小时会先乘以该值 HH：表示小时 MM：表示分钟 SS:表示秒
CREATE TABLE time_db(a TIME)
有很多奇特的插入数据方式，这里只列举常见的，
INSERT time_db() VALUES('22:14:16');
INSERT time_db() VALUES('-2 22:14:16');-- -2表示间隔了2两天
INSERT time_db() VALUES('14:16'); -- 有冒号从小时开始
INSERT time_db() VALUES('30');-- 没有冒号且没有天数则数据从秒开始
5.2 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”);
5.3 DateTime 和 TimeStamp 类型
两者插入数据方式类似，范围不用
INSERT datetime_db() VALUES(20180102235432);
INSERT datetime_db() VALUES("2015-04-21 21:14:32");
INSERT datetime_db() VALUES("2015-04-23");
5.4 YEAR 类型
插入数据方式：
INSERT year_db() VALUES("1993");
INSERT year_db() VALUES(1993);
六、参考链接
参考链接：https://blog.csdn.net/yuzhiqiang_1993/article/details/81453569