文章目录
1.数据类型分类
为什么要有这么多数据类型?
在应用场景和节省资源间趋于平衡
2.数值类型
1个字节8比特位无符号全0到全1,8个1是2^8-1
有符号10000000到011111111:2^ 7, 2^7-1
类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
TINYINT | 1 | -128 | 127 |
0 | 255 | ||
SMALLINT | 2 | -32768 | 32767 |
0 | 65535 | ||
MEDIUMINT | 3 | -8388608 | 8388607 |
0 | 16777215 | ||
INT | 4 | -2147483648 | 2147483647 |
0 | 4294967295 | ||
BIGINT | 8 | 9223372036854775808 | 9223372036854775807 |
0 | 18446744073709551615 |
2.1tinyint 1字节
在MySQL中,整型可以指定是有符号的和无符号的,默认是有符号的。
可以通过UNSIGNED来说明某个字段是无符号的
char= 1234567;//C/C++中允许大数据给小类型–截断–隐式转换
如果向mysql特定的类型中插入不合法的数据,MSQL一般都是直接拦截,不让我们做对应的操作!
反过来,如果我们已经有数据被成功插入到mysql中了,一定插入的时候是合法的!
所以,mysql中,一般而言,数据类型本身也是一种: 约束
保证数据库中的数据是可预期,完整的
限制程序员,让程序员尽可能进行正确的插入
约束的是使用者!
如果你不是一个很好的使用者,mysql也能保证数据插入的合法性
2.2bit 0-64位
bit[(M)] : 位字段类型。M表示每个值的位数,范围从1到64。如果M被忽略,默认为1。
bit字段在显示时,是按照ASCII码对应的值显示
2.3浮点类型
float 4个字节
float[(m, d)] [unsigned] : M指定显示长度,d指定小数位数,占用空间4个字节
float(4,2)表示的范围是-99.99 ~ 99.99,MySQL在保存值时会进行四舍五入。
是float(4,2) unsigned 这时,因为把它指定为无符号的数,范围是 0 ~ 99.99
decimal
decimal(m, d) [unsigned] : 定点数m指定长度,d表示小数点的位数
decimal(5,2) 表示的范围是 -999.99 ~ 999.99
decimal(5,2) unsigned 表示的范围 0 ~ 999.99
decimal和float很像,但是有区别: float和decimal表示的精度不一样
float表示的精度大约是7位。
decimal整数最大位数m为65省略为10。支持小数最大位数d是30省略为0。
建议:如果希望小数的精度高,推荐使用decimal。
3.字符串类型
char开多少空间为多大
char(L): 固定长度字符串,L是可以存储的长度,单位为字符,最大长度值可以为255
之前学的一个字符就是一个字节 mysql中一个字符就是一个符号!
char(2) 表示可以存放两个字符,可以是字母或汉字,但是不能超过2个, 最多只能是255
varchar开多少是上限 存多少占多大空间
varchar(L):可变长度字符串,L表示字符长度,最大长度65535个字节
关于varchar(len),len到底是多大,这个len值,和表的编码密切相关:
varchar字节长度可以指定为0到65535之间的值,但是有1 - 3 个字节用于记录数据大小,所以说有效字
节数是65532。
编码是utf8时,varchar(n)的参数n最大值是65532/3=21844[因为utf中,一个字符占用3个字节];
编码是gbk,varchar(n)的参数n最大是65532/2=32766(因为gbk中,一个字符占用2字节)。
实际上,如果该表中还有其他字段,最大也开不到21844,因为mysql有行大小限制。
了解行大小限制
这个错误 ERROR 1118 (42000): Row size too large 通常出现在使用 MySQL 数据库时,尤其是当你尝试在一个表中创建大量的列,或者某些列的数据类型占用了大量的空间,导致整行的总大小超过了 MySQL 允许的最大值(对于 InnoDB 存储引擎,默认的最大行大小限制是 65,535 字节,这包括了所有固定长度的列、可变长度的列(如 VARCHAR、VARBINARY)以及记录头信息)。
要解决这个问题,你可以采取以下几种策略:
将大字段转换为 TEXT 或 BLOB 类型:
如果你的表中包含了一些非常大的字段(如大文本或二进制数据),考虑将这些字段的类型从 VARCHAR、CHAR 等转换为 TEXT 或 BLOB。TEXT 和 BLOB 类型可以存储大量数据,并且不会计入最大行大小的限制中(但是请注意,它们仍然受到最大行长度限制的影响,只是以不同的方式计算)。
优化数据类型:
检查并优化你的数据类型。例如,如果某个字段存储的数值范围很小,但是使用了 INT 类型,你可以考虑将其更改为 SMALLINT 或 TINYINT。
对于 VARCHAR 类型,检查并设置合理的最大长度。如果某个 VARCHAR(255) 字段实际上从未存储超过 50 个字符的数据,那么你可以将其更改为 VARCHAR(50)。
分割表:
如果表中有大量列,并且业务逻辑允许,考虑将表拆分为多个较小的表。每个表包含相关的列,并通过外键或应用逻辑来维持它们之间的关系。
使用 ROW_FORMAT=DYNAMIC 或 COMPRESSED:
对于 InnoDB 存储引擎,你可以尝试更改表的行格式来允许更大的行大小。通过设置 ROW_FORMAT=DYNAMIC 或 ROW_FORMAT=COMPRESSED,你可以增加允许的行大小(注意,这仍然有一个上限,但比默认的 COMPACT 格式要大)。
你可以通过 ALTER TABLE 语句来更改表的行格式:
sql
ALTER TABLE your_table_name ROW_FORMAT=DYNAMIC;
检查和修改存储引擎:
如果你的应用不特别依赖于 InnoDB 的特性(如事务处理、行级锁定等),并且你可以接受其他限制,你可以考虑使用 MyISAM 存储引擎,它允许更大的行大小(但请注意,MyISAM 不支持事务处理或外键等)。
评估是否需要所有列:
评估你的应用是否真的需要表中的所有列。有时候,一些列可能不再需要,或者可以通过其他方式(如计算字段或查询时的临时计算)来获取。
通过上述方法,你应该能够解决 Row size too large 的错误,并优化你的数据库表结构。
如何选择定长或变长字符串?
数据确定长度都一样,使用定长(char),比如:身份证,手机号,md5
数据长度有变化,使用变长(varchar), 比如:名字,地址,但是保证最长的能存进去。
定长的磁盘空间比较浪费,但是效率高。
变长的磁盘空间比较节省,但是效率低。
定长的意义是,直接开辟好对应的空间
变长的意义是,在不超过自定义范围的情况下,用多少,开辟多少。
日期和时间类型
常用的日期有如下三个:
date :日期 ‘yyyy-mm-dd’ ,占用三字节
datetime 时间日期格式 ‘yyyy-mm-dd HH:ii:ss’ 表示范围从 1000 到 9999 ,占用八字节
timestamp :时间戳,从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致,占用四字节
enum和set
enum:枚举,“单选”类型;
enum(‘选项1’,‘选项2’,‘选项3’,…);
该设定只是提供了若干个选项的值,最终一个单元格中,实际只存储了其中一个值;而且出于效率考虑,这些值实际存储的是“数字”,因为这些选项的每个选项值依次对应如下数字:1,2,3,…最多65535个;当我们添加枚举值时,也可以添加对应的数字编号。
set:集合,“多选”类型;
set(‘选项值1’,‘选项值2’,‘选项值3’, …);
该设定只是提供了若干个选项的值,最终一个单元格中,设计可存储了其中任意多个值;而且出于效率考虑,这些值实际存储的是“数字”,因为这些选项的每个选项值依次对应如下数字:1,2,4,8,16,32,…最多64个。
说明:不建议在添加枚举值,集合值的时候采用数字的方式,因为不利于阅读。
NULL vs ’ ’
空表示什么都没有不存在;’ '表示存在为空
枚举和集合按数字插入的不同 枚举表示1~n的下标 集合表示位图
find_in_set(sub,str_list) :
如果 sub 在 str_list 中,则返回下标;如果不在,返回0;str_list 用逗号分隔的字符串。