建表：选择合适的数据类型

为每一列选择合适的数据类型，对于设计一个合适且高效的表来说，是充分必要条件。因为合适的数据类型不仅可以减少存储空间，还能提高查询效率。

下面就介绍一些关于数据类型的挑选原则。

char和varchar

字符（串）类型是常用的数据类型之一（Java中用String表示），但mysql对应的数据有两种，分别是char和varchar。具体选择哪一种呢，这就要看具体的场景和他们各自的特性了。

CHAR是一种固定长度的字符串类型。当你声明一个CHAR列时，你需要指定一个固定长度，例如CHAR(10)。这意味着无论你存储的字符串实际长度是多少，列都将分配固定数量的字节空间。如果存储的字符串短于指定长度，CHAR类型会自动用空格填充到指定长度；如果字符串长于指定长度，则会被截断到指定长度。

VARCHAR是一种可变长度的字符串类型。与CHAR不同，VARCHAR列的存储空间根据实际存储的字符串长度变化。例如，VARCHAR(10)可以存储长度从0到10（包括10）的字符串，存储时只会分配实际需要的字节空间加上一些额外的开销（用于存储字符串长度）。

总的来说，因为VARCHAR列的长度不是固定的，这可能会影响索引和缓存的效率，所以查询性能可能略低。如果列长度（如身份证等）是不变的，推荐使用char，或者说，如果想要查询效率更高，推荐使用char。

innodb的影响

但，对于字符串的处理，Innodb引擎却推荐使用”varchar“。

其原因主要是：对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针）。每一行数据都有一个头指针，这个头指针指向行中各个列值的实际存储位置。这种设计使得无论是固定长度的CHAR列还是可变长度的VARCHAR列，InnoDB都可以统一地进行管理，而无需在物理存储上对它们作出区分。这意味着，即使CHAR列理论上是固定长度的，InnoDB也不会像某些其他存储引擎那样简单地按预设长度连续存储数据，而是使用指针间接访问。

因此，在InnoDB存储引擎中，对于查询效率而言，VARCHAR和CHAR类型在多数情况下的确表现得非常接近。

由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。所以，对于字符串的处理，Innodb引擎却推荐使用”varchar“。

所以，在日常开发中，如果使用存储引擎为innodb，推荐使用varchar。

小实验

下面用一个简单的例子来实验的在Innodb下，varchar和char的执行效率。

 #-- 创建包含 CHAR 类型的表
 CREATE TABLE char_test (
     id INT AUTO_INCREMENT PRIMARY KEY,
     name CHAR(20)
 );
 ​
 -- 创建包含 VARCHAR 类型的表
 CREATE TABLE varchar_test (
     id INT AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(20)
 );
 ​
 -- 向 char_test 表插入数据
 INSERT INTO char_test (name) VALUES ('John Doe'), ('Jane Smith'), ('A'), ('Very Long Name');
 ​
 -- 向 varchar_test 表插入数据
 INSERT INTO varchar_test (name) VALUES ('John Doe'), ('Jane Smith'), ('A'), ('Very Long Name');

准备好我们需要的数据后，就开始测试了。

 -- 测试 CHAR 类型
 SET @start_time = UNIX_TIMESTAMP();
 SELECT id FROM char_test WHERE name = 'lip Doe';
 SET @end_time = UNIX_TIMESTAMP();
 SELECT @end_time - @start_time AS char_query_time;
 ​
 -- 测试 VARCHAR 类型
 SET @start_time = UNIX_TIMESTAMP();
 SELECT id FROM varchar_test WHERE name = 'lip Doe';
 SET @end_time = UNIX_TIMESTAMP();
 SELECT @end_time - @start_time AS varchar_query_time;

这个小实验主要是提供一个思路，因为数据太少，可能差别不太大，本人尝试了5次，有3次相同，有2次甚至varchar耗时更少。大家可以自行扩展。

浮点数与定点数

浮点-double

浮点数一般用于表示含有小数部分的数值。当一个字段被定义为浮点类型后，如果插入数据的精度超过该列定义的实际精度，则插入值会被四舍五入到实际定义的精度值，然后插入，四舍五入的过程不会报错。

在MySQL中float、double用来表示浮点数，而Java语言中也有对应的数据类型，也是float和double。

在MySQL中定义double可以指定小数位数或者不指定：

 alter table char_test add double_value double(4,2);
 # 整数和小数位数 共4位，其中小数位数2位
 ​
 alter table char_test add double2_value double;

当你直接使用double类型（没有指定M和D）时，MySQL会使用其内部的默认精度。根据MySQL文档，如果不指定精度，double类型的默认精度大约是15位数字，这包括小数点两边的所有数字。

如果精度大于定义的精度，用double(4,2)为例，如果插入的数据为“12.123”，MySQL也不会报错，且能插入成功，但多余的小数位数会被舍弃（四舍五入）。不过，如果整数位数超过，则会报错。

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

高精度-decimal

在需要极高精度的计算中，如金融交易或科学计算，通常会使用DECIMAL类型，它可以精确表示固定小数点后位数的数值，避免了浮点数运算中常见的舍入误差，并非不会发生四舍五入的情况。它也被叫做定点数。

舍入误差发生的原因：不是所有的十进制分数都可以精确地表示为二进制分数。其中最著名的例子就是——0.1 + 0.2不等于0.3。

二进制表示的局限性：计算机内部使用二进制数系统进行所有计算。在二进制系统中，并非所有十进制小数都能被精确表示。例如，0.1和0.2在二进制中是无限循环小数，无法被精确表示。在二进制中，0.1大约表示为0.00011001100110011...（二进制下的无限循环），而0.2大约表示为0.0011001100110011...。

舍入误差：由于二进制浮点数的有限精度，计算机必须对这些无限循环小数进行舍入，以适应存储位数的限制。这种舍入会导致微小的误差。当进行加法运算时，这些微小的误差累积起来，最终可能导致看似简单的数学运算结果出现偏差。

这种现象在使用IEEE 754标准的浮点数表示法的编程语言和数据库中很常见，包括C++、Java、Python、JavaScript以及SQL的FLOAT和DOUBLE类型。

但如果使用DECIMAL的话，则不会发生此类情况，但如果插入的数据精度超过定义的，仍会发生四舍五入。

 CREATE TABLE example_table (
     id INT AUTO_INCREMENT PRIMARY KEY,
     amount DECIMAL(10, 2)
 );

上述sql中，amount字段被定义为一个DECIMAL类型，总共有10位数字，其中2位是小数点后的数字。这意味着它可以存储的范围是从-99999999.99到99999999.99.

在Java中，DECIMAL类型通常对应于Java.math.BigDecimal类。BigDecimal提供了任意精度的定点数运算，非常适合处理货币和金融数据，因为它能避免浮点数运算中常见的精度损失问题。

日期类型选择

接下来介绍下MySQL中主要的时间类型：DATE、TIME、DATETIME和TIMESTAMP.

DATE类型用于存储日期值，格式为 YYYY-MM-DD。它不包含时间部分，仅表示年、月、日。DATE类型的范围是从 1000-01-01 到 9999-12-31。

TIME类型用于存储时间值，格式为 HH:MM:SS。它可以表示一天内的时间，也可以表示持续时间，甚至可以表示负的时间（例如，-12:34:56）。TIME类型的范围是从 00:00:00 到 838:59:59。

DATETIME类型结合了DATE和TIME的功能，用于存储日期和时间的组合，格式为 YYYY-MM-DD HH:MM:SS。它既包含了日期信息也包含了时间信息，范围是从 '1000-01-01 00:00:00'到 '9999-12-31 23:59:59'。

TIMESTAMP 存储的格式与 DATETIME 类似，都是 YYYY-MM-DD HH:MM:SS，但它有自己的一套规则和特性。TIMESTAMP 的值范围从 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC'，这是由于 TIMESTAMP 实际上是基于 Unix 时间戳（从 1970 年 1 月 1 日 00:00:00 UTC 开始的秒数）来存储的。

TIMESTAMP类型的值通常以 YYYY-MM-DD HH:MM:SS 的字符串格式显示。

建议：

如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用 DATETIME，而不要使用TIMESTAMP。因为TIMESTAMP表示的日期范围比DATETIME要短得多。

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

TIMESTAMP 值在存储和检索时会受到时区的影响。当从客户端插入 TIMESTAMP 值时，它首先根据客户端的时区转换为 UTC 时间，然后存储。当从服务器检索 TIMESTAMP 值时，它会根据服务器的时区设置从 UTC 转换回本地时间。这意味着在不同的时区环境下，TIMESTAMP 的显示可能会有所不同，但其内部存储始终为 UTC 时间。

UTC 是 "Coordinated Universal Time"（协调世界时）的缩写，是一种国际标准的时间计量系统，用于全球同步时间基准。