MySQL数据库技术与应用第3章 数据类型

第3章数据类型主要内容3.2MySQL的数据类型3.1何为数据类型3.3性能3.4应该选择何种数据类型3.1何为数据类型列的数据类型定义了可存储何种类型的值，以及如何存储。另外，也可能存在与数据类型关联的一些元属性，如大小(例如用于数字的字节数，字符串中包含的最大字符数量等)，以及用于字符串的字符集和排序规则等。尽管数据类型属性似乎不是什么必要的限制，但它们也自有好处。其中包括：数据验证文档优化存储性能正确排序3.1.1数据验证数据类型概念的核心，就是定义所允许的值类型。定义为整数类型的列，只能存储整数值。这是一种保障措施。也是一种限制。3.1.2文档MySQL提供了多种用来记录列的方法：使用可描述值的列名、COMMENT列子句、CHECK约束以及数据类型。数据类型有助于描述你所期望的数据类型。如果选择date列而不是datetime列，那么显然你打算只存储日期部分。使用tinyint而非int，则表明你更期望使用较小的值。设置恰当的数据类型可以间接地帮助优化查询。3.1.3优化存储MySQL会以不同的方式来存储所有数据。选择给定数据类型的存储格式，应该使其尽可能紧凑，以便减少存储空间。选择正确的列最大容量，可减少所需的存储空间。注意不要过度优化。很多情况下，更改数据类型或列的大小需要重建整张表。如果表很大，这样做代价很高。3.1.4性能各种数据类型在创建时成本不同。通过选择正确的数据类型，就可显著提升查询性能。如果两个列的值相对应，要确保它们具有相同的数据类型，包括字符集和字符串排序规则。3.1.5正确排序日期类型对值的排序方式具有重大影响。虽然人脑可以很直观地理解数据，但计算机则需要一些帮助才能理两个值之间是如何进行比较的。因此，数据类型和如何对比字符串将是用于确保数据能正确排序的关键属性。排序的重要原因有二：正确的排序可以快速知道两个值是否相等，或者一个值是否在给定范围内。创建索引时，排序能确保MySQL快速找到你想要的值。3.2MySQL的数据类型1.整型数据类型Bigint数据类型可以表示-263~263-1范围内的所有整数。在数据库中占用8个字节。int数据类型可以表示-231~231-1范围内的所有整数。在数据库中占用4个字节。Smallint数据类型可以表示-215~215-1范围内的所有整数。在数据库中占用2个字节。这种数据类型对表示一些常常限定在特定范围内的数值型数据非常有用。Tinyint数据类型可以表示0~255之间的整数，在数据库中占用1个字节。这种数据类型对表示有限数目的数值型数据非常有用。2.浮点型数据类型浮点型数据类型可以表示包含小数的十进制数。包含精确数值型和近似数值型。（1）精确数值型（2）近似数值型:不能精确记录数据的精度，所保留的精度由二进制数字系统的精度决定。SQLsever提供了两种近似数值型数据类型。3.字符型数据类型字符类型表示符号用法ANSI字符char存储指定长度的字符串varchar存储可变长度字符text存储大量非Unicode字符Unicode字符nchar用于存储Unicode字符nvarchar与varchar类似，存储可变长度Unicode字符ntext与text类似，存储大量Unicode字符数据char类型存储字符数据时，每一个字符占一个字节大小。使用char类型时，应该给定数据的最大长度，定义格式为：char（n）如果没有指定n的大小，默认值是1，最长可以容纳8000个字符；如果实际数据的字符长度大于给定的最大长度时，超过的字符将会被截断；若实际数据的字符长度小于给定的最大长度时，多余的字节被系统使用空格填充。varchar的使用方式与char基本相同。它们的区别在于：当列中字符长度基本一致时（如学号、姓名等），可以使用char类型；当数据长度相差较大时（如备注），使用varchar类型可以节省存储空间；当数据有可能涉及到英语外其他语言时，应该使用Unicode字符编码。每一个Unicode字符需要两个字节存储，因此，Unicode格式比ANSI格式有更大的字符集范围：ANSI字符集仅有256个字符，Unicode字符集有65536个不同的字符。使用Unicode字符可以在表的一个列中同时出现中文、英文、法文等，而不会出现编码冲突。通常使用以下方式来表示Unicode字符：

N’Unicode字符’nchar和nvarchar分别用于存储固定长度和可变长度的Unicode字符数据；text和ntext，分别对应存储长度很大的ANSI字符和Unicode字符数据。微软公司建议，用户应该避免使用text和ntext数据类型，而使用varchar和nvarchar存储大文本数据。4.日期时间数据类型data数据类型：只存储日期型数据类型，存储用字符串表示的日期数据，不存储时间数据，可以表示取值范围从0001-01-01到9999-12-31(公元元年1月1日到公元9999年12月31日)间的任意日期值。引入date类型，克服了datetime类型中既有日期又有时间的缺陷，使对日期的查询更加方便。数据格式为“YYYY-MM-DD”：YYYY：用来表示年份的四位数字，其取值范围为0001~9999；MM：用来表示指定年份中月份的两位数字，其取值范围为01~12；DD：用来表示指定月份中某一天的两位数字，其取值范围为01~31（其最高值取决于具体的月份天数）。time数据类型：与date数据类型类似，以字符的形式记录一天的某个时间。如果只想存储时间数据而不需要存储日期部分就可以利用time数据类型，取值范围从00：00：00.0000000到23：59：59.9999999。数据格式为“hh:mm:ss[.nnnnnnn]”：hh:用来表示小时的两位数字，其取值范围为0~23。mm：用来表示分钟的两位数字，其取值范围为0~59。ss:用来表示秒的两位数字，其取值范围为0~59。n*是0~7为数字，其取值范围为0~9999999，它表示秒的小部分。time值在存储时占用的空间是5个字节。Datetime：Datetime可以用来表示从1753年1月1日到9999上12月31日之间的日期和时间数据，当插入数据或在其它地方使用时，需用单引号或双引号括起来。该类型数据占用8个字节的空间。精确度为3%s(3ms或0.003s)。Smalldatetime：Smalldatetime类型与Datetime类型相似，其表示的是自1900年1月1日到2079年12月31日之间的的日期和时间数据，精确度为1分钟。当日期时间精度较小时，使用Smalldatetime，该类型数据占用4个字节的存储空间。datetime2数据类型：datetime2是datetime的扩展类型，是一种将日期和时间混合的数据类型，不过其时间部分秒数的小数部分可以保留不同位数的值，比datetime数据类型的取值范围更广，默认的最小精度最高，并具有可选的用户定义的精度。可以存储从公元元年1月1日到9999年12月31日的日期。用户可以根据自己的需要设置不同的参数来设定小数位数，最高可以设定到小数点后七位(参数为7)，也可以不要小数部分(参数为0)，以此类推。datetimeoffset数据类型：用于存储与特定的日期和时区相关的日期和时间，采用24小时制与日期相组合并可识别时区的时间。这种数据类型的日期和时间存储为协调世界时(CoordinatedUniversalTime，UTC)的值，然后，根据与该值相关的时区来定义要增加或减少的时间数。datetimeoffset类型是由年、月、日、小时、分钟、秒和小数秒组成的时间戳结构。小数秒的最大小数位数为7。5.位数据类型Bit称为位数据类型，只能取0或1为值，其长度为1字节。Bit数据类型可以表示1、0或NULL数据。Bit用作条件逻辑判断时，可以来判断TRUE(1)或FALSE(0)数据，输入非零值时，系统会将其替换为1。6.货币数据类型货币数据类型可以用于存储货币或现金值，其包括MONEY型和SMALLMONEY型两种。在使用货币数据类型时，需要在数据前加上货币符号，以便系统辨识其为哪国的货币，如果不加货币符号，则系统默认为“￥”。MONEY：用于存储货币值，存储在Money数据类型中的数值以一个正数部分和一个小数部分存储在两个4字节的整型值中，其取值范围从-263～263-1，精确到货币单位的千分之十。Money数据类型中整数部分包含有19个数字，小数部分包含有4个数字，因此Money数据类型的精度是19，存储时占用的存储空间为8个字节。SMALLMONEY：与MONEY数据类型类似，取值范围为-214748.3648～214748.3647，其存储的货币值范围比MONEY数据类型小，SMALLMONEY存储时占用的存储空间为4个字节。7.二进制数据类型二进制数据类型包括Binary[(n)]和varbinay[(n)]两种：Binary[(n)]：表示固定长度的n个字节的二进制数据。n取值必须在1~8000范围内，在数据库中存储在n个字节中。varbinay[(n|max)]：表示可变长度的二进制数据。n取值必须在1~8000范围内。Max表示最大存储大小为231-1字节。对于varbinary类型的数据，存储大小为所输入数据的实际长度加2个字节。8.文本和图形数据类型文本和图形数据类型如下：Text数据类型：用来声明变长的字符数据，用于存储文本数据，服务器代码页中长度可变的非Unicode数据。在定义过程中，不需要指定字符的长度，最大长度为231-1个字节。当服务器代码页使用双字节时，存储量仍为231-1个字节。存储大小也可能小于231-1个字节(取决于字符串)。Ntext数据类型:ntext数据类型与text数据类型的作用相同，是长度可变的Unicode数据，其最大长度范围是230-1个字节，我存储大小是输入字符个数的两倍。Image数据类型：表示可变长度的二进制数据，范围在0~231-1字节之间。用来存储照片、目录图片或者图画等，其存储量也是231-1个字节。Image类型的数据是由系统根据数据的长度来自动分配空间，存储该字段的数据一般不能使用insert语句直接输入。二进制常量以0x(一个零和小写字母x)开始，后面跟位模式的十六进制表示。0x2A表示的是十六进制值2A，它等于十进制的数值42或者单字节位模式的00101010。9.其他数据类型除了以上数据类型以外，系统数据类型还有如下几种：Cursor：游标数据类型，该类型类似于数据表，它保存的数据中包含行和列值，但是没有索引，游标通常用来建立一个数据的数据集，用于创建游标变量或者定义存储过程的输出参数，每次处理一行数据。它是唯一一种不能赋值给表的列字段的基本数据类型。table数据类型：table数据类型可以用于存储对表或视图处理后的结果集，能够用来保存函数结果，并将其作为局部变量数据类型，可以暂时存储应用程序的结果，以便在以后用到，从而使函数或过程返回查询结果更加方便、快捷。rowversion：每个数据都有一个计数器，如果对数据库中包含rowversion列的表执行插入或者更新操作，那么该计数器数值就会增加。Rowversion是公开数据库中自动生成的唯一二进制数字的数据类型。rowversion通常用作给表行加版本戳的机制。在数据库中存储大小为8个字节。rowversion数据类型只是递增的数字，并不保留日期或时间。TIMESTAMP：是一个特殊的用于表示先后顺序的时间戳数据类型。该数据类型可以为表中数据行加上一个版本戳。timestamp的数据类型为rowversion数据类型的同义词，提供数据库范围内的唯一值，能够反映数据修改的唯一顺序，是一个单调上升的计数器，此列的值是被自动更新的。实现timestamp数据类型最初是为了支持SQLServer恢复算法。每一个数据库都有一个时间戳计数器，当对该数据库中包含timestamp列的表执行插入或更新操作时，计数器值就会增加。这样，可以轻易地确定表中的某个数据行的值是否在上次读取后发生了更新：如果发生了更新，则该时戳计数器的值也发生了变化。可以使用@@DBTS函数返回数据库的时戳值。UNIQUEIDENTIFIER：UNIQUEIDENTIFIER是一个具有16字节的全球唯一性标志符（GloballyUniqueIdentifier，GUID），能够用来确保对象的唯一性。在定义列或变量时可以使用该数据类型，在合并复制和事务复制中确保表中数据行的唯一性。sql_variant：能够用于存储除了文本、图形数据和timestamp数据以外的其它任何合法的SqlServer数据，sql_variant数据类型可以方便SqlServer的开发工作。该数据类型可以用在列、变量、用户定义的函数等返回值中XML：XML是用于存储XML数据的数据类型。可以像使用Int数据类型一样使用XML数据类型。能在列中或者xml类型的变量中存储xml实例。需要注意的是，在XML数据类型中存储的数据实例的最大值不能超过2GB。3.2MySQL数据库类型MySQL中的数据类型可分为如下几类。数值类型(Numeric)：这包括整数、固定精度的十进制类型、近似精度的浮点类型，以及位(bit)类型。日期和时间类型(Temporal)：这包括年、日期、时间、日期和时间以及时间戳。字符串类型(Strings)：包括二进制对象和带有字符集的字符串。JSON类型(JSON)：用于存储JSON文档。空间数据类型(Spatial)：用于存储描述坐标系统中的一个或多个点的值。混合数据类型(Hybrid)：MySQL中有两种数据类型，既可作为整数类型，也可作为字符串类型。3.2.1数值类型数值类型是MySQL支持的最简单的数据类型。可在整数、固定精度的十进制以及近似浮点型之间进行选择。表3-1总结了MySQL中的数值类型。包括它们的存储需求(以字节为单位)以及支持的值的范围。对于整数而言，可以选择值是有符号还是无符号的，这会影响支持的值的范围。对于支持的值，其起始值和结束值都包含在所允许的值范围内。MySQL中的数值类型表数据类型存储字节范围tinyint1有符号：-128～127无符号：0～255smallint2有符号：-32768～32767无符号：0～65535mediumint3有符号：-8388608～8388607无符号：0～16777215int4有符号：-2147483648～2147483647无符号：0～4294967295bigint8有符号：-263～263-1无符号：0～264-1decimal(M,N)1～29取决于M和Nfloat4可变double8可变bit(M)1～83.2.2日期和时间类型日期和时间类型的数据定义了一个时间点。精度范围从1年到1微秒。除了年数据类型外，值均以字符串形式输入，不过内部则使用优化的数据格式，并且这些值将根据其所表示的时间点进行正确排序。表3-2列出MySQL支持的日期和时间数据类型，也包括每种类型使用的字节存储量，以及支持的值范围。表3-2

日期和时间数据类型数据类型存储的字节数范围year11901～2155date3～6'1000-01-01'到'9999-12-31'datetime5～8'1000-01-0100:00:00.000000'到'9999-12-3123:59:59.999999'timestamp4～7'1970-01-0100:00:00.000000'到'2038-01-1903:14:07.999999'time3～6'38:59:59.000000'到'838:59:59.000000'3.2.3字符串与二进制类型字符串和二进制类型是用于存储任意数据的非常灵活的类型。二进制值和字符串之间的区别在于，字符串有与之关联的字符集，因此MySQL知道该如何解释数据。另一方面，二进制则存储原始数据，这意味着可将其用于任何类型的数据，包括图形以及自定义数据格式的数据。数据类型存储字节最大长度char(M)M*字符宽度255个字符varchar(M)L+1或L+2对于utf8mb4而言，为16383个字符；对于latin1而言，为65532个字符tinytextL+1255字节textL+265535字节mediumtextL+316777216字节longtextL+44294967296字节binary(M)M255字节varbinary(M)L+1或者L+265535字节tinyblobL+1255字节blobL+265536字节mediumblobL+316777216字节longblobL+44294967296字节3.2.4JSON数据类型JavaScript对象表示法(JSON)格式是一种比关系型表更灵活的流行数据存储格式，也是MySQL8为文档存储选择的格式。MySQL5.7开始引入对JSON数据类型的支持。在MySQL8中，JSON文档的一项与性能相关的功能是支持部分更新操作。这样就可对JSON文档进行更新，这不仅减少了更新操作时完成的工作量，还可只将部分更新写入二进制日志。当然，对于这样的原地(in-place)更新操作，还是有一些要求的。具体如下：● 只支持JSON_SET()、JSON_REPLACE()和JSON_REMOVE()函数。● 只支持列内更新。也就是说，不支持将列的值设置为在另一列上运行上述3个函数所得到的返回值。● 必须是对现有值的替换操作。如果是添加新的对象