【类型转换】Mysql数据类型转换规则
- <=>相对于=增加了null值的比较。
MySQL类型转换规则:
- 两个参数至少有一个是NULL时,比较的结果也是 NULL,例外是使用 <=> 对两个NULL做比较时会返回1,这两种情况都不需要做类型转换
- 两个参数都是字符串,会按照字符串来比较(比较ascii),不做类型转换
- 两个参数都是整数,按照整数来比较,不做类型转换
- 十六进制的值和非数字做比较时,会被当做二进制串
- 有一个参数是TIMESTAMP或DATETIME,并且另外一个参数是常量,常量会被转换为timestamp
- 所有其他情况下,两个参数都会被转换为浮点数再进行比较
如果字符串以数字开头,在转换时截取前面的数字进行转换,如果字符串不是以数字开头,那么被转换成0。
当索引是int型的,然后用索引条件是字符串去查询,索引可以用到,
但是如果索引的列是varchar,用int去查就不能用到索引- (索引字段的隐式类型转换会使索引失效)
varchar(N)
- 在 MySQL InnoDB 中,系统默认单个索引长度最大为 767 bytes,所以作为索引列的话,不要超过767bytes(utf8的255,utf8mb4的191)
为什么utf8mb4不能varchar(65535)而最大只能varchar(16383)呢?
- latin1是单字节编码
- Latin1在ASCII基础上又充分利用了后面那128个值,赋予他们一些泰语、希腊语等字母或符号,将1个字节的256个值全部占满了【所以可以存任何数据,只要读取后按指定编码还原即可】
因为变长列表长度 中使用两个字节(16位)表示一个字段的长度。(内容较小时用1个字节表示长度)
2的16次方= 65536 字节(一个varchar字段占用的字节数)
当使用utf8mb4(mb4就是most bytes 4)编码时 65535 / 4 = 16383.75(字符数(极端情况是所有字符都是4字节))
varchar的临界点
- 超过767字节无法加索引(utf8的255,utf8mb4的191)
- 超过8098字节,会行溢出
- 这里的16384是指页的大小16k(注意不是行的大小)
都用varchar(255)行不行
mysql - Are there disadvantages to using a generic varchar(255) for all text-based fields? - Stack Overflow
VARCHAR fields are converted to CHAR to gain the advantage of working with fixed-width rows. So the strings in memory become padded out to the maximum length of your declared VARCHAR column.
- 这里的16384是指页的大小16k(注意不是行的大小)
a VARCHAR(255) in utf8 storing a short string like “No opinion“ takes 11 bytes on disk (ten lower-charset characters, plus one byte for length) but it takes 765 bytes in memory
从硬盘加载到内存中,MEMORY引擎对于VARCHAR等变长类型实际上是作为定长来分配内存的。
硬盘中使用11bytes,内存中使用765bytes
- 索引最大内容长度767
- MEMORY引擎对于varchar按定长分配内存。
mysql 5.7 中 varchar 的长度直接定义成255 还是按需定义呢? - 知乎
varchar(N)和char(N)
- varchar(N)中的N表示字符的数量
- char(N)中的N表示字节数量
char(1)-性别字段
- 使用int语义不明
订单状态字段
修改字符编码
- alter table t charset=utf8mb4 不修改已有的编码
- alter table t convert to character set utf8mb4 修改所有字段编码
Bit(N) 和 TinyInt(N)区别
tinyint(n)和int一样 n代表的显示位数。实际占用字节数是确定的。
BIT(m)可以存储多达m位的值,m的范围在1到64之间。
Bit的M真代表存储大小。
What is the difference between BIT and TINYINT in MySQL? - Stack Overflow
enum-Mysql不建议使用ENUM类型
bigint-金额使用BIG INT,不用DECIMAL
- DECIMAL通过二进制实现的一种编码,计算效率不如int
- BIG INT能存储千兆金额,以分为单位
inet_aton存ip地址
create table jackbillow (ip int unsigned, name char(1));
插入:insert into jackbillow values(inet_aton(‘192.168.1.200’), ‘A’), 查询: select * from jackbillow where ip = inet_aton(‘192.168.1.200’);
利用mysql的inet_aton()和inet_ntoa()函数存储IP地址的方法分享 - 简书
日期类型
- TIMESTAMP带有时区
- TIMESTAMP占用4个字节,精确到毫秒占用7个字节
- DATETIME占用8个字节
now() & sysdate()
JSON数据类型 5.7
- 5.7开始支持
- 8.0解决JSON的日志性能瓶颈,推荐使用8.0
利用虚拟列索引Json字段
g INT GENERATED ALWAYS AS (c->”$.id”), 建立单独索引方便搜索json
MySQL · 最佳实践 · 如何索引JSON字段JSON类型应用场景
商品详情?
登录场景
json_extract()
用户画像
MEMBER OF
JSON_CONTAINS:或
JSON_OVERLAPS:且?
- Json_contains 和 json_overlaps 之间的区别是,一个包含数组中的值某即可,另一个不行,必须是你查询的数组的值都包含才可以。