第4章--存储引擎、数据类型和字符集.pptx

4,存储引擎、数据类型和字符集,第,章,4.1,存储,引擎,4.1,存储引擎,存储引擎是,MySQL,体系结构的重要组成部分，作用是指定表的,类型,，规定表如何存储和索引数据、是否支持事务等。,4.1.1 MySQL,存储引擎概述,MySQL,支持的存储引擎,1,MySQL,支持的存储引擎包括,InnoDB,，,MRG_MYISAM,，,MEMORY,，,BLACKHOLE,，,MyISAM,，,CSV,，,ARCHIVE,，,PERFORMANCE_SCHEMA,，,FEDERATED,，可以在登录,MySQL,后执行以下命令查看：,mysql SHOW ENGINES G,*1.row*,Engine:InnoDB,Support:DEFAULT,Comment:Supports transactions,row-level locking,and foreign keys,Transactions:YES,XA:YES,Savepoints:YES,*2.row*,Engine:MRG_MYISAM,省略部分,提示,上述命令以“,G”,结尾，其作用是将查询结果按列显示。,4.1,存储引擎,4.1.1 MySQL,存储引擎概述,查看,MySQL,默认存储,引擎,2,MySQL 5.5,之前版本的默认存储引擎为,MyISAM,，,MySQL 5.5,及之后版本的默认存储引擎为,InnoDB,，可以执行以下命令查看默认存储引擎：,mysql SHOW VARIABLES LIKE default_storage_engine;,+-+-+,|Variable_name|Value|,+-+-+,|default_storage_engine|InnoDB|,+-+-+,1 row in set,1 warning(0.00 sec),4.1,存储引擎,4.1.1 MySQL,存储引擎概述,修改,MySQL,默认存储,引擎,3,创建新表时如果不指定存储引擎，则系统使用,默认存储引擎,。如果用户想改变默认存储引擎，可以通过修改配置文件来实现。,打开,my.ini,配置文件，在“,mysqld”,组下面添加以下内容，然后保存文件即完成修改。,#,设置,MySQL,服务器的默认存储引擎,default_storage_engine=MyISAM,修改完毕后，需要重启,MySQL,服务，修改才能生效。可以重启并重新登录,MySQL,，并采用前面的方法查询修改结果。,4.1,存储引擎,4.1.2,常用的存储引擎,InnoDB,1,InnoDB,为,MySQL,提供具有提交、回滚、崩溃恢复能力和多版本并发控制的事务安全型表，能够高效地处理大量数据。适用于,需要事务支持、高并发、数据更新频繁、对数据的一致性和完整性要求较高的计费系统或者财务系统,等。,MyISAM,2,MyISAM,存储引擎基于,ISAM,，并对其进行扩展。具有较高的插入和查询速度，但不支持事务和外键。,每个使用,MyISAM,存储引擎创建的数据表都会生成,3,个文件，文件名和数据表名称相同，但扩展名不同，各个文件及其作用分别如下：,.frm,：,存储表定义；,.MYD,：,存储数据；,.MYI,：,存储索引。,MEMORY,3,MEMORY,存储引擎主要用于内容变化不频繁的表。另外，由于,MEMORY,存储引擎是将数据存储到内存中，所以太大的表无法使用此存储引擎。,对于,数据更新不频繁、存活周期不长和需要对统计结果进行分析的数据表,可以使用,MEMORY,存储引擎。,对事务完整性没有要求、并发相对较低、数据更新不频繁、以读为主和对数据一致性要求不高的数据表,，推荐使用,MyISAM,存储引擎。,4.2,数据类型,4.2,数据类型,数据表由多个,字段,构成，每个字段可以指定不同的,数据类型,，数据类型用于规定数据的存储格式、约束和有效范围。在定义数据表字段时，应根据实际情况选择合适的数据类型。,MySQL,提供的数据类型主要包括,数值类型,、,日期和时间类型,、,字符串类型,以及,JSON,类型,。,【,实例,4-1】,参照表,4-1,的信息，创建数据表,tb_demo,。,表,4-1 tb_demo,表中各字段及其数据类型,字,段,数据类型,注,释,id,INT(11),编,号,name,VARCHAR(30),姓,名,age,TINYINT(4),年,龄,score,FLOAT(4,1),分,数,sex,ENUM(w,m,),性,别,hobby,SET(football,basketball,volleyball),爱,好,photo,VARBINARY(255),相,片,spend,DECIMAL(5,1),生活费,address,JSON,住,址,Intro,TEXT,简,介,4.2,数据类型,4.2.1,数值类型,数值类型用于存储数字型数据，包括,整数,类型（,TINYINT,，,SMALLINT,，,MEDIUMINT,，,INT,，,BIGINT,）、,浮点数,类型（,FLOAT,，,DOUBLE,）和,定点数,类型（,DECIMAL,）,。,其中,，整数类型的字段用于存储整数，浮点数和定点数类型的字段用于存储小数。,不同的数值类型提供不同的存储范围，并且每种类型可以设置两种格式，分别为,有符号,和,无符号,，有符号表示可以存储负数，无符号表示只能存储,0,或正数。,4.2,数据类型,4.2.1,数值类型,表,4-2,整数类型,数据类型,所占字节,存储范围（有符号）,存储范围（无符号）,TINYINT,1,字节,-128,127,0,255,SMALLINT,2,字节,-32768,32767,0,65535,MEDIUMINT,3,字节,-8388608,8388607,0,16777215,INT,4,字节,-2147483648,2147483647,0,4294967295,BIGINT,8,字节,-9223372036854775808,9223372036854775807,0,18446744073709551615,表,4-3 TINYINT,类型,二进制,正整数最大值,0,1,1,1,1,1,1,1,表,4-4 TINYINT,类型,二进制,负整数最小值,1,0,0,0,0,0,0,0,整数类型,1,4.2,数据类型,4.2.1,数值类型,以,tb_demo,表为例，其中有一个,age,字段，它的,数据类型为,TINYINT,，此处向,age,字段插入数据，当插入的数值在,TINYINT,类型的存储范围内时，可以正常插入，如果插入的数据长度超出所选数据类型的存储范围，或者为其他类型的数据时，系统就会提示“,Out of range”,错误信息，如下所示。,mysql INSERT INTO tb_demo(age)VALUES(123);,Query OK,1 row affected(0.27 sec),mysql INSERT INTO tb_demo(age)VALUES(1234);,ERROR 1264(22003):Out of range value for column age at row 1,mysql SELECT age FROM tb_demo;,+-+,|age|,+-+,|123|,+-+,1 rows in set(0.02 sec),4.2,数据类型,4.2.1,数值类型,mysql HELP CONTENTS;,You asked for help about help category:Contents,For more information,type help,where is one of the following,categories:,Account Management,Administration,Compound Statements,Data Definition,Data,Types,#,部分内容省略,【,实,例4-2】,查看系统,帮助。,首先,使用“,HELP CONTENTS”,查看,MySQL,帮助文档支持的目录列表。然后根据需要查看的条目输入相关命令进行查看，例如，执行“,HELP DATA TYPES”,可以查看,MySQL,支持的所有数据类型。最后以“,HELP”,开头，加上任意一个数据类型，可以查看其存储范围，如以下代码所示：,mysql HELP DATA TYPES;,You asked for help about help category:Data Types,For more information,type help,where is one of the following,topics:,AUTO_INCREMENT,#,部分内容省略,mysql HELP TINYINT;,Name:TINYINT,Description:,TINYINT(M)UNSIGNED ZEROFILL,A very small integer.The signed range is-128 to 127.The unsigned,range is 0 to 255.,4.2,数据类型,4.2.1,数值类型,浮点数类型和定点数类型,2,浮点数类型分为两种，,单精度（,FLOAT,）,和,双精度（,DOUBLE,）,，而定点数类型只有,DECIMAL,一种。,浮点数类型和定点数类型都可以使用类型名称后面加,“,(M,D)”,的形式来定义，其中,M,称为,精度,，表示数值的,位数,（整数位,+,小数位）；,D,称为,标度,，表示,小数点后的位数,。例如，,tb_demo,表中,score,字段的数据类型为,FLOAT(4,1),，表示可正常插入的数据长度最大是,4,位，小数点后保留,1,位，例如，,123.4,这样的小数符合要求。小数类型的数据占用字节和存储范围如表,4-5,所示。,表,4-5,小数类型,数据类型,占用字节,负数存储范围,非负数存储范围,FLOAT,4,字节,-3.402823466E+38,-1.175494351E-38,0,和,1.175494351E-38,3.402823466E+38,DOUBLE,8,字节,-1.7976931348623157E+308,-2.2250738585072014E-308,0,和,2.2250738585072014E-308,1.7976931348623157E+308,DECIMAL,M+2,字节,与,DOUBLE,相同,与,DOUBLE,相同,4.2,数据类型,4.2.1,数值类型,【,实例,4-3】,浮点数类型和定点数类型的,区别,。,mysql INSERT INTO tb_demo(score,spend)VALUES(90.16,2000.16);,Query OK,1 row affected,1 warning(0.10 sec),mysql SHOW warnings;,+-+-+-+,|Level|Code|Message,|,+-+-+-+,|Note|1265|Data truncated for column spend at row 1,|,+-+-+-+,1 row in set(0.00 sec),mysql select score,spend from tb_demo;,+-+-,-,-+,|score|spend,|,+-+-,-,-+,|90.2|2000.2|,+-+-,-,+,1 row in set(0.01 sec),4.2,数据类型,4.2.2,日期和时间类型,MySQL 5.7,版本中用于存储日期和时间的数据类型有,YEAR,，,DATE,，,TIME,，,DATETIME,和,TIMESTAMP,，每一种类型都有固定的存储范围。,表,4-6,日期和时间类型,数据类型,占用字节,存储格式,存储范围,YEAR,1,字节,YYYY,1901,2155,DATE,3,字节,YYYY-MM-DD,1000-01-01,9999-12-31,TIME,3,字节,+,小数秒存储,HH:MM:SS,-838:59:59,838:59:59,DATETIME,5,字节,+,小数秒存储,YYYY-MM-DD HH:MM:SS,1000-01-01 00:00:00,9999-12-31 23:59:59,TIMESTAMP,4,字节,+,小数秒存储,YYYY-MM-DD HH:MM:SS,1970-01-01 00:00:01 UTC,2038-01-19 03:14:07 UTC,提示,向日期和时间类型的字段插入数据时，最好使用引号将值包含起来。,4.2,数据类型,4.2.2,日期和时间类型,YEAR,1,YEAR,类型用于存储只有年份的值，存储格式为,YYYY,，它可以被声明为,YEAR,或,YEAR(4),，具有四个字符的显示宽度。在向数据库中插入,YEAR,类型的数据时，可以输入不严格的值，如,17,，系统会自动转换为,2017,，转换规则有以下几点：,数值,1,69,和,70,99,自动转换为,2001,2069,和,1970,1999,。,字符串,1,69,和,70,99,自动转换为,2001,2069,和,1970,1999,。,字符串,0,或,00,自动转换为,2000,。,DATE,2,DATE,类型用于存储具有日期部分但没有时间部分的值，存储格式为,YYYY-MM-DD,。,格式为,YYYY-DD-MM,和,YYYYDDMM,的字符串会转换为标准格式。,格式为,YYYYDDMM,和,YYDDMM,的数值会转换为标准格式。,格式为,YY-DD-MM,和,YYDDMM,的字符串会转换为标准格式。,提示,2017/10/10,，,2017.10.10,和,2017#10#10,都会被系统转换为,2017-10-10,格式,。,4.2,数据类型,4.2.2,日期和时间类型,TIME,3,TIME,类型用于存储具有时间部分但没有日期部分的值，存储格式为,HH:MM:SS,。,TIME,类型的存储范围是,-838:59:59,到,838:59:59,，其不仅可用于表示一天中的时间（小于,24,小时），而且可用于表示两个事件之间的时间间隔（可能远远大于,24,小时，或者为负）。,TIME,类型支持,D HH:MM:SS,格式，,D,表示日，可以在,0,34,之间取值，会被转换为小时，即,D*24+HH,。,TIME,类型支持,没有分隔符的字符串或数值,的格式。例如，,113050,会被系统转换为,11:30:50,。,当数据为,TIME,类型的缩写值时，如果,没有分隔符,，,MySQL,会认为值的,最右边两位表示秒,，例如，,1010,会被转换成,00:10:10,；如果,有分隔符,，,MySQL,会认为值的,最左边两位表示小时,，例如，,10:10,会被转换为,10:10:00,。,4.2,数据类型,4.2.2,日期和时间类型,DATETIME,4,DATETIME,类型用于存储同时具有日期和时间的值，存储格式为,YYYY-MM-DD,HH:MM:SS,。,格式为,YYYYMMDDHHMMSS,和,YYMMDDHHMMSS,的数值会转换为标准格式。,格式为,YYYYMMDDHHMMSS,和,YYMMDDHHMMSS,的字符串会转换为标准格式。,TIMESTAMP,5,TIMESTAMP,类型与,DATETIME,类型相似，用于存储同时具有日期和时间的值，存储格式为,YYYY-MM-DD HH:MM:SS,，但是,TIMESTAMP,类型的存储范围较小。,TIMESTAMP,类型与,DATETIME,类型最大的区别是：,DATETIME,类型存储的数据是什么，显示的就是什么；而,TIMESTAMP,类型的时间可以根据时区进行转换，在查询时，当前时区不同，显示的时间值也不同。,4.2,数据类型,4.2.3,字符串类型,MySQL,支持的字符串数据类型包括,CHAR,，,VARCHAR,，,BINARY,，,VARBINARY,，,BLOB,，,TEXT,，,ENUM,和,SET,等。,表,4-7,字符串类型,类型名称,占用字节,存储范围,CHAR(M),M*w,字节,0=M=255,VARCHAR(M),L+1,字节,0=M=65535,BINARY(N),N,字节,0=N=255,VARBINARY(N),L+1,字节,0=N=65535,BLOB,L+2,字节,L2,16,TEXT,L+2,字节,L INSERT INTO tb_demo(sex)VALUES(w),(m),(null);,Query OK,3 rows affected(0.10 sec),Records:3 Duplicates:0 Warnings:0,mysql SELECT sex,sex+0 FROM tb_demo;,+-+-+,|,sex,|sex+0|,+-+-+,|NULL|NULL|,|NULL|NULL|,|NULL|NULL|,|w,|,1|,|m,|,2,|,|NULL|NULL|,+-+-+,6 rows in set(0.01 sec),mysql INSERT INTO tb_demo(sex)VALUES(w),(1),(2);,Query OK,3 rows affected(0.04 sec),Records:3 Duplicates:0 Warnings:,0,mysql select sex from tb_demo;,+-+,|,sex,|,+-+,|NULL|,|NULL|,|NULL|,|w,|,|m,|,|NULL|,|w,|,|w,|,|m,|,+-+,9 rows in set(0.00 sec),4.2,数据类型,4.2.3,字符串类型,SET,5,SET,类型用于存储“多选项”形式的数据，如,tb_demo,表中的,hobby,字段。选项值列表最多可以包含,64,个成员，每个插入值都必须从创建表时指定的选项值列表中选择。其语法形式如下：,col_name SET(value1,value2,valuen),定义的选项值不能重复。,选项值必须是字符串文字。,插入值中如果有重复值，则只取其中一个。,使用,SET,类型时应注意以下几点：,插入值的顺序会按照选项值的顺序自动排列。,4.2,数据类型,4.2.3,字符串类型,【,实例,4-5】,数据类型,SET,的使用,方法。,mysql INSERT INTO tb_demo(hobby),VALUES(basketball),(volleyball,football),(football,football,basketball);,Query OK,3 rows affected(0.05 sec),Records:3 Duplicates:0 Warnings:0,mysql select hobby from tb_demo;,+-,-,-+,|hobby,|,+-,-,+,|basketball,|,|football,volleyball,|,|football,basketball,|,+-,-,+,3 rows in set(0.00 sec),4.2,数据类型,4.2.4 JSON,类型,从,MySQL 5.7.8,版本开始，,MySQL,新增了一种数据类型：,JSON,，用于存储,JSON,数据。,JSON,类型字段的插入值可分为,数组,和,对象,。,JSON,数组是一个由逗号分隔并包含在括号“,”,中的值列表，例如：,abc,10,null,true,false,JSON,对象是一组键值，由逗号分隔，包含在括号“,”,中，例如：,k1:value,k2:10,而且,JSON,数组和,JSON,对象允许嵌套，例如：,99,id:HK500,cost:75.99,hot,cold,k1:value,k2:10,20,4.2,数据类型,4.2.4 JSON,类型,【,实例,4-6】,数据类型,JSON,的使用,方法,。,mysql INSERT INTO tb_demo(address)VALUES(province:,河北省,city:,石家庄市,);,Query OK,1 row affected(0.04 sec),mysql INSERT INTO tb_demo(address)VALUES(1,2,);,ERROR 3140(22032):Invalid JSON text:Invalid value.at position 5 in value for column tb_demo.address.,在,tb_demo,表中，,address,字段的数据类型为,JSON,，此处向,address,字段分别插入数据,province:,河北省,city:,石家庄市,和,1,2,。如果插入值是有效的,JSON,值，则会将值插入到列中，否则会插入失败，如下所示：,4.3,字符集,字符集是指,一套文字符号及其编码和排序规则的集合,。实际应用中，用户通常会向,MySQL,系统中插入大量文字，但计算机只能存储二进制码，这时就需要,通过字符集将文字转换为二进制码再存入数据库,；而不同国家和地区的文字不同，有些字符集只支持一种文字，有些字符集支持多种文字，所以在创建数据库时，要根据系统需求选择合适的字符集。,MySQL,支持多种字符集，其中常用的字符集包括,latin1,，,GBK,和,UTF-8,等。在,MySQL,中，可以为服务器、数据库、数据表和字段设置不同的字符集，这是,MySQL,比其他数据库管理系统更灵活的地方。,执行以下,SQL,语句，可以查看,MySQL,中所有可用的字符集。,SHOW CHARACTER SET;,MySQL,字符集包括,字符集,和,排序规则,两个概念，其中字符集,定义,MySQL,存储字符串的方式,，排序规则,定义字符串的排序方式,。每个字符集至少对应一个排序规则，可以执行以下语句，查看字符集所对应的排序规则。,SHOW COLLATION LIKE,字符集名称,%;,【,实例,4-7】,查看,GBK,字符集对应的排序规则。,mysql SHOW COLLATION LIKE GBK%;,+-+-+-+-+-+-+,|Collation|Charset|Id|Default|Compiled|Sortlen|,+-+-+-+-+-+-+,|gbk_chinese_ci|gbk|28|Yes|Yes|1|,|gbk_bin|gbk|87|Yes|1|,+-+-+-+-+-+-+,2 rows in set(0.00 sec),输入,SQL,语句并执行，查看,GBK,字符集对应的排序规则，效果如下：,知识库,排序规则命名一般以它所属字符集开始，有时包括一个语言名，最后以“,_ci”,（大小写不敏感）、“,_cs”,（大小写敏感）或者“,_bin”,（二元，基于字符编码的值并且与,language,无关）结束。,在,MySQL,中，为数据库设置字符集时应考虑以下几点：,如果数据库要处理不同地区的文字，就应选择,Unicode,字符集,，在,MySQL,中，就是,utf8,。,如果数据库只需要支持一般中文，并且数据量很大，性能要求很高，可以使用,GBK,字符集,。,如果数据库需要做大量运算，就应该选择定长字符集，如,latin1,和,GBK,等。,用户应该在向数据库插入数据之前确定字符集的种类。,MySQL,默认的字符集为,latin1,，字符集和排序规则分为四个级别：,服务器级、数据库级、数据表级和字段级,。,服务器级,1,执行,SQL,语句，查看当前服务器使用的字符集，结果如下所示：,mysql SHOW VARIABLES LIKE character_set_server;,+-+-+,|Variable_name|Value|,+-+-+,|character_set_server|utf8|,+-+-+,1 row in set,1 warning(0.00 sec),打开,my.ini,配置文件，在“,mysqld”,组中添加以下内容，然后保存文件即完成修改。,#,设置默认字符集和排序规则,character-set-server=utf8,collation-server=utf8_general_ci,数据库级,2,数据库默认的字符集与服务器设置的字符集相同，数据库的字符集和排序规则可以在,数据库创建时指定,，也可以在,数据库创建之后修改,。,mysql SHOW VARIABLES LIKE character_set_database;,+-+-+,|Variable_name|Value|,+-+-+,|character_set_database|utf8|,+-+-+,1 row in set,1 warning(0.00 sec),使用关键字,DEFAULT CHARSET,可以在创建数据库时选择字符集，语法形式如下：,CREATE DATABASE database_name DEFAULT CHARSET set_name;,使用关键字,ALTER,可以修改数据库的字符集，语法形式如下。,ALTER DATABASE database_name DEFAULT CHARSET utf8;,感谢观看,