mysql数据类型学习笔记

数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型	大小	范围（有符号）	范围（无符号）	用途
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的值	小数值

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小 ( 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、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	大小	用途
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	极大文本数据

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

MySQL 5.0 以上的版本：

1、一个汉字占多少长度与编码有关：

UTF－8：一个汉字＝3个字节

GBK：一个汉字＝2个字节

2、varchar(n) 表示 n 个字符，无论汉字和英文，Mysql 都能存入 n 个字符，仅是实际字节长度有所区别

3、MySQL 检查长度，可用 SQL 语言来查看：

关于 char、varchar 与 text 平时没有太在意，一般来说，可能现在大家都是用 varchar。但是当要存储的内容比较大时，究竟是选择 varchar 还是 text 呢？

这三种类型比较：

· （1）char:  char 不用多说了，它是定长格式的，但是长度范围是 0~255. 当你想要储存一个长度不足 255 的字符时，Mysql 会用空格来填充剩下的字符。因此在读取数据时，char 类型的数据要进行处理，把后面的空格去除。

· （2）varchar:  关于 varchar，有的说最大长度是 255，也有的说是 65535，查阅很多资料后发现是这样的：varchar 类型在 5.0.3 以下的版本中的最大长度限制为 255，而在 5.0.3 及以上的版本中，varchar 数据类型的长度支持到了 65535，也就是说可以存放 65532 个字节（注意是字节而不是字符！！！）的数据（起始位和结束位占去了3个字节），也就是说，在 5.0.3 以下版本中需要使用固定的 TEXT 或 BLOB 格式存放的数据可以在高版本中使用可变长的 varchar 来存放，这样就能有效的减少数据库文件的大小。如果表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储，会很浪费空间。需要使用1或2个额外字节记录字符长度，最大长度小于或等于255字节，则只用1个字节，否则用2个字节。第一个字节是空的，不存在任何数据，最大有效长度由最大行大小和使用的字符集确定。例如：create table t1(c1 int ,c2 char(30), c3 varchar(n)) default charset=utf-8 n最大=（65535-1-2-4-30*3）/3

· （3）text: 与 char 和 varchar 不同的是，text 不可以有默认值，其最大长度是 2 的 16 次方-1

· （4）decimal (18,9）指小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后数字用4个字节，小数点本身占1个字节。最多允许65个数字，只是一种存储格式，实际计算会转换为double类型

· （5）binary存储的是字节码而不是字符，比较binary 字符串时，每次按一个字节，根据字节的数值进行比较，因此二进制字符比较简单很多，所以就更快。

· （6）当blob和text值太大时，使用专门的外部存储区域进行存储，此时每个值在行内需要1-4个字节存储一个指针，然后在外部存储区域存储实际值。排序只对每个我的max_sort_length字节而不是整个字符串做排序，或者使用order by substring(column,length)

· （7）blob是smallblob的同义词，text是smalltext的同义词

如果存储uuid值，则应该移除'-'符号。

ipv4地址：inet_aton()将ip转换数据，inet_ntoa()将数据字转地址，32位无符号整数。

enum和set

适合存储固定信息，例如有序状态，产品类型，人的性别。mysql在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或两个字节中，mysql内部会将每个值在列表中的位置保存为整数。并且在.frm文件中保存数字-字符串映射关系的"查找表"。枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。

 

在查询中使用field()函数显式地指定排序顺序，会导致mysql无法利用索引消除排序。例如

select e from t order by field(e,'c','d','a');

枚举最不好的是字符串列表是固定的，添加或是删除必须使用alter，这将导致表重建。

 

 

timestamp和datetime

timestamp占4字节，datetime占8字节，date占4字节，time占3字节，year占1字节。timestamp的取值范围是1970到2038年的某一天。datetime从1001到9999年精度为秒。datetime(3)3表示毫秒位数。

mysql提供了from_unixtime()函数把unix时间戳转换为日期，并提供unix_timestamp函数把日期转换为unix时间戳。可以使用bigint类型存储微秒级别的时间戳。

mysql中只给表中第一个timestamp字段设置默认系统日期，如果有第二个timestamp类型，则默认为0；可以修改第二个timestamp字段默认值为其他常量日期，但是不能再修改为current_timestamp;

timestamp和时区有关，插入数据时，会先转换为本地时区后存入。而从数据库里取出来时，也同样需要将日期转换为本地时区后显示。

 

 

总结起来，有几点：

· 经常变化的字段用 varchar

· 知道固定长度的用 char

· 尽量用 varchar

· 超过 255 字符的只能用 varchar 或者 text

· 能用 varchar 的地方不用 text