数据类型
数值类型
类型大小范围(有符号)范围(无符号)用途
TINYINT
1 byte
(-128,127)
(0,255)
小整数值
SMALLINT
2 bytes
(-32 768,32 767)
(0,65 535)
大整数值
MEDIUMINT
3 bytes
(-8 388 608,8 388 607)
(0,16 777 215)
大整数值
INT或INTEGER
4 bytes
(-2 147 483 648,2 147 483 647)
(0,4 294 967 295)
大整数值
BIGINT
8 bytes
(-9,223,372,036,854,775,808,9 223 372 036 854 775 807)
(0,18 446 744 073 709 551 615)
极大整数值
FLOAT
4 bytes
(-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38)
0,(1.175 494 351 E-38,3.402 823 466 E+38)
单精度
浮点数值
DOUBLE
8 bytes
(-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)
0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)
双精度
浮点数值
DECIMAL
对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2
依赖于M和D的值
依赖于M和D的值
小数值
注意:长度:可以为整数类型指定宽度,例如:int(11),对大多数应用是没有意义的,它不会限制值的合法范围,只会影响显示字符的个数
日期和时间类型
类型大小
( bytes)范围格式用途
DATE
3
1000-01-01/9999-12-31
YYYY-MM-DD
日期值
TIME
3
'-838:59:59'/'838:59:59'
HH:MM:SS
时间值或持续时间
YEAR
1
1901/2155
YYYY
年份值
DATETIME
8
1000-01-01 00:00:00/9999-12-31 23:59:59
YYYY-MM-DD HH:MM:SS
混合日期和时间值
TIMESTAMP
4
1970-01-01 00:00:00/2038
结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07
YYYYMMDD HHMMSS
混合日期和时间值,时间戳
字符串类型
类型大小用途
CHAR
0-255 bytes
定长字符串
VARCHAR
0-65535 bytes
变长字符串
TINYBLOB
0-255 bytes
不超过 255 个字符的二进制字符串
TINYTEXT
0-255 bytes
短文本字符串
BLOB
0-65 535 bytes
二进制形式的长文本数据
TEXT
0-65 535 bytes
长文本数据
MEDIUMBLOB
0-16 777 215 bytes
二进制形式的中等长度文本数据
MEDIUMTEXT
0-16 777 215 bytes
中等长度文本数据
LONGBLOB
0-4 294 967 295 bytes
二进制形式的极大文本数据
LONGTEXT
0-4 294 967 295 bytes
极大文本数据
注意:
varchar使用1个或者2个额外字节记录字符串的长度,列长度小于255字节,使用1个字节表示,否则用2个
char适合存储很短的字符串,或者所有的值都接近同一个长度
char长度,超出设定的长度,会被截断
对于经常变更的数据,char比varchar更好,char不容易产生碎片
对于非常短的列,char比varchar在存储空间上更有效率
只分配真正需要的空间,更长的列会消耗更多短的内存
尽量避免使用blob/text类型,查询会使用临时表,导致严重的性能开销
枚举
有时可以使用枚举代替常用的字符串类型
把不重复的集合存储成一个预定义的集合
非常紧凑,把列表值压缩成一个或两个字节
内部存储的是整数
尽量避免使用数字作为ENUM枚举的常量,易混乱
排序是按照内部存储的整数进行排序
枚举表会使表大小大大减小
日期和时间类型
尽量使用timestamp,比datetime空间效率高
用整数保存时间戳的格式通常不方便处理,可以使用timestamp
如果需要存储到微秒,可以使用bigint存储
列属性
auto_increment、default、not null、zerofill
MySQL数据表引擎
InnoDB表引擎
默认事务性引擎,最重要最广泛的存储引擎,性能非常优秀
存储在共享表空间,可以通过配置分开
对主键查询的性能高于其他类型的存储引擎
内部做了很多优化,从磁盘读取数据时自动在内存构建hash索引,插入数据时自动构建插入缓冲区
通过一些机制和工具支持真正的热备份
支持奔溃后的安全恢复
支持行级锁
支持外键
MyISAM表引擎
5.1版本以前,myisam是默认的存储引擎
拥有全文索引、压缩、空间函数
不支持事务和行级锁,不支持奔溃后的安全恢复
表存储在两个文件,MYD和MYI
设计简单,某些场景下性能很好
其他表引擎
Archive、Blackhole、CSV、Memory
在使用过程中,优先使用InnoDB
MySQL锁机制
基础概念
表锁是日常开发中常见的问题,因此也是面试当中最常见的考察点,当多个查询同一时刻进行数据修改时,就会产生并发控制的问题
共享锁和排他锁,其实就是读锁和写锁
读锁:共享的,不堵塞,多个用户可以同时,读一个资源,互不干扰
写锁:排他的,一个写锁会阻塞其他的写锁和读锁,这样可以只允许一个人进行写入,防止其他用户读取和正在写入的资源
锁粒度:表锁,系统性能开销最小,会锁定整张表,MyISAM使用表锁
InnoDB实现行级锁
事务处理:
MySQL提供事务处理的表引擎:InnoDB
服务器层不管理事务,由下层的引擎实现,所以同一个事务中,使用多种存储引擎不靠谱
在非事务到的表上执行事务操作MySQL不会发出提醒,也不会报错
MySQL存储过程
存储过程:
为以后的使用而保存的一条或者多条MySQL语句的集合
存储过程就是有业务逻辑和流程的集合
可以在存储过程中创建表,更新数据,删除等等
使用场景:
通过把处理封装在容易使用的单元中,简化复杂的操作
保证数据的一致性
简化对变动的管理
MySQL触发器
触发器:提供给程序员和数据分析来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程
使用场景:
可通过数据库中的相关表实现级联更改
实时监控某张表中的某个字段的更改而需要作出相应的处理
某些业务编号的生成等
滥用会造成数据库及应用程序的维护困难