005_MySQL数据类型

1. MySQL中定义数据字段的类型对你数据库的优化是非常重要的。

2. MySQL支持多种类型, 大致可以分为三类: 数值、日期/时间和字符串(字符)类型。

3. 数值类型

3.1. 数值类型列表

类型	字节数	取值范围
tinyint(迷你整型)	1 byte	(-128, 127)
smallint(小整型)	2 bytes	(-32768, 32767)
mediumint(中整型)	3 bytes	(-8388608, 8388607)
int或integer(标准整型)	4 bytes	(-2147483648, 2147483647)
bigint(大整型)	8 bytes	(-9223372036854775808, 9223372036854775807)
float(单精度)	4 bytes	(-3.402823466E+38, -1.175494351E-38),  0, (1.175494351E-38, 3.402823466E+38)
double(双精度)	8 bytes	(-1.7976931348623157E+308,  -2.2250738585072014E-308), 0, (2.2250738585072014E-308,  1.7976931348623157E+308)
decimal(M, D)	m + 2	M是数字的最大长度, 取值范围1～65, 默认值为10。 D是小数点位数, 取值范围为0～30, 不得超过M的长度。

3.2. 长度

3.2.1. 长度和字节数不是一个东西。

3.2.2. tinyint、smallint、mediumint、int、bigint、float和double最大长度可设置为255。

3.2.3. tinyint类型的长度默认是4。长度大概是-128的字符位数。

3.2.4. smallint类型的长度默认是6。长度大概是-32768的字符位数。

3.2.5. mediumint类型的长度默认是9。长度大概是-8388608的字符位数。

3.2.6. int类型的长度默认是11。长度大概是-2147483648的字符位数。

3.2.7. bigint类型的长度默认是20。长度大概是-9223372036854775808的字符位数。

3.2.8. float和double小数点后最大长度30。

3.2.9. decimal类型M的最大长度65, D的最大长度30, D不能大于M的长度。

3.2.10. 长度和取值范围无关。即使你把tinyint类型的长度设置为2(虽然一般不这么做), 依然可以存值-128或127。

3.3. 创建Number表使用数值类型

3.3.1. Number表结构

3.3.2. Number表

4. 日期类型

4.1. 日期类型列表

类型	字节数	范围	格式
date(日期值)	3 bytes	1000-01-01 ～ 9999-12-31	YYYY-MM-DD
time(时间值)	3 bytes	'-838:59:59' ～ '838:59:59'	HH:MM:SS
year(年份值)	1 bytes	1901 ～ 2155	YYYY
datetime(日期和时间)	8 bytes	1000-01-01 00:00:00 ～ 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS
timestamp(时间戳)	4 bytes	1970-01-01 00:00:00 结束时间是第2147483647秒, 北京时间2038-1-19 11:14:07, 格林尼治时间2038年1月19日 凌晨03:14:07	YYYY-MM-DD HH:MM:SS

4.2. year类型只有一个字节, 为什么能表示1901 ～ 2155? 实际上一个year类型所存的一个字节的无符号值的范围是0 ～ 255, 年份从1900年开始算起, 1900加上0 ～ 255就表示1901 ～ 2155了。

4.3. time类型的HH段怎么表示-838 ～ 838了, 怎么不是23? 因为time类型并不是表示的一天的时间值, 它可以表示从一段时间到一段时间的时间值, 一段时间到一段时间就会出现比较大的HH, 因此它们设计了'-838:59:59' ～ '838:59:59'这样一个时间范围。

4.4. year类型有一个长度值, 默认是4。date、time、datetime和timestamp类型没有长度值。

4.5. Date表结构

4.6. Date表内容

5. 字符串类型

5.1. 字符串类型列表

类 型	字节数	说明
char(N)	0 ～ 最大字符字节长度*N bytes	定长字符串
varchar	0 ～ 65535 bytes	可变长度字符串
tinytext	0 ～ 255 bytes	短长度文本字符串
text	0 ～ 65535 bytes	长文本字符串
mediumtext	0 ～ 16777215 bytes	中长度文本字符串
longtext	0 ～ 4294967295 bytes	极大文本字符串
tinyblob	0 ～ 255 bytes	短长度二进制字符串
blob	0 ～ 65535 bytes	长文本二进制字符串
mediumblob	0 ～ 16777215 bytes	中长度二进制字符串
longblob	0 ～ 4294967295 bytes	极大二进制字符串

5.2. varchar使用1到2个额外的字节标识实际存储的字符串长度。如果列的最大长度小于256个字节, 用一个字节标识。如果最大长度大于等于256, 使用两个字节标识。

5.3. 长度

5.3.1. char类型的长度最大可以设置为255, 表示可以容纳255个字符(无论是汉字还是其它字符都可以容纳255个), 如果实际存储字符不够255个, 字符串后添加空格补全。因此在读取数据时, char类型的数据要进行处理, 把后面的空格去除。

5.3.2. varchar类型的长度最大可以设置为65535, 表示可以容纳65535个字符, 如果实际存储字符不够65535个, 按实际存储字符长度(不会用空格补全)。

5.3.3. tinytext、text、mediumtext、longtext、tinyblob、blob、mediumblob和longblob类型没有长度值。

5.3.4. NULL标识位, 如果varchar字段定义中带有default null允许列空, 则需要需要1bit来标识, 每8个bits的标识组成一个字段。

5.3.5. 如果使用的字符编码是utf8, varchar类型最大长度(65535-1-2)/3=21848。事实上 varchar类型长度还和很多因素有关, 这个时候就需要研究InnoDB引擎了, 一般不超过20000都没有问题, 我们根据实际需求来设置就好了。

5.4. text是一个文本对象, 它和varchar基本相同。有4种text类型: tinytext、text、mediumtext和longtext。它们区别在于可容纳存储范围不同。text一般用来存储一篇文章或者一段话等。

5.5. 如果使用的字符编码是utf8， tinytext可以容纳255/3=85个汉字, 单字节字符255个。

5.6. 如果使用的字符编码是utf8， text可以容纳65535/3=21845个汉字, 单字节字符65535个。

5.7. 如果使用的字符编码是utf8， mediumtext可以容纳16777215/3=5592405个汉字, 单字节字符16777215个。

5.8. 如果使用的字符编码是utf8， longtext可以容纳4294967295/3=1431655765个汉字, 单字节字符4294967295个。

5.9. blob是一个二进制大对象, 可以容纳可变数量的数据。有4种blob类型: tinyblob、blob、mediumblob和longblob。它们区别在于可容纳存储范围不同。blob一般用来存储文件, 例如: 图片、视频等。

5.10. String表结构

5.11. String表内容