mysql数据类型

类型	类型举例
整数类型	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型	FLOAT、DOUBLE
定点数类型	DECIMAL
位类型	BIT
日期时间类型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型	ENUM
集合类型	SET
二进制字符串类	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型	JSON对象、JSON数组
空间数据类型	单值：GEOMETRY、POINT、LINESTRING、POLYGON；<br />集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

常见数据类型的属性，如下：

MySQL关键字	含义
NULL	数据列可包含NULL值
NOT NULL	数据列不允许包含NULL值
DEFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	自动递增，适用于整数类型
UNSIGNED	无符号
CHARACTER SET name	指定一个字符集

---

声明字符集
CHARACTER SET "utf8"
可以在 数据库上、表上、字段上 声明其字符集，有继承关系.

整数类型

5种：TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT

整数类型	字节	有符号数取值范围	无符号数取值范围
TINYINT	1	-128~127	0~255
SMALLINT	2	-32768~32767	0~65535
MEDIUMINT	3	-8388608~8388607	0~16777215
INT、INTEGER	4	-2147483648~2147483647	0~4294967295
BIGINT	8	-9223372036854775808~9223372036854775807	0~18446744073709551615

INT(11) 的 11 是数据的显示宽度，即数据的长度，INT的无符号最大值是：4294967295，加上符号,一共11位，所以INT的11就是这么来的；

但是注意：不是说写了显示宽度就限制这么长，比如 INT(2),你存999999进去是能存的；显示宽度如果不配合 ZEROFILL 是没有任何意义的：

filed INT(3) ZEROFILL      -- 存入 5 ，则查出来是：005

MySQL8.0.17 开始，整数类型不再推荐使用 显示宽度 了;

UNSIGNED 无符号
没有负数；
filed INT UNSIGNED

浮点数

2种：float 4、double 8、real(real其实就是double,可以通过修改my.ini将real表示为float)

DOUBLE(5,2);  5表示整数位 + 小数位 = 5位，2表示小数位2位，即：-999.99 ~ 999.99 都可以存入，超出报错；
说明：浮点类型，也可以加UNSIGNED，但是不会改变数据范围，例如：FLOAT(3,2) UNSIGNED仍然只能表示0-9.99的范围。

从MySQL 8.0.17开始，FLOAT(M,D)和DOUBLE(M,D)用法在官方文档中已经明确不推荐使用，将来可能被移除。另外，关于浮点型FLOAT和DOUBLE的UNSIGNED也不推荐使用了，将来也可能被移除。

对于浮点数，不要用 = 判断；

定点数

1种：Decimal

假设定义 DECIMAL(5,2) 的类型，表示该列取值范围是 -999.99~999.99。

DECIMAL(M,D) 的最大取值范围与DOUBLE类型一样，但是有效的数据范围是由M和D决定的。

定点数在MySQL内部是以 字符串 的形式进行存储，这就决定了它一定是精准的。

当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)。

“由于 DECIMAL 数据类型的精准性，在我们的项目中，除了极少数（比如商品编号）用到整数类型外，其他的数值都用的是 DECIMAL，原因就是这个项目所处的零售行业，要求精准，一分钱也不能差。 ” ——来自某项目经理

   

日期类型

5种：YEAR、TIME、DATE、DATETIME、TIMESTAMP

• YEAR 年
• DATE 年月日
• TIME 时分秒
• DATETIME 年月日时分秒
• TIMESTAMP 带时区的年月日时分秒

类型	名称	字节	日期格式	最小值	最大值
YEAR	年	1	YYYY	1901	2155
TIME	时间	3	HH:MM:SS	-838:59:59	838:59:59
DATE	日期	3	YYYY-MM-DD	1000-01-01	9999-12-03
DATETIME	日期时间	8	YYYY-MM-DD HH:MM:SS	1000-01-01  00:00:00	9999-12-31 23:59:59
TIMESTAMP	日期时间	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00 UTC	2038-01-19 03:14:07UTC

上表中，时间为何是 -838 ~ 838  而不是 24？因为mysql在设计TIME的时候，不关表示一天之内的时间，还可以用来表示一个时间间隔，这个时间间隔是可以超过24小时的；
上表中， TimeStamp范围 比 Datetime 小；

当前日期函数：CURRENT_DATE() 或 NOW()，前者只有年月日，后者年月日时分秒
当前时间函数：CURRENT_TIME() /CURTIME() 或 NOW()，前者只有时分秒，后者年月日时分秒

DateTime

可以使用 CURRENT_TIMESTAMP() 或者 NOW() 向 Datetime类型的字段中添加当前日期和时间；

TIMESTAMP

存储范围比较小，只 1970~2038年内；

在开发时，使用DateTime 或者 时间戳（无符号int或者bigint字段存储） ！
select UNIX_TIMESTAMP();    -- 1689153840

   

文本字符串

• 6种 CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
• 枚举类型        ENUM
• 集合类型        SET

CHAR 和 VARCHAR

字符串(文本)类型	特点	长度	长度范围	占用的存储空间
CHAR(M)	固定长度	M	0 <= M <= 255	M个字节
VARCHAR(M)	可变长度	M	0 <= M <= 65535	(实际长度 + 1) 个字节

CHAR

• CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。
• 如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在右侧填充空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。
• 定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占的存储空间的字节数。

VARCHAR

• VARCHAR(M) 定义时，必须指定长度M，否则报错。
• 检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节(这个1字节记录占用的长度)。

什么情况下用char和varchar?

类型	特点	空间上	时间上	适用场景
CHAR(M)	固定长度	浪费存储空间	效率高	存储不大，速度要求高
VARCHAR(M)	可变长度	节省存储空间	效率低	非CHAR的情况

情况1：存储很短的信息。比如门牌号码101，201……这样很短的信息应该用char，因为varchar还要占个byte用于存储信息长度，本来打算节约存储的，结果得不偿失。

情况2：固定长度的。比如使用uuid作为主键，那用char应该更合适。因为他固定长度，varchar动态根据长度的特性就消失了，而且还要占个长度信息。

情况3：十分频繁改变的column。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。

情况4：具体存储引擎中的情况：

• MyISAM数据存储引擎和数据列：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。这样使得整个表静态化，从而使数据检索更快，用空间换时间。
• MEMORY存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系，两者都是作为CHAR类型处理的。
• InnoDB 存储引擎，建议使用VARCHAR类型。因为对于InnoDB数据表，内部的行存储格式并没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），而且主要影响性能的因素是数据行使用的存储总量，由于char平均占用的空间多于varchar，所以除了简短并且固定长度的，其他考虑varchar。这样节省空间，对磁盘I/O和数据存储总量比较好。