7. MySQL数据类型

前言¶

每种编程语言都有自己的数据模型，这些数据模型互不相同，而且通常有相当大的差异。

多数编程语言处理数据所遵循的基本原则是，每个程序都可以访问我们用于表示存储区域的"框"。每个框都具有一个类型，比如 int 或 char。框中可以存储类型对应的值，通常将可以存储到这些框中的值称为数据对象。

我们还要为这些框命名。一般来说，框的名称可以是任何指示该框的表述性词语。我们通常会将框的名称视作该程序的变量，不过情况并非完全如此。

例如，如果 x 是递归函数 F 的局部变量，那么就可能会有很多名为 x 的框，每个 x 都与对 F 的不同调用相关联。这样的话，这种框的真实名称就是 x 与对 F 的某次调用的组合。

C 语言中的多数数据类型都是我们熟悉的：整数、浮点数、字符、数组、结构和指针。这些都是静态的概念。

类型系统包含整数这样的基本类型以及一些类型构成规则（type-formation rule），利用这些规则，我们可以用已知的类型逐步构建更为复杂的类型。

数据类型概述¶

MySQL 支持以下几种类别的 SQL 数据类型：

• 数值类型 numeric types
• "日期和时间"类型 date and time types
• 字符串（字符和字节）类型 string (character and byte) types
• 空间类型 spatial types
• JSON 数据类型

数据类型的描述使用了以下约定：

• 对于整型（integer），int(M) 中 M 表示最大的**显示宽度**（display width）；
• 对于浮点型（floating-point）和定点型（fixed-point）类型，M 表示可被存储数字的总个数（the precision）；
• 对于字符串类型，varchar(M) 中 M 是字符串中的字符数量。另外，M 的最大允许值取决于具体的数据类型和字符编码。
• D 应用于浮点型和定点型，它表示小数点后可存在多少个数字（the scale）。可能的最大值为 30，但不应大于 M-2。
• fsp 应用于 TIME、DATETIME 和 TIMESTAMP 类型，它表示小数的精度（即毫秒、微秒），即小数点后数字的个数。fsp 的取值范围为 [0, 6]，其中 0 表示没有小数。若缺省（即不传递该参数），默认精度为 0（与 SQL 标准的默认值 6 不同，主要为了兼容 MySQL 旧版本）。
• 方括号（[]）表示类型定义的可选部分。

数值类型¶

MySQL 支持 SQL 标准中所有数值数据类型，包括：

• 精确数值类型（INTEGER、SMALLINT、DECIMAL 和 NUMERIC）
• 近似数值类型（FLOAT、REAL 和 DOUBLE PRECISION）

关键字 INT 是 INTEGER 的别名，关键字 DEC 和 FIXED 是 DECIMAL 的别名。

MySQL 将 DOUBLE 视为 DOUBLE PRECISION 的别名（一种非标准扩展）。

除非启用 SQL 的 REAL_AS_FLOAT 模式，否则 MySQL 也把 REAL 视为 DOUBLE PRECISION 的别名（一种非标准变体）。

精确整型¶

MySQL 支持 SQL 标准的整型类型：INTEGER（或 INT）和 SMALLINT。不能存小数位。

作为标准 SQL 的扩展，MySQL 也支持这些整型类型：TINYINT、MEDIUMINT 和 BIGINT。以下表格展示了每种整型类型所需的存储空间和取值范围。

• TINYINT 在开发中，可以使用 TINYINT 类型的字段来存储一些状态标识或布尔值等信息，因为使用 TINYINT 类型能够有效地降低存储空间的占用。
• SMALLINT 通常用于需要较小整数值的情况，如统计数据、计算等。在实际开发中常常用于存储状态码、数量等数据。例如，在一个电商网站中，可以使用 SMALLINT 类型来存储订单的状态码，如 0：待付款，1：已完成，2：已取消等。
• MEDIUMINT 在实际开发中，MEDIUMINT 可以用于存储一些中等规模的数据，例如在一个博客系统中，可以使用 MEDIUMINT 类型来存储文章的浏览量或评论数量。
• INT 数据类型通常是存储整数值的首选类型，例如用户 ID、年龄、订单数量、金额等。在实际开发中，INT 也可以用于存储一些较大规模的数据，如浏览次数、喜欢次数等。

CREATE  TABLE demo_test (
    id INT(11)  NOT NULL AUTO_INCREMENT,
    a  INT(11)  NOT NULL,
    b  INT(11)  UNSIGNED ZEROFILL NOT NULL,
    PRIMARY KEY (`id`)
);

-- 数字数据类型既可以 UNSIGNED 属性修饰，也允许使用 SIGNED 属性。默认是 SIGNED 有符号的，可为负的。

-- 对于INTEGER类型，括号中的数字称为字段的显示宽度。显示宽度并不影响可以存储在该列中的最大值。INT(5) 和 INT(11)可以存储相同的最大值，都是INT最大值。
-- int(11)表示显示宽度，当列设置为UNSIGNED ZEROFILL时，INT(11)才有意义，其表示的意思为如果要存储的数字少于11个字符，则这些数字将在左侧补零。
-- 对于MySQL 8.0.17，INT类型的列宽度属性已经被deprecated

-- SERIAL 是 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的别名。



INSERT INTO demo_a(a,b) VALUES(1, 1);
-- 查询时，显示的宽度是11位数
mysql> SELECT * FROM demo_a;
+----+---+-------------+
| id | a | b           |
+----+---+-------------+
|  1 | 1 | 00000000001 |
+----+---+-------------+
1 row in set (0.18 sec)

定点型类型（精确值）¶

DECIMAL 和 NUMERIC 类型能存储精确的数值数据。可以存小数位。这些类型适用于重视精确度的需求，如货币数据。

-- 浮点类型和定点类型都可以用 TYPE(M, D)来表示，其中M称为精度，表示总共的位数；D称为标度，表示小数的位数。
-- FLOAT(7,4) 表示 -999.9999
-- 不论是定点还是浮点类型，如果用户指定的精度超出精度范围，则会四舍五入进行处理。
-- FLOAT 和 DOUBLE 在不指定精度时，默认会按照实际的精度（由计算机硬件和操作系统决定），DECIMAL 如果不指定精度，默认为（10，0）。
-- DOUBLE 实际上是以字符串的形式存放的，DECIMAL 可能的最大取值范围与 DOUBLE 相同，但是有效的取值范围由 M 和 D 决定。如果改变 M 而固定 D，则取值范围将随 M 的变大而变大。

-- 浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的范围；缺点是会引起精度问题。
-- 在MySQL中，定点数以字符串形式存储，在对精度要求比较高的时候（如货币、科学数据），使用 DECIMAL 的类型比较好，另外两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点数时需要注意，并尽量避免做浮点数比较。

整数字段设计¶

在真实业务场景中，整型类型最常见的就是在业务中用来表示某件物品的数量。例如电商业务中的产品表的销售数量，产品的库存数量、购买次数等。

自增列¶

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

自增主键还是 UUID 主键¶

UUID 是设计成一个时间和空间上全局唯一的数字。两次调用 UUID() 函数会产生不同的值。

返回的是是 128 位的数字，

数据溢出¶

MySQL 中的各种数值类型都有取值范围，所以插入过大的值肯定存在溢出。MySQL 如何处理溢出，取决于sql_mode：

• 如果是严格模式，处理溢出情况直接报错，与标准 SQL 的处理方式一致。

-

字符和字符串类型¶

字符类型包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET。

• 对于字符串列（CHAR、VARCHAR、TEXT），MySQL 以字符数量为单位声明长度。

• 对于二进制串（BINARY、VARBINARY、BLOB），MySQL 以字节数为单位声明长度。

• 字节：字节是计算机中的存储单位：1GB=1024MB，1MB=1024KB，1KB=1024Byte，1Byte=8Bit。其中的Byte就是字节。

• 字符(character)：可以理解为一个数字、字母、汉字、标点符号，比如：数字 1 是一个字符，数字 9 也是一个字符，但是数字 10 就是两个字符；字母 a 就是一个字符，字母 B 也是一个字符，字母 aB 就是两个字符；汉字你也是一个字符，汉字我也是一个字符，汉字你我就是两个字符；标点符号?是一个字符，标点符号!也是一个字符，当然这里的标点符号有全角和半角之分，但是不管全角还是半角，它们都是属于不同的字符，也就是说半角的?和全角的？是两个不同的字符。

在 MySQL 中，"变长"指的是存储在数据库中的数据长度是根据数据实际长度来变化的；而"定长"则指数据存储在数据库中时有固定的长度，在占用空间上比较刚性。

具体来说，在 MySQL 中，VARBINARY 和 VARCHAR 都是**变长数据类型**，存储的数据长度是可变的，因此在存储数据时不需要预留空间（除了一些额外的存储开销），从而节省了存储空间。

而在存储定长数据时，可以使用 BINARY 和 CHAR 等数据类型，这些类型的数据长度是固定的，无论实际存储的数据长度是多少，所占用的空间一直是固定的。另外，通常情况下，变长数据类型更适合用于存储文本数据，而定长数据类型则更适合用于存储二进制数据，如音频、视频、图像等。

• 在 MySQL5.0 以后的版本中，M 表示当前字段的字符集编码下，字符的个数，也叫列长度。对于不同的字符集编码，单个字符所占用的空间可能有所不同。
• row size limit 限制：无论什么字符集，无论什么类型的字符串，表中的一行数据的所有列加在一起最字节大长度限制为65535个 byte。（blob 和 text 类型的列只占用 9-12 个 bytes，因为它们是单独存储的）

对于列宽的限制：

• MySQL Server 层对单表支持最多 4096 列（参考），InnoDB 引擎层最多支持 1017 列。（参考）
• 如果表只有一个 utf8mb4 编码的 VARCHAR 列，列最大字符长度为 65535/4 = 16383
• MySQL 内部规定，表内所有列加在一起占用的字节数不能大于 65535 ，不管任何存储引擎，都不能超过这个范围，即存储引擎支持一行存储更长的数据。

• 对于 InnoDB 引擎，列宽还取决于 innodb_page_size，对于 4K,8K,16K 和 32K 的页面大小，限制一条记录最多使用半个页面，64K 页面比 16KB 页面限制稍小一些。参考

• 对于不同的字符集的字段，varchar 类型的列最大字符数量如下：

• 假设在 latin1 字符集下。插入值占用情况如下：

• 对于定长字符串，无论插入过长或过短，都要占据固定长度的字符数量和存储空间，过短会在右边补空字符，过长会截取。
• 对于变长字符串，实际存入多少，就占用多少。存储超过的，可能会报错（取决于是否设置为严格模式）或者截取。

CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
INSERT INTO vc VALUES ('12', '12');
INSERT INTO vc VALUES ('ab  ', 'ab  ');
INSERT INTO vc VALUES ('123456', '123456'); --这行会报错:Data to long...
SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;






-- 列宽限制

-- 对于这样的建表语句，直接报错
-- ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535
CREATE TABLE t (
    a VARCHAR(10000),
    b VARCHAR(10000),
    c VARCHAR(10000),
    d VARCHAR(10000),
    e VARCHAR(10000),
    f VARCHAR(10000),
    g VARCHAR(6000)
) ENGINE=InnoDB CHARACTER SET latin1;


CREATE TABLE t (
    a VARCHAR(10000),
    b VARCHAR(10000),
    c VARCHAR(10000),
    d VARCHAR(10000),
    e VARCHAR(10000),
    f VARCHAR(10000),
    g TEXT(6000)
) ENGINE=InnoDB CHARACTER SET latin1;

MySQL 为什么建议定义 varchar(255) 而不是 varchar(256)¶

我们在定义一个字段varchar(n)的时候，数据库在底层要知道这个字段的最大长度是多少。这个有点像元数据。

• 我们定义为 varchar(1)，那么他就知道这个字段的最大长度为 1，底层要记录好这个 1；

• 我们定义为 varchar(255)，那么他就知道这个字段的最大长度为 255，底层要记录好这个 255；

• 我们定义为 varchar(256)，那么他就知道这个字段的最大长度为 256，底层要记录好这个 256；

数据在底层存储的时候，它不是直接把 1 或 255 这样的整数直接以十进制的方式存储的，它是把 1 和 255 这样的整数转换为二进制的方式来存储的，全部都是 0 或 1 的方式来存储。

关于字段最大长度标识的元数据，如果是 255，用 tinyint 来标识，元数据占用一个字节。如果超过 255，要用 smallint 来标识，元数据占用两个字节。

当实际长度大于 255 的时候，varchar 变长字段最大长度需要用两个字节存储描述，也就意味着每一行数据都会增加 1 个字节，所以在我们的数据长度不可能超过 255 的情况下，我们尽量不要创建超过 255 长度的 varchar 类型的字段。

当我们定义一个 varchar(255)的字段时，它真实使用的空间是 256（255+1）字节；（注意，字段非空，latin1 编码）

当我们定义一个 varchar(256)的字段时，它真实使用的空间是 258（256+2）字节

--验证


-- 试着创建一个字段长度超过mysql限制的表，从系统提示知道，列长度不能超过65535
mysql> create table test_varchar_length(v varchar(65536) not null) CHARSET=latin1;
ERROR 1074 (42000): Column length too big for column 'v' (max = 65535); use BLOB or TEXT instead

-- 创建一个字段长度小于max, 65534的表，继续失败
mysql> create table test_varchar_length(v varchar(65534) not null) CHARSET=latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

-- 创建一个65533的表，65533+2=65535(max), 成功
mysql> create table test_varchar_length(v varchar(65533) not null) CHARSET=latin1;
Query OK, 0 rows affected (0.05 sec)

varchar 变长为什么不是越大越好？¶

varchar(10) 和 VARCHAR(1000) 都是变长，反正不额外占用存储，为什么不是越大越好，省得以后要加长又要改变字段定义。

• 第一个原因，存储字段时，字段最大字符长度这个元数据也要占不同空间，上面说的 255 和 256 区别，避免浪费
• 字段长度是数据库一种约束，可以保证进入数据库的数据符合长度要求，定义合理的字段长度可以减少一部份非法数据进入。

长文本类型¶

TEXT 是一个能够存储大量的数据的大对象，有四种类型：TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, 不同类型存储的值范围不同

TEXT 值被视为非二进制字符串（字符串）。它们具有 binary 以外的字符集，并根据字符集的校对方式对值进行排序和比较。

BLOB 是二进制大对象，可容纳可变数据量。四种 BLOB 类型是 TINYBLOB 、 BLOB 、 MEDIUMBLOB 和 LONGBLOB

BLOB 值被视为二进制字符串（字节字符串）。它们具有 binary 字符集和校对，比较和排序基于列值中字节的数值。

日期和时间类型¶

MySQL 提供了多种存储时态 （与时间相关的）值的数据类型。主要有 YEAR、TIME、DATE、DATETIME、TIMESTAMP 等。

每个时间类型均拥有各自合法的取值范围，以及一个零值，即当你指定一个 MySQL 无法表达的非法值时会被替换为该值。TIMESTAMP 和 DATETIME 数据类型拥有特殊的自动更新能力。

二进制类型¶

MySQL 支持两类字符型数据：文本字符串和二进制字符串。二进制字符串类型有时候也直接被称为二进制类型。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，只是它们存储的是二进制字符串。这意味着它们拥有binary字符集和排序规则。

简单说，二进制字符串适用于存储那些程序员认为是"原始字节"的数据， 而字符串适合存储文本。

• BINARY (M)为固定长度的二进制字符串，M 表示最多能存储的字节数，取值范围是 0~255 个字符。如果未指定(M)，表示只能存储 1 个字节。例如 BINARY (8)，表示最多能存储 8 个字节，如果字段值不足(M)个字节，将在右边填充'0'以补齐指定长度。

• VARBINARY (M)为可变长度的二进制字符串，M 表示最多能存储的字节数，总字节数不能超过行的字节长度限制 65535，另外还要考虑额外字节开销，VARBINARY 类型的数据除了存储数据本身外，还需要 1 或 2 个字节来存储数据的字节数。VARBINARY 类型必须指定(M)，否则报错。

CREATE TABLE t1 (
    a CHAR(4) BINARY,
    b VARCHAR(255)  BINARY,
    c BINARY(4)
);

 INSERT INTO t1 VALUES("尚方宝剑","ABCD","abc");

 SELECT * FROM t1;

注意，char binary 和 varchar binary 并不是二进制串，它们本质上还是 文本字符串 。后面的binary属性只是用于修饰其排序规则

-- 对于默认地utf8mb4字符集

CHAR(5) BINARY ;  --这个是文本字符串，binary只是用于修饰排序规则，等价于下面的完整显式定义
CHAR(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ;



BINARY(5) ;  --这个是二进制串，存了5字节位的二进制数据

枚举类型¶

空间类型¶

JSON 类型¶

Table Schema 就是强一致的，约束开发不要乱搞，json 这种弱约束的就是开后门，时间一长 json 字段就成了下水道 MySQL JSON 很垃圾，5.7 系列都有性能问题，测试 8.0 好很多。强烈建义大家，使用前压测一下

布尔类型¶

大多数编程语言都包括布尔型（ BOOL 型、 BOOLEAN 型）这种数据类型。当然，SQL 语言里也有。SQL-99 里将布尔型定义为可以由用户直接操作的数据类型。此外，在 WHERE 子句等地方进行条件判断时也经常会用到布尔型的运算。

虽然 boolean 型是 SQL 标准的数据类型，但很多数据库都没有提供 boolean 类型，理由是这个数据类型太容易代替了，没有必要专门用一个数据类型。比如用 char(1)或者 tinyint 等。

MySQL 中，有 boolean 类型，但这个类型是 tinyint(1)的同义词，就是说 boolean 也是用数值存储的。阿里的数据库设计规范也是要求使用 tinyint(1)存储 boolean 类型。在使用时，可以使用 0 或 1 来代替 False 或 True，也可以使用关键字 TRUE 或 FALSE 来表示 True 或 False

CREATE TABLE example (
    id INT PRIMARY KEY,
    is_active TINYINT(1)
);
INSERT INTO example (id, is_active) VALUES (1, 1);  -- 在插入时，使用1表示真值。
SELECT * FROM example WHERE is_active = TRUE;       -- 在查询时，使用了TRUE关键字来代替1。

-- 使用1或0来代表True或False，而不是使用其他数字。虽然TINYINT类型可以存储-128到127之间的整数，但是在使用TINYINT(1)表示布尔类型时，建议只使用1或0来表示True或False。
-- 注意在使用ORM框架时，可能需要进行特殊处理，以便正确地处理TINYINT(1)类型的数据。有些ORM框架可能会将TINYINT(1)类型的数据转换为布尔类型，而有些则可能会将其转换为整数类型。因此，在使用ORM框架时，需要仔细查看文档，以确保正确地处理TINYINT(1)类型的数据。

数据库中的 boolean 字段，以什么方式命名？通常会要求 boolean 值以 is 开头，比如阿里的数据库设计规范强制要求 boolean 型以 is_开头，比如 is_deleted。

MySQL 中的 NULL 值¶

大多数编程语言都是基于二值逻辑的，即逻辑真值只有真和假两个。而 SQL 语言则采用一种特别的逻辑体系——三值逻辑，即逻辑真值除了真和假，还有第三个值“不确定”。

作为计算机基础的布尔代数是二值逻辑的，我们在中小学里学的数学和逻辑学也是基于二值逻辑的，关系模型理论基础之一的谓词逻辑也是二值逻辑的。

在二值逻辑的应用如此广泛的情况下，为什么关系数据库的世界特立独行，选择了三值逻辑这样风格迥异的逻辑体系呢？

关系数据库里引进了 NULL ，所以不得不同时引进第三个值。这样的三值逻辑一次次地违背常识，深深地困扰着数据库工程师们。

SQL 里只存在一种 NULL 。然而在讨论 NULL 时，我们一般都会将它分成两种类型来思考。

两种 NULL 分别指的是“未知”（unknown） 和“不适用”（notapplicable, inapplicable）。

以“不知道戴墨镜的人眼睛是什么颜色”这种情况为例，这个人的眼睛肯定是有颜色的，但是如果他不摘掉眼镜，别人就不知道他的眼睛是什么颜色。

这就叫作未知。而“不知道冰箱的眼睛是什么颜色”则属于“不适用”。因为冰箱根本就没有眼睛，所以“眼睛的颜色”这一属性并不适用于冰箱。

“冰箱的眼睛的颜色”这种说法和“圆的体积”“男性的分娩次数”一样，都是没有意义的。平时，我们习惯了说“不知道”，但是“不知道”也分很多种。

“不适用”这种情况下的 NULL ，在语义上更接近于“无意义”，而不是“不确定”。

这里总结一下：“未知”指的是“虽然现在不知道，但加上某些条件后就可以知道”；而“不适用”指的是“无论怎么努力都无法知道”。

Codd 曾经认为应该严格地区分两种类型的 NULL ，并提倡在关系数据库中使用四值逻辑。

不知道是幸运还是不幸（笔者认为肯定是幸运），他的这个想法并没有得到广泛支持，现在所有的 DBMS 都将两种类型的 NULL 归为了一类并采用了三值逻辑。

但是他的这种分类方法本身还是有很多优点的，因此后来依然有很多学者支持。

三值逻辑通过导入第三个真值，从语义学的角度对二值逻辑发起了挑战；而直觉主义逻辑从语法学的角度对二值逻辑发起了挑战。自此以摧枯拉朽之势一扫十九世纪后期以来逻辑学停滞不前的阴霾，非古典逻辑学迎来了百花齐放的春天。

二值原理的意思是“一个命题必然有真假”。虽然二值原理非常简洁，但是对于我们人类而言，并不能那么轻易地认同它。

在这个充满不确定性的世界里，无法判断真假的命题难道不是有很多吗（例如“神是存在的”“存在死后的世界”“杀人是罪恶的”）。

在某次演讲会上，他说出了自己的一个观点——“在圆周率 π 的小数部分，9 将重复出现 10 次”这个命题无法判断真假。

有一位听众这样反驳：“也许我们人类无法判断，但是神肯定知道它的真假”。讲台上的布劳威尔这样回答： “但是，我们没有他老人家的电话号码啊”。

我们人类和神中断了联系——布劳威尔和卢卡西维茨都生活在这样一个暗淡的时期。人类和神失去了联系之后，只能以有限的认知活在有限的世界里。

既然如此，多出一种与人类有限的认知相称并且能够适当地描述这个充满未知的世界的新的逻辑学，难道不是一件好事吗？

数据库的使用者当然是人类，而不是神。因此，数据的表达方式也应该基于有限而且不完善的人类的认知，而不是神的完美无缺的认知。

这就是关系数据库采用三值逻辑的原因。

在 SQL 中建表，每个字段后面都可以加上 NULL 或 NOT NULL 修饰符来指定该字段是否可以为空(NULL)。MySQL 默认情况下指定字段为 NULL 修饰符。

如果一个字段指定为 NOT NULL，MySQL 则不允许向该字段插入空值(这里面说的空值都为 NULL)。在标准 SQL 中也可以称为 NOT NULL 约束。

SQL92 标准中定义了 NULL 不与任何值相等, NULL 只是代表 missing values

但是在自增列和 TIMESTAMP 字段中，这个规则并不适用。向这些字段中插入 NULL 值将会导致插入下一个自动增加的值或者当前的时间戳。

NULL 不是一个「值」，而是「没有值」。

• 判断是否为 NULL 只能用where name is null或 where name is not null 这种语句。

• 在 MySQL 中将 null 与其他字段或者变量（函数、存储过程）进行算术运算或逻辑运算，结果总是 NULL

• count 计数，COUNT(*)：不管字段为什么，计算存在的行，COUNT(column_name)：结果不包含字段为 NULL 的记录。
• 排序，ASC时，NULL 值在所有其他值之前，DESC时，NULL 值在所有其他值之后。请注意与查询中的范围匹配区分，这只是一种约定，并不是因为 NULL 更小

除了上面使用的困惑，NULL 值过多会影响统计信息，可能影响执行计划。MySQL 很不负责的把对 NULL 值的统计方式交给了用户 innodb_stats_method, 默认值是 nulls_equal

Specifies how InnoDB index statistics collection code should treat NULLs. Possible values are NULLS_EQUAL (default), NULLS_UNEQUAL and NULLS_IGNORED

• 当变量设置为 nulls_equal 时，所有 NULL 值都被视为相同(即，它们都形成一个 value group)
• 当变量设置为 nulls_unequal 时，NULL 值不被视为相同。相反，每个 NULL value 形成一个单独的 value group, 大小为 1
• 当变量设置为 nulls_ignored 时，将忽略 NULL 值

阿里也有过一篇文章，讲 unique 索引有 NULL 值导致主备延迟，感兴趣的可以看看

在写 SQL 条件语句时关于字段判断或排序时，要注意考虑字段中可能为 NULL 的情况。

• 一般强烈建议为业务字段设置为 not null ， 如果插入时没有值，可以设置为空字符串或业务字段的零值。最骚的是有人，给一个字段赋值 'NULL', 注意是字符串 'NULL' 不是 NULL

-- 示例

mysql> select 1 is  null  , 1 is not null , 1 and null , 1 = null , 1 > null , 1 + null  , null = null , null is null ;
+------------+---------------+------------+----------+----------+----------+-------------+--------------+
| 1 is  null | 1 is not null | 1 and null | 1 = null | 1 > null | 1 + null | null = null | null is null |
+------------+---------------+------------+----------+----------+----------+-------------+--------------+
|          0 |             1 |       NULL |     NULL |     NULL |     NULL |        NULL |            1 |
+------------+---------------+------------+----------+----------+----------+-------------+--------------+
1 row in set (0.00 sec)

mysql>

 mysql> select length(''), length(null), length(0), length('0');
 +------------+--------------+-----------+-------------+
 | length('') | length(null) | length(0) | length('0') |
 +------------+--------------+-----------+-------------+
 |          0 |         NULL |         1 |           1 |
 +------------+--------------+-----------+-------------+
 1 row in set (0.00 sec)

参考

选择正确的数据类型¶

选择的原则是：

• 更小的更好：更小的数据类型通常更快，因为它们占用更少的磁盘、内存和 CPU 缓存，并且处理时需要的 CPU 周期也更少。

• 尽量避免 NULL：

• 整形比字符操作代价更低。

• 保存日期格式时，要选用 MySQL 内置的日志格式（TimeStamp, DateTime），而不使用字符串。

• 小数点的保存如何有涉及到运算，用 float 和 double 可能会精度不准，可以考虑将小数乘于相应的倍数，然后用 BIGINT 来存储。

• 当数据存储的是字符，且长度是一个固定区间定值的话就可以考虑使用 char 来进行存储，如果字符长度是未知的且长度变化特别明显的话，这个时候最好使用 varchar 来存储。但是不管使用的是哪种字段来进行存储，都不要把字段的初始长度设置为最大化，应该是根据业务需求来存储最合适的长度字段。

• 数据库设计过程中尽量使用 int 来作为字段类型，因为在所有的数据类型中 int 不管是存储空间还是执行速度方面都是最好的。例如：如果业务中存储的都是数据的话而且长度不是特别长的话，就可以考虑使用 int 来进行存储，或者业务中要对数据进行排序的时候需要使用某一标识权重之类的，也可以使用 tinyint 来进行存储。但是不要因为 int 高效，而有意识的把所有字段都设计成 int 来处理，最终还是要根据业务的具体需求来设计相应的字段。

InnoDB 数据行格式¶

InnoDB 存储引擎和大多数数据库一样，记录是以行的形式存储的，这意味着页中保存着表中一行行的数据。数据一行一行的存储在页中。一页存满了，马上到下一页。

在 InnoDB 1.0.X 之前，InnoDB 存储引擎提供了 Compact 和 Redundant 两种格式来存放行记录数据。

Redundant 是 mysql5.0 版本之前的行记录存储方式，之后仍然支持这个格式是为了兼容之前版本的格式，5.1 之后很少用到了。

因为 Compact 的结构设计比它好得多，Compact 格式消耗的磁盘空间和备份耗时更小，Redundant 相比之下大了一些。compact 格式更适用于大多数的业务场景。

在 InnoDB 1.0.X 版本开始又引入了新的文件格式(file format)，以前支持 Compact 和 Redundant 格式称为 Antelope 文件格式，新引入的文件格式称为 Barracuda 文件格式。

Barracuda 文件格式下拥有两种新的行记录格式：Compressed 和 Dynamic，同时，Barracuda 文件格式也包括了 Antelope 所有的文件格式。这样 Barracuda 文件格式支持 4 种 row_format：

Redundant、Compact、Compressed、Dynamic

所谓 Row Format 行格式是指数据记录(或者称之为行)在磁盘中的物理存储方式：

具体地，对于 InnoDB 存储引擎而言，常见的行格式类型有如下四种：Compact、Redundant、Dynamic 和 Compressed

-- 配置文件指定 InnoDB 表的默认文件格式。5.7和8.0的 innodb_default_row_format 默认值都是是 DYNAMIC
show variables like 'innodb_default_row_format';

-- 用户可以在建表时，显示指定表的 row format，也可以是其他格式
CREATE TABLE t1 (c1 INT) ROW_FORMAT=DYNAMIC;

现在基本上都是 Barracuda 格式。Dynamic 和 Compact 基本是类似的，但是它们在行溢出数据的处理上却完全不同：

表的行格式决定了它的每行数据是怎么物理存储的，其对查询和 DML 操作也是有影响。每个磁盘页存的行数越多，查询和索引的查找就越快，缓冲池需要的内存也越少，同时也能减少更新数据的 I/O。

每个表的数据都被分成多个页，这些页都存在 B-tree 索引的树数据结构中。表数据和非主键索引都用这种数据结构。保存了整个表数据的 B-tree 索引叫做聚簇索引，它是根据表的主键来进行组织的。聚簇索引树的节点保存了一行的所有列的值，非主键索引的节点只包含索引列和主键列的值。

变长列在 B-树索引节点的存储策略不太一样，如果长度超过了 B-树页的长度，则它们会保存在单独申请的磁盘页中，即溢出页。这些列也叫做 off-page 列。off-page 列的值保存在多个溢出页中，这些溢出页使用单独的链表连接在一起，每个列都有它自己的溢出页链表。为了避免浪费空间或者读取额外的页数据，当列长度超过 B-树页大小时，会将变长列的部分前缀串保存在 B-树种。

InnoDB 存储引擎支持四种行格式： REDUNDANT, COMPACT, DYNAMIC, COMPRESSED。

REDUNDANT¶

REDUNDANT format 提供了对老版本 MySQL 的兼容性。

COMPACT¶

对于像 varchar， varbinary，text，blob，json 以及他们的各种类型的**可变长度字段**，需要将他们到底占用多少字节存储起来，这样就省去了列数据之间的边界定义，MySQL 就可以分清楚哪些数据属于这一列，那些不属于。Compact 行格式存储，开头就是变长字段长度列表，这个列表包括**数据不为 NULL**的每个**可变长度字段**的长度，并按照列的顺序**逆序**排列。

数据类型转换¶

自定义数据类型¶

用户定义数据类型并不是真正的数据类型，它只是提供了一种提高数据库内部元素和基本数据类型之间一致性的机制。

在 MySQL 中，数据类型用于定义列的类型和属性。MySQL 提供了一些基本数据类型，如整数、字符、日期等。但在某些情况下，这些基本数据类型可能不足以满足我们的需求。

自定义数据类型允许我们创建自己的数据类型，以便更好地满足特定业务需求。通过自定义数据类型，我们可以将一组相关的数据和操作封装在一个单一的数据类型中，并将其作为列的数据类型使用。