MySQL 数据类型

老牛浏览 346评论 0发表于 8 年前

数据库优化：

减少数据冗余
尽量避免数据维护中出现更新，插入和删除异常
节约数据存储空间
提高查询效率

数据库表字段选择合适的数据类型：

当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期或二进制类型，最后是字符类型。
对于相同级别的数据类型，应该优先选择占用空间小的数据类型。

1. 整型

类型	长度	有符号表示范围	无符号表示范围
tinyint	1个字节	-128~127	0~255
smallint	2个字节	-32768~32767	0~65535
mediumint	3个字节	-8388608~8388607	0~16777215
int	4个字节	-21474836448~2148493647	0~4294967295
bigint	8个字节	-9223372036854775808~9223372036854775807	0~18446744073709551615

int(n) n 并不代表长度，和 zerofill 配合使用，当整数位数小于 n 时，前面的位数用 0 填充；当整数位数大于 n 时，这时是看不出效果的。

2. 浮点型

float(M,D) 单精度浮点数精确到约 7 位小数位（小数位超出 D 的四舍五入）
decimal(M,D) M最大为 65，支持的十进制数的最大位数 D 是 30。如果 D 被省略，默认是 0；如果 M 被省略，默认是 10。
float 和 decimal 表示的范围一致，如 float(4,2) 和 decimal(4,2) 表示范围均为 -99.99~99.99（unsigned 为 0~99.99），区别在于精度不一样，decimal 表示的精度更高。

3. char 和 varchar

char(n) 定长，n 表示字符数，1 个中/英文字符均为 1 个字符数，最大值为 255。如char(4)，即使插入「aa」，也会占用 4 个字符的空间。

varchar(n) 变长，n 表示字符数。如 varchar(4)，插入「aa」，实际占用空间为 L+1 [注：通 char 对比，varchar 值保存时只保存需要的字符数，另外加一个字节来记录长度（如果列声明的长度超过 255，则使用两个字节）]。

若 m 表示字节数，最大值为 65535，其中有 1~3 个字节用于记录数据大小，所以实际可用的为 65532 字节，可以存储的数据长度取决于所用的字符编码。如果为 utf8（一个字符占用 3 个字节），则最大可存放字符数为 65532/3=21844 个字符，即 n 不超过 21844；如果为 gbk（一个字符占用 2 个字节），则最大可存放字符数为 65532/2=32766 个字符，即 n 不超过 32766。还有一点要注意的是，每行记录总的字节数不能超过 65535，如果一个表 CREATE TABLE table1 charset=utf8; 该表会创建失败，原因是 id 占用了 4 个字节，所以 varchar 实际可以使用的最大字节为 65535-3-4=65528，最大字符数为 21842。

4. 日期数据

MySQL 中有多种表示日期和时间的数据类型。其中 YEAR 表示年份，DATE 表示日期，TIME 表示时间，DATETIM E 和 TIMESTAMP 表示日期和实践。它们的对比如下：

YEAR，字节数为 1，取值范围为「1901——2155」
DATE，字节数为 4，取值范围为「1000-01-01——9999-12-31」
TIME，字节数为 3，取值范围为「-838:59:59——838:59:59」
DATETIME，字节数为 8，取值范围为「1000-01-01 00:00:00——9999-12-31 23:59:59」
TIMESTAMP，字节数为 4，取值范围为「19700101080001——20380119111407」

当插入值超出有效取值范围时，系统会报错，并将零值插入到数据库中。

4.1 YEAR 类型

给 YEAR 类型赋值可以有三种方法。

第一种是直接插入 4 位字符串或者 4 位数字。
第二种是插入 2 位字符串，这种情况下如果插入 00~69，则相当于插入 2000~2069；如果插入 70~99，则相当于插入1970~1999。第二种情况下插入的如果是 0，则与插入 00 效果相同，都是表示 2000 年。
第三种是插入 2 位数字，它与第二种（插入两位字符串）不同之处仅在于：如果插入的是一位数字 0，则表示的是 0000，而不是 2000 年。所以在给 YEAR 类型赋值时，一定要分清 0 和 '0'，虽然两者相差个引号，但实际效果确实相差了 2000 年。