数据类型
- 整数类型
有两种类型的数字, 整数和实数。
整数可选:TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。分别使用8,16,24,32,64位存储空间。他们可以存储值的范围 从 -2 (n-1)到2(n-1)-1,n是存储空间的位数。
- 整数类型可选 UNSIGNED 属性,大致可使正数上限提高一倍。
- 我们的选择决定mysql是怎么在内存和磁盘中保存数据。 然而,整数计算一般使用64位BIGINT整数,即使在32位环境也是如此。(一些聚合函数例外,他们使用decimal或double进行计算)
- 指定宽度意义不大,mysql可以为整数类型指定宽度 例如INT(11),对大多数应用这是没有意义的,他不会限制合法范围,只是规定了mysql的一些交互工具(如mysql命令行客户端)用来显示字符的个数,对于存储和计算来说,INT(1)和INT(20)是相同的。
- 实数类型
实数是带有小数部分的数字。然而,他们不只是为了存储小数部分,也可以使用DECIMAL存储比BIGINT还大的整数。mysql既支持精确类型,也支持不精确类型。
float和double支持使用标准的浮点数运算进行近似计算。
decimal 用于存储精确的小数。mysql5.0及以上,decimal支持精确计算。
float使用4个字节存储、double8个字节,decimal最多存储65个数字。
MySQL分别为整数和小数部分分配存储空间。 MySQL使用二进制格式存储DECIMAL值。它将9位数字包装成4个字节。
对于每个部分,需要4个字节来存储9位数的每个倍数。剩余数字所需的存储如下表所示
剩余数字 位
剩余数字 | 位 |
---|---|
1–2 | 1 |
3–4 | 2 |
5–6 | 3 |
7-9 | 4 |
- 字符串类型
varchar
varchar 类型用于存储可变字符串,是最常见的字符串数据类型。比定长更节省空间,因为他仅使用必要的空间。例外:如果mysql表使用ROW_FORMAT=FIXED创建的话,每行都会使用定长。
- varchar 需要使用1或两个额外字节记录字符串长度。如果列的最大长度<=255字节 则使用1个字节表示,否则使用两个字节。例如:varchar(10)11个字节 varchar(1000)1002个字节
- varchar节省了存储空间,所以对性能也有帮助。但是,由于行是变长的,在update时可能使行变得比原来更长,这就需要额外的工作。如果一个行占用的空间增长率,并且当页内没有更多存储空间,这种情况下不同引擎处理方式不同,Myisam拆分成不同片段存储,InnoDB则需要分裂页来使行可以放进页内
- 5.0或更高版本,保留末尾空格,但在4.1或更早版本会剔除末尾空格。
- varchar适用情况:
- 字符串列的最大长度比平均长度大很多
2)列更新很少,所以碎片不是问题
3)使用了想UTF-8这样复杂的字符集,每个字符使用不同的字节进行存储
-
关于varchar 最多能存多少值
mysql的记录行长度是有限制的,不是无限长的,这个长度是64K,即65535个字节,对所有的表都是一样的。MySQL对于变长类型的字段会有1-2个字节来保存字符长度。 当字符数小于等于255时,MySQL只用1个字节来记录,因为2的8次方减1只能存到255。 当字符数多余255时,就得用2个字节来存长度了。 在utf-8状态下的varchar,最大只能到 (65535 - 2) / 3 = 21844 余 1。 在gbk状态下的varchar, 最大只能到 (65535 - 2) / 2 = 32766 余 1
char
char 是定长的,mysql总是根据定义的字符串长度分配足够的空间。当存储char值时,会删除所有末尾空格。适合存储很短的字符串,或者所有字符串都接近同一个长度
-
char 比varchar 更好,因为定长的char类型不易产生碎片。
-
非常短的列,char比varchar在存储空间上更有效率
varchar(5)和varchar(200) 存储 hello 的空间开销是一样的,那么使用短列意义何在? 事实证明有很大的优势。更长的列会消耗更多内存,因为mysql通常分配固定大小的内存来保存内部值。 尤其是使用内存临时表进行排序或操作时候回特别糟糕。在利用磁盘临时表的时候也同样糟糕。 所以最好只分配真正需要的空间。
-
BLOB和TEXT类型
blob 和text 都是为存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。
区别:-
blob 存储的是二进制,没有排序规则或字符集
-
text 有字符集和排序规则
Mysql把每个blob和text值当做一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当blob和text太大 时,InnoDB会使用专门的外部存储区域来存储,此时每个值在行内需要1-4个字节存储一个指针。
-
-
日期和时间类型
mysq 最小时间粒度为秒
DATETIME
范围大,1001~9999,精度为秒。与时区无关,使用8个字节存储。
TIMESTAMP
范围小,1970~2038,精度秒。显示依赖时区,可使用 FROM_UNIXtIME()转换为日期,UNIX_TIMESTAMP() 把日期转换为Unix时间戳。
使用推荐
除了特殊行为外,通常应该尽量使用TIMESTAMP,它比DATETIME空间效率更高。 -
位数据类型(不推荐使用)
mysql 有少数几种存储类型使用紧凑的位存储数据。所有这些类型,不管底层存储格式和处理方式如何,从技术上来说都是字符串类型。
BIT
mysql5.0之前bit是tinyint的同义词。5.0及其以后版本,完全不同。可以使用BIT存储一个或多个true/false值。BIT(n)定义包含n位,最大64 -
SET
如果需要保存很多true/false 值,可以考虑合并这些列到一个set数据类型。
补充知识
irow_format Innodb 对应 Compact ,MyISAM 一般对应静态与动态
mysql中若一张表里面存在varchar、text以及其变形、blob以及其变形的字段的话,那么这个表其实也叫动态表,即该表的 row_format是dynamic,就是说每条记录所占用的字节是动态的。其优点节省空间,缺点增加读取的时间开销。反之,这张表叫静态表,该表 row_format为fixed,即每条记录占用字节一样。优点读取快,缺点浪费部分空间
所以,做搜索查询量大的表一般都以空间来换取时间,设计成静态表。
row_format还有其他一些值:
DEFAULT
FIXED
DYNAMIC
COMPRESSED
REDUNDANT
COMPACT
修改行格式
ALTER TABLE table_name ROW_FORMAT = DEFAULT
修改过程导致:
fixed—>dynamic: 这会导致CHAR变成VARCHAR
dynamic—>fixed: 这会导致VARCHAR变成CHAR
待补充 varchar 究竟能存几个汉字
参考高性能mysql 第四章
https://www.cnblogs.com/owenma/p/7097602.html