html5 公众号 网站开发,顺德手机网站建设,中国互联网协会新春茶话会,青岛做网站seoMySQL-数据类型
整数类型
整数类型包含以下五种#xff1a;
整数类型字节说明tinyint1一般用于枚举数据#xff0c;比如系统设定取值范围很小且固定的场景。smallint2可以用于较小范围的统计数据#xff0c;比如统计工厂的固定资产库存数量等。mediumint3用于较大整数的计…MySQL-数据类型
整数类型
整数类型包含以下五种
整数类型字节说明tinyint1一般用于枚举数据比如系统设定取值范围很小且固定的场景。smallint2可以用于较小范围的统计数据比如统计工厂的固定资产库存数量等。mediumint3用于较大整数的计算比如车站每日的客流量等。int、integer4取值范围足够大一般情况下不用考虑超限问题用得最多。比如商品编号。bigint8处理特别巨大的整数时才会用到。比如大型门户网站点击量
可选属性
①M表示显示宽度M的取值范围是(0, 255)。例如int(5)当数据宽度小于5位的时候在数字前面需要用 字符填满宽度。该项功能需要配合“ ZEROFILL ”使用表示用“0”填满宽度否则指定显示宽度无效。如
alter table rqtanc add number int(10) zerofill显示宽度与数据类型可以存储的值范围无关但在 MySQL 8 中不在推荐使用 显示数据宽度属性
②UNSIGNED无符号类型非负int类型默认显示宽度为int(11)无符号int类型默认显示宽度为int(10)。如
alter table rqtanc add number int(10) unsigned zerofill;③ZEROFILL: 0填充,如果某列是ZEROFILLMySQL会自动为当前列添加UNSIGNED属性如果指 定了ZEROFILL只是表示不够M位时用0在左边填充如果超过M位只要不超过数据存储范围即可。int(M)必须和UNSIGNED ZEROFILL一起使用才有意义。如果整 数值超过M位就按照实际位数存储。只是无须再用字符 0 进行填充。
浮点类型
浮点数类型分别是 FLOAT、DOUBLE、REAL。REAL默认情况下就是DOUBLE通过
SET sql_mode REAL_AS_FLOAT;设置为REAL 为FLOAT单精度浮点数
FLOAT表示单精度浮点数DOUBLE表示双精度浮点数
类型字节FLOAT4DOUBLE8
存储浮点数的格式为 符号(S) 、 尾数(M) 和 阶码(E)
相关精度说明
①允许使用 非标准语法 FLOAT(M,D) 或 DOUBLE(M,D)M称为 精度 D称为 标度 。(M,D)中 M整数位小数 位D小数位。 DM2550D30。
②FLOAT和DOUBLE类型在不指定(M,D)时默认会按照实际的精度由实际的硬件和操作系统决定 来显示。
③浮点类型也可以加 UNSIGNED表示非负 但是不会改变数据范围
④相关处理方案
Ⅰ如果存储时整数部分超出了范围就会报错不允许存这样的值
Ⅱ如果存储时小数点部分若超出范围分为以下两种情况
1.若四舍五入后整数部分没有超出范围则只警告但能成功操作并四舍五入删除多余 的小数位后保存。
2.若四舍五入后整数部分超出范围则MySQL报错并拒绝处理
注意
①FLOAT(M,D) 和DOUBLE(M,D)浮点型FLOAT和DOUBLE的UNSIGNED 在8.0及以后版本不在推荐使用
②浮点数是不准确的所以我们要避免使用“”来 判断两个数是否相等
定点数类型
只有 DECIMAL 一种类型。
类型字节说明DECIMAL(M,D),DEC,NUMERICM2有效范围由M和D决定
相关说明
①使用 DECIMAL(M,D) 的方式表示高精度小数。其中M被称为精度D被称为标度。0M65 0D30DM。
②DECIMAL(M,D)的最大取值范围与DOUBLE类型一样但是有效的数据范围是由M和D决定的。
③定点数在MySQL内部是以 字符串 的形式进行存储这就决定了它一定是精准的。
④当DECIMAL类型不指定精度和标度时其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的 精度范围时则MySQL同样会进行四舍五入处理。
⑤定点数类型取值范围相对小但是精准没有误差适合于对精度要求极高的场景 比如涉 及金额计算的场景
位类型BIT
BIT类型中存储的是二进制值类似010110。
类型长度范围占用空间BITMM1M64约M7/8
相关说明
①如果没有指定(M)默认是1位。这个1位表示只能存1位的二进制值。这里(M)是表示二进制的 位数位数最小值为1最大值为64。
②在向BIT类型的字段中插入数据时一定要确保插入的数据在BIT类型支持的范围内。
③使用b0查询数据时可以直接查询出存储的十进制数据的值
日期与时间类型
日期和时间 类型主要有YEAR类型、TIME类型、DATE类型、DATETIME类型和TIMESTAMP类型。
类型说明字节格式YEAR年1YYYY或YYTIME时间3HH:MM:SSDATE日期3YYYY-MM-DDDATETIME日期 时间8YYYY-MM-DD HH:MM:SSTIMESTAMP日期 时间4YYYY-MM-DD HH:MM:SS
相关说明
YEAR类型
①用来表示年份在所有的日期时间类型中所占用的存储空间最小只需要 1个字节 的存储空间。
②存储格式如下
Ⅰ以4位字符串或数字格式表示YEAR类型其格式为YYYY最小值为1901最大值为2155。
Ⅱ以2位字符串格式表示YEAR类型最小值为00最大值为99。
Ⅲ5.5.27版本不在推荐使用YY格式8.0.19版本不在推荐使用显示宽度格式YEAR4 DATE类型
①表示日期没有时间部分格式为 YYYY-MM-DD 其中YYYY表示年份MM表示月份DD表示 日期。需要 3个字节 的存储空间。
②存储格式如下
Ⅰ以 YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期。YYYYMMDD格式会被转化为YYYY-MM-DD格式。
Ⅱ以 YY-MM-DD 格式或者 YYMMDD 格式表示的字符串日期此格式中年份为两位数值或字符串满足 YEAR类型的格式条件为当年份取值为00到69时会被转化为2000到2069当年份取值为70到99 时会被转化为1970到1999。
Ⅲ使用 CURRENT_DATE() 或者 NOW() 函数会插入当前系统的日期。 TIME类型
①用来表示时间不包含日期部分。在MySQL中需要 3个字节 的存储空间来存储TIME类型的数 据可以使用“HH:MM:SS”格式来表示TIME类型其中HH表示小时MM表示分钟SS表示秒。
存储格式说明
Ⅰ可以使用带有冒号的 字符串比如’ D HH:MM:SS’ 、’ HH:MM:SS ‘、’ HH:MM ‘、’ D HH:MM ‘、’ D HH ‘或’ SS 格式其中D表示天其最小值为0最大值为34。如果使用带有D格式的字符串 插入TIME类型的字段时D会被转化为小时计算格式为D*24HH。当使用带有冒号并且不带D的字符串 表示时间时表示当天的时间比如12:10表示12:10:00而不是00:12:10
Ⅱ使用不带有冒号的 字符串或者数字格式为’ HHMMSS 或者 HHMMSS 。
Ⅲ使用 CURRENT_TIME() 或者 NOW() 会插入当前系统的时间 DATETIME类型
①在所有的日期时间类型中占用的存储空间最大总共需要 8 个字节的存储空间。在格式上 为DATE类型和TIME类型的组合可以表示为 YYYY-MM-DD HH:MM:SS 其中YYYY表示年份MM表示月 份DD表示日期HH表示小时MM表示分钟SS表示秒。
②存储格式说明
Ⅰ以 YYYY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时 最小值为1000-01-01 00:00:00最大值为9999-12-03 23:59:59。
Ⅱ以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时会被转化为YYYY-MM-DD HH:MM:SS格式。
Ⅲ使用函数 CURRENT_TIMESTAMP() 和 NOW() 可以向DATETIME类型的字段插入系统的当前日期和 时间。 TIMESTAMP类型
①TIMESTAMP类型也可以表示日期时间其显示格式与DATETIME类型相同都是 YYYY-MM-DD HH:MM:SS 需要4个字节的存储空间。但是TIMESTAMP存储的时间范围比DATETIME要小很多
②存储数据的时候需要对当前时间所在的时区进行转换查询数据的时候再将时间转换回当前的时 区。因此使用TIMESTAMP存储的同一个时间值在不同的时区查询时会显示不同的时间。
③修改当前市区
SET time_zone 9:00;CHAR与VARCHAR类型
都可以存储比较短的字符串。
类型特点长度范围存储空间CHAR(M)固定长度M0 M 255M个字节VARCHAR(M)可变长度M0 M 65535(实际长度 1) 个字节
CHAR类型
①CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)则表示长度默认是1个字符。
②保存时数据的实际长度比CHAR类型声明的长度小则会在 右侧填充 空格以达到指定的长 度。当MySQL检索CHAR类型的数据时CHAR类型的字段会去除尾部的空格。
③ 定义CHAR类型字段时声明的字段长度即为CHAR类型字段所占的存储空间的字节数。
VARCHAR类型
①VARCHAR(M) 定义时 必须指定 长度M否则报错。
②varchar(20)指的是20字符。
③ 检索VARCHAR类型的字段数据时会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间 为字符串实际长度加1个字节。
对比
类型特点空间上时间上适用场景CHAR(M)固定长度浪费存储空间效率高存储不大速度要求高VARCHAR(M)可变长度节省存储空间效率低非CHAR的情况
不同存储引擎相关说明
①MyISAM 数据存储引擎和数据列MyISAM数据表最好使用固定长度(CHAR)的数据列代替可变长 度(VARCHAR)的数据列。这样使得整个表静态化从而使 数据检索更快 用空间换时间。
②MEMORY 存储引擎和数据列MEMORY数据表目前都使用固定长度的数据行存储因此无论使用 CHAR或VARCHAR列都没有关系两者都是作为CHAR类型处理的。
③ InnoDB 存储引擎建议使用VARCHAR类型。因为对于InnoDB数据表内部的行存储格式并没有区 分固定长度和可变长度列所有数据行都使用指向数据列值的头指针而且主要影响性能的因素 是数据行使用的存储总量由于char平均占用的空间多于varchar所以除了简短并且固定长度的 其他考虑varchar。这样节省空间对磁盘I/O和数据存储总量比较好。
TEXT类型
TEXT用来保存文本类型的字符串总共包含4种类型分别为TINYTEXT、TEXT、 MEDIUMTEXT 和 LONGTEXT 类型。
类型特点长度长度范围占用的存储空TINYTEXT小文本、可变长度L0 L 255L 2 个字节TEXT文本、可变长度L0 L 65535L 2 个字节MEDIUMTEXT中等文本、可变长度L0 L 16777215L 3 个字节LONGTEXT大文本、可变长度L0 L 4294967295相当于 4GBL 4 个字节
相关说明
①由于实际存储的长度不确定MySQL 不允许 TEXT 类型的字段做主键。遇到这种情况你只能采用 CHAR(M)或者 VARCHAR(M)。
②TEXT文本类型可以存比较大的文本段搜索速度稍慢因此如果不是特别大的内容建议使用CHAR VARCHAR来代替。
③TEXT类型不用加默认值添加也无任何意义
④且text和blob类型的数据删除后容易导致 “空洞”使得文件碎片比较多所以频繁使用的表不建议包含TEXT类型字段建议单独分出去单独用 一个表。
ENUM类型
ENUM类型也叫作枚举类型ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时ENUM 类型只允许从成员中选取单个值不能一次选取多个值。
其所需要的存储空间由定义ENUM类型时指定的成员个数决定。
类型长度长度范围占用的存储空间ENUML1 L 655351或2个字节
相关说明
①当ENUM类型包含1255个成员时需要1个字节的存储空间
②当ENUM类型包含25665535个成员时需要2个字节的存储空间。
③ ENUM类型的成员个数的上限为65535个。
示例
CREATE TABLE test_enum(
season ENUM(春,夏,秋,冬,unknow)
);INSERT INTO test_enum
VALUES(春),(秋);
# 忽略大小写
INSERT INTO test_enum
VALUES(UNKNOW);
# 允许按照角标的方式获取指定索引位置的枚举值
INSERT INTO test_enum
VALUES(1),(3);
# Data truncated for column season at row 1
INSERT INTO test_enum
VALUES(ab);
# 当ENUM类型的字段没有声明为NOT NULL时插入NULL也是有效的
INSERT INTO test_enum
VALUES(NULL);SET类型
当SET类型包含的成员个数不同时其所占用的存储空间也是不同的具体如下
成员个数范围L表示实际成员个数占用的存储空间1 L 81个字节9 L 162个字节17 L 243个字节25 L 324个字节33 L 648个字节
SET类型在存储数据时成员个数越多其占用的存储空间越大。注意SET类型在选取成员时可以一次 选择多个成员这一点与ENUM类型不同。
示例
CREATE TABLE test_set(
s SET (A, B, C)
);INSERT INTO test_set (s) VALUES (A), (A,B);
#插入重复的SET类型成员时MySQL会自动删除重复的成员
INSERT INTO test_set (s) VALUES (A,B,C,A);
#向SET类型的字段插入SET成员中不存在的值时MySQL会抛出错误。
INSERT INTO test_set (s) VALUES (A,B,C,D);
SELECT *
FROM test_set;二进制字符串类型
二进制字符串类型主要存储一些二进制数据比如可以存储图片、音频和视频等二进制数 据。
二进制字符串类型主要包括BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB类型。 BINARY与VARBINARY类型
①BINARY和VARBINARY类似于CHAR和VARCHAR只是它们存储的是二进制字符串。
②BINARY (M)为固定长度的二进制字符串M表示最多能存储的字节数取值范围是0~255个字符。如果未 指定(M)表示只能存储 1个字节 。如果字段值不足(M)个字 节将在右边填充’\0’以补齐指定长度。
③VARBINARY (M)为可变长度的二进制字符串M表示最多能存储的字节数总字节数不能超过行的字节长 度限制65535另外还要考虑额外字节开销VARBINARY类型的数据除了存储数据本身外还需要1或2个 字节来存储数据的字节数。VARBINARY类型 必须指定(M) 否则报错。
二进制字符串类型特点值的长度占用空间BINARY(M)固定长度M 0 M 255M个字节VARBINARY(M)可变长度M0 M 65535M1个字节
示例
CREATE TABLE test_binary1(
f1 BINARY,
f2 BINARY(3),
# f3 VARBINARY,
f4 VARBINARY(10)
);INSERT INTO test_binary1(f2,f4)
VALUES(ab,ab);
mysql SELECT LENGTH(f2),LENGTH(f4)
- FROM test_binary1;
------------------------
| LENGTH(f2) | LENGTH(f4) |
------------------------
| 3 | NULL |
| 3 | 2 |
------------------------
INSERT INTO test_binary1(f1,f2)
VALUES(a,a);
INSERT INTO test_binary1(f1,f2)
VALUES(尚,尚);#失败 BLOB类型
①的BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB 4种类型它们可容纳值的最大 长度不同。可以存储一个二进制的大对象比如 图片 、 音频 和 视频 等。
②在实际工作中往往不会在MySQL数据库中使用BLOB类型存储大对象数据通常会将图 片、音频和视频文件存储到 服务器的磁盘上 并将图片、音频和视频的访问路径存储到MySQL中。
二进制字符串类型值的长度长度范围占用空间TINYBLOBL0 L 255L 1 个字节BLOBL0 L 65535相当于64KBL 2 个字节MEDIUMBLOBL0 L 16777215 相当于16MBL 3 个字节LONGBLOBL0 L 4294967295相当于4GBL 4 个字节
TEXT和BLOB的使用注意事项
① BLOB和TEXT值也会引起自己的一些问题特别是执行了大量的删除或更新操作的时候。删除这种值 会在数据表中留下很大的 空洞 以后填入这些空洞的记录可能长度不同。为了提高性能建议定期 使用 OPTIMIZE TABLE 功能对这类表进行 碎片整理 。
② 如果需要对大文本字段进行模糊查询MySQL 提供了 前缀索引 。但是仍然要在不必要的时候避免检 索大型的BLOB或TEXT值。例如SELECT * 查询就不是很好的想法除非你能够确定作为约束条件的 WHERE子句只会找到所需要的数据行。否则你可能毫无目的地在网络上传输大量的值。
③ 把BLOB或TEXT列 分离到单独的表 中。在某些环境中如果把这些数据列移动到第二张数据表中可 以让你把原数据表中的数据列转换为固定长度的数据行格式那么它就是有意义的。这会 减少主表中的 碎片 使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT * 查询的时候不会通过 网络传输大量的BLOB或TEXT值。 JSON 类型
①JSONJavaScript Object Notation是一种轻量级的 数据交换格式 。简洁和清晰的层次结构使得 JSON 成 为理想的数据交换语言。它易于人阅读和编写同时也易于机器解析和生成并有效地提升网络传输效 率。JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串然后就可以在网络或者程序之间轻 松地传递这个字符串并在需要的时候将它还原为各编程语言所支持的数据格式。
②在MySQL 5.7中就已经支持JSON数据类型
③在MySQL 8.x版本中JSON类型提供了可以进行自动验证的 JSON文档和优化的存储结构使得在MySQL中存储和读取JSON类型的数据更加方便和高效。 创建数据 表表中包含一个JSON类型的字段 js 。
示例
create table test_json(
js json
);INSERT INTO test_json (js)
VALUES ({name:songhk, age:18, address:{province:beijing,
city:beijing}});当需要检索JSON类型的字段中数据的某个具体值时可以使用“-”和“-”符号。
mysql SELECT js - $.name AS NAME,js - $.age AS age ,js - $.address.province
AS province, js - $.address.city AS city
- FROM test_json;
--------------------------------------
| NAME | age | province | city |
--------------------------------------
| songhk | 18 | beijing | beijing |
--------------------------------------
1 row in set (0.00 sec)
相关建议
①任何字段如果为非负数必须是 UNSIGNED
②小数类型为 DECIMAL禁止使用 FLOAT 和 DOUBLE。
③如果存储的字符串长度几乎相等使用 CHAR 定长字符串类型。
④VARCHAR 是可变长字符串不预先分配存储空间长度不要超过 5000。如果存储长度大 于此值定义字段类型为 TEXT独立出来一张表用主键来对应避免影响其它字段索引效率。
