《MySQL数据库应用案例教程》教学课件第4章存储引擎、数据类型和字符集

1、4存储引擎、数据类型和字符集第章第1页，共34页。4.1存储引擎第2页，共34页。4.1 存储引擎存储引擎是MySQL体系结构的重要组成部分，作用是指定表的类型，规定表如何存储和索引数据、是否支持事务等。4.1.1 MySQL存储引擎概述MySQL支持的存储引擎1MySQL支持的存储引擎包括InnoDB，MRG_MYISAM，MEMORY，BLACKHOLE，MyISAM，CSV，ARCHIVE，PERFORMANCE_SCHEMA，FEDERATED，可以在登录MySQL后执行以下命令查看：mysql SHOW ENGINES G* 1. row * Engine: InnoDB Suppo

2、rt: DEFAULT Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES* 2. row * Engine: MRG_MYISAM 省略部分 提示上述命令以“G”结尾，其作用是将查询结果按列显示。第3页，共34页。4.1 存储引擎4.1.1 MySQL存储引擎概述查看MySQL默认存储引擎2MySQL 5.5之前版本的默认存储引擎为MyISAM，MySQL 5.5及之后版本的默认存储引擎为InnoDB，可以执行以下命令查看默认

3、存储引擎：mysql SHOW VARIABLES LIKE default_storage_engine;+-+-+| Variable_name | Value |+-+-+| default_storage_engine | InnoDB |+-+-+1 row in set, 1 warning (0.00 sec)第4页，共34页。4.1 存储引擎4.1.1 MySQL存储引擎概述修改MySQL默认存储引擎3创建新表时如果不指定存储引擎，则系统使用默认存储引擎。如果用户想改变默认存储引擎，可以通过修改配置文件来实现。打开my.ini配置文件，在“mysqld”组下面添加以下内容，然后

4、保存文件即完成修改。#设置MySQL服务器的默认存储引擎default_storage_engine = MyISAM修改完毕后，需要重启MySQL服务，修改才能生效。可以重启并重新登录MySQL，并采用前面的方法查询修改结果。第5页，共34页。4.1 存储引擎4.1.2 常用的存储引擎InnoDB1InnoDB为MySQL提供具有提交、回滚、崩溃恢复能力和多版本并发控制的事务安全型表，能够高效地处理大量数据。适用于需要事务支持、高并发、数据更新频繁、对数据的一致性和完整性要求较高的计费系统或者财务系统等。MyISAM2MyISAM存储引擎基于ISAM，并对其进行扩展。具有较高的插入和查询速度

5、，但不支持事务和外键。每个使用MyISAM存储引擎创建的数据表都会生成3个文件，文件名和数据表名称相同，但扩展名不同，各个文件及其作用分别如下：.frm：存储表定义；.MYD：存储数据；.MYI：存储索引。MEMORY3MEMORY存储引擎主要用于内容变化不频繁的表。另外，由于MEMORY存储引擎是将数据存储到内存中，所以太大的表无法使用此存储引擎。对于数据更新不频繁、存活周期不长和需要对统计结果进行分析的数据表可以使用MEMORY存储引擎。对事务完整性没有要求、并发相对较低、数据更新不频繁、以读为主和对数据一致性要求不高的数据表，推荐使用MyISAM存储引擎。第6页，共34页。4.2数据类型

6、第7页，共34页。4.2 数据类型数据表由多个字段构成，每个字段可以指定不同的数据类型，数据类型用于规定数据的存储格式、约束和有效范围。在定义数据表字段时，应根据实际情况选择合适的数据类型。MySQL提供的数据类型主要包括数值类型、日期和时间类型、字符串类型以及JSON类型。【实例4-1】参照表4-1的信息，创建数据表tb_demo。表4-1 tb_demo表中各字段及其数据类型字 段数据类型注 释idINT(11)编 号nameVARCHAR(30)姓 名ageTINYINT(4)年 龄scoreFLOAT(4,1)分 数sexENUM(w,m,)性 别hobbySET(football,b

7、asketball,volleyball)爱 好photoVARBINARY(255)相 片spendDECIMAL(5,1)生活费addressJSON住 址IntroTEXT简 介第8页，共34页。4.2 数据类型4.2.1 数值类型数值类型用于存储数字型数据，包括整数类型（TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT）、浮点数类型（FLOAT，DOUBLE）和定点数类型（DECIMAL）。其中，整数类型的字段用于存储整数，浮点数和定点数类型的字段用于存储小数。不同的数值类型提供不同的存储范围，并且每种类型可以设置两种格式，分别为有符号和无符号，有符号表示可以

8、存储负数，无符号表示只能存储0或正数。第9页，共34页。4.2 数据类型4.2.1 数值类型表4-2 整数类型数据类型所占字节存储范围（有符号）存储范围（无符号）TINYINT1字节-1281270255SMALLINT2字节-3276832767065535MEDIUMINT3字节-83886088388607016777215INT4字节-2147483648214748364704294967295BIGINT8字节-92233720368547758089223372036854775807018446744073709551615表4-3 TINYINT类型二进制正整数最大值0111

9、1111表4-4 TINYINT类型二进制负整数最小值10000000整数类型1第10页，共34页。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 I

10、NSERT INTO tb_demo(age) VALUES(1234);ERROR 1264 (22003): Out of range value for column age at row 1mysql SELECT age FROM tb_demo;+-+| age |+-+| 123 |+-+1 rows in set (0.02 sec)第11页，共34页。4.2 数据类型4.2.1 数值类型mysql HELP CONTENTS;You asked for help about help category: ContentsFor more information, type h

11、elp , where is one of the followingcategories: Account Management Administration Compound Statements Data Definition Data Types #部分内容省略【实例4-2】查看系统帮助。 首先使用“HELP CONTENTS”查看MySQL帮助文档支持的目录列表。然后根据需要查看的条目输入相关命令进行查看，例如，执行“HELP DATA TYPES”可以查看MySQL支持的所有数据类型。最后以“HELP”开头，加上任意一个数据类型，可以查看其存储范围，如以下代码所示：mysql HE

12、LP DATA TYPES;You asked for help about help category: Data TypesFor more information, type help , where is one of the followingtopics: AUTO_INCREMENT#部分内容省略mysql HELP TINYINT;Name: TINYINTDescription:TINYINT(M) UNSIGNED ZEROFILLA very small integer. The signed range is -128 to 127. The unsignedrange

13、 is 0 to 255.第12页，共34页。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 小数类型数据类型

14、占用字节负数存储范围非负数存储范围FLOAT4字节-3.402823466E+38-1.175494351E-380和1.175494351E-383.402823466E+38DOUBLE8字节-1.7976931348623157E+308-2.2250738585072014E-3080和2.2250738585072014E-3081.7976931348623157E+308DECIMALM+2字节与DOUBLE相同与DOUBLE相同第13页，共34页。4.2 数据类型4.2.1 数值类型【实例4-3】浮点数类型和定点数类型的区别。mysql INSERT INTO tb_demo(

15、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

16、| 2000.2|+-+-+1 row in set (0.01 sec)第14页，共34页。4.2 数据类型4.2.2 日期和时间类型MySQL 5.7版本中用于存储日期和时间的数据类型有YEAR，DATE，TIME，DATETIME和TIMESTAMP，每一种类型都有固定的存储范围。表4-6 日期和时间类型数据类型占用字节存储格式存储范围YEAR1字节YYYY19012155DATE3字节YYYY-MM-DD1000-01-019999-12-31TIME3字节+小数秒存储HH:MM:SS-838:59:59838:59:59DATETIME5字节+小数秒存储YYYY-MM-DD HH:M

17、M:SS1000-01-01 00:00:009999-12-31 23:59:59TIMESTAMP4字节+小数秒存储YYYY-MM-DD HH:MM:SS1970-01-01 00:00:01 UTC2038-01-19 03:14:07 UTC 提示向日期和时间类型的字段插入数据时，最好使用引号将值包含起来。第15页，共34页。4.2 数据类型4.2.2 日期和时间类型YEAR1YEAR类型用于存储只有年份的值，存储格式为YYYY，它可以被声明为YEAR或YEAR(4)，具有四个字符的显示宽度。在向数据库中插入YEAR类型的数据时，可以输入不严格的值，如17，系统会自动转换为2017，转

18、换规则有以下几点：数值169和7099自动转换为20012069和19701999。字符串169和7099自动转换为20012069和19701999。字符串0或00自动转换为2000。DATE2DATE类型用于存储具有日期部分但没有时间部分的值，存储格式为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格式。第16页，共34

19、页。4.2 数据类型4.2.2 日期和时间类型TIME3TIME类型用于存储具有时间部分但没有日期部分的值，存储格式为HH:MM:SS。TIME类型的存储范围是-838:59:59到838:59:59，其不仅可用于表示一天中的时间（小于24小时），而且可用于表示两个事件之间的时间间隔（可能远远大于24小时，或者为负）。TIME类型支持D HH:MM:SS格式，D表示日，可以在034之间取值，会被转换为小时，即D*24+HH。TIME类型支持没有分隔符的字符串或数值的格式。例如，113050会被系统转换为11:30:50。当数据为TIME类型的缩写值时，如果没有分隔符，MySQL会认为值的最右边

20、两位表示秒，例如，1010会被转换成00:10:10；如果有分隔符，MySQL会认为值的最左边两位表示小时，例如，10:10会被转换为10:10:00。第17页，共34页。4.2 数据类型4.2.2 日期和时间类型DATETIME4DATETIME类型用于存储同时具有日期和时间的值，存储格式为YYYY-MM-DD HH:MM:SS。格式为YYYYMMDDHHMMSS和YYMMDDHHMMSS的数值会转换为标准格式。格式为YYYYMMDDHHMMSS和YYMMDDHHMMSS的字符串会转换为标准格式。TIMESTAMP5TIMESTAMP类型与DATETIME类型相似，用于存储同时具有日期和时间

21、的值，存储格式为YYYY-MM-DD HH:MM:SS，但是TIMESTAMP类型的存储范围较小。TIMESTAMP类型与DATETIME类型最大的区别是：DATETIME类型存储的数据是什么，显示的就是什么；而TIMESTAMP类型的时间可以根据时区进行转换，在查询时，当前时区不同，显示的时间值也不同。第18页，共34页。4.2 数据类型4.2.3 字符串类型MySQL支持的字符串数据类型包括CHAR，VARCHAR，BINARY，VARBINARY，BLOB，TEXT，ENUM和SET等。表4-7 字符串类型类型名称占用字节存储范围CHAR(M)M*w字节0=M=255VARCHAR(M)

22、L+1字节0=M=65535BINARY(N)N字节0=N=255VARBINARY(N)L+1字节0=N=65535BLOBL+2字节L216TEXTL+2字节L INSERT INTO tb_demo(sex) VALUES(w),(m),(null);Query OK, 3 rows affected (0.10 sec)Records: 3 Duplicates: 0 Warnings: 0mysql SELECT sex,sex+0 FROM tb_demo;+-+-+| sex | sex+0 |+-+-+| NULL| NULL | NULL| NULL | NULL| NULL

23、 | 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: 0mysql select sex from tb_demo;+-+| sex |+-+| NULL | NULL | NULL | w | m | NULL | w | w | m |+-+9 rows in set (0.00 sec)

24、第23页，共34页。4.2 数据类型4.2.3 字符串类型SET5SET类型用于存储“多选项”形式的数据，如tb_demo表中的hobby字段。选项值列表最多可以包含64个成员，每个插入值都必须从创建表时指定的选项值列表中选择。其语法形式如下：col_name SET(value1,value2,valuen)定义的选项值不能重复。选项值必须是字符串文字。插入值中如果有重复值，则只取其中一个。使用SET类型时应注意以下几点：插入值的顺序会按照选项值的顺序自动排列。第24页，共34页。4.2 数据类型4.2.3 字符串类型【实例4-5】数据类型SET的使用方法。mysql INSERT INTO

25、 tb_demo(hobby) VALUES(basketball),(volleyball,football),(football,football,basketball);Query OK, 3 rows affected (0.05 sec)Records: 3 Duplicates: 0 Warnings: 0mysql select hobby from tb_demo;+-+| hobby |+-+| basketball| football,volleyball| football,basketball|+-+3 rows in set (0.00 sec)第25页，共34页。4

26、.2 数据类型4.2.4 JSON类型从MySQL 5.7.8版本开始，MySQL新增了一种数据类型：JSON，用于存储JSON数据。JSON类型字段的插入值可分为数组和对象。JSON数组是一个由逗号分隔并包含在括号“”中的值列表，例如：abc, 10, null, true, falseJSON对象是一组键值，由逗号分隔，包含在括号“”中，例如：k1: value, k2: 10而且JSON数组和JSON对象允许嵌套，例如：99, id: HK500, cost: 75.99, hot, coldk1: value, k2: 10, 20第26页，共34页。4.2 数据类型4.2.4 JSO

27、N类型【实例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字段的

28、数据类型为JSON，此处向address字段分别插入数据province:河北省,city:石家庄市和1,2,。如果插入值是有效的JSON值，则会将值插入到列中，否则会插入失败，如下所示：第27页，共34页。4.3字符集第28页，共34页。字符集是指一套文字符号及其编码和排序规则的集合。实际应用中，用户通常会向MySQL系统中插入大量文字，但计算机只能存储二进制码，这时就需要通过字符集将文字转换为二进制码再存入数据库；而不同国家和地区的文字不同，有些字符集只支持一种文字，有些字符集支持多种文字，所以在创建数据库时，要根据系统需求选择合适的字符集。MySQL支持多种字符集，其中常用的字符集包括l

29、atin1，GBK和UTF-8等。在MySQL中，可以为服务器、数据库、数据表和字段设置不同的字符集，这是MySQL比其他数据库管理系统更灵活的地方。执行以下SQL语句，可以查看MySQL中所有可用的字符集。SHOW CHARACTER SET;MySQL字符集包括字符集和排序规则两个概念，其中字符集定义MySQL存储字符串的方式，排序规则定义字符串的排序方式。每个字符集至少对应一个排序规则，可以执行以下语句，查看字符集所对应的排序规则。SHOW COLLATION LIKE 字符集名称%;第29页，共34页。【实例4-7】查看GBK字符集对应的排序规则。mysql SHOW COLLATIO

30、N 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”（二元，基于字符