1、4存储引擎、数据类型和字符集第章4.1 存储引擎 3 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 Support:DEFAULT Comment:
2、Supports transactions,row-level locking,and foreign keys Transactions:YES XA:YES Savepoints:YES*2.row*Engine:MRG_MYISAM 省略部分 提示上述命令以“G”结尾，其作用是将查询结果按列显示。4 4.1 存储引擎 4.1.1 MySQL存储引擎概述查看MySQL默认存储引擎2MySQL 5.5之前版本的默认存储引擎为MyISAM，MySQL 5.5及之后版本的默认存储引擎为InnoDB，可以执行以下命令查看默认存储引擎：mysql SHOW VARIABLES LIKE defaul
3、t_storage_engine;+-+-+|Variable_name|Value|+-+-+|default_storage_engine|InnoDB|+-+-+1 row in set,1 warning(0.00 sec)5 4.1 存储引擎 4.1.1 MySQL存储引擎概述修改MySQL默认存储引擎3创建新表时如果不指定存储引擎，则系统使用默认存储引擎。如果用户想改变默认存储引擎，可以通过修改配置文件来实现。打开my.ini配置文件，在“mysqld”组下面添加以下内容，然后保存文件即完成修改。#设置MySQL服务器的默认存储引擎default_storage_engine=My
4、ISAM修改完毕后，需要重启MySQL服务，修改才能生效。可以重启并重新登录MySQL，并采用前面的方法查询修改结果。6 4.1 存储引擎 4.1.2 常用的存储引擎InnoDB1InnoDB为MySQL提供具有提交、回滚、崩溃恢复能力和多版本并发控制的事务安全型表，能够高效地处理大量数据。适用于需要事务支持、高并发、数据更新频繁、对数据的一致性和完整性要求较高的计费系统或者财务系统等。MyISAM2MyISAM存储引擎基于ISAM，并对其进行扩展。具有较高的插入和查询速度，但不支持事务和外键。每个使用MyISAM存储引擎创建的数据表都会生成3个文件，文件名和数据表名称相同，但扩展名不同，各个
5、文件及其作用分别如下：u.frm：存储表定义；u.MYD：存储数据；u.MYI：存储索引。MEMORY3MEMORY存储引擎主要用于内容变化不频繁的表。另外，由于MEMORY存储引擎是将数据存储到内存中，所以太大的表无法使用此存储引擎。对于数据更新不频繁、存活周期不长和需要对统计结果进行分析的数据表可以使用MEMORY存储引擎。对事务完整性没有要求、并发相对较低、数据更新不频繁、以读为主和对数据一致性要求不高的数据表，推荐使用MyISAM存储引擎。4.2 数据类型 8 4.2 数据类型数据表由多个字段构成，每个字段可以指定不同的数据类型，数据类型用于规定数据的存储格式、约束和有效范围。在定义数
6、据表字段时，应根据实际情况选择合适的数据类型。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,basketball,volleyball)爱 好photoVARBINARY(255)相 片spendDECIMAL(5,1)生活费addres
7、sJSON住 址IntroTEXT简 介 9 4.2 数据类型 4.2.1 数值类型数值类型用于存储数字型数据，包括整数类型（TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT）、浮点数类型（FLOAT，DOUBLE）和定点数类型（DECIMAL）。其中，整数类型的字段用于存储整数，浮点数和定点数类型的字段用于存储小数。不同的数值类型提供不同的存储范围，并且每种类型可以设置两种格式，分别为有符号和无符号，有符号表示可以存储负数，无符号表示只能存储0或正数。10 4.2 数据类型 4.2.1 数值类型表4-2 整数类型数据类型所占字节存储范围（有符号）存储范围（无符号）T
8、INYINT1字节-1281270255SMALLINT2字节-3276832767065535MEDIUMINT3字节-83886088388607016777215INT4字节-2147483648214748364704294967295BIGINT8字节-92233720368547758089223372036854775807018446744073709551615表4-3 TINYINT类型二进制正整数最大值01111111表4-4 TINYINT类型二进制负整数最小值10000000整数类型1 11 4.2 数据类型 4.2.1 数值类型以tb_demo表为例，其中有一个ag
9、e字段，它的数据类型为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
10、 row 1mysql SELECT age FROM tb_demo;+-+|age|+-+|123|+-+1 rows in set(0.02 sec)12 4.2 数据类型 4.2.1 数值类型mysql HELP CONTENTS;You asked for help about help category:ContentsFor more information,type help,where is one of the followingcategories:Account Management Administration Compound Statements Data Def
11、inition Data Types#部分内容省略【实例4-2】查看系统帮助。首先使用“HELP CONTENTS”查看MySQL帮助文档支持的目录列表。然后根据需要查看的条目输入相关命令进行查看，例如，执行“HELP DATA TYPES”可以查看MySQL支持的所有数据类型。最后以“HELP”开头，加上任意一个数据类型，可以查看其存储范围，如以下代码所示：mysql HELP DATA TYPES;You asked for help about help category:Data TypesFor more information,type help,where is one of t
12、he followingtopics:AUTO_INCREMENT#部分内容省略mysql HELP TINYINT;Name:TINYINTDescription:TINYINT(M)UNSIGNED ZEROFILLA very small integer.The signed range is-128 to 127.The unsignedrange is 0 to 255.13 4.2 数据类型 4.2.1 数值类型浮点数类型和定点数类型2浮点数类型分为两种，单精度（FLOAT）和双精度（DOUBLE），而定点数类型只有DECIMAL一种。浮点数类型和定点数类型都可以使用类型名称后面加
13、“(M,D)”的形式来定义，其中M称为精度，表示数值的位数（整数位+小数位）；D称为标度，表示小数点后的位数。例如，tb_demo表中score字段的数据类型为FLOAT(4,1)，表示可正常插入的数据长度最大是4位，小数点后保留1位，例如，123.4这样的小数符合要求。小数类型的数据占用字节和存储范围如表4-5所示。表4-5 小数类型数据类型占用字节负数存储范围非负数存储范围FLOAT4字节-3.402823466E+38-1.175494351E-380和1.175494351E-383.402823466E+38DOUBLE8字节-1.7976931348623157E+308-2.22
14、50738585072014E-3080和2.2250738585072014E-3081.7976931348623157E+308DECIMALM+2字节与DOUBLE相同与DOUBLE相同 14 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|+-+-+-+|
15、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)15 4.2 数据类型 4.2.2 日期和时间类型MySQL 5.7版本中用于存储日期和时间的数据类型有YEAR，DATE，TIME，DATETIME和TIMESTAMP，每一种类型都有固定的存储范围。表4-6 日期和时间类型数据类型占用
16、字节存储格式存储范围YEAR1字节YYYY19012155DATE3字节YYYY-MM-DD1000-01-019999-12-31TIME3字节+小数秒存储HH:MM:SS-838:59:59838:59:59DATETIME5字节+小数秒存储YYYY-MM-DD HH:MM: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 提示向日期和时间类型的字段插入数据时，最好使用引号将值包含起来。16 4
17、.2 数据类型 4.2.2 日期和时间类型YEAR1YEAR类型用于存储只有年份的值，存储格式为YYYY，它可以被声明为YEAR或YEAR(4)，具有四个字符的显示宽度。在向数据库中插入YEAR类型的数据时，可以输入不严格的值，如17，系统会自动转换为2017，转换规则有以下几点：u 数值169和7099自动转换为20012069和19701999。u 字符串169和7099自动转换为20012069和19701999。u 字符串0或00自动转换为2000。DATE2DATE类型用于存储具有日期部分但没有时间部分的值，存储格式为YYYY-MM-DD。u 格式为YYYY-DD-MM和YYYYDD
18、MM的字符串会转换为标准格式。u 格式为YYYYDDMM和YYDDMM的数值会转换为标准格式。u 格式为YY-DD-MM和YYDDMM的字符串会转换为标准格式。提示2017/10/10，2017.10.10和2017#10#10都会被系统转换为2017-10-10格式。17 4.2 数据类型 4.2.2 日期和时间类型TIME3TIME类型用于存储具有时间部分但没有日期部分的值，存储格式为HH:MM:SS。TIME类型的存储范围是-838:59:59到838:59:59，其不仅可用于表示一天中的时间（小于24小时），而且可用于表示两个事件之间的时间间隔（可能远远大于24小时，或者为负）。u T
19、IME类型支持D HH:MM:SS格式，D表示日，可以在034之间取值，会被转换为小时，即D*24+HH。u TIME类型支持没有分隔符的字符串或数值的格式。例如，113050会被系统转换为11:30:50。u 当数据为TIME类型的缩写值时，如果没有分隔符，MySQL会认为值的最右边两位表示秒，例如，1010会被转换成00:10:10；如果有分隔符，MySQL会认为值的最左边两位表示小时，例如，10:10会被转换为10:10:00。18 4.2 数据类型 4.2.2 日期和时间类型DATETIME4DATETIME类型用于存储同时具有日期和时间的值，存储格式为YYYY-MM-DD HH:MM
20、:SS。u 格式为YYYYMMDDHHMMSS和YYMMDDHHMMSS的数值会转换为标准格式。u 格式为YYYYMMDDHHMMSS和YYMMDDHHMMSS的字符串会转换为标准格式。TIMESTAMP5TIMESTAMP类型与DATETIME类型相似，用于存储同时具有日期和时间的值，存储格式为YYYY-MM-DD HH:MM:SS，但是TIMESTAMP类型的存储范围较小。TIMESTAMP类型与DATETIME类型最大的区别是：DATETIME类型存储的数据是什么，显示的就是什么；而TIMESTAMP类型的时间可以根据时区进行转换，在查询时，当前时区不同，显示的时间值也不同。19 4.2
21、 数据类型 4.2.3 字符串类型MySQL支持的字符串数据类型包括CHAR，VARCHAR，BINARY，VARBINARY，BLOB，TEXT，ENUM和SET等。表4-7 字符串类型类型名称占用字节存储范围CHAR(M)M*w字节0=M=255VARCHAR(M)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)Rec
22、ords:3 Duplicates:0 Warnings:0mysql 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:0mysql select sex from
23、 tb_demo;+-+|sex|+-+|NULL|NULL|NULL|w|m|NULL|w|w|m|+-+9 rows in set(0.00 sec)24 4.2 数据类型 4.2.3 字符串类型SET5SET类型用于存储“多选项”形式的数据，如tb_demo表中的hobby字段。选项值列表最多可以包含64个成员，每个插入值都必须从创建表时指定的选项值列表中选择。其语法形式如下：col_name SET(value1,value2,valuen)u 定义的选项值不能重复。u 选项值必须是字符串文字。u 插入值中如果有重复值，则只取其中一个。使用SET类型时应注意以下几点：u 插入值的顺序会
24、按照选项值的顺序自动排列。25 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:0mysql select hobby from tb_demo;+-+|hobby|+-+|basketball|football,volleyb
25、all|football,basketball|+-+3 rows in set(0.00 sec)26 4.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:val
26、ue,k2:10,20 27 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.addres
27、s.在tb_demo表中，address字段的数据类型为JSON，此处向address字段分别插入数据province:河北省,city:石家庄市和1,2,。如果插入值是有效的JSON值，则会将值插入到列中，否则会插入失败，如下所示：4.3 字符集 29 4.3 字符集 4.3.1 MySQL中的字符集字符集是指一套文字符号及其编码和排序规则的集合。实际应用中，用户通常会向MySQL系统中插入大量文字，但计算机只能存储二进制码，这时就需要通过字符集将文字转换为二进制码再存入数据库；而不同国家和地区的文字不同，有些字符集只支持一种文字，有些字符集支持多种文字，所以在创建数据库时，要根据系统需求选
28、择合适的字符集。MySQL支持多种字符集，其中常用的字符集包括latin1，GBK和UTF-8等。在MySQL中，可以为服务器、数据库、数据表和字段设置不同的字符集，这是MySQL比其他数据库管理系统更灵活的地方。执行以下SQL语句，可以查看MySQL中所有可用的字符集。SHOW CHARACTER SET;MySQL字符集包括字符集和排序规则两个概念，其中字符集定义MySQL存储字符串的方式，排序规则定义字符串的排序方式。每个字符集至少对应一个排序规则，可以执行以下语句，查看字符集所对应的排序规则。SHOW COLLATION LIKE 字符集名称%;30 4.3 字符集 4.3.1 MyS
29、QL中的字符集【实例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”（大小写不
30、敏感）、“_cs”（大小写敏感）或者“_bin”（二元，基于字符编码的值并且与language无关）结束。31 4.3 字符集 4.3.2 选择字符集在MySQL中，为数据库设置字符集时应考虑以下几点：u 如果数据库要处理不同地区的文字，就应选择Unicode字符集，在MySQL中，就是utf8。u 如果数据库只需要支持一般中文，并且数据量很大，性能要求很高，可以使用GBK字符集。u 如果数据库需要做大量运算，就应该选择定长字符集，如latin1和GBK等。32 4.3 字符集 4.3.3 查看与修改字符集用户应该在向数据库插入数据之前确定字符集的种类。MySQL默认的字符集为latin1，字
31、符集和排序规则分为四个级别：服务器级、数据库级、数据表级和字段级。服务器级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=utf8collation-
32、server=utf8_general_ci 33 4.3 字符集 4.3.3 查看与修改字符集数据库级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;感谢观看
