mysql学习-建表时每个列如何选择数据类型01

最新推荐文章于 2023-01-31 12:42:19 发布

一路狂飚飚

最新推荐文章于 2023-01-31 12:42:19 发布

阅读量1.3k

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_34555905/article/details/107362838

版权

mysql 专栏收录该内容

19 篇文章 1 订阅

订阅专栏

建表时每个列如何选择数据类型

MySQL 提供了大量的数据类型，为了优化存储和提高数据库性能，在任何情况下都应该使用最精确的数据类型。

字符串类型是通用的数据类型，任何内容都可以保存在字符串中，数字和日期都可以表示成字符串形式。
但是也不能把所有的列都定义为字符串类型。对于数值类型，如果把它们设置为字符串类型的，会使用很多的空间。
并且在这种情况下使用数值类型列来存储数字，比使用字符串类型更有效率。

另外需要注意的是，由于对数字和字符串的处理方式不同，查询结果也会存在差异。
例如，对数字的排序与对字符串的排序是不一样的。
例如， 数字 2 小于数字 11，但字符串 ‘2’ 却比字符串 ‘11’ 大 。此问题可以通过把列放到数字上下文中来解决，如下面 SQL 语句：

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

让 course 列加上 0，可以强制列按数字的方式来排序，但这么做很明显是不合理的。
如果让 MySQL 把一个字符串列当作一个数字列来对待，会引发很严重的问题。这样做会迫使让列里的每一个值都执行从字符串到数字的转换，操作效率低。而且在计算过程中使用这样的列，会导致 MySQL 不会使用这些列上的任何索引，从而进一步降低查询的速度。

数值类型

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

例如，如果列的取值范围是 1~99999 之间的整数，则 MEDIUMINT UNSIGNED 类型是最好的选择。

MEDIUMINT 是整数类型，UNSIGNED 用来将数字类型无符号化。比如 INT 类型的取值范围是 -2 147 483 648 ～ 2 147 483 647，那么 INT UNSIGNED 类型的取值范围就是 0 ～ 4 294 967 295。

如果需要存储某些整数值，则值的范围决定了可选用的数据类型。如果取值范围是 0~1000，那么可以选择 SMALLINT~BIGINT 之间的任何一种类型。如果取值范围超过了 200 万，则不能使用 SMALLINT，可以选择的类型变为从 MEDIUMINT 到 BIGINT 之间的某一种。

当然，完全可以为要存储的值选择一种最“大”的数据类型。但是，如果正确选择数据类型，不仅可以使表的存储空间变小，也会提高性能。因为与较长的列相比，较短的列的处理速度更快。当读取较短的值时，所需的磁盘读写操作会更少，并且可以把更多的键值放入内存索引缓冲区里。

如果无法获知各种可能值的范围，则只能靠猜测，或者使用 BIGINT 以满足最坏情况的需要。如果猜测的类型偏小，那么也不是就无药可救。将来，还可以使用 ALTER TABLE 让该列变得更大些。

如果数值类型需要存储的数据为货币，如人民币。在计算时，使用到的值常带有元和分两个部分。它们看起来像是浮点值，但 FLOAT 和 DOUBLE 类型都存在四舍五入的误差问题 ，因此不太适合。
因为人们对自己的金钱都很敏感，所以需要一个可以提供完美精度的数据类型。
可以把货币表示成 DECIMAL(M,2) 类型，其中 M 为所需取值范围的最大宽度。这种类型的数值可以精确到小数点后 2 位。
DECIMAL 的优点在于不存在舍入误差，计算是精确的。

对于电话号码、信用卡号和社会保险号都会使用非数字字符 。因为空格和短划线不能直接存储到数字类型列里，除非去掉其中的非数字字符。但即使去掉了其中的非数字字符，也不能把它们存储成数值类型，以避免丢失开头的“零”。

日期和时间类型

MySQL 对于不同种类的日期和时间都提供了数据类型，比如 YEAR 和 TIME。如果只需要记录年份，则使用 YEAR 类型即可；如果只记录时间，可以使用 TIME 类型。

如果同时需要记录日期和时间，则可以使用 TIMESTAMP 或者 DATETIME 类型。由于TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，因此存储较大的日期最好使用 DATETIME。

字符串类型

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

如果某个字符串列用于表示某种固定集合的值，那么可以考虑使用数据类型 ENUM 或 SET。
CHAR 和 VARCHAR 之间的特点和选择
CHAR 和 VARCHAR 的区别如下:
CHAR 是固定长度字符，VARCHAR 是可变长度字符。

CHAR 会自动删除插入数据的尾部空格，VARCHAR 不会删除尾部空格。

CHAR 是固定长度，所以它的处理速度比 VARCHAR 的速度要快，但是它的缺点就是浪费存储空间。所以对存储不大，但在速度上有要求的可以使用 CHAR 类型，反之可以使用 VARCHAR类型来实现。

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

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