Mysql数据类型

数据类型

整型类型

• 合理选择bit、int、tinyint、decimal等数字类型
• int使用固定4个字节存储，int(11)与int(4)只是显示宽度的区别
• 建议使用UNSIGNED存储非负整数，可存储更大的数字

在MySQL中支持的主要整数类型是TINYINT，SMALLINT，MEDIUMINT，INT 和 BIGINT。这些类型在很大程度上是相同的，只有它们存储的值的大小是不相同的。MySQL以一个可选的显示宽度指示器的形式对 SQL 标准进行扩展。当从数据库检索一个值时，可以把这个值加长到指定的长度。例如，指定一个字段的类型为 INT(6)，就可以保证所包含数字少于 6 个的值从数据库中检索出来时能够自动地用空格填充。需要注意的是，使用一个宽度指示器不会影响字段的大小和它可以存储的值的范围。万一我们需要对一个字段存储一个超出许可范围的数字，MySQL 会根据允许范围最接近它的一端截短后再进行存储。还有一个比较特别的地方是，MySQL 会在不合规定的值插入表前自动修改为 0。

UNSIGNED：修饰符规定字段只保存正值。因为不需要保存数字的正、负符号，可以在储时节约一个“位”的空间。从而增大这个字段可以存储的值的范围。

ZEROFILL：修饰符规定 0（不是空格）可以用来填补输出的值。使用这个修饰符可以阻止 MySQL 数据库存储负值。

浮点类型

• CPU处理float的速度比处理double快，消耗内存和占用空间也小；若值很大或很小则float 变得不精确。
• 浮点类型的xxx中(m,d) 的含义差不多，m是小数点前后的数字个数(定义长度)，d是小数点后面的数字个数(定义精度)，比如decimal(5,2) 可以存储 -999.99 ~ 999.99。
• 对于精度比较高的数据，比如金额建议使用decimal，不要考虑float/double。

MySQL支持的三个浮点类型是 FLOAT、DOUBLE 和 DECIMAL 类型。FLOAT 数值类型用于表示单精度浮点数值，而 DOUBLE 数值类型用于表示双精度浮点数值。

与整数一样，这些类型也带有附加参数：一个显示宽度指示器和一个小数点指示器。比如语句 FLOAT(7,3) 规定显示的值不会超过 7 位数字，小数点后面带有 3 位数字。对于小数点后面的位数超过允许范围的值，MySQL 会自动将它四舍五入为最接近它的值，再插入它。

DECIMAL数据类型用于精度要求非常高的计算中，这种类型允许指定数值的精度和计数方法作为选择参数。精度在这里指为这个值保存的有效数字的总个数，而计数方法表示小数点后数字的位数。比如语句 DECIMAL(7,3) 规定了存储的值不会超过 7 位数字，并且小数点后不超过 3 位。忽略DECIMAL数据类型的精度和计数方法修饰符将会使 MySQL 数据库把所有标识为这个数据类型的字段精度设置为10，计数方法设置为0。

UNSIGNED 和 ZEROFILL 修饰符也可以被 FLOAT、DOUBLE 和 DECIMAL 数据类型使用。并且效果与 INT 数据类型相同。

字符串类型

• MySQL的字符串类型分为：文本字符串、二进制字符串。
• char(n)中的 n 代表最大可容纳的字符的个数，并不代表字节个数。

CHAR 和 VARCHAR 类型

CHAR类型用于定长字符串，并且必须在圆括号内指定大小。这个大小的范围从 0-255。比指定长度大的值将被截短，而比指定长度小的值将会用空格作填补。CHAR类型可以使用 BINARY修饰符。当用于比较运算时，这个修饰符使 CHAR 以二进制方式参于运算，而不是以传统的区分大小写的方式。

CHAR类型的一个变体是VARCHAR类型。它是一种可变长度的字符串类型，并且也必须带有一个范围在 0-255 之间的指示器。CHAR和VARCHGAR不同之处在于MySQL数据库处理这个指示器的方式：CHAR把这个大小视为值的大小，长度不不足的情况下就用空格补足。而VARCHAR类型把它视为最大值并且只使用存储字符串实际需要的长度（增加一个额外字节来存储字符串本身的长度）来存储值。所以短于指定长度的VARCHAR类型不会被空格填补，但大于指定值的部分仍然会被截短。因为VARCHAR类型可以根据实际内容动态改变存储值的长度，所以在不能确定字段需要多少字符时使用VARCHAR类型可以大大地节约磁盘空间、提高存储效率。

VARCHAR 类型在使用 BINARY 修饰符时与 CHAR 类型完全相同。

TEXT 和 BLOB 类型

对于字段长度要求超过255个的情况下，MySQL提供了TEXT和BLOB两种类型。根据存储数据的大小，它们都有不同的子类型。这些大型的数据用于存储文本块或图像、声音文件等二进制数据类型。

TEXT和BLOB类型在分类和比较上存在区别。BLOB类型区分大小写，而TEXT不区分大小写。大小修饰符不用于各种BLOB和TEXT子类型。比指定类型支持的最大范围大的值将被自动截短。

时间类型

• 精度方面，MySQL的timestamp(3)等于SQLServer的datetime，比如 2021-12-31 23:59:09.856。
• MySQL5.7之前，timestamp默认值是CURRENT_TIMESTAMP，并且随着记录更新而更新。
• MySQL5.7之前，datetime支持DEFAULT CURRENT_TIMESTAMP，但不支持 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。
• 导出的时候，timestamp以UTC时间格式导出，导入则自动由UTC格式转为系统默认时区，如果需要看到和导入与实际相符的时间戳，需要加入参数--tz-utc=false用于禁止timestamp时区转换，默认是开启的。

DATE、TIME和YEAR类型

MySQL用DATE和YEAR类型存储简单的日期值，使用TIME类型存储时间值。这些类型可以描述为字符串或不带分隔符的整数序列。如果描述为字符串，DATE类型的值应该使用连字号作为分隔符分开，而TIME类型的值应该使用冒号作为分隔符分开。需要注意的是，没有冒号分隔符的TIME类型值，将会被MySQL理解为持续的时间，而不是时间戳。

MySQL还对日期的年份中的两个数字的值，或是SQL语句中为YEAR类型输入的两个数字进行最大限度的通译。因为所有YEAR类型的值必须用4个数字存储。MySQL试图将2个数字的年份转换为4个数字的值。把在00-69范围内的值转换到 2000-2069 范围内。把70-99范围内的值转换到1970-1979之内。如果MySQL自动转换后的值并不符合我们的需要，请输入4个数字表示的年份。

DATETIME和TIMESTAMP类型

除了日期和时间数据类型，MySQL还支持DATETIME和TIMESTAMP这两种混合类型。它们可以把日期和时间作为单个的值进行存储。这两种类型通常用于自动存储包含当前日期和时间的时间戳，并可在需要执行大量数据库事务和需要建立一个调试和审查用途的审计跟踪的应用程序中发挥良好作用。如果我们对TIMESTAMP类型的字段没有明确赋值，或是被赋与了null值。MySQL 会自动使用系统当前的日期和时间来填充它。