MySQL字段类型和约束

MySQL 中有很多字段类型，比如整数、文本、浮点数，等等。
如果类型定义合理，就能节省存储空间，提升数据查询和处理的速度，相反，如果数据类型定义不合理，就有可能会导致数据超出取值范围，引发系统报错，甚至可能会出现计算错误的情况，进而影响到整个系统。
准确地定义字段类型，不但关系到数据存储的效率，而且会影响整个信息系统的可靠性。

1、整数类型

整数类型一共有 5 种，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT，它们的区别如下表所示：

整数.png

这么多整数类型，该怎么选择呢？
其实，在评估用哪种整数类型的时候，你需要考虑存储空间和可靠性的平衡问题：
一方面，用占用字节数少的整数类型可以节省存储空间；
另一方面，要是为了节省存储空间，使用的整数类型取值范围太小，一旦遇到超出取值范围的情况，就可能引起系统错误，影响可靠性。

2、浮点数类型和定点数类型

浮点数和定点数类型的特点是可以处理小数，你可以把整数看成小数的一个特例。因此，浮点数和定点数的使用场景，就比整数大多了。
MySQL 支持的浮点数类型，分别是 FLOAT、DOUBLE、REAL。

FLOAT 表示单精度浮点数；
DOUBLE 表示双精度浮点数；
REAL 默认就是 DOUBLE。如果你把 SQL 模式设定为启用“REAL_AS_FLOAT”，那么，MySQL 就认为 REAL 是 FLOAT。如果要启用“REAL_AS_FLOAT”，就可以通过以下 SQL 语句实现：

SET sql_mode = “REAL_AS_FLOAT”;

FLOAT 和 DOUBLE 这两种数据类型的区别是啥呢？
其实就是，FLOAT 占用字节数少，取值范围小；DOUBLE 占用字节数多，取值范围也大。
浮点数类型有个缺陷，就是不精准。因此，在一些对精确度要求较高的项目中，千万不要使用浮点数，不然会导致结果错误，甚至是造成不可挽回的损失。

为什么会存在这样的误差呢？
问题还是出在 MySQL 对浮点类型数据的存储方式上。MySQL 用 4 个字节存储 FLOAT 类型数据，用 8 个字节来存储 DOUBLE 类型数据。
无论哪个，都是采用二进制的方式来进行存储的。比如 9.625，用二进制来表达，就是 1001.101，或者表达成 1.001101×2^3。看到了吗？如果尾数不是 0 或 5（比如 9.624），你就无法用一个二进制数来精确表达。怎么办呢？就只好在取值允许的范围内进行近似（四舍五入）。
现在你一定明白了，为什么数据类型是 DOUBLE 的时候，我们得到的结果误差更小一些，而数据类型是 FLOAT 的时候，误差会更大一下。原因就是，DOUBLE 有 8 位字节，精度更高。

那么，MySQL 有没有精准的数据类型呢？
当然有，这就是定点数类型：DECIMAL。
就像浮点数类型的存储方式，决定了它不可能精准一样，DECIMAL 的存储方式决定了它一定是精准的。浮点数类型是把十进制数转换成二进制数存储，DECIMAL 则不同，它是把十进制数的整数部分和小数部分拆开，分别转换成十六进制数，进行存储。
这样，所有的数值，就都可以精准表达了，不会存在因为无法表达而损失精度的问题。MySQL 用 DECIMAL（M,D）的方式表示高精度小数。
其中，M 表示整数部分加小数部分，一共有多少位，M<=65。D 表示小数部分位数，D<M。

3、文本类型

TEXT 类型是 MySQL 支持的文本类型的一种。此外，MySQL 还支持 CHAR、VARCHAR、ENUM 和 SET 等文本类型。

CHAR(M)：固定长度字符串。CHAR(M) 类型必须预先定义字符串长度。如果太短，数据可能会超出范围；如果太长，又浪费存储空间。
VARCHAR(M)：可变长度字符串。VARCHAR(M) 也需要预先知道字符串的最大长度，不过只要不超过这个最大长度，具体存储的时候，是按照实际字符串长度存储的。
TEXT：字符串。系统自动按照实际长度存储，不需要预先定义长度。
ENUM：枚举类型，取值必须是预先设定的一组字符串值范围之内的一个，必须要知道字符串所有可能的取值。
SET：是一个字符串对象，取值必须是在预先设定的字符串值范围之内的 0 个或多个，也必须知道字符串所有可能的取值。

TEXT类型也有 4 种，它们的区别就是最大长度不同。

TINYTEXT：255 字符（这里假设字符是 ASCII 码，一个字符占用一个字节，下同）。
TEXT： 65535 字符。
MEDIUMTEXT：16777215 字符。
LONGTEXT： 4294967295 字符（相当于 4GB）。

不过，需要注意的是，TEXT 也有一个问题：由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键。
遇到这种情况，只能采用 CHAR(M)，或者 VARCHAR(M)。

4、日期与时间类型

用得最多的日期时间类型，就是 $\color{#FF3030}{DATETIME}$
虽然 MySQL 也支持 $\color{#FF3030}{ YEAR}$ （年）、 $\color{#FF3030}{TIME}$ （时间）、 $\color{#FF3030}{DATE}$ （日期），以及 $\color{#FF3030}{TIMESTAMP}$ 类型，但是建议你，在实际项目中，尽量用 DATETIME 类型。因为这个数据类型包括了完整的日期和时间信息，使用起来比较方便。
毕竟，如果日期时间信息分散在好几个字段，就会很不容易记，而且查询的时候，SQL 语句也会更加复杂。

image.png

默认约束，主键约束、外键约束、非空约束、唯一性约束和自增约束。

1. 非空约束

非空约束表示字段值不能为空，如果创建表的时候，指明某个字段非空，那么添加数据的时候，这个字段必须有值，否则系统就会提示错误。

2. 唯一性约束

唯一性约束表示这个字段的值不能重复，否则系统会提示错误。
跟主键约束相比，唯一性约束要更加弱一些。在一个表中，我们可以指定多个字段满足唯一性约束，而主键约束则只能有一个，这也是 MySQL 系统决定的。
另外，满足主键约束的字段，自动满足非空约束，但是满足唯一性约束的字段，则可以是空值。

3. 自增约束

自增约束可以让 MySQL 自动给字段赋值，且保证不会重复。

第一，在数据表中，只有整数类型的字段（包括 TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT），才可以定义自增约束。自增约束的字段，每增加一条数据，值自动增加 1。
第二，可以给自增约束的字段赋值，这个时候，MySQL 会重置自增约束字段的自增基数，下次添加数据的时候，自动以自增约束字段的最大值加 1 为新的字段值。

在修改表时，我们可以通过修改已经存在的表创建新表，也可以通过添加字段、修改字段的方式来修改数据表。

CREATE TABLE(
字段名 字段类型 PRIMARY KEY
);
CREATE TABLE(
字段名 字段类型 NOT NULL
);
CREATE TABLE(
字段名 字段类型 UNIQUE
);
CREATE TABLE(
字段名 字段类型 DEFAULT 值
);
-- 这里要注意自增类型的条件，字段类型必须是整数类型。
CREATE TABLE(
字段名 字段类型 AUTO_INCREMENT
);
-- 在一个已经存在的表基础上，创建一个新表
CREATE TABLE demo.importheadhist LIKE demo.importhead;
-- 修改表的相关语句
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型;
ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 FIRST|AFTER 字段名;
ALTER TABLE 表名 MODIFY 字段名 字段类型 FIRST|AFTER 字段名;

可以在表一级指定表的存储引擎：

ALTER TABLE 表名 ENGINE=INNODB;

还可以通过指定关键字 AUTO_EXTENDSIZE，来指定存储文件自增空间的大小，从而提高存储空间的利用率。


INSERT INTO 表名 [(字段名 [,字段名] ...)] VALUES (值的列表);
 
INSERT INTO 表名 （字段名）
SELECT 字段名或值
FROM 表名
WHERE 条件
 
DELETE FROM 表名
WHERE 条件
 
UPDATE 表名
SET 字段名=值
WHERE 条件

SELECT *|字段列表
FROM 数据源
WHERE 条件
GROUP BY 字段
HAVING 条件
ORDER BY 字段
LIMIT 起始点，行数