P5MySQL数据类型和类型选择

官方文档&＃xff1a;https://dev.mysql.com/doc/refman/5.7/en/data-types.html

MySQL支持多种类型&＃xff0c;大致可以分为三类&＃xff1a;数值、日期/时间和字符串(字符)类型。

在MySQL中&＃xff0c;选择正确的数据类型&＃xff0c;对于性能至关重要。一般应该遵循下面两步&＃xff1a;&＃xff08;1&＃xff09;确定合适的大类型&＃xff1a;数字、字符串、时间、二进制&＃xff1b;&＃xff08;2&＃xff09;确定具体的类型&＃xff1a;有无符号、取值范围、变长定长等。

在MySQL数据类型设置方面&＃xff0c;尽量用更小的数据类型&＃xff0c;因为它们通常有更好的性能&＃xff0c;花费更少的硬件资源。并且&＃xff0c;尽量把字段定义为NOT NULL&＃xff0c;避免使用NULL。
 

一、数值类型

• 关键字INT是INTEGER的同义词&＃xff0c;关键字DEC是DECIMAL的同义词。

• BIT数据类型保存位字段值&＃xff0c;并且支持MyISAM、MEMORY、InnoDB和BDB表。

• 金钱最好用int/bigint(整数,单位用分,拿出来进行*100换成元),千万不要直接用浮点,会有精度损失.

优化建议

• 如果整形数据没有负数&＃xff0c;如ID号&＃xff0c;建议指定为UNSIGNED无符号类型&＃xff0c;容量可以扩大一倍。
• 建议使用TINYINT代替ENUM、BITENUM、SET。
• 避免使用整数的显示宽度&＃xff0c;也就是说&＃xff0c;不要用INT(10)类似的方法指定字段显示宽度&＃xff0c;直接用INT。关于INT显示宽度。
• 用INT UNSIGNED来存储IPv4地址&＃xff0c;用VARBINARY来存储IPv6地址&＃xff0c;当然存储之前需要用PHP函数转换。
• DECIMAL最适合保存准确度要求高&＃xff0c;而且用于计算的数据&＃xff0c;比如价格。但是在使用DECIMAL类型的时候&＃xff0c;注意长度设置。
• 建议使用整形类型来运算和存储实数&＃xff0c;方法是&＃xff0c;实数乘以相应的倍数后再操作。
• 整数通常是最佳的数据类型&＃xff0c;因为它速度快&＃xff0c;并且能使用AUTO_INCREMENT。
   

二、日期和时间类型

优化建议

• 建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd。
• 用MySQL的内建类型DATE、TIME、DATETIME来存储时间&＃xff0c;而不是使用字符串。
• 当数据格式为TIMESTAMP和DATETIME时&＃xff0c;可以用CURRENT_TIMESTAMP作为默认&＃xff08;MySQL5.6以后&＃xff09;&＃xff0c;MySQL会自动返回记录插入的确切时间。
• TIMESTAMP是UTC时间戳&＃xff0c;与时区相关。
• DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数&＃xff0c;与时区无关&＃xff0c;你存了什么&＃xff0c;读出来就是什么。
• 除非有特殊需求&＃xff0c;否则建议使用TIMESTAMP&＃xff0c;它比DATETIME更节约空间。
• 有时人们把Unix的时间戳保存为整数值&＃xff0c;但是这通常没有任何好处&＃xff0c;这种格式处理起来不太方便&＃xff0c;我们并不推荐它。
   

三、字符串类型

• CHAR 和 VARCHAR 类型类似&＃xff0c;但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

优化建议

• 字符串的长度相差较大用VARCHAR&＃xff1b;字符串短&＃xff0c;且所有值都接近一个长度用CHAR。
• CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些要用来计算的数字不要用VARCHAR类型保存&＃xff0c;因为可能会导致一些与计算相关的问题。换句话说&＃xff0c;可能影响到计算的准确性和完整性。
• BINARY和VARBINARY存储的是二进制字符串&＃xff0c;与字符集无关。
• BLOB系列存储二进制字符串&＃xff0c;与字符集无关。TEXT系列存储非二进制字符串&＃xff0c;与字符集相关。一般情况下&＃xff0c;你可以认为BLOB是一个更大的VARBINARY&＃xff1b;TEXT是一个更大的VARCHAR。
• BLOB和TEXT都不能有默认值。
   

四、扩&＃xff1a;INT显示宽度

我们经常会使用命令来创建数据表&＃xff0c;而且同时会指定一个长度&＃xff0c;如下。但是&＃xff0c;这里的长度并非是TINYINT类型存储的最大长度&＃xff0c;而是显示的最大长度。

CREATE TABLE &＃96;user&＃96;(&＃96;id&＃96; TINYINT(2) UNSIGNED
);


这里表示user表的id字段的类型是TINYINT&＃xff0c;可以存储的最大数值是255。所以&＃xff0c;

在存储数据时&＃xff0c;如果存入值小于等于255&＃xff0c;如200&＃xff0c;虽然超过2位&＃xff0c;但是没有超出TINYINT类型长度&＃xff0c;所以可以正常保存&＃xff1b;如果存入值大于255&＃xff0c;如500&＃xff0c;那么MySQL会自动保存为TINYINT类型的最大值255。

在查询数据时&＃xff0c;不管查询结果为何值&＃xff0c;都按实际输出。这里TINYINT(2)中2的作用就是&＃xff0c;当需要在查询结果前填充0时&＃xff0c;命令中加上ZEROFILL就可以实现&＃xff0c;如&＃xff1a;

&＃96;id&＃96; TINYINT(2) UNSIGNED ZEROFILL


这样&＃xff0c;查询结果如果是5&＃xff0c;那输出就是05。如果指定TINYINT(5)&＃xff0c;那输出就是00005&＃xff0c;其实实际存储的值还是5&＃xff0c;而且存储的数据不会超过255&＃xff0c;只是MySQL输出数据时在前面填充了0。

换句话说&＃xff0c;在MySQL命令中&＃xff0c;字段的类型长度TINYINT(2)、INT(11)不会影响数据的插入&＃xff0c;只会在使用ZEROFILL时有用&＃xff0c;让查询结果前填充0。
 

 
参考资料&＃xff1a;

• 菜鸟教程
• 歪麦博客 https://www.awaimai.com/