MySQL数据类型的选择

  目录

数值类型

日期和时间类型

字符串类型

二进制类型

---

        在选择数据类型时，应该考虑数据的存储需求和计算精度等因素。以下是一些常见数据类型的选择建议：

1. 整数类型（int、bigint等）用于存储整数数据。如果需要存储较大的整数，可以选择bigint甚至decimal类型。
2. 浮点数类型（float、double等）用于存储具有小数部分的数据。浮点数类型可以存储非常大的值，但是其精度相对较低。
3. 定点数类型（decimal）可用于需要高精度的数值计算，例如货币计算。
4. 字符串类型（varchar、char等）用于存储文本数据，需要根据实际需求选择适当的长度。
5. 时间类型（date、time、datetime等）用于存储日期和时间信息。
6. 布尔类型（bool）用于存储true和false这两个值。
7. 枚举类型（enum）用于存储一组固定的值中的一个，例如性别、颜色等。
8. JSON类型（json）用于存储结构化的数据，通常用于NoSQL数据库中。

        在选择数据类型时，还需要考虑数据库的性能和空间占用等方面的因素，同时也需要根据具体的业务需求和数据特征进行选择。

        可以说字符串类型是通用的数据类型，任何内容都可以保存在字符串中，数字和日期都可以表示成字符串形式。
        但是也不能把所有的列都定义为字符串类型。对于数值类型，如果把它们设置为字符串类型的，会使用很多的空间。另外需要注意的是，由于对数字和字符串的处理方式不同，查询结果也会存在差异。例如，对数字的排序与对字符串的排序是不一样的。
 
        例如，数字 2 小于数字 11，但字符串 '2' 却比字符串 '11' 大（字符串比大小是一个一个比的)。此问题可以通过把列放到数字上下文中来解决，如下面 SQL 语句：

SELECT course+ 0 as num ... ORDER BY num;

让 course 列加上 0，可以强制列按数字的方式来排序，但这么做很明显是不合理的。
 
        如果让 MySQL 把一个字符串列当作一个数字列来对待，会引发很严重的问题。这样做会迫使让列里的每一个值都执行从字符串到数字的转换，操作效率低。而且在计算过程中使用这样的列，会导致 MySQL 不会使用这些列上的任何索引，从而进一步降低查询的速度。
 
        在选择数据类型时，首先要考虑这个列存放的值是什么类型的。一般来说，用数值类型列存储数字、用字符类型列存储字符串、用时态类型列存储日期和时间。

数值类型

        对于数值类型列，如果要存储的数字是整数，则使用整数类型；如果要存储的数字是小数（带有小数部分），则可以选用 DECIMAL 或浮点类型，但是一般选择 FLOAT 类型。

        例如，如果列的取值范围是 1~99999 之间的整数，则 MEDIUMINT UNSIGNED 类型是最好的选择。UNSIGNED 用来将数字类型无符号化。如果需要存储某些整数值，则值的范围决定了可选用的数据类型。如果取值范围是 0~1000，那么可以选择 SMALLINT~BIGINT 之间的任何一种类型。如果取值范围超过了 200 万，可以选择的类型为 MEDIUMINT 到 BIGINT 之间的某一种。
 
        当然，完全可以为要存储的值选择一种最“大”的数据类型。但是，如果正确选择数据类型，不仅可以使表的存储空间变小，也会提高性能。因为与较长的列相比，较短的列的处理速度更快。当读取较短的值时，所需的磁盘读写操作会更少，并且可以把更多的键值放入内存索引缓冲区里。
 
        如果无法获知各种可能值的范围，则只能靠猜测，或者使用 BIGINT 以满足最坏情况的需要。如果猜测的类型偏小，那么也不是无药可救。将来，还可以使用 ALTER TABLE 让该列变得更大些。
 
        如果数值类型需要存储的数据为货币，如人民币。在计算时，使用到的值常带有元和分两个部分。但 FLOAT 和 DOUBLE 类型都存在四舍五入的误差问题，因此不太适合。可以把货币表示成 DECIMAL(M,2) 类型，其中 M 为所需取值范围的最大宽度。这种类型的数值可以精确到小数点后 2 位。DECIMAL 的优点在于不存在舍入误差，计算是精确的。
 
        对于电话号码、信用卡号和社会保险号都会使用非数字字符。因为空格和短划线不能直接存储到数字类型列里，除非去掉其中的非数字字符。但即使去掉了其中的非数字字符，也不能把它们存储成数值类型，以避免丢失开头的“零”。

日期和时间类型

        MySQL 对于不同种类的日期和时间都提供了数据类型，比如 YEAR 和 TIME。如果只需要记录年份，则使用 YEAR 类型即可；如果只记录时间，可以使用 TIME 类型；如果同时需要记录日期和时间，则可以使用 TIMESTAMP 或者 DATETIME 类型。由于TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，因此存储较大的日期最好使用 DATETIME。
 
        TIMESTAMP 也有一个 DATETIME 不具备的属性。默认情况下，当插入一条记录但并没有指定 TIMESTAMP 这个列值时，MySQL 会把 TIMESTAMP 列设为当前的时间。因此当需要插入记录和当前时间时，使用 TIMESTAMP 是方便的，另外 TIMESTAMP 在空间上比 DATETIME 更有效。
 
        MySQL 没有提供时间部分为可选的日期类型。DATE 没有时间部分，DATETIME 必须有时间部分。如果时间部分是可选的，那么可以使用 DATE 列来记录日期，再用一个单独的 TIME 列来记录时间。然后，设置 TIME 列可以为 NULL。SQL 语句如下：

CREATE TABLE mytb1 (
    date DATE NOT NULL,  #日期是必需的
    time TIME NULL  #时间可选(可能为NULL)
);

字符串类型

        字符串类型没有像数字类型列那样的“取值范围"，但它们都有长度的概念。如果需要存储的字符串短于 256 个字符，那么可以使用 CHAR、VARCHAR 或 TINYTEXT。如果需要存储更长一点的字符串，则可以选用 VARCHAR 或某种更长的 TEXT 类型。
 
        如果某个字符串列用于表示某种固定集合的值，那么可以考虑使用数据类型 ENUM 或 SET。

        CHAR 和 VARCHAR 的区别如下:

• CHAR 是固定长度字符，VARCHAR 是可变长度字符，所以它的处理速度比 VARCHAR 的速度要快，但是它的缺点就是浪费存储空间。
• CHAR 会自动删除插入数据的尾部空格，VARCHAR 不会删除尾部空格。

       存储引擎对于选择 CHAR 和 VARCHAR 的影响:

• 对于 MyISAM 存储引擎，最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用空间换时间。
• 对于InnoDB存储引擎，最好使用可变长度的数据列，因为 InnoDB 数据表的存储格式不分固定长度和可变长度，因此使用 CHAR 不一定比使用 VARCHAR 更好，但由于 VARCHAR 是按照实际的长度存储，比较节省空间，所以对磁盘 I/O 和数据存储总量比较好。

        ENUM 只能取单值，它的数据列表是一个枚举集合。它的合法取值列表最多允许有 65 535个成员。因此，在需要从多个值中选取一个时，可以使用 ENUM。比如，性别字段适合定义，为 ENUM 类型，每次只能从‘男’或‘女’中取一个值。
 
        SET 可取多值。它的合法取值列表最多允许有 64 个成员。空字符串也是一个合法的 SET值。在需要取多个值的时候，适合使用 SET 类型，比如，要存储一个人兴趣爱好，最好使用SET类型。
 
        ENUM 和 SET 的值是以字符串形式出现的，但在内部，MySQL 以数值的形式存储它们。

二进制类型

        BLOB 是二进制字符串，TEXT 是非二进制字符串，两者均可存放大容量的信息。BLOB 主要存储图片、音频信息等，而 TEXT 只能存储纯文本文件。