MySQL建表时如何选中合适的数据类型

char与varchar

🍓char:固定長度的字符类型。varchar属于可变的字符类型。（那我们究竟选哪个，记住选择varchar没毛病！！！）

🍓为什么选择varchar,总结为三点

•    char处理速度快，但检索时char列时，会删除尾部的的空格，部分数据丢失的风险我们不想承担。
•    MySQL5.5后我们默认存储引擎为InnoDB，它对长度是否固定没有区分。主要性能取决于数据行的存储量，我们的可变长度的存储空间肯定是最小的。
•    varchar随着MySQL版本的不断升性能不断提升，除非程序对查询速度要求很高，或字段长度变化不大可以考虑char.

char与varchar的区别如下

在MySQL中，不同的存储引擎对CHAR和VARCHAR的使用原则有所不同，这里简单概括如下。
MyISAM存储引擎：建议使用固定长度的数据列代替可变长度的数据列。
MEMORY 存储引擎：目前都使用固定长度的数据行存储，因此无论使用 CHAR 或VARCHAR列都没有关系。两者都是作为CHAR类型处理。
InnoDB存储引擎：建议使用VARCHAR类型。对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。

 

text与blob

🍓保存少量字符串时，我们会选择char或者varchar.在保存较大文本时，通常选择使用text或者blob。

🍓text与blob的区别

• blob可以保存二进制数据，比如照片，text只能保存字符数据，如一篇文章或者日志。

🍓text和blob执行大量删除操作后，在数据表中留下大量“空洞”。为提高性能我们可定期使用optimize table功能对这类表进行碎片整理。

演示如下

我们查看表的物理大小

 

 这里数据文件显示为351.89MB。从表t1中删除id为“1”的数据

再查看表的大小

 

 

 

可以发现，表t1的数据文件仍然为351.89MB，并没有因为数据删除而减少。接下来对表进行OPTIMIZE（优化）操作：

这里mysql给的提示是Note>> Table does not support optimize, doing recreate + analyze instead
Status>> OK
也就是说 optimize table 对于innodb来说，无法作为a single operation.以上无效。
MySQL5.7已经推荐对于InnoDB的table使用 alter table table_name engine=innodb;语句的方式来进行表碎片优化。

 finish!

🍓可以使用合成的（Synthetic）索引来提高大文本字段（BLOB或TEXT）的查询性能。要注意这种技术只能用于精确匹配的查询.

🍓在不必要的时候避免检索大型的BLOB或TEXT值。

例如，SELECT *查询就不是很好的想法，除非能够确定作为约束条件的WHERE子句只会找到所需要的数据行。否则，很可能毫无目的地在网络上传输大量的值。这也是 BLOB 或TEXT标识符信息存储在合成的索引列中对用户有所帮助的例子。用户可以搜索索引列，决定需要的哪些数据行，然后从符合条件的数据行中检索BLOB或TEXT值。

🍓把BLOB或TEXT列分离到单独的表中。
在某些环境中，如果把这些数据列移动到第二张数据表中，可以把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会减少主表中的碎片，可以得到固定长度数据行的性能优势。它还可以使主数据表在运行 SELECT *查询的时候不会通过网络传输大量的BLOB或TEXT值。

 浮点数与定点数

🍓浮点数（float,double)定点数（decimal,numberic)

🍓定点数不同于浮点数，定点数实际上是以字符串形式存放的，所以定点数可以更精确的保存数据。

 

🍓浮点数的精度问题。

在选择浮点型数据保存小数时，要注意四舍五入的问题，并尽量保留足够的小数位，避免存储的数据不准确。

点数的比较也是一个普遍存在的问题，下面的程序片断中对两个浮点数做减法运算：

 

public class Test {
　public static void main(String[] args) throws Exception {
　　System.out.print("7.22-7.0=" + (7.22f-7.0f));
　}
　}

 

 

 

 对上面Java程序的输出结果可能会想当然地认为是0.22，但是，实际结果却是7.22-7.0=0.21999979，因此，在编程中应尽量避免浮点数的比较，如果非要使用浮点数的比较则最好使用范围比较而不要使用“==”比较。

下面使用定点数来实现上面的例子

 

 

 

 

 注意：在今后关于浮点数和定点数的应用中，用户要考虑到以下几个原则：
浮点数存在误差问题；
对货币等对精度敏感的数据，应该用定点数表示或存储；
在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；
要注意浮点数中一些特殊值的处理。

 日期类型的选择

🍓日期类型包括：DATE \TIME\TIMESTAMP\DETATIME.


🍓根据实际需要选择满足应用最小的存储的日期类型。

• 如果应用只需要记录“年份”，那么用1个字节来存储的YEAR类型完全可以满足，而不需要用4个字节来存储的DATE类型。这样不仅仅能节约存储，更能够提高表的操作效率。
• 如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用 DATETIME，而不要使用TIMESTAMP。因为TIMESTAMP表示的日期范围比DATETIME要短得多。

• 如果记录的日期需要让不同时区的用户使用，那么最好使用TIMESTAMP，因为日期类型中只有它能够和实际时区相对应。

小结

对于字符类型，要根据存储引擎来进行相应的选择。
对精度要求较高的应用中，建议使用定点数来存储数值，以保证结果的准确性。
对含有 TEXT 和 BLOB 字段的表，如果经常做删除和修改记录的操作要定时执行OPTIMIZE TABLE功能对表进行碎片整理。
日期类型要根据实际需要选择能够满足应用的最小存储的日期类型。