mysql创建表时字段类型选择与优化

一、选择原则

1.应该尽量使用可以正确存储数据的最小字段类型

2.选用简单的数据类型，例如：一个是尽量用mysql内置的字段类型来存储日期和时间；另一个存储IP地址尽量用整型；能用整型的尽量不用字符串类型

3.尽量避免使用NULL，例如：打算创建索引的字段，不要设置为NULL

二、常用的字段类型的选择说明

1.日期和时间类型

TIMESTAMP与DATETIME都可以存储相同类型的数据，并且精确到秒，可是TIMESTAMP只使用DATETIME一半的存储空间，并且它可以根据时区变化，具有特殊的自动更新能力。

存储空间：TIMESTAMP占用4个字节，DATETIME占用8个字节

2.整数类型

tinyint(8bit), smallint(16bit), mediumint(24bit), int(32bit), bigint(64bit)

整数计算一般使用64位的bigint整数，一个细节需要注意的地方，INT(1)与INT(20)对于存储和计算来说宽度是相同的，对于INT型字段来说指定宽度是多余的

3.实数类型

浮点类型(float, double)与decimal类型在性能上，浮点类型更好一些，同样范围的值，浮点类型比decimal占用更少的空间
转自：https://www.cnblogs.com/goujian/p/9346823.html

4.不要使用null
为什么这么说呢，因为MYSQL对NULL字段索引优化不佳，增加更多的计算难度，同时在保存与处理NULL类形时，也会做更多的工作，所以从效率上来说，不建议用过多的NULL。有些值他确实有可能没有值，怎么办呢？解决方法是数值弄用整数0，字符串用空来定义默认值即可。

5.字符串类型的使用
字符串数据类型是一个万能数据类型，可以储存数值、字符串、日期等。
保存数值类型最好不要用字符串数据类型，这样存储的空间显然是会更大，而且在排序时字符串的9是大于22的，其实如果进行运算时mysql会将字符串转换为数值类型，大大降低效果，而且这种转换是不会走原有的索引的。
如果明确数据在一个完整的集合中如男，女，那么可以使用set或enum数据类型，这种数据类型在运算及储存时以数值方式操作，所以效率要比字符串更好，同时空间占用更少。

6.VARCHAR与CHAR
VARCHAR是可变长度字符串类型，那么即然长度是可变的就会使用1，2个字节来保存字符的长度，如果长度在255内使用1个字节来保存字符长度，否则使用2个字符来保存长度。由于varchar是根据储存的值来保存数据，所以可以大大节约磁盘空间。
如果数据经常被执行更新操作，由于VARCHAR是根据内容来进行储存的，所以mysql将做更多的工作来完成更新操作，如果新数据长度大于老数据长度一些存储引擎会进行拆分操作处理。同时varchar会完全保留内部所有数据，最典型的说明就是尾部的空格。
CHAR固定长度的字符串保存类型，CHAR会去掉尾部的空格。在数据长度相近时使用char类型比较合适，比如md5加密的密码用户名等。
如果数据经常进行更新修改操作，那么CHAR更好些，因为char长度固定，性能上要快。

7.数值类型的选择
数值数据类型要比字符串执行更快，区间小的数据类型占用空间更少，处理速度更快，如tinyint可比bigint要快的多
选择数据类型时要考虑内容长度，比如是保存毫米单位还是米而选择不同的数值类型
整数
整数类型很多比如tinyint、int、smallint、bigint等，那么我们要根据自己需要存储的数据长度决定使用的类型，同时tinyint(10)与tinyint(100)在储存与计算上并无任何差别，区别只是显示层面上，但是我们也要选择适合合适的数据类型长度。可以通过指定zerofill属性查看显示时区别。

8.浮点数与精度数值
浮点数float在储存空间及运行效率上要优于精度数值类型decimal，但float与double会有舍入错误而decimal则可以提供更加准确的小数级精确运算不会有错误产生计算更精确，适用于金融类型数据的存储。