[Mysql] 数据类型

常用数据类型面向对象 和 数据库的对应关系SQL 常用数据类型数值类型存储数字整数、小数例如年龄、价格、编号字符串类型存储文本姓名、地址、手机号二进制类型存放二进制数据图片、文件等原始字节日期类型存储时间日期创建时间、生日数据值类型数值类型重要概念1M 显示宽度整数类型后面的(M)只是显示宽度不限制存储范围搭配 zerofill 补零使用不影响能存数字大小2浮点数 FLOAT/DOUBLE存在精度丢失问题适合近似值金钱、财务计算必须用DECIMAL3BOOLMySQL 没有真正布尔类型底层就是 TINYINT (1)存 1/0整数类型取值范围有符号无符号1有符号一半存负数、一半存正数2无符号只能存非负数把原本负数的空间全部用来扩大正数上限3声明方式INT UNSIGNED面试常考点1INT 有符号最大值2147483647很多老项目 id 超过这个值就会溢出所以改用 BIGINT2TINYINT (1) 常用来做布尔标记0/1也就是前面看到的 BOOL 别名3INT(M)的 M 是显示宽度不限制存储范围和取值范围无关选型参考1状态、性别、开关TINYINT2小数量年份SMALLINT3普通自增 IDINT4雪花 ID、超大自增主键BIGINT字符串类型类型列表字符字符串存文本二进制类型存原始二进制如图片、文件区分TEXT 系列存文本字符有字符集BLOB 系列存原始二进制字节没有字符集枚举 ENUM / 集合 SET1ENUM (v1,v2,...) 枚举a只能选其中一个值还可以为空字符串、NULLb最多 65535 个选项c底层存储是整数节省空间2SET (v1,v2,...) 集合a可以多选0 个或多个b最多 64 个选项c底层存储也是整数易混考点1CHAR固定长适合长度统一的数据手机号、身份证VARCHAR 变长适合长短不一的内容简介2长度单位CHAR/VARCHAR 的 M 是字符BINARY/VARBINARY M 是字节3TEXT/BLOB 不能设置默认值并且不能有索引前缀面试常考字符串排序规则字符型列CHAR / VARCHAR / TEXT / ENUM / SET1以字符为单位处理2可以单独给字段指定字符集 排序规则 (collation)3示例字符集 utf8mb4排序规则 utf8_0900_ai_ci后缀含义ai accent insensitive不区分重音ci case insensitive不区分大小写cs case sensitive区分大小写二进制类型列BINARY / VARBINARY / BLOB1以字节为单位处理2常用 _bin 结尾的排序规则例如 utf8mb4_bin3_bin二进制比较直接按照字符的数字编码值比较大小a区分大小写A 和 a 编码不同b严格逐字节对比核心对比例子1utf8mb4_0900_ai_ciA 和 a 认为相等2utf8mb4_binA0x41和a0x61编码不一样判定不相等CHAR 和 VARCHAR 区别CHAR固定长度字符串1长度范围M单位是字符取值0~255不写 M 默认长度 12存储机制存入数据时在右侧补空格填满定义的固定长度 M读取数据时自动删除尾部空格3示例CHAR(4)存入ab ab 加一个空格存储时会补空格占满 4 字符查询取出时末尾空格会被删掉拿到ab4占用空间固定不管内容长短始终占用 M 个字符对应的字节例 latin1 字符集CHAR(4)存ab固定占用 4 字节VARCHAR可变长度字符串1长度范围M是最大字符长度所有列总长上限 65535 字节2存储机制不会补齐空格只存真实数据x 额外使用1~2 字节记录字符串真实长度x 真实字节 ≤2551 字节记录长度y 真实字节 2552 字节记录长度3空格特性读取数据保留尾部空格插入时如果超出最大长度会截断超长部分并告警例 latin1 字符集VARCHAR(4)存ab2 字符数据 1 字节长度信息一共 3 字节存储对比表字符集 latin1如何选择 CHAR / VARCHAR1CHARa字段长度固定手机号、身份证号、MD5 哈希编码等长度永远一样b数据很短长度几乎不变优点查询速度更快不需要计算长度直接按固定地址读取缺点数据短时浪费存储空间2VARCHARa字符串长度波动很大昵称、地址、备注、文章摘要等b大部分数据很短少数较长优点节省磁盘空间只占用实际数据大小缺点需要额外字节保存长度读取性能略低于 CHAR行长度变化会带来碎片问题核心一句话区分CHAR定长存的时候补空格读的时候删末尾空格空间固定查询快VARCHAR变长不补空格读的时候保留末尾空格空间按需分配节省存储如何选择 CHAR 与 VARCHARCHAR定长适用场景1数据长度固定选 CHAR身份证、MD5、学号、邮编2特点直接开辟固定大小存储空间浪费磁盘空间但查询效率高VARCHAR变长适用场景1数据长度变化不定选 VARCHAR名字、地址需要预估最大长度2特点不超过定义长度时用多少开辟多少空间节省磁盘效率偏低VARCHAR 与 TEXT 的区别容量大小1VARCHAR最大 65535 字节2TEXT最大 65535 字节a超过 65535 字节 → 自动转为 MEDIUMTEXT16,777,215 字节b超过 16,777,215 字节 → 自动转为LONGTEXT存储位置1VARCHAR内容768 字节数据存在当前行内容768 字节放到溢出页当前行只存溢出页地址2TEXT全部数据都放在溢出页当前行只保存溢出页地址查询性能 索引1VARCHAR支持普通索引频繁查询可以建索引性能更高2TEXT不能直接创建普通索引仅支持FULLTEXT全文索引性能比 VARCHAR 差适用场景1VARCHAR数据较短需要建立索引检索例如姓名、用户名、邮箱2TEXT数据量大不需要频繁拿该字段做查询条件例如文章正文、大段备注日期类型类型列表其他知识点fsp小数秒精度可选参数取值0--6fsp0代表无小数秒省略不写时默认精度 0时间函数同义词CURRENT_DATE/CURRENT_DATE()CURDATE()获取当前日期CURRENT_TIME/CURRENT_TIME([fsp])CURTIME()获取当前时间CURRENT_TIMESTAMP/CURRENT_TIMESTAMP([fsp])NOW()获取当前日期 时间高频考点区分1TIMESTAMP4 字节时间范围小2038 年问题会自动时区转换2DATETIME8 字节时间范围很大不受时区影响业务开发最常用