《MySQL入门很简单》学习笔记（4）之第4章MySQL数据类型（关键词：数据库/MySQL/数据类型）

第4章 MySQL数据类型

    数据类型是数据的一种属性，数据类型可以决定数据的存储格式、有效范围、相应的限制。    MySQL的数据类型包括：    整数类型；    浮点数类型；    定点数类型；    日期和时间类型；    字符串类型；    二进制数据类型。    本章介绍了各种数据类型的含义、特点、使用范围、存储方式。

4.1 MySQL数据类型介绍

4.1.1 整数类型

    标准SQL中支持了INTEGER、SMALLINT两类整数类型。    MySQL还扩展支持了TINYINT、MEDIUMINT、BIGINT。

    MySQL中，INT类型和INTEGER类型是一样的。    MySQL支持数据类型的名称后面指定该类型的显示宽度。基本形式如下：    数据类型（显示宽度）    说明：    数据类型参数是整数数据类型的名称；    显示宽度参数是指定宽度的数值，是能够显示的最大数据的长度。    例如：    INT(4)是指定INT类型的显示宽度为4。    补充：    在不指定宽度的情况下，每个整数类型都有默认的显示宽度。    例如：    TINYINT类型的默认显示宽度与其有符号数的最小值的显示宽度相同（因为此处负号是占一个位置的）。    在整数类型使用时，还可以搭配使用zerofill参数。    zerofill参数表示数字不足的显示空间由0来填补。    注意：    使用zerofill参数时，MySQL会自动加上UNSIGNED属性。那么，该整数类型只能表示无符号数，其显示宽度比默认宽度小1。    补充：    1.虽然上面提到可以设置显示宽度，但依然可以插入大于显示宽度的值。    2.当插入数据的显示宽度大于设置的显示宽度时，数据依然可以插入。而且，可以完整的显示出来，设置的显示宽度在显示该记录时失效。    整数类型还有一个AUTO_INCREMENT属性。该属性可以使字段成为自增字段。具有该属性的字段，在插入新的记录时，该字段的值都会在前一条记录的基础上加1。

4.1.2 浮点数类型和定点数类型

    MySQL中使用浮点数类型和定点数类型类表示小数。    浮点数类型包括：    单精度浮点数（FLOAT型）；    双精度浮点型（DOUBLE型）。    定点数类型就是DECIMAL型。

    MySQL中可以指定浮点数和定点数的精度。基本形式如下：    数据类型（M,D）    说明：    “数据类型”参数是浮点数或定点数的数据类型名称；    M参数称为精度，是数据的总长度，小数点不占位置；    D参数称为标度，是指小数点后的长度为D。    例如：    FLOAT(6,2)的含义，数据FLOAT型，数据长度为6，小数点后保留2位。按此定义1234.56是符合要求的。    注意：    如果不指定精度，浮点数和定点数有其默认的精度。    FLOAT型和DOUBLE型默认会保存实际精度，但这与操作系统和硬件的精度有关。    DECIMAL类型默认整数位为10，小数位为0，即默认为整数。    提示：    在MySQL中，定点数以字符串形式存储。    因此，其精度比浮点数要高。    而且，浮点数会出现误差，这是浮点数一直存在的缺陷。    如果对数据的精度要求比较高，还是选择定点数（DECIMAL）比较安全。

4.1.3 日期与时间类型

    YEAR类型表示时间；    DATE类型表示日期；    TIME类型表示时间；    DATETIME、TIMESTAMP表示日期和时间。

    从上表可以看到，每种日期与时间类型都有一个有效范围。    如果插入的值超过了这个范围，系统会报错，并将零值插入到数据库中。1.YEAR类型    YEAR类型使用1个字节来表示年份。MySQL中以YYYY的形式显示YEAR类型的值。    表示方法：    □使用4位字符串或数字表示。    范围：    1901~2155。    输入格式：    'YYYY'或YYYY。    例如：    输入'2008'或者2008，可直接保存为2008。    如果超过了范围，就会插入0000。    □使用2位字符串表示。    范围：    '00'~'69'转换为2000~2069，'70'~'99'转换为1970~1999。    例如：    输入'35'，YEAR值会转换为2035；    输入'90'，YEAR值会转换为1990;    '0'和'00'的效果是一样的。    □使用两位数字表示。    范围：    1~69转换为2001~2069,70~99转换为1970~1999。    注意：    2位数字与2位字符串是不一样的。    因为，如果插入0，转换后的YEAR值不是2000，而是0000。    如果只需要记录年份，选择YEAR类型可以节约空间。    注意：    使用YEAR类型时，要区分0和'0'。    向YEAR类型的字段插入0，  存入该字段的年份是0000；    插入'0'，存入该字段的年份是2000；    '00'和'0'是一样的效果。2.TIME类型    TIME类型使用3个字节来表示时间。MySQL中以HH:MM:SS的形式显示TIME类型的值。说明：    HH表示时；    MM表示分，取值范围为0~59；    SS表示秒，取值范围为0~59。    TIME类型的范围可以从‘-838:59:59’~‘838:59:59’。    虽然，小时的范围是0~23，但是，为了表示某种特殊需要的时间间隔，将TIME类型的范围扩大了。而且，还支持了负值。    TIME类型的字段赋值的表示方法如下：    □'D HH:MM:SS'格式的字符串表示。    说明：    D表示天数，取值范围0~34；    保存时，小时的值等于（D*24+HH）。    例如：    输入'2 11:30:50'，TIME类型会转换为59:30:50。    注意：    输入时可以不严格按照这个形式，也可以是：    'HH:MM:SS'；    'HH:MM'；    'D HH:MM'；    'D HH'；    'SS'。    例如：    输入'30'，TIME类型会转换为00:00:30。    □'HHMMSS'格式的字符串、HHMMSS格式的数值表示。    例如：    输入'345454'，TIME类型会转换为34:54:54；    输入345454，TIME类型会转换为34:54:54。    输入0或'0'，TIME类型会转换为0000:00:00。    □使用CURRENT_TIME或者NOW()输入当前系统时间。    注意：    一个合法的TIME值，如果超出TIME的范围，将被裁为范围最接近的断点。    例如：    '880:00:00'被转换为838:59:59。    无效TIME值，在命令行下，无法被插入到表中。3.DATE类型    DATE类型使用4个字节来表示日期。    MySQL中是以YYYY-MM-DD的形式显示DATE类型的值。    说明：    YYYY表示年；    MM表示月；    DD表示日。    范围：    DATE类型的范围可以从‘1000-01-01’~‘9999-12-31’。    □'YYYY-MM-DD'或'YYYYMMDD'格式的字符串表示。    范围：    '1000-01-01'~'9999-12-31'。    例如：    输入'4008-2-8'，DATE类型将转换为4008-02-08；    输入'20220308'，DATA类型将转换为2022-03-08。    MySQL中还支持一些不严格的语法格式，任何标点都可以用来做间隔符。例如：    'YYYY/MM/DD'；    'YYYY@MM@DD'；    'YYYY.MM.DD'等等。    例如：    输入'2011.3.8'，DATE类型将转换为2011-03-08。    □'YY-MM-DD'或者'YYMMDD'格式的字符串表示。    说明：    'YY'的取值，'00'~'69'转换为2000~2069，'70'~'99'转换为1970~1999。    例如：    输入'35-01-02'，DATE类型将转换为2035-01-02。    MySQL也支持一些不严格的语法形式，如'YY/MM/DD'、'YY@MM@DD'、'YY.MM.DD'等。例子见书上。    □YYYYMMDD、YYMMDD格式的数字表示。    说明：    'YY'的取值，'00'~'69'转换为2000~2069，'70'~'99'转换为1970~1999。    例如：    输入790808，DATE类型将转换为1979-08-08；    如果输入的值为0，那么DATE类型会转化为0000-00-00。    □使用CURRENT_DATE或NOW()来输入当前系统日期。4.DATETIME类型    DATETIME类型使用8个字节来表示日期和时间。    MYSQL中以‘YYYY-MM-DD HH:MM:SS’的形式显示DATETIME类型的值。从其形式可以看出，DATETIME类型可以直接用DATE类型和TIME类型组合而成。    给DATETIME类型的字段赋值的表示方法如下：    □'YYYY-MM-DD HH:MM:SS'或'YYYYMMDDHHMMSS'格式的字符串表示。    可以表达的范围是：    ‘1000-01-01 00:00:00’~‘9999-12-31 23:59:59’。    □'YY-MM-DD HH:MM:SS'或'YYMMDDHHMMSS'格式的字符串表示。    取值范围：    'YY'，'00'~'69'转换为2000~2069，'70'~'99'转换为1970~1999。    □YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字表示。    例如：    输入20080808080808，DATETIME类型转换为2008-08-08 08:08:08。    □使用NOW()来输入当前系统日期和时间。5.TIMESTAMP类型         TIMESTAMP类型使用4个字节来表示日期和时间。    范围：    1970-01-01 08:00:01~2038-01-19 11:14:07。    MySQL中，TIMESTAMP是以'YYYY-MM-DD HH:MM:SS'的形式显示TIMESTAMP类型的值，形式上与DATETIME类型显示的格式是一样的。    例子见书上。    TIMESTAMP类型的几种与DATETIME类型不同的形式。如下：    （1）使用CURRENT_TIMESTAMP来输入系统当前日期与时间；    （2）输入NULL时，系统会输入系统当前日期与时间；    （3）无任何输入时，系统会输入系统当前日期与时间。

4.1.4 字符串类型

    包括CHAR、VARCHAR、BLOB、TEXT、ENUM、SET。1.CHAR类型和VARCHAR类型    CHAR类型和VARCHAR类型都是在创建表时指定了最大长度，基本形式：    字符串类型(M)    “字符串类型”参数指定了数据类型为CHAR类型还是VARCHAR类型；    M参数指定了该字符串的最大长度为M。    例如，CHAR(4)是指数据类型为CHAR类型，最大长度为4。    CHAR类型的长度是固定的，在创建表时就指定了，长度可以是0~255的任意值。    例如：    CHAR(100)就是指定CHAR类型的长度为100。    VARCHAR类型的长度是可变的，在创建表时指定了最大长度，定义时，最大值可以取0~65535之间的任意值。    指定了VARCHAR类型的最大值后，其长度可以在0到最大长度之间。    例如：    VARCHAR(100)的最大长度是100,但是，不是每条记录都要占用100个字节。而是在这个最大值范围内，使用多少，分配多少。    VARCHAR类型实际占用的空间为字符串的实际长度加1，这样可以有效节约系统空间。

2.TEXT类型    TEXT类型是一种特殊的字符串类型。TEXT只能保存字符类型，如新闻内容等。包括：    TINYTEXT;    TEXT;    MEDIUMTEXT；    LONGTEXT。    4种TEXT类型允许的长度和存储空间的对比：

3.ENUM类型    ENUM类型又被称为枚举类型。    在创建表时，ENUM类型的取值范围就以列表的形式指定了。基本形式：    属性名 ENUM('值1', '值2',..., '值n')    说明：    属性名参数指字段的名称；    “值n”参数表示列表中的第n个值，这些值末尾的空格将会被系统直接删除。    ENUM类型的值只能取列表中的一个元素。    其取值列表中最多能有65535个值。    列表中的每个值都有一个顺序排列的编号，MySQL中存入的是这个编号，而不是列表中的值。    如果ENUM类型加上了NOT NULL属性，其默认值为取值列表的第一个元素。    如果不加NOT NULL属性，ENUM类型将允许插入NULL，而且NULL为默认值。    说明例子见书上。    如果只能选取列表中的一个值，就选择ENUM类型；    如果需要选取列表中多个值的组合，则需要选择SET类型。4.SET类型    在创建表时，SET类型的取值范围就以列表的形式指定了。基本形式：    属性名 SET('值1', '值2', ..., '值n')    说明：    “属性名”参数指字段的名称；    “值n”参数表示列表中的第n个值，这些值末尾的空格将会被系统直接删除。其基本形式与ENUM类型一样。SET类型的值可以取列表中的一个元素或者多个元素的组合。取多个元素时，不同元素之间用逗号隔开。SET类型的值最多只能是由64个元素构成的组合。    同ENUM类型一样，列表中的每个值都有一个顺序排列的编号。MySQL中，存入的是这个编号，而不是列表中的值。    插入记录时，SET字段中的元素顺序无关紧要。存入MySQL数据库后，数据库会字段按照定义时的顺序显示。

4.1.5 二进制类型

    二进制类型是在数据库中存储二进制数据的数据类型。    包括：    BINARY、VARBINARY、BIT、BLOB、MEDIUMBLOB、LONGBLOB。    各种二进制类型对比：

1. BINARY和VARBINARY类型    BINARY类型和VARBINARY类型都是在创建表时指定了最大长度，基本形式：    字符串类型( M )    说明：    “字符串类型”参数指定了数据类型为BINARY类型还是VARBINARY类型；    M参数指定了该二进制数的最大字节长度为。    例如：    BINARY(10)是指数据类型为BINARY，其最大长度为10。    BINARY类型的长度是固定的，在创建表时就指定了，不足最大长度的空间由“\0”补全。例如：    BINARY(50)就是指定BINARY类型的长度为50。    VARBINARY类型的长度是可变的，在创建表时指定了最大长度。指定好了VARBINARY类型的最大值以后，其长度可以在0到最大长度之间。例如：    VARBINARY(50)的最大字节长度是50，但是，不是每条记录的字节长度都是50。在这个最大值范围内，使用多少分配多少。    VARBINARY类型实际占用的空间为实际长度加1，这样，可以有效节约系统的空间。2. BIT类型    BIT类型也是在创建表时指定了最大长度，基本形式：    BIT( M )     说明：    “M”指定了该二进制数的最大字节长度为M，M的最大值为64。    例如：    BIT(4)就是数据类型为BIT类型，长度为4，存储的数据是0~15。    在查询BIT数据类型的数据时，要用BIN(字段名+0)来将值转换为二进制显示。    例如：    SELECT BIN(b+0) FROM bt;    详细见书上。3. BLOB类型    binary large object block 二进制大对象块    BLOB可以用来保存数据量很大的二进制数据，如图片、PDF文档等。    包括：    TINYBLOB；    BLOB；    MEDIUMBLOB；    LONGBLOB。    BLOB类型与TEXT类型很类似。    不同点：    BLOB类型用于存储二进制数据，是根据二进制编码进行比较和排序；    TEXT类型是文本模式进行比较和排序的。    技巧见书上。

4.2 如何选择数据类型

    在MySQL中创建表时，需要考虑为字段选择哪种数据类型是最合适的。只有选择了合适的数据类型，才能提高数据库的效率。    选择数据类型的原则：1. 整数类型和浮点类型    最常用的整数类型是INT类型。    如果需要精确到小数点10位以上，就应该选择DOUBLE类型，而不是FLOAT类型。2. 浮点数类型和定点数类型    对于浮点数和定点数，当插入值的精度高于实际意义的精度时，系统会自动进行四舍五入处理。    浮点数进行四舍五入时，系统不会报警，定点数会出现警告。    在未指定精度的情况下，浮点数和定点数有其默认的精度。FLOAT型和DOUBLE型默认会保存实际精度，这个精度与操作系统和硬件的精度有关。DECIMAL型默认整数位为10，小数位为0，即默认为整数。    在MySQL中，定点数精度比浮点数要高。而且，浮点数会出现误差。如果要对数据的精度要求比较高，应该选择定点数。3. CHAR类型和VARCHAR类型4. 时间和日期5. ENUM类型和SET类型6. TEXT类型和BLOB类型

4.3 常见问题及解答

1. MySQL中什么数据类型能够储存路径？
2. MySQL中如何使用布尔类型？
3. MySQL中如何存储JPG图片和MP3音乐？

4.4 小结

参考文献：
1.《MySQL入门很简单》。