上海网站建设技术托管,提高网站流量,东莞最近发生了什么大事,手机怎么创建自己的网页前言 ✨欢迎来到小K的MySQL专栏#xff0c;本节将为大家带来MySQL数据类型简介 | 整数 | 浮点 | 定点类型的分享✨ 目录 前言0.数据类型简介1 整数类型2 浮点类型3 定点类型4 日期/时间类型总结 0.数据类型简介
数据类型#xff08;data_type#xff09;是指系统中所允许的…前言 ✨欢迎来到小K的MySQL专栏本节将为大家带来MySQL数据类型简介 | 整数 | 浮点 | 定点类型的分享✨ 目录 前言0.数据类型简介1 整数类型2 浮点类型3 定点类型4 日期/时间类型总结 0.数据类型简介
数据类型data_type是指系统中所允许的数据的类型。MySQL 数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。
数据库中的每个列都应该有适当的数据类型用于限制或允许该列中存储的数据。例如列中存储的为数字则相应的数据类型应该为数值类型。
如果使用错误的数据类型可能会严重影响应用程序的功能和性能所以在设计表时应该特别重视数据列所用的数据类型。更改包含数据的列不是一件小事这样做可能会导致数据丢失。因此在创建表时必须为每个列设置正确的数据类型和长度。
MySQL 的数据类型有大概可以分为 5 种分别是整数类型、浮点数类型和定点数类型、日期和时间类型、字符串类型、二进制类型等。 注意整数类型和浮点数类型可以统称为数值数据类型。 1 整数类型
1.1 类型介绍
整数类型包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
类型说明大小(字节)TINYINT很小的整数1SMALLINT小的整数2MEDIUMINT中等大小的整数3INT/INTEGER普通大小的整数4BIGINT大整数8
1.2 可选属性
1.2.1 M
M表示显示宽度M的取值范围是(0,255)。例如INT(5)当数据小于5位时可以用字符填充 补满所需宽度。该功能需要配置ZEROFILL使用表示用 0 填满宽度否则指定显示宽度无效。
注意设置宽度不会对原来的数据或者插入数据产生任何影响仅仅是显示效果不同。从MySQL 8.0.17开始整数类型不推荐使用显示宽度属性。
1.2.2 UNSIGNED
所有整数类型都有一个可选的属性UNSIGNED无符号整数类型的最小取值为0。所以如果需要在MySQL中保存非负整数是可以将整数类型设置为无符号类型。
1.3 使用场景
TINYINT一般用于枚举类型比如系统设定取值范围很小且固定的场景。
SMALLINT可以用于较小范围的统计数据比如统计工厂的固定资产库存数量等。
MEDIUMINT用于较大整数的计算比如车站每日的客流量等。
INT、INTEGER取值范围足够大一般情况下不用考虑超限问题用的最多比如商品编号。
BIGINT只有当你处理特别巨大的整数时才会用到比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。
1.4 如何选择
在评估用哪种整数类型的时候你需要考虑存储空间和可靠性的平衡问题一方面用占用字节数少的整数类型可以节省存储空间另一方面要是为了节省存储空间使用的整数类型取值范围太小一旦遇到超出取值范围的情况就可能引起系统错误影响可靠性。
举个栗子商品编号采用的数据类型是INT。原因就在于客户门店中流通的商品种类较多而且每天都有旧的商品下架新的商品上架这样不断迭代日积月累。
如果使用 SMALLINT 类型虽然占用字节数比 INT 类型的证书少但是却不能保证数据不会超出 655345。相反使用INT 就能确保有足够大的取值范围不用担心数据超出范围影响可靠性。
你要注意的是在实际工作中系统故障产生的成本远远超过增加几个字段存储空间所产生的成本。因此我建议你首先确保数据不会超过取值范围在这个前提下再去考虑如何节省存储空间。
2 浮点类型
2.1 类型介绍
浮点数类型包括 FLOAT、DOUBLE和REAL。
类型说明大小(字节)FLOAT单精度浮点数4DOUBLE双精度浮点数8REAL默认为DOUBLE把SQL模式设定为“REAL_AS_FLOAT”REAL就是FLOATSET sql_mode REAL_AS_FLOAT
2.2 精度说明 MySQL允许使用非标准语法(其他数据库未必支持因此如果涉及到数据前移则最好不要这么用)FLOAT(M,D)或DOUBLE(M,D)。其中M称为精度表示总共的位数D称为标度表示小数的位数。DM2550D30。 例如定义为FLOAT(5,2)的一个列可以显示为[-999.99999.99]超出这个范围会报错 FLOAT 和 DOUBLE 在不指定精度时默认会按照实际的精度由计算机硬件和操作系统决定。 注意浮点类型也可以加UNSIGNED但是不会改变数据范围例如FLOAT(3,2) UNSIGNED任然只能表示0-9.99的范围。 不管是否显示设置了精度(M,D)MySQL有自己的处理方案 如果存储时整数部分超出了范围则直接报错。如果存储时小数点部分超出范围就分一下情况 若四舍五入后整数部分没有超出范围就可以存储。如在FLOAT(5,2)列插入999.009近似结果是999.01。若四舍五入后整数部分超出范围则直接报错。如在FLOAT(5,2)列插入999.995和-999.995都会报错。 从MySQL8.0.17开始FLOAT(M,D)和DOUBLE(M,D)用法在官方文档中已经明确不推荐使用了将来可能会被移除另外关于浮点类型FLOAT和DOUBLE的UNSIGNED也不推荐使用了将来也可能被移除。
浮点数相对于定点数的优点是在长度一定的情况下浮点数能够表示更大的范围缺点是会引起精度问题。
最后再强调一下在 MySQL 中定点数以字符串形式存储在对精度要求比较高的时候如货币、科学数据使用 DECIMAL 的类型比较好另外两个浮点数进行减法和比较运算时也容易出问题所以在使用浮点数时需要注意并尽量避免做浮点数比较
3 定点类型
定点数类型为 DECIMAL。
类型说明大小(字节)DECIMAL/DEC压缩的“严格”定点数M2
DECIMAL(M,D)。其中M称为精度表示总共的位数D称为标度表示小数的位数。0M650D30DM。例如定义为DECIMAL(5,2)表示该列的取值范围是[-999.99999.99]。
DECIMAL(M,D)的最大值范围与DOUELE类型一样但是有效的数据方位是由M和D决定的。DECIMAL的存储空间并不固定由精度M决定总共占用的存储空间为M2个字节。也就是说在一些对精度要求不高的场景下比起占用同样字节长度的定点数浮点数表达的数值范围可以更大。定点数在MySQL内部是以字符串的形式进行存储的这就决定了它一定是精确的。当DECIMAL类型不指定精度和标度时其默认为DECIMAL(10,0)当数据的精度超出了定点数的精度范围时则MySQL同样会进行四舍五入。浮点数 vs 定点数 浮点数相对于定点数的有点是在长度一定的情况下浮点类型取值范围大但是不精确适用于需要取值范围大又可以容忍误差的科学计算场景定点数类型取值范围相对小但是精准没有误差设置对于精度要求极高的场景(比如设计金额计算的场景)
4 日期/时间类型
包括 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。
类型说明日期范围大小(字节)YEARYYYY1901~21551TIMEHH:MM:SS-838:59:59 ~ 838:59:593DATEYYYY-MM-DD1000-01-01 ~ 9999-12-33DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:00 ~ 9999-12-31 23:59:598TIMESTAMPYYYY-MM-DD HH:MM:SS1970-01-01 00:00:00 UTC ~ 2038-01-19 03:14:07 UTC4
YEAR类型
YEAR 类型是一个单字节类型用于表示年在存储时只需要 1 个字节。可以使用各种格式指定 YEAR如下所示
以 4 位字符串或者 4 位数字格式表示 YEAR范围为 ‘1901’’2155’。输入格式为 ‘YYYY’ 或者 YYYY例如输入 ‘2010’ 或 2010插入数据库的值均为 2010。以 2 位字符串格式表示的 YEAR范围为 ‘00’ 到 ‘99’。‘00’’69’ 和 ‘70’’99’ 范围的值分别被转换为 20002069 和 19701999 范围的 YEAR 值。‘0’ 与 ‘00’ 的作用相同。插入超过取值范围的值将被转换为 2000。
从MySQL5.5.27开始2位格式的YEAR已经不推荐使用。YEAR默认格式就是“YYYY”没必要写成YEAR(4)。
CREATE TABLE test_year
(f1 YEAR,f2 YEAR(4)
);INSERT INTO test_year(f1) VALUES(2021),(2022);
INSERT INTO test_year(f1) VALUES(2155);
-- out of range value for column f1 at row 1
INSERT INTO test_year(f1) VALUES(2156);
INSERT INTO test_year(f1) VALUES(69),(70); #2069 1970
INSERT INTO test_year(f1) VALUES(0),(0);DATE 类型
DATE 类型用于仅需要日期值时没有时间部分在存储时需要 3 个字节。日期格式为 ‘YYYY-MM-DD’其中 YYYY 表示年MM 表示月DD 表示日。
在给 DATE 类型的字段赋值时可以使用字符串类型或者数字类型的数据插入只要符合 DATE 的日期格式即可。如下所示
以 ‘YYYY-MM-DD’ 或者 ‘YYYYMMDD’ 字符中格式表示的日期取值范围为 ‘1000-01-01’’9999-12-3’。例如输入 ‘2015-12-31’ 或者 ‘20151231’插入数据库的日期为2015-12-31。以 ‘YY-MM-DD’ 或者 ‘YYMMDD’ 字符串格式表示日期在这里YY表示两位的年值。MySQL 解释两位年值的规则‘0069’ 范围的年值转换为 ‘2000~ 2069’‘70~99’ 范围的年值转换为 ‘19701999’。例如输入 ‘15-12-31’插入数据库的日期为 2015-12-31输入 ‘991231’插入数据库的日期为 1999-12-31。使用 CURRENT_DATE() 或者 NOW()插入当前系统日期。 提示MySQL 允许“不严格”语法任何标点符号都可以用作日期部分之间的间隔符。例如‘98-11-31’、‘98.11.31’、‘98/11/31’和’981131’ 是等价的这些值也可以正确地插入数据库。 CREATE TABLE test_date1
(f1 DATE
);INSERT INTO test_date1 VALUES(2022-04-16),(20220416),(20220416);
INSERT INTO test_date1 VALUES(22-04-16),(220416),(69-04-16),(690416),(70-04-16),(700416),(99-04-16),(990416);
INSERT INTO test_date1 VALUES(220416),(690416),(700416),(990416);
INSERT INTO test_date1 VALUES(CURRENT_DATE()),(NOW());TIME 类型
TIME 类型用于只需要时间信息的值在存储时需要 3 个字节。格式为 HH:MM:SS。HH 表示小时MM 表示分钟SS 表示秒。
TIME 类型的取值范围为 -83859598385959小时部分如此大的原因是 TIME 类型不仅可以用于表示一天的时间必须小于 24 小时还可能是某个事件过去的时间或两个事件之间的时间间隔可大于 24 小时或者甚至为负。
可以使用各种格式指定 TIME 值如下所示。
‘D HHMMSS’ 格式的字符串。还可以使用这些“非严格”的语法‘HHMMSS’、‘HHMM’、‘D HH’ 或 ‘SS’。这里的 D 表示日可以取 034 之间的值。在插入数据库时D 被转换为小时保存格式为 “D*24HH”。‘HHMMSS’ 格式、没有间隔符的字符串或者 HHMMSS 格式的数值假定是有意义的时间。例如‘101112’ 被理解为’101112’但是 ‘106112’ 是不合法的它有一个没有意义的分钟部分在存储时将变为 000000。使用 CURRENT_TIME() 或者 NOW()插入当前系统时间。 提示为 TIME 列分配简写值时应注意如果没有冒号MySQL 解释值时假定最右边的两位表示秒。MySQL 解释 TIME 值为过去的时间而不是当前的时间。例如读者可能认为 ‘1112’ 和 1112 表示 111200即 11 点过 12 分钟但MySQL 将它们解释为 001112即 11 分 12 秒。同样 ‘12’ 和 12 被解释为000012。相反TIME 值中如果使用冒号则肯定被看作当天的时间也就是说‘1112’ 表示 111200而不是 001112。 CREATE TABLE test_time1
(f1 TIME
);
INSERT INTO test_time1
VALUES(10:35:30),(2 10:35:30),(10:35),(2 10:35),(1 35),(30);
INSERT INTO test_time1 VALUES(103530),(103530),(1035);
INSERT INTO test_time1 VALUES(NOW()),(CURRENT_TIME());DATETIME 类型
DATETIME 类型用于需要同时包含日期和时间信息的值在存储时需要 8 个字节。日期格式为 ‘YYYY-MM-DD HHMMSS’其中 YYYY 表示年MM 表示月DD 表示日HH 表示小时MM 表示分钟SS 表示秒。
在给 DATETIME 类型的字段赋值时可以使用字符串类型或者数字类型的数据插入只要符合 DATETIME 的日期格式即可如下所示。
以 ‘YYYY-MM-DD HHMMSS’ 或者 ‘YYYYMMDDHHMMSS’ 字符串格式表示的日期取值范围为 ‘1000-01-01 000000’’9999-12-3 235959’。以 ‘YY-MM-DD HHMMSS’ 或者 ‘YYMMDDHHMMSS’ 字符串格式表示的日期在这里 YY 表示两位的年值。与前面相同‘0079’ 范围的年值转换为 ‘20002079’‘8099’ 范围的年值转换为 ‘19801999’。以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。 提示**MySQL 允许“不严格”语法任何标点符号都可用作日期部分或时间部分之间的间隔符。**例如‘98-12-31 113045’、‘98.12.31 113035’、‘98/12/31 113045’ 和 ‘981231 11 ^ 30 ^ 45’ 是等价的这些值都可以正确地插入数据库。 CREATE TABLE test_datetime1
(dt DATETIME
);
INSERT INTO test_datetime1 VALUES(2022-04-16 18:50:30),(20220416185030);
INSERT INTO test_datetime1 VALUES(99-04-16 18:50:30),(990416185030),(22-04-16 18:50:30),(220416185030);
INSERT INTO test_datetime1 VALUES(20220202000000);
INSERT INTO test_datetime1 VALUES(CURRENT_TIMESTAMP()),(NOW());TIMESTAMP 类型
TIMESTAMP 的显示格式与 DATETIME 相同显示宽度固定在 19 个字符日期格式为 YYYY-MM-DD HHMMSS在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围为 1970-01-01 000001’UTC’2038-01-19 031407’UTC。在插入数据时要保证在合法的取值范围内。 提示协调世界时英Coordinated Universal Time法Temps Universel Coordonné又称为世界统一时间、世界标准时间、国际协调时间。英文CUT和法文TUC的缩写不同作为妥协简称 UTC。 TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外还有一个最大的区别是
DATETIME 在存储日期数据时按实际输入的格式存储即输入什么就存储什么与时区无关而 TIMESTAMP 值的存储是以 UTC世界标准时间格式保存的存储时对当前时区进行转换检索时再转换回当前时区。即查询时根据当前时区的不同显示的时间值是不同的。 提示如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值结果值的时间部分被设置为 ‘000000’因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值结果值的时间部分被删除因此DATE 值未包含时间信息。 CREATE TABLE test_timestamp
(ts TIMESTAMP
);
INSERT INTO test_timestamp VALUES(1999-01-01 01:02:03),(1999-01-01 01:02:03),(99-01-01 01:02:03),(990101010203);
INSERT INTO test_timestamp VALUES(20220102112233);
INSERT INTO test_timestamp VALUES(CURRENT_TIMESTAMP()),(NOW());TIMESTAMP 和 DATETIME区别
CREATE TABLE temp_time
(d1 DATETIME,d2 TIMESTAMP
);
INSERT INTO temp_time VALUES(2022-04-16 19:09:55,2022-04-16 19:09:55);
INSERT INTO temp_time VALUES(NOW(),NOW());SELECT * FROM temp_time;SET time_zone 9:00SELECT * FROM temp_time; #设置之后再查询一次设置时区
SET time_zone 9:00TIMESTAMP妙用
TIMESTAMP时间戳在创建的时候可以有多重不同的特性如
1.在创建新记录和修改现有记录的时候都对这个数据列刷新
TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP2.在创建新记录的时候把这个字段设置为当前时间但以后修改时不再刷新它
TIMESTAMP DEFAULT CURRENT_TIMESTAMP3.在创建新记录的时候把这个字段设置为0以后修改时刷新它
TIMESTAMP ON UPDATE CURRENT_TIMESTAMP4.在创建新记录的时候把这个字段设置为给定值以后修改时刷新它
TIMESTAMP DEFAULT ‘yyyy-mm-dd hh:mm:ss ON UPDATE CURRENT_TIMESTAMP模拟用户登录
CREATE TABLE t_user
(id INT PRIMARY KEY AUTO_INCREMENT,createtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,lastlogintime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,is_online TINYINT DEFAULT 0
);
INSERT INTO t_user(is_online) VALUES(1);SELECT * FROM t_user;# 数据更新
UPDATE t_user SET is_online0 WHERE id1;本来两个时间是一模一样的但是当用户下线lastlohintime发生自动更新
总结
总的来说选择合适的数据类型可以提高数据库的性能、节省存储空间、确保数据的一致性和有效性并方便进行数据处理和计算。因此在设计数据库时选择合适的数据类型非常重要。~下节带来字符串 | 二进制类型的分享
