整数类型
integer、int、smallint、tinyint、mediumint、bigint
这些种类主要是存储空间和取值范围不同,我就不详细写了
定点类型
decimal、numeric
主要是用来存储精确的数值类型,比如说货币,这俩类型大家可以认为是一样的,如声明一个decimal的精度为5,小位数为2,则为 colunm decimal(5,2),范围为 -999.99~999.99,如果小数点位数超过定义的位数,则会进行 四舍五入,例子如下
![](https://img-blog.csdnimg.cn/img_convert/11815c2ffdc040f49247e8dfa1f9846f.png)
![](https://img-blog.csdnimg.cn/img_convert/76e3a83c5e264cf8adb2528f6f46f1b1.png)
如果是整数超出范围则会报错:
![](https://img-blog.csdnimg.cn/img_convert/cc2706d30a1b4f0280de29b035ce2aaa.png)
但是执行的是非严格的SQL模式,整数部分超出范围则会变为范围内的边界值
![](https://img-blog.csdnimg.cn/img_convert/aaefc8d17fe5444095e9e3e65dcfde11.png)
而在执行非严格SQL模式,小数超出范围也只会进行四舍五入
![](https://img-blog.csdnimg.cn/img_convert/60903d29371946858cf8f49a6bcabd50.png)
把SQL模式改回标准模式
![](https://img-blog.csdnimg.cn/img_convert/4594a57bcdee4d0db27a9a96c62c2738.png)
浮点类型
float、double
float为单精准度,double为双精准度
float单精度小数部分只能精确到后面6位,加上小数点前的一位,即有效数字为7位
double双精度小数部分能精确到小数点后的15位,加上小数点前的一位 有效位数为16位。
最后就区别出了小数点后边位数的长度,越长越精确!
double 和 float 彼此的区别:
在内存中占有的字节数不同, 单精度内存占4个字节, 双精度内存占8个字节
有效数字位数不同(尾数) 单精度小数点后有效位数7位, 双精度小数点后有效位数16位
数值取值范围不同 根据IEEE标准来计算!
在程序中处理速度不同,一般来说,CPU处理单精度浮点数的速度比处理双精度浮点数快
double 和 float 彼此的优缺点:
float单精度
优点: float单精度在一些处理器上比double双精度更快而且只占用double双精度一半的空间
缺点: 但是当值很大或很小的时候,它将变得不精确。
double双精度
优点: double 跟 float比较, 必然是 double 精度高,尾数可以有 16 位,而 float 尾数精度只有 7 位
缺点: double 双精度是消耗内存的,并且是 float 单精度的两倍! ,double 的运算速度比 float 慢得多, 因为double 尾数比float 的尾数多, 所以计算起来必然是有开销的!
位值类型
bit(m),m取值为1~64位,存储的是二进制字符串
数值类型超出范围和溢出处理
超出范围:
当MySQL在存储数值类型时,超出允许的范围时,其结果取决于当时启动的SQL模式。
1.如果启动的是严格的SQL模式,那么在插入超出范围的值时,MySQL会拒绝插入并且报错,插入失败
![](https://img-blog.csdnimg.cn/img_convert/efbffc25e7b34b359be15a12fc6b116c.png)
2.如果没有启动限制模式,MySQL则会将列数据范围边界值作为值来存储,并发出警告
浮点型:
![](https://img-blog.csdnimg.cn/img_convert/d0cb9c10cfab457f95112ecb1538afe1.png)
整型:
![](https://img-blog.csdnimg.cn/img_convert/481adb696dff4994b0547a4a62e9d064.png)
溢出处理:
发生溢出的原因主要是超出数据类型的上限。
如:
![](https://img-blog.csdnimg.cn/img_convert/4e9d2339b5ca4db28a107a9ef9f0ee8f.png)
处理这种错误的一种修改方法就是修改数据类型,如改为浮点型,因为浮点类型的取值范围可要大的多了
日期和时间类型
datetime、date、timestamp、time、year
每种类型都有一个有效值范围和一个 "零"值,当不符合规则日期或者时间数据时,非严格模式下MySQL就会用零值替代(严格模式下会报错),我们也可以将SQL模式设置为no_zero_date,来禁止零值,事实上再某些情况下,这可比null值更为方便,并且使用了更少的数据和索引空间.
![](https://img-blog.csdnimg.cn/img_convert/f3816c3d126346188eda1adf35110198.png)
MySQL一共有五种标准的日期格式分别为:
1、“YYYY-MM-DD HH:MM:SS”格式;
2、“YYYYMMDDHHMMSS”格式;
3、 “YYYY-MM-DD”格式;
4、“HH:MM:SS”格式;
5、“YYYY”格式。
对于包含两位年份值的不确定日期,MySQL会使用一下规则进行解释
70-99范围会转为1970-1999
00-69范围会转为2000-2069
这几个类型主要是范围不一样。
timestamp和datetime 比较:
相同点:
两者都可用来表示YYYY-MM-DD HH:MM:SS 类型的日期。
不同点:
他们的的存储方式,大小(字节),表示的范围不同。
timestamp,它把客户端插入的时间从当前时区转化为UTC(世界标准时间)进行存储。查询时,将其又转化为客户端当前时区进行返回。
datetime,不做任何改变,基本上是原样输入和输出。
总结:timestamp和datetime 都可用来表示YYYY-MM-DD HH:MM:SS 类型的日期, 除了存储方式和存储范围以及大小不一样,没有太大区别。但对于跨时区的业务,timestamp更为合适。
字符串数据类型
char、varchar、text、binary、varbinary、blob、enum、set
对于char、varchar、text列,MySQL以字符(一个字符在utf8中就是一个汉字,这里只讨论mysql5.0以上,oracle不一样)为单位定义长度规范。
对于binary、varbinary、blob列,MySQL以字节(三个字节在utf8中是一个汉字)为单位定义长度规范。(一个字母=一个字符=一个字节)
当列定义为char、varchar、enum、set时,同时需要指定列的字符集,尤其是存储中文时,建议指定为utf-8,防止出现乱码。
char和varchar
char和varchar类型很相似,但是他们被存储和检索的方式有所不同,而且最大长度和是否保留尾随空格方面也不同。声明char和varchar时都需要指定存储最大字符数
![](https://img-blog.csdnimg.cn/img_convert/2e83086e4d28487c85591153f31f1ff1.png)
char类型存储值时,会用空格向右填充到指定长度,当设置SQL模式为pad_char_to_full_length时,char类型列被检索到的值,尾随的空格不会被删除
如下:
![](https://img-blog.csdnimg.cn/img_convert/dbe5f03070424c378d7ed82621ab4346.png)
varchar是可变长度的字符串,长度范围:0~65535,与char相比,varchar会存储一个字节或者两个字节用来保存长度,如果不超过255字节则长度字段为1个字节,如果内容长度超过255则长度字节使用2字节。
如果启用非严格SQL模式并且为char、varchar列分配超过定义的最大长度,则会被截断并生成警告信息
![](https://img-blog.csdnimg.cn/img_convert/f74c95a9f02d403bae132002c60a8070.png)
![](https://img-blog.csdnimg.cn/img_convert/c89a24c5b36c4ccb9fdb1b90a35f1e16.png)
如果是使用严格的SQL模式,则会提示错误信息并且禁止插入
![](https://img-blog.csdnimg.cn/img_convert/89d1a62a54684a85abd79d44f397a52f.png)
![](https://img-blog.csdnimg.cn/img_convert/4ec6590842c04d4d8171305dcae3d272.png)
但是对于超出定义长度的尾随空格在插入前都会被截断多余的尾随空格。
binary和varbinary
binary与varbinary很相似,但与char、varchar不同,他们是以字节为单位测量长度的,当用binary来存储时会向右填充到指定长度,填充值时0x00(0字节),并且没有为检索删除尾随字节的(与char相反),比如binary(3)插入'a'时,就会变为‘a\0\0',两个插入的值在检索时保持不变
![](https://img-blog.csdnimg.cn/img_convert/8bf812ee9b854b6aa016f147ce40c1c9.png)
![](https://img-blog.csdnimg.cn/img_convert/06d4efbdb0384999bef81e6868930157.png)
和binary类型不同的是,varbinary类型列的值是可变长度的字节串,再插入时不会使用0x00填充,查询时也不会丢弃任何字节,在进行列值比较时,所有的字节都是有效的,并且0x00<space(0x20)
blob和text
blob类型的值是一个二进制的大对象,可以容纳可变数量的数据。tinyblob、blob、mediumblob和longblob类型的区别仅在于最大存储长度不同。一般用来保存文件图片。
而text类型被视为非二进制字符串(就是我们平常理解的字符串),具有除二进制之外的字符集,并且根据字符集的排序规则对值进行排序和比较
他们的具体大小我这里就不追述了。
enum
枚举类型,表示一个枚举对象,其值选自表创建时列规范的枚举值。
特点:
在列具有有限的数据集合的情况下压缩数据空间。输入的字符串会自动编码为数字。
可读的查询和输出。在查询时,实际存储的数字被转换为相应的字符串。
注:枚举值必须是带引号的字符串
![](https://img-blog.csdnimg.cn/img_convert/db44183191a84a7f80c6247bbe54c2be.png)
![](https://img-blog.csdnimg.cn/img_convert/3158ca87bea74867b1f3900432634784.png)
注:再严格的SQL模式下,插入数据时只能插入枚举的内容,要不然会报错,在非严格SQL模式下,如果在 ENUM 列中插入无效值,MySQL 将使用带有数字索引 0 的空字符串 '' 进行插入
![](https://img-blog.csdnimg.cn/img_convert/bd5439e7fd884e5fbbec93504d05f09e.png)
![](https://img-blog.csdnimg.cn/img_convert/1fc400d8ceba4d2fb2ee13c81641bd1f.png)
使用非严格下的SQL模式下插入 ''(空字串):空字串的索引为0,严格情况会报错
![](https://img-blog.csdnimg.cn/img_convert/2a0249c0d2bf440a82b3837af465004e.png)
![](https://img-blog.csdnimg.cn/img_convert/fb19381795df475aaa9fad185ab16729.png)
在非严格SQL模式下,插入null值的索引为null,严格情况会报错
![](https://img-blog.csdnimg.cn/img_convert/3fe2ad797b4444e598dce694542be651.png)
更新、删除的时候也可以根据枚举的编号进行更新
![](https://img-blog.csdnimg.cn/img_convert/c60047aaddb943f9ba0ea0595a369a07.png)
使用枚举时需要注意以下几点:
容易混淆,enum类型的字段其在底层是用整型来存储的,如果enum里面存的是'3'、'2'、'1'这样的数字字符串,那么其对于的索引为1,2,3,不熟悉的开发人员就可能会混淆。
如果字段修改频繁,不介意使用enum类型,因为更改枚举成员需要使用ALTER TABLE 语句重建整个表,这在资源和时间方面都是昂贵的。
枚举列表是不可重用的。例如,上面表中的状态枚举值,不能重用到新建的其他表上。
enum最多有65535个不同的元素
如果要检索枚举编号,可以使用
select enum_col+0 from table_name;
![](https://img-blog.csdnimg.cn/img_convert/363949407c7b4184b18dbb2c7a4699f7.png)
set
set类型( 集合类型)的列值可以是零个或者多个字符串对象,一个set类型的列最多可以有64个不同的成员值,并且每个值必须在创建表时指定的列表中选择。
如set('a','b') not null 则列可以保存的值有:'','a','b','a,b'。
无论插入的值时的元素顺序是怎么样的,查询展示时都是根据创建表指定的顺序列出,而且不论插入多少次,该值的每个元素只会展示一次。(跟java里的set类型一样,不会重复)
JSON数据类型
MySQL也支持JSON类型的存储。
特点:
1.存储JSON类型的列中的JSON文档会被自动校验,无效的JSON类型格式插入,会报错
2.存储JSON类型的列中的JSON文档会被转换为快速读取文档元素的内部格式
3.在MySQL8以上,优化器可以执行JSON类型列的局部就地更新,而不用删除旧文档后在将新文档插 入该列。
在MySQL在JSON类型会当成字符串进行存储。
操作JSON的函数
json_type(), 返回对应的数据类型
![](https://img-blog.csdnimg.cn/img_convert/d069eede2f6243c69d4ebd1776eeffe2.png)
json_array()参数可以为空,返回参数值的json数组
![](https://img-blog.csdnimg.cn/img_convert/b396330608024749896801deb1e380f1.png)
json_object()参数可为空, 返回键值对的json对象
![](https://img-blog.csdnimg.cn/img_convert/7163261e560c47f5bed91e18d1f2620b.png)
json_merge_preserve(),获取两个或多个json文档并返回组合集合
![](https://img-blog.csdnimg.cn/img_convert/1ff4b5c23f54451c9495b1ea7210cdcb.png)
json_merge_patch(),返回多个json数据合并之后的对象
![](https://img-blog.csdnimg.cn/img_convert/b5f873a4a8a54588a2cd17cc078afb62.png)
json_extract(),提取json对象值
![](https://img-blog.csdnimg.cn/img_convert/cd1d880f7cc147f78ec1e85b3da6919c.png)
![](https://img-blog.csdnimg.cn/img_convert/21cf82420e674392a62f49fd36d6d4b0.png)
json_extract(),提取json数组值
![](https://img-blog.csdnimg.cn/img_convert/be27f79460a74a40ae20bcfac4071918.png)
![](https://img-blog.csdnimg.cn/img_convert/ef49530683754f669c3e3739d7f77420.png)
![](https://img-blog.csdnimg.cn/img_convert/ba3259f8bb8f4d368fa810dfafe75823.png)
json_replace(),替换值
![](https://img-blog.csdnimg.cn/img_convert/7c81b01905994bc49ddc42bffcb47de9.png)
![](https://img-blog.csdnimg.cn/img_convert/328d83ee536e49ce9f78cf054bca085d.png)
json_set()设置值(替换旧值,并插入不存在的值)
![](https://img-blog.csdnimg.cn/img_convert/1f94977da8d04fae91a9fd42d2c91ef3.png)
json_insert(),插入值(插入新值,但不替换已经存在的旧值)
![](https://img-blog.csdnimg.cn/img_convert/d6513fcabda44832aae7ca2abd093dfe.png)
如果插入的位置已存在,再插入的话就会插入失败!
json_remove(),删除json数据
![](https://img-blog.csdnimg.cn/img_convert/70a12e32e73e49838b80293d811ee00a.png)