关于MySql字段类型的实践总结

2023-12-28 17:41:09

当字段为数值类型时应使用无符号UNSIGNED修饰

ALTER TABLE `info`
    MODIFY COLUMN `user_id` int UNSIGNED NOT NULL;

当字段为varchar类型时应注意是否选择合适的字符集

????????例如存储一些范围值,数字+英文字符时(IP、生日、客户端标识等或以“,”分隔的数据),可以选择使用asciiascii_general_ci 编码,内部空间占用更小。

巧用MySQL位运算,存储关联数据(tinyint、smallint、int、bigint)

? ? ? ? 我们知道MySQL是支持位运算的,因此可以更好的利用int等数值类型的存储空间。

????????例如:IP监控功能中,关联多个处罚的枚举类型字段、用户关联的多客户端字段,可以选择使用tinyint(1字节8位)、smallint(2字节16位)、int(4字节32位)、bigint(8字节64位),而不是以‘,’分隔的“1,2,3”。

????????具体字段的类型应该根据类型的数量选择,例如:客户端包括{PC、WAP、Android、IOS、Wechat、WechatMP} 6种类型,可以选择tinyint预留两个拓展类型位置,也可以选择smallint预留10个拓展类型位置。

此种存储方式如何写查询SQL?

????????首先,通过枚举的ordinal(是枚举的下标可以通过java.lang.Enum实现)可以计算类型对应的二进制数据形式(以tinyint为例):

PC		    0	00000001
WAP		    1	00000010
Android	    2	00000100
IOS		    3	00001000
Wechat	    4	00010000
WechatMP    5	00100000
	public static void main(String[] args) {
		int PC = 1 << 0, WAP = 1 << 1, Android = 1 << 2, IOS = 1 << 3, Wechat = 1 << 4, WechatMP = 1 << 5;
		System.out.println("PC = " + Integer.toBinaryString(PC));
		System.out.println("WAP = " + Integer.toBinaryString(WAP));
		System.out.println("Android = " + Integer.toBinaryString(Android));
		System.out.println("IOS = " + Integer.toBinaryString(IOS));
		System.out.println("Wechat = " + Integer.toBinaryString(Wechat));
		System.out.println("WechatMP = " + Integer.toBinaryString(WechatMP));
		// 输出:
		// PC = 1
		// WAP = 10
		// Android = 100
		// IOS = 1000
		// Wechat = 10000
		// WechatMP = 100000
	}

????????通常查询包含两种类型,匹配多类型中的一个(查询在PC或IOS登录过的用户)和同时包含多类型(查询在PC和IOS登录过的用户)

????????假设存在type=62=00111110的记录,它表示这位用户在WAP、Android、IOS、Wechat、WechatMP登录过。

1、合并不同类型二进制值

		System.out.println("合并PC和IOS:" + Integer.toBinaryString(PC |= IOS) + "\t十进制:" + (PC |= IOS));
		System.out.println("合并PC、Android、IOS(合并顺序不印象结果):" + Integer.toBinaryString(PC |= IOS |= Android) + "\t十进制:" + (PC |= IOS |= Android));
		// 输出:
		// 合并PC和IOS:1001	十进制:9
		// 合并PC、Android、IOS(合并顺序不印象结果):1101	十进制:13

2、SQL匹配多类型中的一个 = 进行&运算求交集,存在交集

where (type & $合并结果) > 0

-- 1101不足8为补零:00001101,条件00111110 & 00001101 > 0,大于零表示存在交集,计算过程:
-- 00111110
-- 00001101
-- 00001100(交集)

3、SQL同时包含多类型 = 进行&运算求交集,交集等于给定值

where (type & $合并结果) = $合并结果

-- 1101不足8为补零:00001101,条件00111110 & 00001101 = 00001101,大于零表示存在交集,计算过程:
-- 00111110
-- 00001101
-- 00001100(交集)

????????结果可想而知,00001100不等于00001101,因此无法匹配这条记录。那么现在可以假设存在type=61=00111101记录,表示用户在PC、Android、IOS、Wechat、WechatMP登录过,此时计算过程:

-- 00111101 & 00001101 = 00001101,大于零表示存在交集,计算过程:
-- 00111101
-- 00001101
-- 00001101(交集)

????????此时结果等于00001101,命中数据行。

????????此种数据存储方案存在问题:mysql数据可读性降低,需要一定理解能力,且对前端输出时应做响应的格式转换

????????例如:前端识别类型(PC、WAP、Android、IOS、Wechat、WechatMP)可能是0、1、2、3、4、5,那么从数据库读取到的62=00111110,则应该进行解析,解析的过程就是验证每个类型是否包含在00111110中,也就是 00111110&$类型=$类型 是否成立。
????????而具体的实践,我们可以封装成枚举(enum),枚举实体应该有一个ordinal属性提供给前端使用,如果你有兴趣可以研究下如何基于jdk的java.lang.Enum进行实现,其内部已提供ordinal属性,后续有时间会出一个更详细的MySql与枚举的自动映射实践总结。

文章来源:https://blog.csdn.net/weixin_38652136/article/details/135273616
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。