MySQL数据库管理与开发实践教程 (清华电脑学堂)
上QQ阅读APP看书，第一时间看更新
上一章目录下一章

2.5 数据类型

数据类型在数据库中扮演着基础但又非常重要的角色，因为对数据类型的选择将影响与数据库交互的应用程序的性能。通常来说，如果在一个页面中可以存放尽可能多的行，那么数据库的性能就越好，因此选择一个正确的数据类型至关重要。

另一方面，如果在数据库中创建表时选择了错误的数据类型，那么后期维护成本可能非常大。用户需要花大量时间来进行ALTER TABLE操作。对于一张大型表，可能需要等待更长的时间。因此，设计表时了解各个数据类型及其适用范围是非常必要的。

2.5.1 整数类型

MySQL中的整数类型主要有5个，分别是TINYINT，SMALLINT，MEDIUMINT，INT和BIGINT。它们在很大程度上是相同的，只有存储值的大小是不相同的，详细说明如表2-1所示。

表2-1 整数类型说明

如果整数类型的列要存储的数据超出范围之外，MySQL会根据允许范围最接近它的一端截短后再进行存储。另外，MySQL会在不合规定的值插入表前，将其自动修改为0。

整数类型的列有如下两种属性。

1．UNSIGNED属性

UNSIGNED属性就是将数字类型无符号化，与C、C++等程序语言中unsigned关键字的作用相同。例如，INT类型有符号的范围是类2 147 483 648～2 147 483 647，INT UNSIGNED（无符号）的范围是0～4 294 967 295。

2．ZEROFILL属性

ZEROFILL属性的作用是如果整数类型列的值小于设置的宽度，则在前面自动填充0。例如，INT(4)类型的列在插入1之后显示为“0001”。

注意

ZEROFILL属性只是一种格式化的输出形式，而不会真正影响数字在内部的存储。

2.5.2 浮点类型

MySQL支持三个浮点类型，分别是：FLOAT、DOUBLE和DECIMAL类型。其数值大小及范围说明如表2-2所示。

表2-2 浮点类型说明

浮点类型通常都有两个参数，表示显示宽度和小数点位数。例如，FLOAT(5,2)规定显示的值不会超过5位数字，小数点后面带有两位数字。对于小数点后面的位数超过允许范围的值，MySQL会自动将它四舍五入为最接近它的值，再插入它。

DECIMAL数据类型用于精度要求非常高的计算中，这种类型允许指定数值的精度和计数方法作为选择参数。精度在这里指为这个值保存的有效数字的总个数，而计数方法表示小数点后数字的位数。例如，语句DECIMAL(5,2)规定了存储的值不会超过5位数字，并且小数点后不超过两位。

忽略DECIMAL数据类型的精度和计数方法修饰符将会使MySQL数据库把所有标识为这个数据类型的字段精度设置为10，计算方法设置为0。

提示

UNSIGNE 和ZEROFILL 属性也可以被FLOAT、DOUBLE 和DECIMAL 数据类型使用，并且效果与INT 数据类型相同。

2.5.3 字符串类型

字符串类型是最常用的数据类型，MySQL提供了10个基本的字符串类型，可以存储的字符串范围从简单的一个字符到巨大的文本块或二进制字符串数据。如表2-3所示列出了这10个字符串类型及其说明。

表2-3 字符串类型说明

表2-3简单列举了字符串类型的用途，其各个类型的使用方法和注意事项如下所示。

1．CHAR和VARCHAR类型

CHAR类型用于固定字符串，并且必须在圆括号内用一个修饰符来定义其大小。这个大小修饰符的范围从0到255。比指定长度大的值将被截断，而比指定长度小的值将会用空格作填补。

CHAR类型可以使用BINARY修饰符。当用于比较运算时，这个修饰符使CHAR以二进制方式参与运算，而不是以传统的区分大小写的方式。

CHAR类型的一个变体是VARCHAR类型。它是一种可变长度的字符串类型，并且也必须带有一个范围在0～255之间的修饰符。

CHAR和VARCHAR的不同之处在于MySQL数据库处理这个修饰符的方式，如下所示。

（1）CHAR把这个大小视为值的大小，长度不足的情况下就用空格补足。

（2）VARCHAR类型把它视为最大值并且只使用存储字符串实际需要的长度（增加一个额外字节来存储字符串本身的长度）来存储值。

所以VARCHAR类型中，短于修饰符长度的VARCHAR类型不会被空格填补，但长于修饰符的值仍然会被截断。

因为VARCHAR类型可以根据实际内容动态改变存储值的长度，所以在不能确定字段需要多少字符时，使用VARCHAR类型可以大大地节约磁盘空间、提高存储效率。

提示

VARCHAR 类型在使用BINARY 修饰符时与CHAR 类型完全相同。

2．TEXT和BLOB类型

对于字段长度要求超过255个的情况下，MySQL提供了TEXT和BLOB两种类型。根据存储数据的大小，它们都有不同的子类型。这些大型的数据用于存储文本块或图像、声音文件等二进制数据类型。

TEXT和BLOB类型在分类和比较上存在区别。BLOB类型区分大小写，而TEXT不区分大小写。大小写修饰符不用于各种BLOB和TEXT子类型。比指定类型支持的最大范围大的值将被自动截断。

2.5.4 时间日期

时间和日期数据被广泛使用，如新闻发布时间、商场活动的持续时间和职员的出生日期等。

在处理日期和时间类型的值时，MySQL带有5个不同的数据类型可供选择。它们可以被分成简单的日期、时间类型和混合日期、时间类型，如表2-4所示。

表2-4 时间日期类型说明

表2-4中的类型有着不同的格式和用途，但MySQL带有内置功能可以把多样化的输入格式变为一个标准格式。其具体的用法如下所示。

1．DATE、TIME和YEAR

MySQL用DATE和YEAR类型存储简单的日期值，使用TIME类型存储时间值。这些类型可以描述为字符串或不带分隔符的整数序列。

如果描述为字符串，DATE类型的值应该使用连字号作为分隔符分开，而TIME类型的值应该使用冒号作为分隔符分开。没有冒号分隔符的TIME类型值，将会被MySQL理解为持续的时间，而不是时间戳。

在MySQL中，YEAR类型的值必须用4个数字存储，但部分用户习惯使用两个数字来表示年份，如使用89来表示1989年，而使用02来表示2002年。

对输入数据中YEAR类型的或SQL语句的YEAR类型，MySQL能够将输入的两个数字进行最大限度的通译。把在00～69范围内的值转换到2000—2069范围内。把70～99范围内的值转换到1970—1979之内。

注意

MySQL 并不能够确保自动转换后的值符合用户的需要，因此最好输入4个数字表示年份。

2．DATETIME和TIMESTAMP类型

除了日期和时间数据类型，MySQL还支持DATETIME和TIMESTAMP这两种混合类型。它们可以把日期和时间作为单个的值进行存储。这两种类型通常用于自动存储包含当前日期和时间的时间戳，并可在需要执行大量数据库事务和需要建立一个调试和审查用途的审计跟踪的应用程序中发挥良好作用。

如果对TIMESTAMP类型的字段没有明确赋值，或是被赋予了null值，MySQL会自动使用系统当前的日期和时间来填充它。

2.5.5 集合类型

MySQL支持两种集合类数据类型ENUM和SET。一个ENUM类型只允许从一个集合中取得一个值；而SET类型允许从一个集合中取得任意多个值。

1．ENUM类型

ENUM类型只允许在集合中取得一个值，其作用类似于单选项，常用于处理相互排斥的数据，例如人的性别。

ENUM类型字段可以从集合中取得一个值或使用NULL值，除此之外的输入将会使MySQL在这个字段中插入一个空字符串。另外，如果插入值的大小写与集合中值的大小写不匹配，MySQL会自动使用插入值的大小写转换成与集合中大小写一致的值。

ENUM类型在系统内部可以存储为数字，并且从1开始用数字作索引。一个ENUM类型最多可以包含65 536个元素，其中一个元素被MySQL保留，用来存储错误信息，这个错误值用索引0或者一个空字符串表示。

提示

通过搜索包含空字符串或对应数字索引为0的行就可以很容易地找到错误记录的位置。

【范例3】

假设，在t表中有一个sex列，该列的值只能是male或者female。下面通过ENUM类型实现CHECK约束，即检查sex列中值的有效性。

首先创建一个包含两列的数据表t，语句如下：

mysql> CREATE TABLE t(
        -> user varchar(20),
        -> sex ENUM('male','female')
        -> );

上述语句使用CREATE TABLE语句来创建表，在第3章中将详细介绍该语句的语法。这里为t表分配了两列，第一列user是varchar类型，最大长度为20；第二列为ENUM类型，其值的范围在括号内指定，多个值之间用逗号分隔，这里指定只能是male或者female。

为了测试ENUM类型是否有效，需要使用SQL_MODE选项设置为STRICT_TRANS_TABLES模式，语句如下：

mysql> SET SQL_MODE='STRICT_TRANS_TABLES';

接下来，向t表中插入两行数据，语句如下：

mysql> INSERT INTO t VALUES('leeon','male');
    Query OK, 1 row affected (0.14 sec)
    mysql> INSERT INTO t VALUES('join','female');
    Query OK, 1 row affected (0.10 sec)

上述两个语句都可以执行成功，因为都是使用ENUM类型允许的值。有关INSERT语句插入数据的更多内容将在第6章中详细介绍。

下面插入一个不在ENUM类型范围内的值，语句如下：

mysql> INSERT INTO t VALUES('mary','girl');
    ERROR 1265 (01000): Data truncated for column 'sex' at row 1

在上述语句中，尝试为t数据表的sex列插入值girl，由于该值没有在ENUM类型中进行定义，所以会报出警告信息。

2．SET类型

SET类型与ENUM类型相似但不相同。SET类型可以从预定义的集合中取得任意数量的值，其作用类似于复选框。

与ENUM类型相同的是，任何试图在SET类型字段中插入非预定义的值都会使MySQL插入一个空字符串。如果插入一个既有合法的元素又有非法的元素的记录，MySQL将会保留合法的元素，忽略非法的元素。

一个SET类型最多可以包含64项元素。在SET元素中值被存储为一个分离的“位”序列，这些“位”表示与它相对应的元素。“位”是创建有序元素集合的一种简单而有效的方式。并且它还去除了重复的元素，所以SET类型中不可能包含两个相同的元素。

提示

查找包含空字符串或二进制值为0的行，可以找出SET 类型字段中的非法记录。

【范例4】

例如，指定为SET('one', 'two')类型的列有4个可选值，分别是''、'one'、'two'和'one,two'。下面通过一个范例介绍SET类型的具体应用。

（1）创建一个仅包含一列的myset数据表，该列指定为SET('a','b','c','d')，语句如下。

mysql> CREATE TABLE myset (
        -> col SET('a', 'b', 'c', 'd')
        -> );

（2）分别使用不同的组合形式向myset表中插入数据，语句如下。

mysql> INSERT INTO myset (col) VALUES
        -> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

（3）上述语句执行之后，myset表中将增加5条数据。查询这些数据，语句如下。

mysql> SELECT col FROM myset;
    +------+
    | col  |
    +------+
    | a,d  |
    | a,d  |
    | a,d  |
    | a,d  |
    | a,d  |
    +------+

（4）再次插入一行数据，这次为SET列设置一个不支持的值，语句如下。

mysql> INSERT INTO myset (col) VALUES ('a,d,d,s');
    ERROR 1265 (01000): Data truncated for column 'col' at row 1

此时执行后将会提示警告信息，说明插入失败。

上一章目录下一章