MySQL -SQL语言3-数据类型
数据类型:
- 系统内置数据类型
- 用户定义数据类型
MySQL支持多种内置数据类型
- 数值类型
- 日期/时间类型
- 字符串(字符)类型
选择正确的数据类型对于获得高性能至关重要,三大原则:
- 更小的通常更好,尽量使用可正确存储数据的最小数据类型
- 简单就好,简单数据类型的操作通常需要更少的CPU周期
- 尽量避免NULL,包含为NULL的列,对MySQL更难优化
整数型
- tinyint(m) 1个字节 范围(-128~127)
- smallint(m) 2个字节 范围(-32768~32767)
- mediumint(m) 3个字节 范围(-8388608~8388607)
- int(m) 4个字节 范围(-2147483648~2147483647)
- bigint(m) 8个字节 范围(+-9.22*10的18次方)
上述数据类型,如果加修饰符unsigned后,则最大值翻倍
如:tinyint unsigned的取值范围为(0~255)
int(m)里的m是表示SELECT查询结果集中的显示宽度,并不影响实际的取值范围,规定了MySQL的一些 交互工具(例如MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说,Int(1)和Int(20)是 相同的
BOOL,BOOLEAN:布尔型,是TINYINT(1)的同义词。zero值被视为假,非zero值视为真
浮点型(float和double),近似值
- float(m,d) 单精度浮点型 8位精度(4字节) m总个数,d小数位, 注意: 小数点不占用总个数
- double(m,d) 双精度浮点型16位精度(8字节) m总个数,d小数位, 注意: 小数点不占用总个数
- 设一个字段定义为float(6,3),如果插入一个数123.45678,实际数据库里存的是123.457,但总个数还以 实际为准,即6位
定点数
- 在数据库中存放的是精确值,存为十进制
- 格式 decimal(m,d) 表示 最多 m 位数字,其中 d 个小数,小数点不算在长度内
比如: DECIMAL(6,2) 总共能存6位数字,末尾2位是小数,字段最大值 9999.99 (小数点不算在长度内)
参数m<65 是总个数,d<30且 d<m 是小数位
- MySQL5.0和更高版本将数字打包保存到一个二进制字符串中(每4个字节存9个数字)。
例如: decimal(18,9)小数点两边将各存储9个数字,一共使用9个字节:其中,小数点前的9个数字用4个 字节,小数点后的9个数字用4个字节,小数点本身占1个字节
- 浮点类型在存储同样范围的值时,通常比decimal使用更少的空间。float使用4个字节存储。double占用 8个字节
因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用decimal,例如存储 财务数据。但在数据量比较大的时候,可以考虑使用bigint代替decimal
字符串(char,varchar,text)
- char(n) 固定长度,最多255个字符,注意不是字节
- varchar(n) 可变长度,最多65535个字符
- tinytext 可变长度,最多255个字符
- text 可变长度,最多65535个字符
- mediumtext 可变长度,最多2的24次方-1个字符
- longtext 可变长度,最多2的32次方-1个字符
- BINARY(M) 固定长度,可存二进制或字符,长度为0-M字节
- VARBINARY(M) 可变长度,可存二进制或字符,允许长度为0-M字节
内建类型:ENUM枚举, SET集合
char和varchar的比较:
- char(n) 若存入字符数小于n,则以空格补于其后,查询之时再将空格去掉,所以char类型存储的字符 串末尾不能有空格,varchar不限于此
- char(n) 固定长度,char(4)不管是存入几个字符,都将占用4个字节,varchar是存入的实际字符数+1 个字节(n< n>255),所以varchar(4),存入3个字符将占用4个字节
- char类型的字符串检索速度要比varchar类型的快
varchar 和 text:
- varchar可指定n,text不能指定,内部存储varchar是存入的实际字符数+1个字节(n< n>255),text 是实际字符数+2个字节。
- text类型不能有默认值
- varchar可直接创建索引,text创建索引要指定前多少个字符。varchar查询速度快于text
VARCHAR(50) 能存放几个 UTF8 编码的汉字?
存放的汉字个数与版本相关。 mysql 4.0以下版本,varchar(50) 指的是 50 字节,如果存放 UTF8 格式编码的汉字时(每个汉字3字 节),只能存放16 个。 mysql 5.0以上版本,varchar(50) 指的是 50 字符,无论存放的是数字、字母还是 UTF8 编码的汉字, 都可以存放 50 个。
二进制数据BLOB
- BLOB和text存储方式不同,TEXT以文本方式存储,英文存储区分大小写,而Blob以二进制方式存储, 不分大小写
- BLOB存储的数据只能整体读出
- TEXT可以指定字符集,BLOB不用指定字符集
日期时间类型
- date 日期 '2008-12-2'
- time 时间 '12:25:36'
- datetime 日期时间 '2008-12-2 22:06:44'
- timestamp 自动存储记录修改时间
- YEAR(2), YEAR(4):年份
timestamp 此字段里的时间数据会随其他字段修改的时候自动刷新,这个数据类型的字段可以存放这 条记录最后被修改的时间
修饰符
适用所有类型的修饰符:
- NULL 数据列可包含NULL值,默认值
- NOT NULL 数据列不允许包含NULL值,相当于网站注册表中的 * 为必填选项
- DEFAULT 默认值
- PRIMARY KEY 主键,所有记录中此字段的值不能重复,且不能为NULL
- UNIQUE KEY 唯一键,所有记录中此字段的值不能重复,但可以为NULL
- CHARACTER SET name 指定一个字符集
适用数值型的修饰符:
- AUTO_INCREMENT 自动递增,适用于整数类型, 必须作用于某个 key 的字段,比如primary key
- UNSIGNED 无符号