Mysql(4)数据类型

提示文章写完后目录可以自动生成如何生成可参考右边的帮助文档文章目录数值类型整数类型浮点数类型BIT类型字符串类型定长与变长字符串枚举与集合文本类型二进制类型日期时间类型一、数值类型1. 整数类型INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT2. 浮点数与定点数3. BIT 类型二、字符串类型1. 定长与变长字符串2. ENUM枚举与 SET集合3. 文本类型TEXT和二进制类型BLOB三、日期时间类型四、高频易错点总结数值类型整数类型对于整数类型MySQL还支持在类型名称后面加小括号(M)而小括号中的M表示显示宽度M的取值范围是(0, 255)。int(M)这个M在字段的属性中指定了unsigned无符号和zerofill零填充的情况下才有意义。表示当整数值不够M位时用0填充。如果整数值超过M位但是没有超过当前数据类型的范围时就按照实际位数存储。当M宽度超过当前数据类型可存储数值范围的最大宽度时也是以实际存储范围为准。MySQL8之前int没有指定(M)默认显示(11)。最多能存储和显示11位整数。从MySQL 8.0.17开始整数数据类型不推荐使用显示宽度属性默认显示int。示例--创建表 create table t_int(i1int,i2int(2)--没有unsigned zerofill(2)没有意义);--查看表结构 desc t_int;--创建表 create table t_int2(i1int,i2int(2)unsigned zerofill);--查看表结构 desc t_int2;--添加数据 insert into t_int2 values(1234,1234);--查询数据 select*fromt_int2;--添加数据 insert into t_int2 values(1,1);--查询数据 select*fromt_int2;没有添加”unsigned zerofil“l则这个2不起作用无符号0填充结果如下不够两位零填充浮点数类型MySQL中使用浮点数和定点数来表示小数。浮点数有两种类型单精度浮点数FLOAT和双精度浮点数DOUBLE定点数只有DECIMAL。浮点数和定点数都可以用MD来表示M是精度表示该值总共显示M位包括整数位和小数位对于FLOAT和DOUBLE类型来说M取值范围为0 ~ 255而对于DECIMAL来说M取值范围为0 ~ 65。D是标度表示小数的位数取值范围为0~30同时必须M。FLOAT和DOUBLE类型在不指定MD时默认会按照实际的精度来显示。DECIMAL类型在不指定MD时默认为100即只保留整数部分。例如定义DECIMAL5,2的类型表示该列取值范围是-999.99~999.99。如果用户插入数据的小数部分位数超过D位MySQL会四舍五入处理但是如果用户插入数据的整数部分位数超过M-D位则会报Out of range的错误。DECIMAL实际是以字符串形式存放的在对精度要求比较高的时候如货币、科学数据等使用DECIMAL类型会比较好。浮点数相对于定点数的优点是在长度一定的情况下浮点数能够表示更大的数据范围它的缺点是会引起精度问题。一个按照科学计数法一个按照十进制保存示例--创建表 create table t_double(d1 double,d2 double(5,2)---999.99~999.99);--查看表结构 desc t_double;--添加数据 insert into t_double values(2.5,2.5);--查询数据 select*fromt_double;--添加数据 insert into t_double values(2.5526,2.5526);insert into t_double values(2.5586,2.5586);--查询数据 select*fromt_double;--添加数据 insert into t_double values(12852.5526,12852.5526);--d2字段整数部分超过(5-23)位添加失败--创建表 create table t_decimal(d1 decimal,--没有指定(M,D)默认是(10,0)d2 decimal(5,2));--查看表结构 desc t_decimal;--添加数据 insert into t_decimal values(2.5,2.5);--查询数据 select*fromt_decimal;--把小数赋值给整数类型的字段时会截断小数部分考虑四舍五入 insert into t_int2 values(1.5,1.5);select*fromt_int2;结果如下decimal默认没有指定是为100所以小数部分四舍五入当插入到整数类型的时候也是四舍五入BIT类型BIT是一种存储位数据的类型每个字段的长度可以指定为1到64位。数据以位流的形式存储并以字节byte对齐。即使只存储1位最小存储单元也是1字节。可以使用十六进制、二进制或整数形式插入数据。插入值的位数不能超过定义的位数否则会报错。查询BIT字段时MySQL会以二进制格式显示数据。如果需要以整数形式显示可以使用BIN()或CONV()。可以对BIT数据进行逻辑运算例如ANDORXOR等。若是使用这种方式则是将二进制转化为十进制使用bin或者使用conv字符串类型定长与变长字符串CHAR(M)为固定长度的字符串 M表示最多能存储的字符数取值范围是0~255个字符如果未指定(M)表示只能存储1个字符。例如CHAR(4)定义了一个固定长度的字符串列其包含的字符个数最大为4如果存储的值少于4个字符右侧将用空格填充以达到指定的长度当查询显示CHAR值时尾部的空格将被删掉。VARCHAR(M)为可变长度的字符串M表示最多能存储的字符数M的范围由最长的行的大小通常是65535字节和使用的字符集确定。例如utf8mb4字符编码单个字符所需最长字节值为4个字节所以M的范围是[0, 16383]。而VARCHAR类型的字段实际占用的空间为字符串的实际长度加1或2个字节这1或2个字节用于描述字符串值的实际字节数即字符串值在[0,255]个字节范围内那么额外增加1个字节否则需要额外增加2个字节。在VARCHAR后面的M必须指定。例如身份证号、手机号码、QQ号、用户名username、密码password、银行卡号等固定长度的文本字符串适合使用CHAR类型而评论、朋友圈、微博不定长度的文本字符串更适合使用VARCHAR类型。另外存储引擎对于选择CHAR和VARCHAR是有影响的对于MyISAM存储引擎最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化从而使数据检索更快用空间换时间。对于InnoDB存储引擎使用可变长度的数据列因为InnoDB数据表的存储格式不分固定长度和可变长度因此使用CHAR不一定比使用VARCHAR更好但由于VARCHAR是按照实际的长度存储的比较节省空间所以对磁盘I/O和数据存储总量比较好。drop tableifEXISTS t_char;CREATE table t_char(c1 char,c2 char(3));insert into t_char VALUES(男,女);#insert into t_char VALUES(孙晓明,孙小明);#失败insert into t_char values(男,孙晓明);select*fromt_char;drop tableifexists t_char_1;create table t_char_1(c1 varchar(3));insert into t_char_1 VALUES(孙晓明);insert into t_char_1 values(孙晓明好人)#太长出入不了drop tableifexists t_char_2;create table t_char_2(name varchar(65535))枚举与集合有时候我们指定在固定的几个值范围内选择一个或多个那么就需要使用ENUM枚举类型和SET集合类型了。比如性别只有“男”或“女”上下班交通方式可以有“地铁”、“公交”、“出租车”、“自行车”、“步行”等。枚举和集合类型字段声明的语法格式如下字段名 ENUM(值1,值2,值3)字段名 SET(值1,值2,值3)ENUM类型的字段在赋值时只能在指定的枚举列表中取值而且一次只能取一个。枚举列表最多可以有65535个成员。ENUM值在内部用整数表示每个枚举值均有一个索引值 MySQL存储的就是这个索引编号。SET类型的字段在赋值时可从定义的值列表中选择1个或多个值的组合。SET列最多可以有64个成员。SET值在内部也用整数表示分别是1248……都是2的n次方值因为这些整数值对应的二进制都是只有1位是1其余是0。drop tableifexists t_enum;create table t_enum(gender enum(男,女),hobbyset(睡觉,打游戏,运动,写代码));desc t_enum;insert into t_enum values(男,睡觉,打游戏);--成功 insert into t_enum values(男,女,睡觉,打游戏);--失败 insert into t_enum values(妖,睡觉,打游戏);--失败 insert into t_enum values(男,睡觉,打游戏,吃饭);--失败 select*fromt_enum;insert into t_enum values(2,2);select*fromt_enum;insert into t_enum values(1,5);--5(0101)是1(0001)、4(0100)的组合 select*fromt_enum;insert into t_enum values(1,7);--7(0111)是1(0001)、2(0010)、4(0100)的组合 select*fromt_enum;insert into t_enum values(2,15);# 不能成功值最大为124select*fromt_enum;文本类型二进制类型BINARY和VARBINARY类似于CHAR和VARCHAR只是它们存储的是二进制字符串。BINARY (M)为固定长度的二进制字符串M表示最多能存储的字节数取值范围是0~255个字节如果未指定(M)表示只能存储1个字节。例如BINARY (8)表示最多能存储8个字节如果字段值不足(M)个字节将在右边填充’\0’以补齐指定长度。VARBINARY (M)为可变长度的二进制字符串M表示最多能存储的字节数总字节数不能超过行的字节长度限制65535另外还要考虑额外字节开销VARBINARY类型的数据除了存储数据本身外还需要1或2个字节来存储数据的字节数。VARBINARY类型和VARCHAR类型一样必须指定(M)否则报错。日期时间类型如果仅仅是表示年份信息可以只使用YEAR类型这样更节省空间格式为“YYYY”例如“2022”。YEAR允许的值范围是1901~ 2155。YEAR还有格式为“YY”2位数字的形式值是00~ 69表示2000~ 2069年值是70~ 99表示1970~1999年从MySQL5.5.27开始2位格式的YEAR已经不推荐使用。YEAR默认格式就是“YYYY”没必要写成YEAR(4)从MySQL 8.0.19开始不推荐使用指定显示宽度的YEAR(4)数据类型。这个0年如果是以整数的0添加的话那么是0000年如果是以日期/字符串的’0’添加的话是2000年。如果要表示年月日可以使用DATE类型格式为“YYYY-MM-DD”例如“2022-02-04”。如果要表示时分秒可以使用TIME类型格式为“HH:MM:SS”例如“10:08:08”。如果要表示年月日时分秒的完整日期时间可以使用DATATIME类型格式为“YYYY-MM-DD HH:MM:SS”例如“2022-02-04 10:08:08”。如果需要经常插入或更新日期时间为系统日期时间则通常使用TIMESTAMP类型格式为“YYYY-MM-DD HH:MM:SS”例如“2022-02-04 10:08:08”。TIMESTAMP与DATETIME的区别在于TIMESTAMP的取值范围小只支持1970-01-01 00:00:01 UTC至2038-01-19 03:14:07 UTC范围的日期时间值其中UTC是世界标准时间并且TIMESTAMP类型的日期时间值在存储时会将当前时区的日期时间值转换为时间标准时间值检索时再转换回当前时区的日期时间值这会更友好。而DATETIME则只能反映出插入时当地的时区其他时区的人查看数据必然会有误差的。另外TIMESTAMP的属性受MySQL版本和服务器SQLMode的影响很大。drop tableifexists t_date;create table t_date(d1 datetime,d2 timestamp);insert into t_date values(2021-9-2 14:45:52,2021-9-2 14:45:52);select*fromt_date;--修改当前的时区settime_zone2:00;insert into t_date values(202192144552,202192144552);--格式错误 insert into t_date values(20210902144552,20210902144552);# 要么填充满也就是0 也写上insert into t_date values(202192 14%45%52,2021#9#2 144552);# 要么使用特殊符号select*fromt_date;drop tableifexists t_date;create table t_date(d year);insert into t_date values(2021),(85),(22),(69),(0),(0);select*fromt_date;总结一、数值类型1. 整数类型INT, TINYINT, SMALLINT, MEDIUMINT, BIGINTINT(M)中的M是显示宽度只有配合UNSIGNED ZEROFILL时才有实际意义。从MySQL 8.0.17 开始不推荐使用显示宽度INT(M)默认INT即可。UNSIGNED表示无符号非负数取值范围扩大一倍。ZEROFILL表示零填充不够位数时左边补 0。示例drop table if exists t_date;CREATE table t_date(d1 int(3));INSERT into t_date VALUES(12345),(12);select * from t_date;drop table if exists t_date_1;CREATE table t_date_1(d1 int(3) UNSIGNED ZEROFILL);INSERT into t_date_1 VALUES(12345),(12);select * from t_date_1;通过结果可以看出来这个M若是没有搭配UNSIGNED ZEROFILL是一点作用都没有的即使是搭配了UNSIGNED ZEROFILL当超过了设置的长度也是没有任何事情的不会报任何错误2. 浮点数与定点数浮点数FLOAT单精度、DOUBLE双精度定点数DECIMAL也叫 NUMERICM,D含义M精度总位数包括整数和小数D标度小数位数D ≤ M核心区别DECIMAL精确存储适合金额、财务等对精度要求高的场景。FLOAT/DOUBLE采用类似科学计数法存储范围更大但存在精度误差不适合存钱。示例CREATETABLEt_decimal(d1DECIMAL,-- 默认 (10,0)d2DECIMAL(5,2)-- 范围-999.99 ~ 999.99);INSERTINTOt_decimalVALUES(2.556,2.556);-- d2 会四舍五入为 2.563. BIT 类型用于存储位数据可指定 1~64 位。插入时可用二进制、十六进制或十进制。查询时默认以二进制形式显示可用BIN()、CONV()转为十进制查看。二、字符串类型1. 定长与变长字符串CHAR(M)固定长度最多 255 个字符。不够长度时右边补空格查询时自动删除尾部空格。这个M不是用来限制范围的只是用于不足位数填充零的VARCHAR(M)可变长度必须指定 M。实际占用空间 字符串长度 1或2字节用于存长度信息。选择建议MyISAM引擎推荐使用CHAR速度快。InnoDB引擎推荐使用VARCHAR节省空间。适用场景固定长度如手机号、身份证号、性别→ 用CHAR长度变化大如姓名、地址、评论→ 用VARCHAR2. ENUM枚举与 SET集合ENUM单选只能从列表中选一个值。内部用整数索引存储。SET多选可以从列表中选多个值用逗号分隔最多 64 个成员。示例CREATETABLEt_enum(genderENUM(男,女),hobbySET(睡觉,打游戏,运动,写代码));INSERTINTOt_enumVALUES(男,睡觉,打游戏);INSERTINTOt_enumVALUES(2,5);-- 2女5睡觉运动3. 文本类型TEXT和二进制类型BLOBTEXT存储大文本TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。BLOB存储二进制数据TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。三、日期时间类型类型格式范围特点YEARYYYY1901 ~ 2155只存年份DATEYYYY-MM-DD1000-01-01 ~ 9999-12-31只存日期TIMEHH:MM:SS-838:59:59 ~ 838:59:59只存时间DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:00 ~ 9999-12-31 23:59:59不自动转换时区TIMESTAMPYYYY-MM-DD HH:MM:SS1970-01-01 00:00:01 UTC ~ 2038-01-19自动转换时区推荐TIMESTAMP 与 DATETIME 的核心区别TIMESTAMP会根据当前时区进行转换检索时自动转回当前时区。DATETIME则只保存当时插入的字面值不进行时区转换。四、高频易错点总结以下是本章最容易出错的地方按重要程度排序INT(M) 的误解错误认为INT(2)只能存两位数。正确M仅在配合ZEROFILL时才有显示补零的作用本身不限制取值范围。浮点数精度问题使用FLOAT或DOUBLE存储金额、数量等需要精确计算的字段。经典错误0.1 0.2结果不是0.3。DECIMAL 不指定精度DECIMAL不写(M,D)默认是(10,0)小数部分会被直接四舍五入。CHAR 尾部空格被自动删除插入男 查询出来可能是男容易在字符串比较时出问题。VARCHAR(M) 中的 M 是字符数不是字节数在utf8mb4字符集下一个汉字占 3~4 字节要注意长度限制。ENUM 和 SET 插入错误ENUM 只能选一个值插入两个会失败。SET 用逗号分隔顺序不重要但不能插入未定义的值。TIMESTAMP 和 DATETIME 混淆没有理解TIMESTAMP会自动转换时区而DATETIME不会。修改时区后两者查询结果可能不同。日期格式不规范随意使用 # %等分隔符虽然部分能被识别但极易出错。推荐始终使用标准格式2021-09-02 14:45:52。BIT 类型查询困惑查询 BIT 类型默认显示为二进制容易误以为存的是十进制。YEAR 类型插入0和0的区别0→ 0000 年0→ 2000 年