MySQL数据库应用与实例：数据类型

在创建表的列时，必须为其指定数据类型，列的数据类型决定了数据的取值、范围和存储格式。MySQL提供的数据类型包括数值类型（整数类型和小数类型）、字符串类型、日期类型、复合类型（复合类型包括enum类型和set类型）以及二进制类型 ，将其列于表4.1中。

表4.1 MySQL提供的数据类型

在讨论数据类型时，涉及精度、小数位数和长度三个概念，前两个概念是针对数值型数据的，它们的含义如下：

·精度。指数值数据中所存储的十进制数据的总位数。

·小数位数。指数值数据中小数点右边可以有的数字位数的最大值。例如，数值数据3560.697的精度是7，小数位数是3。

·长度。指存储数据所使用的字节数。

下面分别介绍数据类型。

1.MySQL整数类型

MySQL整数类型分类如图4.2所示。

图4.2 MySQL整数类型

整数型包括BIGINT、INT、MEDIUMINT、SMALLINT和TINYINT，从标志符的含义可以看出，它们表示数的范围在逐渐缩小。

① BIGINT。大整数，数值范围为-263（-9 223 372 036 854 775 808）～263-1（9 223 372 036 854 775 807），其精度为19，小数位数为0，长度为8字节。

② INTEGER（简写为INT）。整数，数值范围为-231（-2 147 483 648）～231-1（2 147 483 647），其精度为10，小数位数为0，长度为4字节。

③ MEDIUMINT。中等长度整数，数值范围为-223（-8 388 608）～223-1（8 388 607），其精度为7，小数位数为0，长度为3字节。

④ SMALLINT。短整数，数值范围为-215（-32 768）～215-1（32 767），其精度为5，小数位数为0，长度为2字节。

⑤ TINYINT。微短整数，数值范围为-27（-128）～27-1（127），其精度为3，小数位数为0，长度为1字节。

整数类型的数，默认情况下既可以表示正整数又可以表示负整数（此时称为有符号数）。如果只希望表示零和正整数，可以使用无符号关键字“unsigned”对整数类型进行修饰（此时称为无符号整数）。例如：score tinyint unsigned

不同整型数据的区别如表4.2所示。

表4.2 不同整型数据的区别

2.MySQL小数类型

MySQL小数类型如图4.3所示。

图4.3 MySQL小数类型

（1）精确数值型。

精确数值型由整数部分和小数部分构成，其所有的数字都是有效位，能够以完整的精确表示数据的精度存储十进制数。精确数值型包括DECIMAL、NUMERIC两类。从功能上说两者完全等价，两者的唯一区别在于DECIMAL不能用于带有IDENTITY关键字的列。

声明精确数值型数据的格式是NUMERIC | DECIMAL（P[，S]），其中P为精度，S为小数位数，S的默认值为0。例如，指定某列为精确数值型，精度为6，小数位数为3，即DECIMAL（6，3），那么若向某记录的该列赋值56.342689时，该列实际存储的是56.3427。decimal（length，precision）用于表示精度确定（小数点后数字的位数确定）的小数类型，其中length决定了该小数的最大位数，precision用于设置精度（小数点后数字的位数）。

例如：

decimal（5，2）表示小数取值范围：999.99～999.99

decimal（5，0）表示：-99999～99999的整数。

（2）浮点型。

浮点型也称近似数值型。这种类型不能提供精确表示数据的精度。使用这种类型来存储某些数值时，有可能会损失一些精度，所以它可用于处理取值范围非常大且对精确度要求不是十分高的数值量，如一些统计量。

有两种浮点数据类型：单精度（FLOAT）和双精度（DOUBLE）。两者通常都使用科学计数法表示数据，即形为：尾数E阶数，如5.6432E20，-2.98E10，1.287 659E-9等。

① FLOAT[（M，D）] [ZEROFILL]。

取值范围：-3.402 823 466E+38到-1.175 494 351E-38之间、0、1.175 494 351E-38到3.402 823 466E+38之间。M是小数总位数，D是小数点后面的位数。如果省略M和D，根据硬件允许的限制来保存值。单精度浮点数精确到大约7位小数位。

存储要求：4个字节，数据精确为7位小数位。

② DOUBLE[（M，D）] [ZEROFILL]。

取值范围：-1.797 693 134 862 315 7E+308到-2.225 073 858 507 201 4E-308之间、0、2.225 073 858 507 201 4E-308到1.797 693 134 862 315 7E+308之间。DOUBLE PRECISION和REAL是DOUBLE的同义词。

存储要求：8个字节，数据精度为15位小数位。

③ FLOAT（P）[UNSIGNED] [ZEROFILL]。

P表示精度（以位数表示），MYSQL只使用该值来确定是否结果列的数据类型为FLOAT或DOUBLE。如果P为0～24，数据类型为没有M或D值的FLOAT。如果P为25～53，数据类型为没有M或D值的DOUBLE。

单精度（FLOAT）和双精度（DOUBLE）的取值范围如表4.3所示。

表4.3 单精度（FLOAT）和双精度（DOUBLE）的取值范围

3.MySQL二进制类型

二进制类型的字段主要用于存储由‘0’和‘1’组成的字符串，因此从某种意义上讲，二进制类型的数据是一种特殊格式的字符串。

二进制类型与字符串类型的区别在于：字符串类型的数据按字符为单位进行存储，因此存在多种字符集、多种字符序；而二进制类型的数据按字节为单位进行存储，仅存在二进制字符集binary，如图4.4所示。

图4.4 二进制类型

（1）位字段类型，表示如下：(www.xing528.com)

BIT[（M）]

其中，M表示位值的位数，范围为1～64。如果省略M，默认为1。

（2）BINARY和VARBINARY型。

BINARY和VARBINARY类型数据包含的是二进制字符串，它们没有字符集，并且排序和比较基于列值字节的数值。

·BINARY [（N）]。固定长度的N字节二进制数据。N取值范围为1～255，默认为1。BINARY（N）数据的存储长度为N+4字节。若输入的数据长度小于N，则不足部分用0填充；若输入的数据长度大于N，则多余部分被截断。

输入二进制值时，在数据前面要加上0X，可以用的数字符号为0～9、A～F（字母大小写均可）。例如，0XFF、0X12A0分别表示十六进制的FF和12A0。因为每字节的数最大为FF，故在“0X”格式的数据每两位占1字节。

·VARBINARY[（N）]。N字节变长二进制数据。N取值范围为1～65535，默认为1。VARBINARY（N）数据的存储长度为实际输入数据长度+4字节。

（3）BLOB类型。

在数据库中，对于数码照片、视频和扫描的文档等的存储是必需的，MySQL可以通过BLOB数据类型来存储这些数据。BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。

4.MySQL字符串

字符串类型的数据外观上使用单引号括起来，例如学生姓名'张三'，课程名'java程序设计 '等。字符串类型如图4.5所示。

图4.5 MySQL字符串数据

字符型数据用于存储字符串，字符串中可包括字母、数字和其他特殊符号（如#、@、&等）。在输入字符串时，需将串中的符号用单引号或双引号括起来，如'ABC'、"ABC<CDE"。

MySQL字符型包括固定长度（CHAR）和可变长度（VARCHAR）字符数据类型。CHAR和VARCHAR数据类型类似于BINARY和VARBINARY，不同的是它们包含的是字符字符串，而非字节字符串。

·CHAR[（N）]。定长字符数据类型，其中N定义字符型数据的长度，N为1～255，默认为1。当表中的列定义为CHAR（N）类型时，若实际要存储的字符串长度不足N时，则在串的尾部添加空格以达到长度N，所以CHAR（N）的长度为N。例如，某列的数据类型为CHAR（20），而输入的字符串为“AHJM1922”，则存储的是字符AHJM1922和12个空格。若输入的字符个数超出了N，则超出的部分被截断。

·VARCHAR[（N）]。变长字符数据类型，其中N可以指定为0～65 535的值，但这里N表示的是字符串可达到的最大长度。VARCHAR（N）的长度为输入的字符串的实际字符个数，而不一定是N。例如，表中某列的数据类型为VARCHAR（100），而输入的字符串为“AHJM1922”，则存储的就是字符AHJM1922，其长度为8字节。

对于简体中文字符集gbk的字符串而言，varchar（255）表示可以存储255个汉字，而每个汉字占用两个字节的存储空间。假如这个字符串没有那么多汉字，例如仅仅包含一个‘中’字，那么varchar（255）仅仅占用1个字符（两个字节）的储存空间；而char（255）则必须占用255个字符长度的存储空间，哪怕里面只存储一个汉字。

5.文本型

当需要存储大量的字符数据，如较长的备注、日志信息等，字符型数据的最长65 535个字符的限制可能使它们不能满足应用需求，此时可使用文本型数据。文本型数据对应ASCII字符，其数据的存储长度为实际字符数个字节。

文本型数据可分为4种：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT，分别对应4种BLOB类型。不同的是TEX表示的是最大字符长度，BLOB表示的是最大字节长度。各种文本数据类型的最大字符数如表4.4所示。

表4.4 各种文本数据类型的最大字符数

6.MySQL日期类型

MySQL支持5种时间日期类型：DATE、TIME、DATETIME、TIMESTAMP、YEAR。

DATE表示日期，默认格式为‘YYYY-MM-DD’；

TIME表示时间，格式为‘HH：ii：ss’；

YEAR表示年份；

DATETIME与TIMESTAMP是日期和时间的混合类型，格式为'YYYY-MM-DD HH：ii：ss' ，如图4.6所示。

图4.6 MySQL日期类型数据

（1）DATE。DATE数据类型由年份、月份和日期组成，代表一个实际存在的日期。DATE的使用格式为字符形式'YYYY-MM-DD'，年份、月份和日期之间使用连字符“-”隔开，除了“-”，还可以使用其他字符如“/”、“@”等，也可以不使用任何连接符，如'19970806'表示1997年8月6日。DATE数据支持的范围是'1000-01-01'～'9999-12-31'。虽然不在此范围的日期数据也允许，但是不能保证能正确进行计算。

（2）TIME。TIME数据类型代表一天中的一个时间，由小时数、分钟数、秒数和微秒数组成。格式为'HH：MM：SS.fraction'，其中fraction为微秒部分，是一个6位的数字，可以省略。TIME值必须是一个有意义的时间，例如'10:08:34'表示10点08分34秒，而'10:98:10'是不合法的，它将变成'00:00:00'。

（3）DATETIME，TIMESTAMP。DATETIME和TIMESTAMP数据类型是日期和时间的组合，日期和时间之间用空格隔开，如'2008-10-20 10:53:20'。大多数适用于日期和时间的规则在此也适用。DATETIME和TIMESTAMP有很多共同点，但也有区别。对于DATETIME，年份在1000～9999之间，而TIMESTAMP的年份在1970～2037之间。另一个重要的区别是：TIMESTAMP支持时区，即在操作系统时区发生改变时，TIMESTAMP类型的时间值也相应改变，而DATETIME则不支持时区。

也就是说，虽然datetime与timestamp都是日期和时间的混合类型，区别在于：

·表示的取值范围不同，datetime的取值范围远远大于timestamp的取值范围。

·将NULL插入timestamp字段后，该字段的值实际上是MySQL服务器当前的日期和时间。

·同一个timestamp类型的日期或时间，不同的时区，显示结果不同。

学会使用now（）函数。

注意：now（）函数用于获得MySQL服务器的当前时间，该时间与时区的设置密切相关。

（4）YEAR。YEAR用来记录年份值。MySQL以YYYY格式检索和显示YEAR值，范围是1901～2155。

7.MySQL复合类型

MySQL支持两种复合数据类型：enum枚举类型和set集合类型。

ENUM和SET是比较特殊的字符串数据列类型，它们的取值范围是一个预先定义好的列表。ENUM或SET数据列的取值只能从这个列表中进行选择。ENUM和SET的主要区别是：ENUM只能取单值，它的数据列表是一个枚举集合。ENUM的合法取值列表最多允许有65535个成员。例如，ENUM（"N"，"Y"）表示该数据列的取值要么是“Y”，要么是“N”。SET可取多值。它的合法取值列表最多允许有64个成员。空字符串也是一个合法的SET值。

8.选择合适的数据类型

选择合适的数据类型，不仅可以节省储存空间，还可以有效地提升数据的计算性能。

（1）在符合应用要求（取值范围、精度）的前提下，尽量使用“短”数据类型。

（2）数据类型越简单越好。

（3）在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储日期和时间。

（4）尽量采用精确小数类型（例如decimal），而不采用浮点数类型。使用精确小数类型不仅能够保证数据计算更为精确，还可以节省储存空间，例如百分比使用decimal（4，2）即可。

（5）尽量避免NULL字段，建议将字段指定为NOT NULL约束。