MySQL约束和数据类型

MySQL约束和数据类型
约束条件
约束条件就是在给字段加⼀些约束，使该字段存储的值更加符合我们的预期。

常⽤约束条件如下：
UNSIGNED ：⽆符号，值从0开始，⽆负数
ZEROFILL：零填充，当数据的显⽰长度不够的时候，在数据前使⽤0填充⾄指定长度,字段会⾃动添加UNSIGNED
NOT NULL：⾮空约束，表⽰该字段的值不能为空
DEFAULT：表⽰如果插⼊数据时没有给该字段赋值，那么就使⽤默认值
PRIMARY KEY：主键约束，表⽰唯⼀标识，不能为空，且⼀个表只能有⼀个主键。

⼀般都是⽤来约束id
AUTO_INCREMENT：⾃增长，只能⽤于数值列，⽽且配合索引使⽤,默认起始值从1开始，每次增长1
UNIQUE KEY：唯⼀值，表⽰该字段下的值不能重复，null除外。

⽐如⾝份证号是⼀⼈⼀号的，⼀般都会⽤这个进⾏约束
FOREIGN KEY：外键约束，某个表的字段是另⼀个表的主键，我们称这个字段为另⼀个表的外键。

⽬的是为了保证数据的完成性和唯⼀性，以及实现⼀对⼀或⼀对多关系
MySQL数据类型
MySQL⽀持多种类型的SQL数据类型：数值，⽇期和时间类型，字符串（字符和字节）类型，空间类型和 JSON数据类型等
数据类型描述使⽤以下约定：
M表⽰整数类型的最⼤显⽰宽度。

M表⽰整数类型的最⼤显⽰宽度。

对于浮点和定点类型， M是可以存储的总位数（精度）。

对于字符串类型，M是最⼤长度。

允许的最⼤值M取决于数据类型。

D适⽤于浮点和定点类型，并指⽰⼩数点后⾯的位数。

最⼤可能值为30，但不应⼤于 M-2。

[ ]表⽰类型定义的可选部分。

在MySQL中常⽤数据类型主要分为以下⼏类
数值类型
字符串类型
⽇期时间类型
1、数值类型
数值类型包括整数型、浮点型、定点型
1.1 整数型
类型⼤⼩范围（有符号）范围（⽆符号）⽤途
TINYINT 1 字节(-128，127)(0，255)⼩整数值SMALLINT 2 字节(-32 768，32 767)(0，65 535)⼤整数值MEDIUMINT 3 字节(-8 388 608，8 388 607)(0，16 777 215)⼤整数值
INT或INTEGER4 字节(-2 147 483 648，2 147 483 647)(0，4 294 967 295)⼤整数值BIGINT8 字节(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)(0，18 446 744 073 709 551 615)极⼤整数值
有符号表⽰数值可以为负数，⽆符号表⽰数值为⾮负数，如果不⼿动指定UNSIGNED，那么默认就是有符号的。

创建⼀个表
CREATE TABLE int_db(
a TINYINT,
b SMALLINT,
c MIDDLEINT,
d INT,
e BIGINT
);
查看表结构
我们来看看type这⼀列，可以看到，每个字段类型后⾯都有⼀个括号，括号⾥⾯的有个数值，这个数值实际上就是字段的显⽰宽度，也就是M的值，M表⽰整数类型的最⼤显⽰宽度。

最⼤显⽰宽度为255.显⽰宽度与类型可包含的值范围⽆关
我们在创建表的时候并没有指定字段类型的显⽰宽度，那么，默认的显⽰宽度则是该字段类型最⼤的显⽰宽度
例如字段a的显⽰宽度为4，是因为TINYINT有符号值的范围是-128到127，
-128的长度为4（负号、1、2、8共四位），所以默认的显⽰宽度最⼤为4，其他的以此类推
下⾯我们再新建⼀个表，将字段a的修改为⽆符号类型
CREATE TABLE int_db(
a TINYINT UNSIGNED,
b SMALLINT,
c MIDDLEINT,
d INT,
e BIGINT
);
再看看a字段的默认显⽰宽度
可以看到，默认显宽度就变成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
);
可以看到，12变成了00000012，⾃动在前⾯补了0，这是因为指定的显⽰宽度是8，但是12只有两位，所以在前⾯补0，使长度为8。

这就是ZEROFILL 的效果
1.2 浮点型
类型⼤
⼩范围（有符号）范围（⽆符号）⽤途
FLOAT 4字节
(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402823 466 351 E+38)0，(1.175 494 351 E-38，3.402 823 466E+38)单精
度
浮点
数值
DOUBLE 8字节(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)双精度浮点数值创建表
CREATE TABLE float_db(
a FLOAT (3,2),
b DOUBLE (5,3)
);
指定a 字段为FLOAT 类型，字段总长度为3，⼩数点后长度为2,b 字段为DOUBLE 类型，字段总长度为5，⼩数点后长度为3
插⼊数据
INSERT float_db VALUES (1.1111,2.2222
);
可以看到，我们给a 字段的值是1.1111，但是只存进去了1.11 ，给b 字段的值是2.2222，但是只存进去了2.222
也就是说 FLOAR 和 DOUBLE 类型存储的⼩数位数与类型关系不打。

在这⾥我建议,⼲脆忘记mysql 有double 这个数据类型。

2、字符串类型
常⽤的字符串类型有如下
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个不同的元素
2.1 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"); #在严格模式下，该条数据会插⼊失败，⾮严格模式则会对数据进⾏截取
我们看到查询的结果是⼀样的，但实际上他们存储时占⽤的长度是不⼀样的。

CHAR类型不管存储的值的长度是多少，都会占⽤M个字节，⽽VARCHAR则占⽤实际长度+1个字节。

但是CHAR的查询效果要⾼于VARCHAR，所以说，如果字段的长度能够确定的话，⽐如⼿机号，⾝份证号之类的字段，可以⽤CHAR类型，像地址，邮箱之类的就⽤VARCHAR
2.2 TEXT系列
TEXT系列的存储范围⽐VARCHAR要⼤，当VARCHAR不满⾜时可以⽤TEXT系列中的类型。

需要注意的是TEXT系列类型的字段不能有默认值，在检索的时候不存在⼤⼩写转换，没有CHAR和VARCHAR的效率⾼
2.3 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); #等同于"⼥"
下⾯我们插⼊⼀条不是枚举集合中的数据试⼀下
3、⽇期时间类型
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
MySQL中的常⽤数据类型⼤概就这些。

我们在数据类型的选择上，数据类型能满⾜使⽤就⾏的，越⼩越好，⽐如⼈的年龄，任何整数类型都可以，但是我们⼀般使⽤TINYINT类型。