声明

本文中的案例仅供参考，如需使用请严格做好相关测试及评估，对于因参照本文内容进行操作而导致的任何直接或间接损失，作者概不负责。

MySQL时间格式化存VARCHAR：yyyyMMddHHmmss

在 MySQL 中，你可以使用 NOW() 函数获取当前日期和时间，然后使用 DATE_FORMAT() 函数将其格式化为你需要的 yyyyMMddHHmmss 字符串格式。

时间字段是 VARCHAR 类型，格式是 yyyyMMddHHmmss。

格式化字符串说明:

• %Y: 四位数字的年份 (例如 2023)
• %m: 两位数字的月份 (01-12)
• %d: 两位数字的日期 (01-31)
• %H: 两位数字的小时 (24小时制, 00-23)
• %i: 两位数字的分钟 (00-59)
• %s: 两位数字的秒 (00-59)

所以，你需要使用的格式化字符串是 '%Y%m%d%H%i%s'。

1. INSERT 语句

假设你的表名为 your_table，时间字段名为 time_varchar_col，其他字段为 other_col：

INSERT INTO your_table (time_varchar_col, other_col)
VALUES (DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), '一些其他数据');

2. UPDATE 语句

假设你要更新 id 为 1 的记录：

UPDATE your_table
SET time_varchar_col = DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'),
    other_col = '更新后的数据'
WHERE id = 1;

3. INSERT ... ON DUPLICATE KEY UPDATE 语句

如果你想在插入时如果主键或唯一键冲突则执行更新操作：

INSERT INTO your_table (id, time_varchar_col, other_col)
VALUES (1, DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), '初始数据')
ON DUPLICATE KEY UPDATE
    time_varchar_col = DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'),
    other_col = '数据已存在，已更新';

或者，如果你希望 time_varchar_col 在冲突时也使用新生成的值：

INSERT INTO your_table (id, time_varchar_col, other_col)
VALUES (1, DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), '初始数据')
ON DUPLICATE KEY UPDATE
    time_varchar_col = VALUES(time_varchar_col), -- 或者再次调用 DATE_FORMAT(NOW(), '%Y%m%d%H%i%s')
    other_col = '数据已存在，已更新';

VALUES(time_varchar_col) 会引用 INSERT 部分为该列提供的值。

示例:

假设有表 logs:

CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_time VARCHAR(14),
    message VARCHAR(255)
);

插入一条新日志：

INSERT INTO logs (event_time, message)
VALUES (DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), '用户登录成功');

更新 id 为 1 的日志的 event_time（通常你可能只更新其他字段，但为了演示）：

UPDATE logs
SET event_time = DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'),
    message = '用户登录信息已更新'
WHERE id = 1;

重要提示：关于使用 VARCHAR 存储时间

虽然上述方法可以满足你的需求，但通常不推荐使用 VARCHAR 类型来存储日期和时间。原因如下：

1. 查询效率低：基于字符串的日期比较、排序和范围查询比使用原生的 DATETIME 或 TIMESTAMP 类型要慢得多。
2. 无法使用日期函数：你不能直接对 VARCHAR 字段使用 MySQL 内置的日期/时间函数（如 DATE_ADD(), DATEDIFF() 等），除非先将其转换为日期类型。
3. 数据校验困难：更容易插入无效的日期格式。
4. 存储空间：VARCHAR(14) 通常比 DATETIME (8字节) 或 TIMESTAMP (4字节 + 时区转换开销) 占用更多空间（取决于字符集，UTF-8下14个字符可能需要14-42字节）。

推荐的做法是：

• 使用 DATETIME 或 TIMESTAMP 类型存储时间。
• 在应用层面或者在查询时，如果需要 yyyyMMddHHmmss 格式，再使用 DATE_FORMAT() 进行格式化输出。

例如，如果你的字段是 DATETIME 类型：

CREATE TABLE logs_datetime (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_time DATETIME,
    message VARCHAR(255)
);

-- 插入
INSERT INTO logs_datetime (event_time, message)
VALUES (NOW(), '用户登录成功');

-- 更新
UPDATE logs_datetime
SET event_time = NOW(), -- 或者 specific_datetime
    message = '用户登录信息已更新'
WHERE id = 1;

-- 查询时格式化
SELECT
    id,
    DATE_FORMAT(event_time, '%Y%m%d%H%i%s') AS formatted_event_time,
    message
FROM logs_datetime;

如果由于历史原因或与其他系统集成等原因，你必须使用 VARCHAR，那么 DATE_FORMAT(NOW(), '%Y%m%d%H%i%s') 就是正确的做法。

更多文章一键直达

冷不叮的小知识