数据类型选择
完整性约束
-
完整性约束是为了保证数据库中数据的正确性和相容性,对关系模型提出的某种约束条件或规则。
-
通常包括:实体完整性约束、参照完整性约束、域完整性约束、用户自定义完整性约束。
- 实体完整性(Entity integrity)是指主键必须非空唯一
- 参照完整性(Referential Integrity)也就是外键约束
- 域完整性是保证数据库字段取值的合理性,一般包括检查(CHECK)、默认值(DEFAULT)、不为空(NOT NULL)、外键(FOREIGN KEY)等约束。
delete,drop,truncate 都有删除表的作用,区别在于:
- delete 和 truncate 仅仅删除表数据,drop 连表数据和表结构一起删除
- delete 是 DML 语句,操作完以后如果没有不想提交事务还可以回滚,truncate 和 drop 是 DDL 语句,操作完马上生效,不能回滚
- 执行的速度上,drop>truncate>delete
between和in的区别
-
between是min和max范围内的,包括min和max,是数字型的
-
in是离散数据,between是连续值(1到2之间会有无数个值)
like 模糊查询,”_“代表一个字符,”%“代表多个字符
left、right、full、inner
- left、right 简单的说就是最终结果以谁为主
- full join全连接则是保留所有的查询记录,没有的对应位置则为空。
- inner join,只保留所有查询都有结果的记录,其它都丢弃。
数据类型的选择:
- 尽量使用可以正确存储数据的最小数据类型,因为更小的数据类型,占用的资源也更少,处理的速度也更快,例如:整型比字符串操作代价更低
- 避免使用null,可以给null默认值,除非真的需要存储null值
- 数字类型分为:整数和实数
- 整数例如:tinyint、smallint、mediumint、int、bigint,还能使用unsigned,来禁止负数
- 实数是指带有小数部分的数字,例如float、double、decimal
- 字符类型:Varchar和char
- BLOB和TEXT类型
- 枚举类型
- 日期类型:date和datetime、timestamp
- ip:ip实际是32位无符号整数,不是字符串,用小数点分成四段只是为了方便阅读,mysql提供了专门转换ip的函数
mysql不要设计过多的列和过多的关联,也不要过渡使用枚举,避免使用null
char和varchar区别
- 最大长度:char最大长度是255字符,varchar最大长度是65535个字节,也就是能存储21,845个字符,但是因为需要用2两个字节记录变长字段的长度,和1个字节来记录null值的标识,所以其实最大可以存放65532个字节
- char是定长的,不足的部分用隐藏空格填充,varchar是不定长的。
- 用varchar(5)和varchar(200)来存储“hello”的空间开销是一样的,但是更长的列会消耗更多的内存,mysql通常会分配固定大小的内存块来保存内部值,尤其是使用内存临时表进行排序或其他操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕,所以最好的策略是按需分配
- char会浪费空间但查找效率会很高,varchar会更加节省空间但是查找效率会更低
- 对于尾部空格,char插入时可省略,varchar插入时不会省略,查找时省略
- char适合长度等长的字符串,varchar适合最大长度比平均长度大很多的
日期类型:date和datetime、timestamp 的区别:
-
date保存精度到天,格式为:YYYY-MM-DD,如2016-11-07
-
datetime和timestamp精度保存到秒,格式为:YYYY-MM-DD HH:MM:SS
-
timestamp 储存占用4个字节,更轻量,索引相对datetime更快,记录的时间会跟随设置的时区变化而变化;
-
而datetime 储存占用8个字节,保存的是绝对值不会变化,如果应用场景有跨时区要求的要特别注意这点。
-
timestamp可表示范围:1970-01-01 00:00:00~2038-01-09 03:14:07,datetime支持的范围更宽1000-01-01 00:00:00 ~ 9999-12-31 23:59:59。
枚举类型:
- 尽量少使用数字作为枚举常量,很容易混乱
- 枚举其实就是 “数字-字符串”,可以有效的节省空间,但是每次查找也需要额外的转换,不过这个开销比较小
- 枚举字段是按照内部存储的整数来排序的,而不是根据字符串排序,可以显式的使用FIELD()来指定排序
- 枚举最不好的地方是字符串列表是固定的,添加或者删除都需要使用ALTER TABLE操作,如果是可能会改变的字符串不建议使用枚举
set和enum类型的用法和区别
- 都只能在固定值中选择,可以在数据库层面限制非法值。
- set可以取多个值,enum只能取一个值
BLOB和TEXT类型
- 两者都是为了存储很大的数据而设计的字符串数据类型,区别是分别采用二进制和字符方式存储
- TEXT: tinytext、smalltext、text、mediumtext、longtext,BLOB类似
- 每个BLOB或者TEXT都会被当作一个独立的对象处理,值太大时,innodb还会再外部存储,行内只存地址
- memory引擎不支持这两种类型,如果查询使用了BLOB或者TEXT列,并且需要使用隐式临时表,临时表会是MYISAM引擎的,这样会导致严重的性能开销,最好的办法就是尽量少用这两种类型