MySQL教程（第4版） 课件 第2章 数据类型

第2章

数

据

类

型——数

值

型01整

数

类

型整数类型整数类型包括的数据类型如表。数据类型字节数有符号范围无符号范围（含）tinyint1-128~127（-27~27-1）0~255（0~28-1）smallint2-32768~32767（-215~215-1）0~65535（0~216-1）mediumint3-8388608~8388607（-223~223-1）0~16777215（0~224-1）intinteger4-2147483648~2147483647（-231~231-1）0~4294967295（0~232-1）bigint8±9.22×1018（-263~263-1）0~1.84×1019（0~264-1）02实

数

类

型单精度浮点类型双精度浮点类型定点类型实数类型实数类型包括浮点类型和定点类型，浮类点类型分为单精度浮点型（float和real）和双精度浮点型（double），它们遵循IEEE754标准；而定点型为decimal或者numeric型，实数类型定义格式如下：实数类型名(长度.小数位)1．单精度浮点类型表达单精度浮点数的float或real类型数据占4字节，其中1位表示符号位，8位表示指数，23位表示尾数。其中：有符号数表示范围：（-3.402823466E+38~1.175494351E-38)，0，(1.175494351E-38~3.402823466351E+38）；无符号数表示范围：0，（1.175494351E-38~3.402823466E+38）。float可以用含小数的数值表示，也可以用科学记数法表示，但只保证6位有效数字的准确性。定义float(m,d)表示数值总长为m位，其中有d位小数。例如，列设置为float(10,2)，输入-3584678.268，查询结果为-3584678.25。实数类型2．双精度浮点类型表达双精度浮点数double类型数据占8字节，其中1位表示符号位，11位表示指数，52位表示尾数。其中：有符号数表示范围：（-1.7976931348623157E+308~2.2250738585072014E-308），0，（2.2250738585072014E-308~1.7976931348623157E+308）；无符号数表示范围：0,（2.2250738585072014E-308~1.7976931348623157E+308）。定义double(m,d)表示数值总长为m位，其中有d位小数。double只保证16位有效数字的准确性。实数类型3．定点类型定点类型为decimal或者numeric类型，它们是同义词，用于存储定点数，保存必须为确切精度的值，例如会计系统中的货币数据。定点类型数值总长度范围为1～65，默认为10位数字，没有小数位。定义decimal(m,d)表示总长度m位，其中有d位小数。decimal(m)没有小数。小数部分超出d位，若四舍五入后，整数部分没有超出范围，则只给出警告，但能成功操作并四舍五入删除多余的小数位后保存；若四舍五入后，整数部分超出范围，则会报错，并拒绝处理。例如，列设置为decimal(10,2)，输入-3584678.268，查询结果为-3584678.27。03位

类

型位

类

型位类型就是bit类型，bit(n)型允许存储n位二进制位值（n取值为1～64，默认为1）。与tinyint相比，即使仅仅存储0和1两种状态的值，也需要1个字节，而bit(1)存储0和1两种状态的值只需要1个二进制位。如果我们存储的状态不超过2n，就可以使用bit(n)。另外，向位类型列输入的值只要<2n，就可以表达为二进制、十进制、十六进制等（例如0b10011、63、0x41），但bit(n)查询显示输出的是二进制值。第2章

数

据

类

型——字符串和二进制串型01字符串类型字符串描述字符集字符排序规则字符串类型1．字符串描述字符串数据类型如表。数据类型最多存放字符个数（字节）char(n)255varchar(n)65535（2）tinytext255（1）text65535（2）mediumtext16777215（3）longtext4294967295（4）字符串类型说明：（1）char(n)和varchar(n)括号中的n代表字符的个数，不是字节数。例如，采用UTF8字符集，存放n个纯ASCII码字符，占用n个字节。（2）实际上，tinytext、text、mediumtext、longtexttext类型就相当于varchar(n)，n就是该类型最大可存放的字符个数。例如：text类型相当于varchar(65535)。（3）如果一个表中的任何列都有可变长度，结果会使整个行变长。如果一张表包含任何变长的列（varchar、text等），则所有大于3个字符的char列都将被改变为varchar列。（4）可变长度实际存储空间除了字符本身外，还需要额外存储当前字符占用的空间字节数，而它本身占的字节为其数据类型最大字符数。例如：varchar(60000)，当前值'abc'，占用空间为3+2=5，前者是内容占用，后者为长度占用，因为最大长度60000需要用2个字节才能存放。（5）字符串数据类型数据用单引号和双引号括起，内容可以是平台字符，也可以是转义字符。例如“\'”为单引号，“\n”为回车。字符串类型2．字符集ASCII码及其字符集ASCII码即美国标准信息交换码，是目前世界上的计算机中使用最广泛的西文字符集编码。西文字符集由拉丁字母、数字、标点符号及一些特殊符号组成。在标准ASCII码表中，20H～7EH部分属于可打印字符，共95个。GB2312-80编码及其字符集1981年，我国颁布信息交换汉字编码的第一个国家标准，称为GB2312-80。其字符集由以下3部分组成。（1）字母、数字和各种符号，包括拉丁字母、俄文、日文平假名与片假名、希腊字母、汉语拼音等，共682个。（2）一级常用汉字，共3755个，按汉语拼音排列。（3）二级常用汉字，共3008个，按偏旁部首排列。GB2312-80字符集构成一个二维码表，它分成94行和94列，行号称为区号，列号称为位号，如图。字符串类型GB2312-80字符集的每个汉字或符号在码表中都有各自的位置，即所在的区号及位号。用区号及位号组合在一起的编码就是该汉字区位码。例如，“中”字的区号是54，位号是48，区位码是（54，48），用十六进制表示为（36H，30H）。为了与西文字符共存，汉字内码不能与ASCII码冲突。因为ASCII码用一个字节表示，最高位为0；汉字内码用2个字节表示，每个字节的最高位为1。如图。字符串类型GBK编码及其字符集GB2312编码汉字太少，缺少繁体字，无法满足人名、地名、古籍整理、古典文献研究等应用的需要。于是在1995年推出了“汉字内码扩充规范”，称为GBK标准，它在GB2312的基础上增加了大量的汉字（包括繁体字）和符号，共21003个汉字和883个图形符号。GB2312中的字符仍然采用原来的编码，仅仅对新增加的符号和汉字进行另外编码。GBK汉字内码也使用双字节表示，但仅第1字节最高位为“1”，如图。字符串类型Unicode编码及UTF8字符集Unicode只是字符集，却没有规定该如何存储、传输，而UTF规定了字符如何传输和存储。UTF又分为UTF-8、UTF-16和UTF-32，它们都可以完全表示Unicode标准中的所有字符，而UTF-8使用较多。UTF-8为单字节可变长编码，它根据Huffman编码的思想（最常出现的字符编码尽量短），以及不同符号使用不同的字节长度表示，因此UTF-8采用了1～4个字节表示一个符号变长的编码方式。从Unicode到UTF-8的编码方式如表。Unicode编码(十六进制)UTF-8字节流(二进制)字符000000-00007F0xxxxxxxASCII字符000080-0007FF110xxxxx10xxxxxx拉丁、希腊、阿拉伯……000800-00FFFF1110xxxx10xxxxxx10xxxxxx中、日、韩统一整理出来的称为CJK汉字010000-10FFFF11110xxx10xxxxxx10xxxxxx10xxxxxx其他字符。字符串类型3．字符排序规则字符排序又称字符序，MySQL中众多的排序规则可以归纳为以下两大类排序集：（1）字符集_语言/other_ci/cs这是默认的排序集，当字符无须区分大小写时采用该集合中的规则，规则命名以其对应的字符集作为前缀，例如：字符序utf8_general_ci，表明它是字符集utf8的字符序。ci对大小写不敏感，而cs对大小写敏感。utf8字符集对应的collation没有cs。（2）字符集_bin当字符需要区分大小写时，选择该排序集。每个字符集对应一定数量的排序规则，可以指定数据集如何排序以及字符串的排序规则。MySQL提供了4个等级的默认字符集以及排序规则，分别是服务器、数据库、表和列级别。02二进制串类型二进制数据类型大二进制数据类型二进制串类型1．二进制数据类型二进制数据类型包括binary和varbinary，它们类似于字符串类型char和varchar，但不同的是，它们存储的不是字符串，而是二进制串。所以它们没有字符集，并且排序和比较需要基于列字节的数值。二进制数据类型列可以保存数值，也可以保存字符串，均变成对应的字节保存。而且经常将声音、图像、视频等文件用系统函数转换成字节数据保存在其中。当保存binary值时，在它们右侧填充0x00值以达到指定长度。取值时不删除尾部的字节。二进制串类型2．大二进制数据类型大二进制数据类型为blob类型，是一个二进制对象，可以存储数据量很大的二进制数据，也可视为足够大的varbinary列，可用于存储大的图片、视频等。有4种blob类型：tinyblob、blob、mediumblob和longblob，它们可容纳值的最大长度不同。tinyblob类型：最大长度为255（28-1）字节。blob类型：最大长度为65535（216-1）字节。mediumblob类型：最大长度为16777215（224-1）字节。longblob类型：最大长度为4294967295（232-1）字节。实际长度与编码有关，比如UTF8编码，长度会减半。二进制串类型说明：（1）当保存或检索blob和text列的值时不删除尾部空格，这与varbinary和varchar列相同。（2）比较时将用空格对text进行扩充以适合比较的对象，正如char和varchar。（3）对于blob和text列的索引，必须指定索引前缀的长度。对于char和varchar，前缀长度是可选的。（4）blob和text列不能有默认值。（5）mysqlconnector/odbc将blob值定义为longvarbinary，将text值定义为longvarchar。（6）blob或text对象的最大值由其类型确定，但在客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定。（7）每个blob或text值分别由内部分配的对象表示。第2章

数

据

类

型——日期时间型日期时间型1．日期类型日期类型包含datetime、date和timestamp类型，这3者都用来表示日期/时间。（1）datetime类型：以'yyyy-mm-ddhh:mm:ss'（年-月-日时:分:秒）格式检索和显示。支持的范围为'1000-01-0100:00:00'～'9999-12-3123:59:59'。（2）date类型：用'yyyy-mm-dd'（年-月-日）格式检索和显示。支持的范围为'1000-01-01'～'9999-12-31'。（3）timestamp类型：包含日期和时间，范围为'1970-01-0100:00:01'utc～'2038-01-1903:14:07'utc。该类型能够自动存储记录修改的时间。timestamp的显示尺寸必须是偶数且在2～14范围内。如果指定0显示尺寸或比14大，尺寸被强制设定为14。在1～13范围内的奇数值尺寸被强制设定为下一个更大的偶数。将它设为NULL，默认为当前的日期和时间。可以使用任何常见格式指定datetime、date和timestamp值。对于包括日期部分间隔符的字符串值，如果日和月的值都小于10，则不需要指定两位数，例如'1979-6-9'与'1979-06-09'相同。如果一个数值是8位或14位长，则假定为'yyyymmdd'或'yyyymmddhhmmss'格式，前4位数表示年。如果为一个datetime或timestamp对象分配一个date值，结果值的时间部分被设置为'00:00:00'。函数返回的结果，其值适合datetime、date或者timestamp上下文，如now()或current_date。日期时间型2．时间类型time值的范围为'-838:59:59'～'838:59:59'。（1）可以用各种格式指定time值，如下。'dhh:mm:ss.fraction'格式的字符串。还可以用'hh:mm:ss.fraction'、'hh:mm:ss'、'hh:mm'、'dhh:mm:ss'、'dhh:mm'、'dhh'或'ss'。这里d表示日期，取值范围为0～31。'hhmmss'格式的没有间隔符的字符串，假定是有意义的时间。例如，'101112'被理解为'10:11:12'。hhmmss格式的数值，假定是有意义的时间。例如，101112被理解为'10:11:12'。（2）函数返回的结果，其值适合time上下文，如current_time。（3）对于指定为包括时间部分间隔符的字符串的time值，如果时、分或者秒值小于10，则不需要指定两位数。例如，'8:3:2'与'08:03:02'相同。（4）如果time值没有冒号，则最右侧的两位数表示秒，time值表示过去的时间而不是当天的时间。例如，'1112'和1112被解释为'00:11:12'（11分12秒），'12'和12被解释为'00:00:12'。相反，如果time值中使用冒号，则肯定被看作当天的时间。例如，'11:12'表示'11:12:00'，而不是'00:11:12'。（5）无效time值被转换为'00:00:00'。由于'00:00:00'本身是一个合法的time值，所以只从一个'00:00:00'值还不能判断原来的值是'00:00:00'还是不合法的。日期时间型3．年类型年类型就是year类型，是一个单字节类型，用于表示年。MySQL以yyyy格式检索和显示year值，范围为1901～2155。（1）可以指定各种格式的year值，如下。4位字符串：如'2018'。4位数字：如2018。2位字符串：'00'～69'转换为2000～2069，'70'～'99'转换为1970～1999。例如，'18'表示2018。一个字符串'0'或'00'可能被解释为0000。2位整数：范围为1～99。1～69转换为2001～2069，70～99转换为1970～1999。不能直接将零指定为数字并将它解释为2000。（2）函数返回的结果，其值适合year上下文，如now()。（3）非法year值被转换为0000。第2章

数

据

类

型——枚举、集合、键值和空间型01枚举类型和集合类型枚举类型：enum集合类型：set枚举类型和集合类型1．枚举类型：enum（1）定义enum('值1','值2','值3'...,'值n')定义需要enum需要设置成员表，成员为字符串（尾部空格将自动被删除），即使成员是数字，也必须是加引号的字符串。成员之间用“,”分隔。enum类型创建时指定的成员就是枚举值，每个枚举值位置对应一个索引编号，MySQL存储的就是这个索引编号。（2）存储enum类型存储的不是成员的字符串，而是成员的索引编号，定义1～255个成员的枚举索引编号需要1个字节存储；对于256～65535个成员，索引编号需要2个字节存储。例如，“专业”枚举定义enum('计算机','通信工程','人工智能')，该列可以取的值和对应的索引编号如表。成员索引编号NULLNULL“0'计算机'1'通信工程'2'人工智能'3枚举类型和集合类型2．集合类型：set（1）定义SET('值1','值2','值3'...,'值n')set类型成员不能为空，最少需要一个成员，最多包含64个成员。实际内容为这些成员构成的组合，1～8个成员的集合占1个字节，9～16个成员的集合占2个字节，17～24个成员的集合占3个字节，25～32个成员的集合占4个字节，33～64个成员的集合占8个字节。（2）存储set类型存储的不是成员的字符串，而是其二进制位信息。例如：“兴趣”集合定义set('唱歌','跳舞','智力游戏','足球运动','书法')。“兴趣”成员和存储值表示如表。成员二进制值十进制值'唱歌'11'跳舞'102'智力游戏'1004'足球运动'10008'书法'100001602键值（JSON）类型JSON对象JSON数组键值（JSON）类型1．JSON对象JSON类型的数据是以JSON对象的形式提供的，一个标准的JSON对象包含一组键值对，用逗号分隔，以“{”和“}”字符括起来：{"键1":"值1",...}创建JSON对象采用JSON_OBJECT系统函数格式如下：JSON_OBJECT(键1,值1,键2,值2,...)创建JSON对象和存储遵循的规则基本如下：（1）键名（无论中英文）都必须以双引号（"）括起来。（2）键值是字符串型（无论中英文）必须加双引号（"）或单引号（'）括起来。（3）键值是数值型（整数或实数类型），可以以数值或字符串两种格式存储。当以数值存储时，直接写出数值即可；而以字符串存储时，键值需加双引号或单引号。（4）键值是日期时间型，可以用两种方式给出：一种是直接以字符串形式写出；另一种是通过系统内置的日期时间函数获得。JSON对象构建的数据如图。键值（JSON）类型2．JSON数组当需要同时存储很多JSON对象或者标量值时，可将它们集中在一起构造出一个数组数据结构来统一管理。JSON数组包含多个值，这些值由逗号分隔格式如下：JSON_ARRAY(值,...)JSON数组元素显示包含在“[”和“]”中如下：[{键1":"值1",...},...]JSON数组构建的数据如图。03空

间

类

型单一空间类空间集合类空

间

类

型1．单一空间类（1）点类型：point(xy)存放一个坐标值，用空格分隔。例如：point(18206)。point(经度，维度)