【MySQL】数据类型

一、 整数类型

类型	位(字节)	SIGNED(默认)	UNSIGNED
TINYINT	8(1)	-2^7 → 2^7-1	0 → 2^8-1
SMALLINT	16(2)	-2^15 → 2^15-1	0 → 2^16-1
MEDIUMINT	24(3)	-2^23 → 2^23-1	0 → 2^24-1
INT	32(4)	-2^31 → 2^31-1	0 → 2^32-1
BIGINT	64(8)	-2^63 → 2^63-1	0 → 2^64-1

• 有符号SIGNED
  1位符号位。共有N位：最小值=-2^(N-1)， 最大值=2^(N-1)-1

• 无符号UNSIGNED
  全部bit用来存储数值。共有N位：最小值=0，最大值=2^N-1

二、 浮点数类型

类型	精度位数(字节)	尾数位数	能精确表示的十进制有效数字
FLOAT	32(4)	23	约6~7位十进制有效数字
DOUBLE	64(8)	52	约15~16位十进制有效数字

浮点数精度 = 保证前多少位有效数字准确 ≠ 保留多少位小数

浮点数格式：符号位sign、指数位exponent、尾数位mantissa

三、 定点数类型

类型	最大值	最小值
DECIMAL(M,D)	10^(M-D) - 10^(-D)	相反数

• M ： 总位数
• D ： 小数位数
• M-D ： 整数位数
• DECIMAL使用十进制存储；按字符串存储，不存在二进制转换误差；可以精确表示任意小数

四、 日期和时间

类型	格式	范围	精度	常见用途
DATE	YYYY-MM-DD	1000-01-01~9999-12-31	无时间	生日/订单日期
TIME	HH:MM:SS	-838:59:59~838:59:59	可加微秒	时长/时间间隔
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01~9999-12-31	可加微秒	常用的日期+时间（不带时区）
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01~2038-01-19	可加微秒	自动记录创建时间、更新时间（带时区）
YEAR	YYYY	1901~2155	年份	记录年份（少用），一般用INT或DATE代替

日期与时间的常见用法

1. 获取当前时间

SELECT CURRENT_DATE();     -- 2025-11-14
SELECT CURRENT_TIME();     -- 13:28:00
SELECT NOW();              -- datetime 格式
SELECT CURRENT_TIMESTAMP;  -- 同 NOW()

2. 提取日期部分和时间部分

SELECT DATE(NOW());   -- 2025-11-14
SELECT TIME(NOW());   -- 13:28:00

3. 提取年月日时分秒部分

SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

4. 日期计算

• 常见语法：
  DATE_ADD(date，INTERVAL，N UNIT)
  DATE_SUB(date，INTERVAL，N UNIT)
• 支持单位UNIT：
  SECOND，MINUTE，HOUR，DAY，WEEK，MONTH，YEAR

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);    -- 加 7 天
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);  -- 减 1 个月

5. 格式化日期

格式符	意义
%Y	年（4位）
%m	月（01-12）
%d	日
%H	时（00-23,24小时）
%h	时（01-12 带AM/PM,12小时）
%i	分
%s	秒

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');
SELECT DATE_FORMAT(NOW(), '%Y/%m/%d %H:%i:%s');

-- %H 专用于 24 小时制，%h/%I 专用于 12 小时制
SELECT DATE_FORMAT('2025-11-14 14:30:00', '%H:%i');   -- 14:30
SELECT DATE_FORMAT('2025-11-14 14:30:00', '%h:%i %p'); -- 02:30 PM

6. 比较日期

SELECT * FROM orders
WHERE order_date >= '2025-01-01';

7. 自动更新时间戳机制

语句	意义
DEFAULT CURRENT_TIMESTAMP	插入记录时自动填入当前时间
ON UPDATE CURRENT_TIMESTAMP	更新记录时自动更改为当前时间

-- 创建表
CREATE TABLE test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content VARCHAR(50),
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                 ON UPDATE CURRENT_TIMESTAMP
);

-- 插入数据时
INSERT INTO test (content) VALUES ('hello'); -- 数据库自动生成update_time为2025-11-14 14:00:00

-- 更新数据时
UPDATE test SET content = 'world' WHERE id = 1; -- 数据库自动更新update_time为2025-11-14 14:05:10，不需要手动写

日期类型之间的转换

1. 字符串 → 日期

-- STR_TO_DATE()：将字符串按照格式模板解析成 DATE / DATETIME / TIME 类型,它能解析年、月、日、时、分、秒
SELECT STR_TO_DATE('2025-11-14', '%Y-%m-%d');
SELECT STR_TO_DATE('11/14/2025 12:30', '%m/%d/%Y %H:%i');

2. 日期 → 字符串（格式化）

SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日');

3. CAST转换（SQL标准，可在多种数据库使用）

SELECT CAST('2025-11-14' AS DATE);
SELECT CAST('2025-11-14 14:00:00' AS DATETIME);

-- 可以舍弃部分信息（截断，而不是四舍五入）
SELECT CAST('2025-11-14 14:00:00' AS DATE); -- 2025-11-14
SELECT CAST('2025-11-14 14:33:55' AS TIME); -- 14:33:55

4. DATE / TIME / DATETIME 互相转换

• DATE → DATETIME

SELECT CONCAT(date_col, ' 00:00:00') AS datetime_value;

• DATETIME → DATE

SELECT DATE(datetime_col);

• DATETIME → TIME

SELECT TIME(datetime_col);

• TIME → 秒数

-- TIME_TO_SEC()：把整个时间（hh:mm:ss）转成总秒数
SELECT TIME_TO_SEC('02:30:00');   -- 9000

• 秒数 → TIME

SELECT SEC_TO_TIME(9000);        -- 02:30:00

五、 字符串类型

在MySQL中，字符串类型：CHAR、VARCHAR、TEXT、BINARY、VARBINARY、BLOB、ENUM、SET

• 以字符为单位定义长度：CHAR、VARCHAR、TEXT
• 以字节为单位定义长度：BINARY、VARBINARY、BLOB
• 可以指定字符集格式：CHAR、VARCHAR、ENUM、SET

CHAR 与 VARCHAR

	CHAR	VARCHAR
长度	固定 （MySQL会填充空格）	可变
存储空间	总是n字节	实际长度 + 1/2字节
写入速度	较快	较慢（要维护长度字段）
空间利用率	低	高
更新成本	低（不变化）	高（可能移动行）
适用	固定长度字段 字段长度始终一致	变长字段 长度不固定

输入	CHAR(10)存储内容	VARCHAR(10)存储内容
'abc'	'abc       '	'abc'
'abcdefghij'	'abcdefjhi'	'abcdefjhi'
'abc   '	'abc       '	'abc   '

CHAR在取出时 MySQL 会自动去掉尾部空格，所以用户看到正常字符串

六、 超出范围和溢出

类型	本质含义	发生场景
超出范围（Out of Range）	插入的值不在该数据类型允许的最小值~最大值区间内	写入数据时（INSERT / UPDATE）
溢出（Overflow）	数据在内部计算或存储时超过了类型能表示的二进制容量	计算过程中（加减乘除、表达式）

• 超出范围 = 写数据时超过边界 → 写不进去或被截断

SQL模式	结果
非严格模式	截断为最大值或最小值，并生成warning
严格模式	报错，写入失败

• 溢出 = 运算过程中数值变太大/太小 → 计算结果错误

SQL模式	结果
非严格模式	截断成最大值
严格模式	产生错误
某些版本	结果为NULL