第3章MySQL存储引擎和数据类型(2)MySQL的数据类型

1、第3章 MySQL存储引擎和数据类型（2）MySQL的数据类型MySQL数据类型简介 数字类型字符串类型日期时间类型选择正确的数据类型1、MySQL的数据类型简介MySQL 的数据类型是一种手段，通过这种手段可以描述一个表列包含什么类型的值，这又决定了 MySQL 怎样处理这些值。例如，数值既可用数字也可用字符串的数据类型来存放，但是根据存放这些值的类型， MySQL 对它们的处理将会有些不同。 1字符串“12345”保存方法2数字12345保存方法不同数据类型的保存方法不同数据类型的保存方法共计占用5个字节存储空间字符“1”编码00110001字符“2”编码00110010字符“3”编码00

2、110011字符“4”编码00110100字符“5”编码00110101共计占用2个字节存储空间数字12345的二进制编码00110000001110011、MySQL的数据类型简介每种列类型都有几个特性如下：其中可以存放什么类型的值。值要占据多少空间，以及该值是否是定长的（所有值占相同数量的空间）或可变长的（所占空间量依赖于所存储的值）。该类型的值怎样比较和存储。此类型是否允许 NULL 值。此类型是否可以索引。1、MySQL的数据类型简介p数据保存的格式不同，读取这些数据时进行的操作当然也就不同，因此数据库在存储数据之前都必须要明确数据的格式，也就是数据的数据类型。p如果不知道某一个数据存

3、储时所使用的数据类型，就无法正确的读出该数据来，因为使用不同的数据格式去读去同一个数据时，得到的结果也是截然不同的。1、MySQL的数据类型简介2.数字类型 数字类型用于存储数字，可分为整数类型和浮点数（小数）类型。如果仅从数学的角度上分析，整数和小数都可以统一为小数，整数只是小数位为零的小数，但在MySQL中这两数据类型的保存方式却有很大的不同。一般而言，能使用整数保存的数据，就尽量选择使用整数类型。比如，要保存商品的价格时，可能会因为一个笔记本的价格是3.20元而为这个数据选择浮点数类型，但更好的选择却是将3.20元变换成320分后以整数方式保存。 MySQL 支持科学表示法，由整数或浮点

4、数后跟“ e”或“E”、一个符号（“+”或“-”）和一个整数指数来表示。1.34E+12 和 43.27e-1 都是合法的科学表示法表示的数。而1.34E12 不是合法的，因为指数前的符号未给出。指数前的“ e”也是一个合法的十六进制数字，因此有可能会弄错。数值前可放一个负号“-”以表示负值。2.数字类型 数字类型可以结合UNSIGNED及ZEROFILL符号使用，UNSIGNED表示无符号数字，ZEROFILL表示使用“0”作为填充符进行填充。2.数字类型 2.1 整数整数在MySQL中保存时，MySQL会将其转换成二进制编码的形式直接保存。整数可以直接以十六进制表示，方法为在整数对应的十六

5、进制编码（“0”到“9”及“a”到“f”）前加上“0 x”。例如，0 x0a为十进制的10，而0 xffff为十进制的65535。十六进制数字不区分大小写，但其前缀“0 x”不能为“0X”。即0 x0a和0 x0A都是合法的，但0X0a和0X0A不是合法的。2.1 整数类型名类型名字节数字节数取值范围取值范围无符号无符号(unsigned)(unsigned)有符号有符号(signed)(signed)TINYINT1 0 28-1-27 27-1SMALLINT20 216-1-215 215-1MEDIUMINT30 224-1-223 223-1INT40 232-1-231 231-1

6、BIGINT80 264-1-263 263-12.1 整数在指定整数类型时，可以采用“类型名显示长度”的写法，如int3，其中“显示长度”用于指定数字在显示时的长度，“显示长度”对数据的大小范围没有影响。当数字的实际长度比显示长度要小时，MySQL显示该数字时会自动使用填充符补足指定的“显示长度”；当数字的实际长度比显示长度大时，MySQL会突破显示长度的限制而显示数字的实际值。2.1 整数默认情况下MySQL会用空格作为填充符进行填充，但“显示长度”通常会与ZEROFILL属性连用，表示MySQL使用“0”作为填充符。我们应该尽量避免数字的实际长度超过其显示长度，出现这种情况时不仅仅是使表

7、中的记录看起来不协调，还会在进行某些复杂的表联结操作时出错。 2.2 浮点数 浮点数可以理解为我们通常所说的小数，MySQL提供三种浮点类型：FLOAT、DOUBLE和DECIMAL。浮点数类型的取值范围与整型不同，除了有最大值和最小值外，浮点数类型还有最小正数和最大负数，这两个值的绝对值相等，用于衡量浮点数的精度，这对于记录科学数据来说是非常重要的。2.2 浮点数 FLOAT型FLOAT类型有两种使用方法：FLOAT(有效位数，小数位数)FLOAT(二进制位数)。2.2 浮点数 FLOAT(有效位数，小数位数)此类型又称为单精度浮点数类型，占用4字节长度，取值范围是：-3.402823466

8、E+38到3.402823466E+38，最小正数是1.175494351E-38，最大负数是-1.175494351E-38。以上列出的只是理论值，不同计算机的硬件可能会使这一范围有所不同。2.2 浮点数 其中，“有效位数”表示有效数字的最大位数；“小数位数”表示小数点后的最大位数。如果“有效位数”和“小数位数”都省略，其默认值会因硬件不同而有所不同，单精度浮点数可精确到小数点后第7位。FLOAT类型表示小数部分时，由于二进制的原因只能准确的表示1/2n（n不大于表示小数位的二进制位数），或它们的组合，因此在表示小数时会有不精确的现象。2.2 浮点数 FLOAT(二进制位数)这是一个混合了F

9、LOAT和DOUBLE两种类型的数据类型，其中“二进制位数”代表表示该浮点数所占用的二进制位数，这个参数唯一的作用就是使MySQL确定该使用FLOAT型还是DOUBLE型来作为实际的数据类型。2.2 浮点数 FLOAT(二进制位数)当“二进制位数”在0到24（包括24）之间时，这一数据类型等同于未指定“有效位数”和“小数位数”的FLOAT型。当“二进制位数”在25到53之间时，这一数据类型会被MySQL转换成未指定“有效位数”和“小数位数”的DOUBLE型。其取值范围也与相应的数据类型一致。2.2 浮点数 DOUBLE (有效位数，小数位数) 此类型又称双精度浮点数类型，占用8字节长度，取值范

10、围是： -1.7976931348623157E+308到1.7976931348623157E+308，最大负数是： -2.2250738585072014E-308, 最小正数是：2.2250738585072014E-308。以上列出的也只是理论值，不同计算机的硬件可能会使这一范围有所不同。2.2 浮点数 声明DOUBLE类型时可使用“DOUBLE(有效位数，小数位数)”的写法。其中，“有效位数”表示有效数字的最大位数；“小数位数”表示小数点后的最大位数。双精度浮点数可精确到小数点后第15位。2.2 浮点数 DECIMAL(有效位数，小数位数)此类型用于保存对精确度要求较高的数值。在My

11、SQL 5.1以前，DECIMAL类型在保存小数时，使用的不是二进制保存法，而是使用的类似字符串的保存方法，对每一位小数位单独保存；在MySQL 5.1中，DECIMAL类型使用的是二进制保存法。2.2 浮点数 声明时可使用“DECIMAL (有效位数，小数位数)”的写法。其中，“有效位数”表示有效数字的最大位数；“小数位数”表示小数点后的最大位数。如果使用DECIMAL(有效位数)的写法，而不指明“小数位数”，则相当于DECIMAL（有效位数，0）的写法，此时不保存小数部分。如果使用DECIMAL的写法，既不指明“有效位数”，也不指定“小数位数”，则等同于DECIMAL（10，0）的写法。2

12、.2 浮点数 DECIMAL类型的最大有效位数是65，最大小数位数是30，其实际的取值范围由声明时指定的有效位数和小数位数而定，如DECIMAL(5,2)的取值范围是-999.99至999.99。如果在插入数值时，小数位数超过了指定的“小数位数”，MySQL会将该值的小数位数转换成指定的小数位数。2.3 位类型 位类型可会为BIT型和BOOL型。BIT位数 此类型用于声明一个指定位数的数据，位数的取值范围是1到64，默认值是1。此类型所占用的字节数由“位数”决定，每满8位即需一个新的字节，其长度的计算方法为“(位数+7)/8”，如有小数则进一。如BIT25占用4个字节，而BIT26则占用5个字

13、节。2.3 位类型 此数据类型用于存储基于位的数值，指定位类型的值时，可以采用b二进制值的写法。如， b111 代表7，b10000000代表128。如果所插入的值的位数比指定的位数少时，MySQL会在值的左边用0填充，如将值b111插入到BIT8时，等同于插入值b00000111。2.3 位类型 bool型bool型数据用于存储逻辑值，它只有两种状态，即“TRUE”和“FALSE”，或“1”和“0”。此类型等价于TINYINT(1)类型。其值为0时表示“FALSE”，所有非0值都表示“TRUE”。3. 字符串类型 字符串类型包括以下类型：CHARVARCHARBINARYVARBINARYB

14、LOBTEXTENUMSET字符串中可使用几个转义序列，它们用来表示特殊的字符。每个序列以一个反斜杠（“”）开始，指出临时不同于通常的字符解释。注意 NUL 字节与 NULL 值不同；NUL 为一个零值字节，而 NULL 为没有值。3. 字符串类型 3. 字符串类型 转义字符转义字符含义含义0NUL (ASCII 0)单引号”双引号b退格n新行r回车t 制表符反斜杠3. 字符串类型 要在串中包括一个引号，可有如下三种选择：如果串是用相同的引号括起来的，那么在串中需要引号的地方双写引号即可。如果串是用另外的引号括起来的，则不需要双写相应引号。用反斜杠方式表示，这种方法不去管用来将串括起的是单引号

15、还是双引号。3.1 CHAR和VARCHAR这两个类型都用于保存小型字符串，只是MySQL保存它们的方法不同。CHAR字符串长度CHAR字符串长度用于声明一个定长类型的列，其中“字符串长度”用于限制可插入的字符串的最大长度，如CHAR100最多只能保存100个字符。“字符串长度”的设置范围为0到255。3.1 CHAR和VARCHARCHAR类型的列，无论插入的字符数量是多少，最终都会被MySQL处理成指定字符串长度的列。当插入的字符串长度小于指定的字符串长度时，MySQL会在字符串尾部以空格进行填充。3.1 CHAR和VARCHARVARCHAR字符串长度VARCHAR字符串长度用于声明一个

16、变长类型的列，其中“字符串长度”也用于限制插入字符串的最大长度，“字符串长度”的设置范围为0到65535。（由于MySQL规定一条记录的所有列的长度之和不能超过65535个字节，受此限制一列的最大长度实际只能到65532字节。）3.1 CHAR和VARCHAR与CHAR类型不同的是，VARCHAR类型在保存数据时使用的是“按需分配”存储空间的策略，它在保存数据的同时记录下该字符串的实际长度，因此所占用的存储空间大多数情况下是小于指定的“字符串长度”的。如果插入的字符串的长度超过了指定的字符串长度，MySQL会截断该字符串。当被截掉的字符串不是空格时，MySQL会进行警告提示。如果MySQL工作

17、在“strict”模式，将不允许截掉空格以外的字符，否则会产生运行错误，而不仅仅是一个警告提示。3.1 CHAR和VARCHAR使用VARCHAR类型是要注意的一个问题是，VARCHAR在保存字符串时，会自动去除字符串结束处的空格，如果要进行字符串的比较，MySQL也会将仅有尾部空格不同的字符串认为是相同的字符串。这一点与标准SQL的处理方法不同。3.2 BINARY和VARBINARY BINARY和VARBINARY与CHAR和VARCHAR类型很相似，它们主要用存储二进制数据，而不是普通的字符串。这两种数据类型没有对应的字符集，对它们的排序或比较也都只是基于二进制值的比较，而不是字符集的

18、比较。BINARY和VARBINARY的最大长度与CHAR和VARCHAR类型一样，唯一的区别是BINARY和VARBINARY类型的最大长度是指的字节数，而CHAR和VARCHAR类型的最大长度则是指的字符数。3.2 BINARY和VARBINARY MySQL在存储BINARY类型时，会在数据的尾部追加0 x00（数字0）以确保长度为指定的长度，但在执行SELECT语句会不会主动删除掉尾部的0 x00。同时，尾部的0 x00也会参与比较和排序，甚至会影响到DISTINCT关键字。另外，0 x00与空格也不相等，0 x00 SELECT * FROM 表名 WHERE enum列=0;3NU

19、LL值的索引为NULL。 3.4 枚举ENUM 可以将ENUM类型的数据直接作为数字使用，该数据所对应的数字即是其索引值。例如，可以用如下语句将ENUM列显示为其索引值：mysql SELECT enum列+0 FROM 表名;3.4 枚举ENUM 如果将一个数字赋值给ENUM类型的列，该数字会被处理成该ENUM类型的索引值，实际保存的值将是该索引值对应的枚举成员。虽然这种做法是可行的，但本书不建议使用这种方法来定义一个ENUM列，因为这很容易引起混淆，并且完全丧失了ENUM类型的优势。 3.4 枚举ENUM MySQL在对ENUM类型的列进行排序时，会按照其索引值进行排序。例如，对于ENUM

20、(a，b)，a排在b前面，但对于ENUM(b，a)，b排在a前面。空字符串排在非空字符串前面，并且NULL值排在所有其它枚举值前面。要想防止意想不到的结果，按字母顺序规定ENUM列。还可以使用GROUP BY CAST(ENUM列 AS CHAR)来确保按照词汇对列进行排序而不是用索引值。3.4 枚举ENUM 如果想查看一个ENUM列的所有可能的值，可以使用如下语句：SHOW COLUMNSFROM 表名LIKE enum列3.5 多值SETSET类型也对应一个值列表，但与ENUM不同的是，SET类型允许被赋值为这些值中若干个值的组合。指定包括多个SET成员的SET列值时各成员之间用逗号(,)

21、隔开，这就要求SET成员值本身不能包含逗号。3.5 多值SETSET最多可以有64个不同的成员。当创建表时，SET成员值的尾部空格将自动被删除。当读取SET类型的列时，MySQL将会使用SET类型定义时所使用的大小写来显示。同ENUM类型一样，也可以为SET列分配字符集和校对规则。3.5 多值SETMySQL保存SET类型的值时，并不会保存相应的成员字符串，而是采用二进制的保存方法。二进制数字的每一位对应于SET值的一项，其中低阶位对应第1个SET成员。如果将一个SET类型的值作为数字使用时，该值的位设置对应组成列值的SET成员。如下代码可以读取出SET类型的列所对应索引值：mysql SEL

22、ECT set列+0 FROM 表名;4. 日期时间类型 日期时间类型包括为DATETIME、DATE、TIMESTAMP、TIME和YEAR等类型。每个类型都有不同的有效值范围和“零”值，当指定的时间值无法保存到对应的类型中时，MySQL将会把“零”值保存在该类型中。TIMESTAMP是比较特殊的类型，它有专有的自动更新特性。4. 日期时间类型 一般情况下，如果试图插入一个不合法的日期，MySQL将给出警告或错误。但是如果打开了“ALLOW_INVALID_DATES”SQL模式，则MySQL会允许接受一些不合法的日期，如1999-11-31。在“ALLOW_INVALID_ DATES”S

23、QL模式下，MySQL只验证月范围为从0到12，日范围为从0到31，注意月和日都可以为零。4. 日期时间类型 MySQL还允许将0000-00-00保存为“伪日期”(如果不找启用NO_ZERO_DATE SQL模式)。这在某些情况下比使用NULL值更方便，而且数据和索引占用的空间会更小。4. 日期时间类型 使用时间日期类型时，应该注意以下几点：1包含两位年值的日期会令人模糊，因为无法确定其世纪。MySQL使用以下规则解释两位年值：70-99范围的年值转换为1970-199900-69范围的年值转换为2000-20692日期总是以年-月-日顺序(如，98-09-04)，而不是月-日-年或日-月-

24、年顺序。3如果需要，MySQL可以将日期或时间类型的值转换为数字，反之亦然。4. 日期时间类型 4当 MySQL遇到一个日期或时间类型的超出范围或对于该类型不合法的值时，它将该值转换为该类的“零”值。但是超出范围的TIME值会被裁剪到TIME范围的相应端点。5“零”值是特殊值，可以使用值0或0来将一个时间日期类型的列设置成“零”值。4.1 DATATIME当需要同时包含日期和时间信息的值时应该使用DATETIME类型。MySQL以YYYY-MM-DD HH:MM:SS格式检索和显示DATETIME值。支持的范围为1000-01-01 00:00:00到9999-12-31 23:59:59。4

25、.1 DATATIME可以使用任何常见格式指定DATETIME值：YYYY-MM-DD HH:MM:SS或YY-MM-DD HH:MM:SS格式的字符串。YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的没有间割符的字符串，如果字符串对于日期类型是有意义的，也允许使用。YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字，如果数字对于日期类型是有意义的，也允许使用。4.1 DATATIME以上写法中，无效DATETIME值被转换为相应类型的“零”值(0000-00-00 00:00:00、0000-00-00或者00000000000000)。如果字符串中有分隔符，则月和

26、日只有一位时可以只写一位，但如果没有分隔符，就必须写成两位，其中十位数字为0。4.2 DATE当只需要日期值而不需要时间部分时应使用DATE类型。MySQL用YYYY-MM-DD格式检索和显示DATE值。支持的范围是1000-01-01到 9999-12-31。 4.3 TIMESTAMPTIMESTAMP列用于INSERT或UPDATE操作时记录日期和时间。如果人为的给它分配一个值，表中的第一个TIMESTAMP列自动设置为最近操作的日期和时间。也可以通过分配一个NULL值，将TIMESTAMP列设置为当前的日期和时间。TIMESTAMP值返回后显示为YYYY-MM-DD HH:MM:SS格

27、式的字符串，显示宽度固定为19个字符。如果想要获得数字值，可以在TIMESTAMP 列添加+0。4.3 TIMESTAMP初始化和更新TIMESTAMP列的方法如下：1可以将当前的时间戳指定为默认值或自动更新的值，但二者不能同时选择。2可以指定任意一个TIMESTAMP列为自动初始化或更新为当前的日期和时间，而不仅限于第1个TIMESTAMP列。4.4 TIMETIME类型用于保存精确到秒的时间，范围是-838:59:59到838:59:59。MySQL以HH:MM:SS格式显示TIME值，但允许使用字符串或数字为TIME列分配值。小时部分之所以没有限制为小于24小时，是因为它还可用于记录两个时刻之间的时间间隔，这个间隔可能会超过一天的时间。 4.4 TIME为TIME赋值的方法可参考DATATIME中的相关内容，如果要保存的值超出TIME