MySQL数据类型

1.        数据类型

1.1.        数据类型：概览

在 MySQL 中，可用的数据类型分为四个主要类别： 数值、字符、二进制、时间

在每个类别中，存在多种特定的数据类型，这些数据类型使用的内存大小和磁盘空间各不相同，因此会对性能产生不同的影响。对于单个记录，为列选择最佳数据类型所产生的性能影响相对较小，但随着数据库的增大，这些较小的影响可能会汇聚成大的影响。应在设计过程中事先考虑这些影响，以免造成性能问题。

MySQL 还支持“空间扩展” 存储引擎功能。通过空间扩展，能够以字符和二进制格式生成、存储和分析地理特征。 数据类型的 ABC 要素：

Appropriate （适当）： 需要以最适合数据所代表的项的类型来表示数据。

Brief （简洁）： 选择所用存储空间最少的数据类型。这可节省资源并提高性能。

Complete （完整）： 选择的数据类型应分配有可存储特定项的最大可能值的充足空间。

  示例 1 ：列声明

CREATE TABLE people (

id INT,

first_name CHAR(30),

last_name CHAR(30) ); 示例 2 ：不允许负值和未知值

CREATE TABLE people (

id INT UNSIGNED NOT NULL,

first_name CHAR(30),

last_name CHAR(30) );

创建表时，声明其中每个列，包括列名、数据类型（指示列中值的种类）以及某些属性（选项，用于更加具体地定义 MySQL 应如何处理列）。

1.2.        数值数据类型

为存储数值数据， MySQL 提供了以下数值数据类型类：

整数： 整数没有小数部分。即，没有小数位的单个整数值。

浮点数： 表示包含整数部分、小数部分或同时包括二者的近似值数值。此类数据类型使用服务器主机的 CPU 所用的本机二进制浮点格式 (IEEE 754) 来表示值。该数据类型用于存储和计算会很高效，但值会存在舍入误差。

定点数： 包含整数部分、小数部分或同时包括二者。

BIT ： BIT 列规范规定了一个宽度，指明每个值的位数（ 1 至 64 位）。

“精度” 和“范围” 是适用于浮点值和定点值（这两种类型可以同时包含整数部分和小数部分）的术语。

精度 ： 有效位数。

范围 ： 小数点右侧的位数。

数值数据类型表：

整数类型按精度升序列出。即，后面的每个整数数据类型可以容纳的整数值范围要大于列表中前一个数据类型的整数值范围。

浮点数据类型包括 FLOAT 和 DOUBLE 。这两种类型均可用于表示包含整数部分、小数部分或同时包括二者的近似值数值。 FLOAT 和 DOUBLE 数据类型使用服务器主机的 CPU 所用的本机二进制浮点格式 (IEEE 754) 来表示值。该数据类型用于存储和计算会很高效，但值会存在舍入误差。对于 FLOAT 和 DOUBLE ：如果列可为空，则默认值为 NULL ；如果列不可为空，则默认值为 0 （数值零）。注： 您可以使用 = 后跟浮点值进行比较；但由于可能会出现舍入误差，因此结果可能并不总是与预期一样。

DECIMAL 使用小数点固定的存储格式： DECIMAL 列中的所有值均包含相同的小数位数，并且完全按给定方式进行存储。 DECIMAL 值存储起来不如 FLOAT 或 DOUBLE 值高效，但 DECIMAL 值没有舍入误差，因此更加精确。例如， DECIMAL 数据类型通常用于存储货币值（其中每个值的精度比其存储大小更加重要）。

BIT 列规范规定了一个宽度，指明每个值的位数（ 1 至 64 位）。

1.3.        字符串数据类型

字符串数据类型表示给定字符集中的一个字母数字字符序列，用于存储文本或二进制数据，几乎在每种编程语言中都有实现，支持字符集和 COLLATION 。

这些类型在以下几个方面有所不同：

²   数据是以固定长度格式还是可变长度格式存储

²   可存储的最大长度

²   该类型是否支持非结构化字符串值

字符串数据类型

l   字符集和 COLLATION 支持

字符集：一组指定的符号和编码

COLLATION ：指定的整理序列，定义字符排序顺序，控制各个字符和字符串如何进行相互比较；

字符串数据类型的列定义可以指定列的字符集或 COLLATION ，属性适用于 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;

使用 COLLATION 可以为相同的字符集选择不同的排序顺序。例如，要显示所有 latin1 字符集，请使用以下语句：

SHOW COLLATION LIKE 'latin1%';

1.4.        二进制字符串数据类型          二进制： 用于存储固定长度和可变长度的二进制字节字符串。          BLOB ： 在数据库管理系统中存储为单个值。 BLOB 通常为图像、音频或其他多媒体对象，尽管有时二进制代码也存储为 BLOB 。

这些二进制类型也是较小单元的序列，从这个意义上看，它们是字符串类型。与字符串不同，组成此类二进制字符串值的字节不代表字符。因此，二进制字符串没有附加的字符语义，而且缺少字符串类所表示的字符集和整理信息。

在 MySQL 中， BLOB 与 TEXT 类型非常相似，没有附加的字符集和 COLLATION 。

1.5.        时间数据类型

存储日期、时间和年份值（或这些值的组合）

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 以自动记录当前日期和时间，而不管对行执行 INSERT 或 UPDATE 操作的时间为何时。

TIMESTAMP 列在很多方面都与 DATETIME 列不同。 TIMESTAMP 列值的范围比 DATETIME 列值的范围小，因此存储每个值所需的字节数更少。通过为不允许出现 NULL 的 TIMESTAMP 指定 NULL 值，可以将其设置为当前日期和时间。  

1.6.        空间数据类型

MySQL 支持空间数据类型扩展，以便允许生成、存储和分析地理特征。地理特征涉及世界上具有位置的任何事物：实体（山脉、池塘或城市）、空间（城镇或热带地区）、可定义的位置（十字路口）。可以对 InnoDB 、 MyISAM 、 NDB 和 ARCHIVE 存储引擎表使用空间扩展。

MySQL 实现了由开放地理空间协会 (Open Geospatial Consortium, OGC) 提出的“包含几何类型的 SQL ” 环境的子集。

l   用于单个几何体值：

- GEOMETRY ：层次结构的根类，任何类型的值

- POINT ：坐标空间中的单个位置

- CURVE ：一维几何体，由点组成的序列

- LINESTRING ：点之间具有线性内插的曲线

- SURFACE ：二维几何体

- POLYGON ：表示多边几何体的平面

l   用于保存几何体值的集合：

- MULTIPOINT ： Point 元素

- MULTICURVE ： Curve 元素

- MULTILINESTRING ： LineString 元素

- MULTISURFACE ： Surface 元素

- MULTIPOLYGON ： Polygon 元素

- GEOMETRYCOLLECTION ：任意类型的几何体

有关空间扩展的更多信息，请参阅《 MySQL 参考手册》：

http://dev.mysql.com/doc/refman/5.6/en/spatial-extensions.html

1.7.        列属性 列属性的类别：

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

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

常规： 适用于所有数据类型

* 除 TIMESTAMP 之外列属性

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

NULL 或 NOT NULL ：如果未指定这两个属性中的任何一个，则默认情况下允许列中出现 NULL 值。

DEFAULT ：例如，如果执行 INSERT 语句时没有为表中的所有列提供值，将使用默认值。 示例：

CREATE TABLE t(

i INT UNSIGNED NOT NULL ,

c CHAR(10) CHARACTER SET utf8 ,

d DATE DEFAULT '2013-01-01');

示例中的表包含一个不能包含 NULL 值的 UNSIGNED 整数列、一个使用 utf8 字符集的字符串列，以及一个默认值为“ 2013-01-01 ” 的日期列。

l   将数据类型设置为 NULL

在 SQL 中，对表达式求值的结果可以为 null 。 null 值是一个特殊的值，表示值无法进行计算或未知。

何时使用 NULL ？

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

何时不应使用 NULL ？

在某些情况下，不应允许列中使用 null 值。最常见的情况即该列为主键时。另一个例子是：当列必须包含值时，数据库设计才有意义。

1.8.        选择数据类型

考虑哪些数据类型和字符集可以最大限度地减少存储和磁盘 I/O ；如果存储的所有字符串值的长度相同，则考虑使用固定长度数据类型；如果存储的字符串值不同或者是多字节字符集则使用可变长度数据类型：对于频繁使用的字符，使用占用空间较少的多字节字符集，使用 BMP (Basic Multilingual Plane) 之外的其他 Unicode 字符集。          为了获得最佳的存储效果，您应该尝试在所有情况下使用最精确的类型。例如，如果整型列用于 1 到 99999 之间的值，则无符号的 MEDIUMINT 是最佳类型。在表示所有所需值的类型中，此类型使用的存储空间最少。          DECIMAL 列的所有基本计算 (+ 、 - 、 * 和 /) 的精度都是 65 位十进制 ( 以 10 为基数 ) 。          如果精度不是太重要，或者速度是最高的优先级， DOUBLE   可能足够好。为了获得更高的精度，可以将其转换定点类型 BIGINT 。这使您能够使用 64 位整数进行所有计算，然后根据需要将结果转换回浮点值。     PROCEDURE ANALYSE 可用于获得最优列数据类型的建议。