MySQL基础篇（三）-- 数据类型_配置表 数据类型说明

        数据表由多个字段组成，每个字段在进行数据定义的时候都要确定不同的数据类型。向每个字段插入的数据内容决定了该字段的数据类型。数据类型用于以下目的：

• 数据类型允许限制可存储在列中的数据。例如，数值数据类型列只能接受数值。
• 数据类型允许在内部更有效地存储数据。可以用一种比文本串更简洁的格式存储数值和日期时间值。
• 数据类型允许变换排序顺序。如果所有数据都作为串处理，则1位于10之前，而10又位于2之前（串以字典顺序排序，从左边开始比较，一次一个字符）。作为数值数据类型，数值才能正确排序。

        在设计表时，应该特别重视所用的数据类型。使用错误的数据类型可能会严重地影响应用程序的功能和性能。更改包含数据的列不是一件小事（而且这样做可能会导致数据丢失）。

1 串数据类型

        最常用的数据类型是串数据类型。它们存储串，如名字、地址、电话号码、邮政编码等。有两种基本的串类型，分别为定长串和变长串(如下表所示)

串数据类型

        定长串接受长度固定的字符串，其长度是在创建表时指定的。例如，名字列可允许30个字符，而社会安全号列允许11个字符（允许的字符数目中包括两个破折号）。定长列不允许多于指定的字符数目。它们分配的存储空间与指定的一样多。

        变长串存储可变长度的文本。有些变长数据类型具有最大的定长，而有些则是完全变长的。不管是哪种，只有指定的数据得到保存（额外的数据不保存）TEXT属于变长串类型。

        既然变长数据类型这样灵活，为什么还要使用定长数据类型？因为性能。MySQL处理定长列远比处理变长列快得多。此外，MySQL不允许对变长列（或一个列的可变部分）进行索引。这也会极大地影响性能。

        温馨提示： 不管使用何种形式的串数据类型，串值都必须括在引号内（通常单引号更好）。

        注：表格中的L表示存储的数据本身占用的字节，L 以外所需的额外字节为存放该值的长度所需的字节数

1.1 char和varchar类型

        CHAR(M) 为固定长度字符串，在定义时指定字符串列长。当保存时，在右侧填充空格以达到指定的长度。M 表示列的长度，范围是 0～255 个字符。

        VARCHAR(M) 是长度可变的字符串，M 表示最大列的长度，M 的范围是 0～65535。VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加 1。

1.2 text类型

        TEXT 列保存非二进制字符串，如文章内容、评论等。当保存或查询 TEXT 列的值时，不删除尾部空格。

1.3 enum类型

        ENUM 是一个字符串对象，值为表创建时列规定中枚举的一列值。其语法格式如下：

<字段名> ENUM( '值1', '值1', …, '值n' )
1

        字段名指将要定义的字段，值 n 指枚举列表中第 n 个值。

        ENUM 类型的字段在取值时，能在指定的枚举列表中获取，而且一次只能取一个。如果创建的成员中有空格，尾部的空格将自动被删除。

        ENUM 值在内部用整数表示，每个枚举值均有一个索引值；列表值所允许的成员值从 1 开始编号，MySQL 存储的就是这个索引编号，枚举最多可以有 65535 个元素。

        温馨提示： ENUM 列总有一个默认值。如果将 ENUM 列声明为 NULL，NULL 值则为该列的一个有效值，并且默认值为 NULL。如果 ENUM 列被声明为 NOT NULL，其默认值为允许的值列表的第 1 个元素。

1.4 set类型

        SET 是一个字符串的对象，可以有零或多个值，SET 列最多可以有 64 个成员，值为表创建时规定的一列值。指定包括多个 SET 成员的 SET 列值时，各成员之间用逗号,隔开，语法格式如下：

SET( '值1', '值2', …, '值n' )
1

        与 ENUM 类型相同，SET 值在内部用整数表示，列表中每个值都有一个索引编号。当创建表时，SET 成员值的尾部空格将自动删除。

        但与 ENUM 类型不同的是，ENUM 类型的字段只能从定义的列值中选择一个值插入，而 SET 类型的列可从定义的列值中选择多个字符的联合。

        温馨提示： 如果插入 SET 字段中的列值有重复，则 MySQL 自动删除重复的值；插入 SET 字段的值的顺序并不重要，MySQL 会在存入数据库时，按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL 将忽视这些值，给出警告。

---

2 数值数据类型

        数值数据类型存储数值。MySQL支持多种数值数据类型，每种存储的数值具有不同的取值范围。显然，支持的取值范围越大，所需存储空间越多。下表中列出了 MySQL 中的数值类型。

数值数据类型

2.1 整数类型

        MySQL 主要提供的整数类型有 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，其属性字段可以添加 AUTO_INCREMENT 自增约束条件。下表中列出了 MySQL 中的整数类型。

        注：上面[]包含的内容是可选的，默认是有符号类型的，无符号的需要在类型后面跟上unsigned。

        在开发中，会碰到有些定义整型的写法是int(11)，这种写法个人感觉在开发过程中没有什么用途，不过还是来说一下， int(N)我们只需要记住两点：

• 无论N等于多少，int永远占4个字节
• N表示的是显示宽度，不足的用0补足，超过的无视长度而直接显示整个数字，但这要整型设置了unsigned zerofill才有效

        下面用一个例子演示：

        注：show create table tb_test01; 输出了表tb_test01的创建语句，和原始的创建语句不一致了，原始的d字段用的是有符号的，可以看出当使用了zerofill自动会将有符号提升为无符号。

        温馨提示： 显示宽度和数据类型的取值范围是无关的。显示宽度只是指明 MySQL 最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充。如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。例如，year 字段插入 19999，当使用 SELECT 查询该列值的时候，MySQL 显示的将是完整的带有 5 位数字的 19999，而不是 4 位数字的值。

2.2 小数类型

        浮点类型有两种，分别是单精度浮点数（FLOAT）和双精度浮点数（DOUBLE）；定点类型只有一种，就是 DECIMAL。

        浮点类型和定点类型都可以用(M, D)来表示，其中M称为精度，表示总共的位数；D称为标度，表示小数的位数。

        float和double在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0。

        下面用实例演示：

说明：

• c是decimal类型，看一下输入和输出，发现decimal采用的是四舍五入
• 看一下a 和b 的输入和输出，尽然不是四舍五入，这里float和double采用的是四舍六入五成双。decimal插入的数据超过精度之后会触发警告。

        什么是四舍六入五成双？
        就是5以下舍弃5以上进位，如果需要处理数字为5的时候，需要看5后面是否还有不为0的任何数字，如果有，则直接进位，如果没有，需要看5前面的数字，若是奇数则进位，若是偶数则将5舍掉。

        将浮点类型的（M,D）精度和标度都去掉，看看效果：

        说明：a和b的数据正确插入，而c被截断了，浮点数float、double如果不写精度和标度，则会按照实际显示。decimal不写精度和标度，小数点后面的会进行四舍五入，并且插入时会有警告!

        从上面sum的结果可以看出float 、double 会存在精度问题，decimal 精度正常的，比如银行对统计

        结果要求比较精准的建议使用decimal 。有符号或无符号 所有数值数据类型（除BIT和BOOLEAN外）都可以有符号或无符号。有符号数值列可以存储正或负的数值，无符号数值列只能存储正数。默认情况为有符号，但如果你知道自己不需要存储负值，可以使用UNSIGNED关键字，这样做将允许你存储两倍大小的值。

         温馨提示： 在 MySQL 中，定点数以字符串形式存储，在对精度要求比较高的时候（如货币、科学数据），使用 DECIMAL 的类型比较好，另外两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点数时需要注意，并尽量避免做浮点数比较。存储货币数据类型 MySQL中没有专门存储货币的数据类型，一般情况下使用DECIMAL(8, 2)。

---

3 日期和时间类型

        MySQL使用专门的数据类型来存储日期和时间值。下表中列出了 MySQL 中的日期与时间类型。

日期与时间数据类型

        每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。下表列出YEAR、TIME、DATE、DTAETIME、TIMESTAMP各自的字节数、取值范围和用途。

        对于日期和时间类型的详细介绍，可以阅读：MySQL DTAETIME、TIMESTAMP、DATE、TIME、YEAR（日期和时间类型）

---

4 二进制类型

        二进制数据类型可存储任何数据（甚至包括二进制信息），如图像、
多媒体、字处理文档等。

二进制数据类型

        BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。如下表所示。

---

5 数据类型选择

5.1 字符串类型

        字符串类型没有像数字类型列那样的“取值范围"，但它们都有长度的概念。如果需要存储的字符串短于 256 个字符，那么可以使用 CHAR、VARCHAR 或 TINYTEXT。如果需要存储更长一点的字符串，则可以选用 VARCHAR 或某种更长的 TEXT 类型。

5.2 数值类型

        如果要存储的数字是整数（没有小数部分），则使用整数类型，根据取值范围进行具体的选择；如果要存储的数字是小数（带有小数部分），则可以选用 DECIMAL 或浮点类型，但是一般选择 FLOAT 类型（浮点类型的一种）。

        当数值不是数值时，你可能会认为电话号码和邮政编码应该存储在数值字段中（数值字段只存储数值数据），但是，这样做却是不可取的。如果在数值字段中存储邮政编码01234，则保存的将是数值1234，实际上丢失了一位数字。

        需要遵守的基本规则是：如果数值是计算（求和、平均等）中使用的数值，则应该存储在数值数据类型列中。如果作为字符串（可能只包含数字）使用，则应该保存在串数据类型列中。

5.3 日期和时间类型

        如果只需要记录年份，则使用 YEAR 类型即可；如果只记录时间，可以使用 TIME 类型。

        如果同时需要记录日期和时间，则可以使用 TIMESTAMP 或者 DATETIME 类型。由于TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，因此存储较大的日期最好使用 DATETIME。

        TIMESTAMP 也有一个 DATETIME 不具备的属性。默认情况下，当插入一条记录但并没有指定 TIMESTAMP 这个列值时，MySQL 会把 TIMESTAMP 列设为当前的时间。因此当需要插入记录和当前时间时，使用 TIMESTAMP 是方便的，另外 TIMESTAMP 在空间上比 DATETIME 更有效。

5.4 二进制

        BLOB 是二进制字符串，TEXT 是非二进制字符串，两者均可存放大容量的信息。BLOB 主要存储图片、音频信息等，而 TEXT 只能存储纯文本文件。

        数据类型选择的一些建议：

• 选小不选大：一般情况下选择可以正确存储数据的最小数据类型，越小的数据类型通常更快，占用磁盘，内存和CPU缓存更小；
• 简单就好：简单的数据类型的操作通常需要更少的CPU周期，例如：整型比字符操作代价要小得多，因为字符集和校对规则(排序规则)使字符比整型比较更加复杂；
• 尽量避免NULL：尽量制定列为NOT NULL，除非真的需要NULL类型的值，有NULL的列值会使得索引、索引统计和值比较更加复杂；
• 浮点类型的建议统一选择decimal；
• 记录时间的建议使用int或者bigint类型，将时间转换为时间戳格式，如将时间转换为秒、毫秒，进行存储，方便走索引。