目录
1. 第一部分: 整数
2. 第二部分: 实数
3. 第三部分: 字符串类型
4. 第四部分 日期和时间类型
选择正确的数据类型，对于提高性能至关重要。
第一步. 大致决定数据类型。——判断是数字，字符串还是时间等。这通常很直观。
第二步. 确定特定的类型。
1. 更小通常更好;
(1)使用最小的数据类型。——更少的磁盘空间，内存和CPU缓存。而且需要的CPU的周期也更少。
2. 简单就好。整数代价小于字符。——因为字符集和排序规则使字符比较更复杂。
(1)mysql内建类型（如timestamp,date)优于使用字符串保存。
(2)使用整数保存ip地址。
3. 尽量避免NULL——如果计划对列进行索引，尽量避免把列设置为NULL
(1)MYSQL难以优化NULL列。NULL列会使索引，索引统计和值更加复杂。
(2)NULL列需要更多的存储空间，还需要在MYSQL内部进行特殊处理。
(3)NULL列加索引，每条记录都需要一个额外的字节，还导致MyISAM中固定大小的索引变成可变大小的索引。
第一部分: 整数
1、存储类型——数据范围为-2(n-1)到2(n-1)-1，这里的n是所需存储空间的位数。1个字节=8位
| 类型名称 | 占用位数/(字节) | 数据范围(有符号) | 数据范围(无有符号) | TYPE(M) 默认 |
|---|---|---|---|---|
| TINYINT | 8/(1) | -2^7 ~ 2^7-1 ((-128，127)) | (0，255) | 4 |
| SMALLINT | 16/(2) | -215~215-1 (-32 768，32 767) | (0，65 535) | 6 |
| MEDIUMINT | 24/(3) | -223~223-1 (-8 388 608，8 388 607) | (0，16 777 215) | 9 |
| INT | 32/(4) | -231-232-1 (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 11 |
| BIGINT | 64/(8) | -263~263-1 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 20 |
2、unsigned属性表示不允许负数，并大致把正上限提高了一倍。如TINYINT UNSIGNED保存的范围为0到255而不是-127到128, 可根据具体情况选择无符号类型;
实例代码
1.1 创建表, 并定义宽度, 为显示完整使用ZEROFILL;
MariaDB [typeDB]> CREATE TABLE int_test
-> (intNum INT(4) ZEROFILL,
-> smallNum SMALLINT(3) ZEROFILL);
Query OK, 0 rows affected (0.02 sec)
1.2 插入数据测试
MariaDB [typeDB]> INSERT INTO int_test
-> (intNum, smallNum) VALUES
-> (1, 2),
-> (10, 20),
-> (1111, 222),
-> (11111, 2222);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
1.3 查询结果, 不足指定宽度时补0
MariaDB [typeDB]> SELECT * FROM int_test;
+--------+----------+
| intNum | smallNum |
+--------+----------+
| 0001 | 002 |
| 0010 | 020 |
| 1111 | 222 |
| 11111 | 2222 |
+--------+----------+
4 rows in set (0.00 sec)
注释: 不论是INT(2)还是int(11)，它在数据库里面存储的都是4个字节(8位)的长度，存储的值的范围为(-2 147 483 648，2 147 483 647), 而不是只能存4位或11位数字, INT(M), M: 代表的只是宽度;
第二部分: 实数
| 类型名称 | 占用位数/(字节) | 数据范围(有符号) | 数据范围(无有符号) | 用途 |
|---|---|---|---|---|
| FLOAT | 32/(4) | -231-232-1 | 0-2^32-1 | 单精度浮点数值 |
| DOUBLE | 64/(8) | -264-264-1 | 0-2^64-1 | 双精度浮点数值 |
| DECIMAL | 对DECIMAL(M, D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
DECIMAL从MySQL 5.1引入，列的声明语法是DECIMAL(M,D)。在MySQL 5.1中，参量的取值范围如下：
·M是数字的最大数（精度）。其范围为1～65（在较旧的MySQL版本中，允许的范围是1～254），M 的默认值是10。
·D是小数点右侧数字的数目（标度）。其范围是0～30，但不得超过M。
说明：float占4个字节，double占8个字节，decimail(M,D)占M+2个字节。
例子: DECIMAL(5,2) 的最大值为9 9 9 9 . 9 9，因为有7 个字节可用。小数点占一位;
1. FLOAT和DOUBLE支持标准浮点运算进行近似计算。
2. DECIMAL只是一个存储格式，在计算时会被转换为DOUBLE类型。
3. DECIMAL只有对小数进行精确计算的时候才使用它，如保存金融数据。
4. DECIMAL只是一个存储格式，在计算时会被转换为DOUBLE类型。
第三部分: 字符串类型
3.1 MYSQL数据类型----字符串类型
屏幕快照 2016-11-28 下午2.10.23.png
可以使用命令查看常见编码所占字节数:
MariaDB [(none)]> SHOW CHARACTER SET;
查看默认字符: SHOW VARIABLES LIKE 'character%';，发现数据库编码均已改成utf8
1. utf8编码一个字符占3个字节；
2. gbk编码一个字符占2个字节；
3. latin1编码一个字符占1个字节。
更改某个字段的字符集: VARCHAR(20) character set utf8;
更改数据库的字符集: CREATE DATABASE 数据库名 character set utf8;;
char(M) 与 varchar(M) 的理解可以参考int(M). varchar(n) 表示n个字符，无论汉字和英文，MySql都能存入 n 个字符，仅实际字节长度有所区别。
3.2 varchar
(1)保存可变长字符串。
理解：比固定长度占用更少的存储空间，因为它只占用自己需要的空间。例外情况：使用ROW_FORMAT=FIXED创建的MyISAM表，它为每行使用固定长度的空间，可能会造成浪费。
（2）存储长度信息。
如果定义的列小于或等于255，则使用1个字节存储长度值，假设使用latin1字符集，如varchar（10）将占用11个字节的存储空间。反过来，varchar（1000），则占用1002个字节的存储空间。
（3）节约空间，对性能有帮助。
（4）5.0版本以上，无论是取值还是保存，MySQL都会保留字符串末尾的空格。
(5)对于变长字段如varchar，mysql会用额外的字节来存储字符长度，255个字符以内用1个字节存，多于255个字符用2字节存;
只分配真正需要的空间
使用varchar(5)和varchar(200)保存'hello'占用空间是一样的---——这里应该指的是磁盘上的空间。
那么使用较短列有何优势？——巨大的优势
较大的列会使用更多的内存，因为MySQL通常会分配固定大小的内存块（如varchar(200)会用200个字符大小的内存空间）来保存值(然后对值进行trim操作，最后放入磁盘)或取值。-----这对排序或使用基于内存的临时表尤其不好。
3.3 char
（1）固定长度。
（2）保存值时，去掉末尾的空格。
char和varchar：
1.char类型是使用固定长度空间进行存储，范围0-255。char善于存储经常改变的值，或者长度相对固定的值，
2.varchar类型保存可变长度字符串，范围0-65535（但受到单行最大64kb的限制）
3.char类型的字符串检索速度要比varchar类型的快。
Blob和Text类型
blob和text唯一区别就是blob保存二进制数据 没有字符集和排序规则
3.4 实例代码
MariaDB [typeDB]> CREATE TABLE char_test
-> (charStr CHAR(10),
-> varCharStr VARCHAR(10));
Query OK, 0 rows affected (0.03 sec)
插入三条数据
INSERT INTO char_test
-> (charStr, varCharStr)
-> VALUES
-> ('string1', 'string1'),没用空格
-> ('string2 ', 'string2 '),尾部空格
-> (' string3', ' string3');头部空格
char自动删除尾部空格
MariaDB [typedb]> SELECT CONCAT("'", charStr, "'")FROM char_test;
+---------------------------+
| CONCAT("'", charStr, "'") |
+---------------------------+
| 'string1' |
| 'string2' 自动删除字符末尾空格
| ' string3' |
+---------------------------+
3 rows in set (0.00 sec)
varchar保留头部与尾部空格
MariaDB [typedb]> SELECT CONCAT("'", varCharStr, "'")FROM char_test;
+------------------------------+
| CONCAT("'", varCharStr, "'") |
+------------------------------+
| 'string1' |
| 'string2 ' 保留尾部空格 |
| ' string3' 保留头部空格 |
+------------------------------+
3 rows in set (0.00 sec)
问题一：varchar(m),char(n)里面的m或n代表的是字节还是字符的个数？
The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters
you want to store. For example, CHAR(30) can hold up to 30 characters.
手册中明确指出，存放的是字符的个数。
问题二：varchar(m),char(n)里面的m和n是否有长度限制？分别是多少？
The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value
**from 0 to 255**. When CHAR values are stored, they are right-padded with spaces to the specified length.When CHAR values
are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH [581]SQL mode is enabled.
char(n):长度的限制是0-255个字符, 没有明确标出字节的限制数。
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from
**0 to 65,535**.The effective maximum length of a VARCHAR is subject to the maximum row
size (65,535 bytes, which is shared among all columns) and the character set used. See Section E.10.4, “Table Column-Count and Row-Size Limits”.
In contrast to CHAR, VARCHAR values are stored as **a 1-byte** or **2-byte length prefix** plus data. The length prefix indicates the number of bytes in
the value. A column uses one length byte if values require no more than 255 bytes,two length bytes if values may require more than 255 bytes.
m也是字符的个数。而上面句子中提到的65535是指varchar列最大可存放的字节数。所以: utf8时: {(65535 / 3)-2}; gbk时:{(65535 / 2)-2}
问题三： char在浪费存储空间的劣势下，获得了较高的效率，varchar相反。接下来我们也要总结一下什么情况下使用char，什么情况下使用varchar?摘录
原则一：根据字符串长度确定，凡是固定长度的字符串或者类似固定长度的字符串一律用char。比如身份证号码，手机号码，银行卡号，MD5，哈希值等这是字符串是固定长度的，毫无疑问用char，还有一类是基本固定长度但是略有出入的，比如中国人的姓名等，一般长度可能是2~5个汉字，这类信息也非常适合用char来存储，只要分配一些略大于通常长度即可。
原则二：数据是否经常更改导致碎片，可能经常变动而产生存储碎片的小字符串一律用char。我们知道char类型的数据是一次性分配存储空间的，无论以后你怎么修改，数据始终在该存储空间内的，不会产生碎片。而varchar则不同，varchar的数据长度是可变的，当修改后的数据大于当前存储长度时，就会产生碎片，如果该应用是反复修改数据的应用，那么久而久之就是产生无数碎片，效率可想而知。
原则三：理解varchar的存储空间和内存空间的区别，合理指定varchar的长度。比如通信地址，通常在100个字符内就能存储完成，于是varchar(100)是一个合理的选择，但是由于之前讲的，可能有人图方便使用varchar(500)，反正用的存储空间是一样的，但是效果确实不一样的。在内存模型中varchar(100)与varchar(500)是两码事，后者比前者占用多5倍的内存空间，在临时表和排序的时候这个差别几乎可能差一个数量级，于是效率可想而知。
varchar和text：
1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。
2.text类型不能有默认值。
3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引似乎不起作用。
第四部分 日期和时间类型
屏幕快照 2016-11-28 下午2.10.13.png
若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。
通常使用TIMESTAMP，它比DATETIME更节约空间。有时人们把Unix的时间戳保存为整数值，但是这通常没有任何好处。——这种格式处理起来不太方便，我们并不推荐它。
参考链接
MySQL字符数据类型char与varchar的区别
由MySQL中char和varchar效率想到的
MySQL之char、varchar和text的设计
川山甲博客
mysql中的varchar到底能存多长的字符
浅谈mysql中varchar(m)与char(n)的区别与联系
更多精彩内容请关注“IT实战联盟”哦~~~
IT实战联盟.jpg
网友评论