数据类型：概述

在 MySQL 中，可用的数据类型可以分为四大类：

• Numeric：数字值
• Binary : 二进制数据字符串
• 字符：文本字符串
• 时间：时间和日期值

在每个类别中，有许多特定的数据类型使用不同数量的内存和磁盘空间，因此对性能有不同的影响。
为列选择最佳数据类型对单个记录的性能影响很小，但随着数据库的增长，这些小的影响可能会导致更大的影响。
在设计过程的早期，在效果成为性能问题之前考虑到这一点。

MySQL 还支持空间扩展作为存储引擎功能。
这些支持字符和二进制格式的地理特征的生成、存储和分析。

数据类型的 ABC：

• 适当的：数据需要以最适合它所代表的项目的类型来表示。
• 简介：选择使用最少存储空间的数据类型。这可以节省资源并提高性能。
• 完整：选择分配足够空间来存储特定项目的最大可能值的数据类型。

列属性

列属性的类别：

数字：适用于数字数据类型（BIT 除外）

字符串：应用于非二进制字符串数据类型

常规：适用于任何数据类型

• TIMESTAMP 除外。

以下属性可用于所有数据类型：

• NULL 或者 NOT NULL ：如果我们未指定这些属性中的任何一个，则默认值是在列中允许 NULL。
• DEFAULT ：例如，当执行 INSERT 语句时使用默认值，该语句未为表中的所有列提供值。

选择数据类型

考虑哪些数据类型和字符集最小化存储和磁盘 I/O。

使用固定长度的数据类型： - 如果所有存储的字符串值具有相同的长度
使用变长数据类型：

• 如果存储的字符串值不同
• 对于多字节字符集

对常用字符使用较少空间的多字节字符集。

在基本多语言平面 (BMP) 之外使用其他 Unicode 字符集。

空间数据类型

MySQL 支持空间数据类型扩展，以实现地理特征的生成、存储和分析。
地理（或者地理空间）特征是世界上任何具有位置的事物：

• 实体：一座山、一座池塘或者一座城市
• 空间：城镇地区或者热带地区
• 可定义位置：十字路口（两条街道相交的特定地方）

我们可以对 InnoDB、MyISAM、NDB 和 ARCHIVE 存储引擎表使用空间扩展。
MyISAM 支持空间和非空间索引。
其他存储引擎支持非空间索引。

MySQL 实现了开放地理空间联盟 (OGC) 提出的“具有几何类型的 SQL”环境的一个子集。

用于单个几何值：

• GEOMETRY : 层次结构的根类，任何类型的值
• POINT：坐标空间中的单个位置
• CURVE : 一维几何，一系列点
• LINESTRING : 点之间线性插值的曲线
• SURFACE : 二维几何
• POLYGON ：表示多边几何的平面

用于保存几何值的集合：

• MULTIPOINT : 点元素
• MULTICURVE : 曲线元素
• MULTILINESTRING : LineString 元素
• MULTISURFACE : 表面元素
• MULTIPOLYGON : 多边形元素
• GEOMETRYCOLLECTION : 任何类的几何

使用列属性创建表

这决定了 MySQL 如何处理列：

CREATE TABLE t(
i INT UNSIGNED NOT NULL,
c CHAR(10) CHARACTER SET utf8,
d DATE DEFAULT '2013-01-01');

上面示例中的表包含一个 UNSIGNED 且不能包含 NULL 的整数列、一个字符集为 utf8 的字符串列和一个默认值为“2013-01-01”的日期列。

二进制字符串数据类型

二进制字符串数据类型基本上是一个字节序列。
例如，以八位分组的二进制数字（位）。
这些二进制类型是字符串类型，因为它们也是较小单元的序列。
与字符串不同，构成这种二进制字符串值的字节不代表字符。
因此，二进制字符串没有添加的字符语义，并且它们缺少字符串类存在的字符集和校对信息。

• Binary ：存储固定和可变长度的二进制字节字符串。
• BLOB ：在数据库管理系统中存储为单个值。 BLOB 通常是图像、音频或者其他多媒体对象，尽管有时二进制代码存储为 BLOB。在 MySQL 中，BLOB 与没有添加字符集和排序规则的 TEXT 类型非常相似。

数字数据类型

数据类型表示的值范围和列值所需的空间量是数字数据类型需要考虑的两个重要因素。
为了存储数值数据，MySQL 提供了以下数值数据类型类：

• 整数：整数没有小数部分。也就是说，单个整数值没有小数位。
• 浮点数：表示具有整数部分、小数部分或者两者的近似值数字。此数据类型类表示服务器主机 CPU 使用的本机二进制浮点格式 (IEEE 754) 中的值。这是一种非常有效的存储和计算类型，但值会受到舍入误差的影响。
• 定点：具有整数部分、小数部分或者两者
• BIT :BIT 列规范采用表示每个值的位数的宽度，从 1 到 64 位。

精度和小数位数是适用于浮点和定点值的术语，它们可以有整数部分和小数部分。

• 精度：有效位数
• Scale : 小数点右边的位数

时间数据类型

时间数据类型存储日期、时间和年份值（或者这些值的组合）。

YYYY 、 MM 、 DD 、 hh 、 mm 、 ss 和 uuuuuu 分别表示年、月、日、时、分、秒和可选的小数秒的值。
通过为类型提供参数来声明可选的小数秒。
例如， TIME(3) 是一种 TIME 类型，具有最多三位数的小数秒分量。

DATE 值范围从 1000-01-01 到 9999-12-31.

DATETIME 值范围从 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999.

TIMESTAMP 值以 UTC 格式存储（根据需要与本地时间相互转换），范围从 1970-01-01 00:00:00.000000 到 2038-01-19 03:14:07.999999.

可以定义 DATETIME 和 TIMESTAMP 以在行被插入或者更新时自动记录当前日期和时间。

TIMESTAMP 列与 DATETIME 列在许多方面不同：

• TIMESTAMP 列的范围比 DATETIME 小，并且每个值需要更少的存储字节。
• 我们可以通过为当前日期和时间分配一个 NULL 值来设置一个不允许 NULL 的 TIMESTAMP 值。

使用数据类型创建表

当你创建一个表时，你声明了它的每一列，包括列名、指示列可以保存什么类型的值的数据类型，以及一些更具体地定义 MySQL 应该如何处理列的属性（选项）。

示例 1：列声明

CREATE TABLE people ( 
id         INT, 
first_name CHAR(30), 
last_name  CHAR(30) );

上面的第一个示例创建了一个名为 people 的表，其中包含一个名为 id 的整数值数字列和两个名为 first_name 和 last_name 的 30 个字符的字符串列。
CREATE TABLE 语句中的列定义仅包含名称和数据类型。
要更具体地控制 MySQL 处理列的方式，请向列定义添加属性。

示例 2：禁止负值和未知值

CREATE TABLE people ( 
id         INT UNSIGNED NOT NULL, 
first_name CHAR(30), 
last_name  CHAR(30) );

第二个示例展示了如何通过添加 UNSIGNED 属性来禁止 id 列中的负值。

字符集和校对支持

字符集是一组命名的符号和编码。
一个字符串属于一个特定的字符集。
字符串具有以下特点：

• 该序列由属于特定字符集的字符组成。
• 多字节字符集可能需要每个字符固定或者可变数量的字节。
• 比较基于与字符串关联的字符集的排序规则。
• 多字节字符比较以字符为单位，而不是以字节为单位。
• 排序规则验证字符的大写和小写版本是否等效。它还定义了字符排序顺序。
• 排序规则确定一个字符的不同重音符号是否等效。它还控制如何将单个字符和字符串相互比较。
• 二进制字符串是字节序列，没有字符集或者排序规则。比较基于这些字节的数值。

字符串数据类型的列定义可以为每列指定字符集或者排序规则或者两者。
属性适用于 CHAR 、 VARCHAR 、 TEXT 、 ENUM 和 SET 数据类型，如下例所示：

CREATE TABLE t 
( c1 VARCHAR(20) CHARACTER SET utf8, 
c2 TEXT CHARACTER SET latin1 
COLLATE latin1_general_cs );

MySQL 有大量可供选择的字符集和排序规则列表。
正确的选择会对性能产生重大影响。
要查看可用的字符集，请使用以下语句：

SHOW CHARACTER SET;

排序规则选择可以让我们为同一字符集选择不同的排序顺序。
例如，要显示所有 latin1 字符集，请使用以下语句：

SHOW COLLATION LIKE 'latin1%';

将数据类型设置为 NULL

在 SQL 中，表达式可以计算为空值。
空值是一个特殊值，表示值无法计算或者未知的事实。
它可以允许缺失值或者空查询结果。

何时使用 NULL

在数据库设计的开始阶段，当我们列出要包含的数据时，很明显某些数据可能不适用于所有列。
检查这些情况并确定是否应允许空值。
此外，如果由于列中出现空值而检测到问题，我们可以为现有表更改此设置。

何时不使用 NULL

在某些情况下，我们不应在列中允许空值。
最常见的情况是当它是主键时。
另一个示例是必须具有数据库设计有意义的值的任何列。

字符串数据类型

字符串数据类型表示给定字符集的字母数字字符序列。
它存储文本或者二进制数据，并在几乎所有编程语言中实现。
字符串支持字符集和排序规则。
字符串数据类型在几个方面有所不同，包括：

• 数据是以固定长度还是可变长度格式存储
• 可以存储的最大长度是多少
• 类型是否支持非结构化字符串值

字符串数据属于以下存储类别之一：

文本

表示真正的字符串数据类型。
我们可以使用这种类型来存储非结构化、自由格式化的字符串（如果它们的长度适合定义的空间量）。

整数

表示结构化字符串类型。
当要存储在列中的值是从一组固定值中选择时，请使用这些类型。
我们根据字符串值为这两种类型定义列，但 MySQL 在内部将它们表示为整数。

数字数据类型

整数类型按精度升序列出。
也就是说，每个连续的整数数据类型可以包含比列表中前一个更大范围的整数值。

浮点数据类型包括 FLOAT 和 DOUBLE。
这些类型中的每一种都可用于表示具有整数部分、小数部分或者两者的近似值数。
FLOAT 和 DOUBLE 数据类型表示服务器主机 CPU 使用的本机二进制浮点格式 (IEEE 754) 中的值。
这是一种非常有效的存储和计算类型，但值会受到舍入误差的影响。
对于 FLOAT 和 DOUBLE，如果列可以为空，则默认值为 NULL，如果列不可为空，则默认值为 0（数字零）。

DECIMAL 使用固定十进制存储格式：DECIMAL 列中的所有值都具有相同的小数位数，并且完全按照给定的方式存储。
DECIMAL 值的存储效率不如 FLOAT 或者 DOUBLE 值，但 DECIMAL 值不受舍入误差的影响，因此它们更精确。
例如，DECIMAL 数据类型通常用于存储货币值，其中每个值的精度比其存储的大小更重要。
BITcolumn 规范采用表示每个值的位数的宽度，从 1 到 64 位。