主页 / dba / 问题 / 305395
Accepted
Déjà vu
Asked: 2021-12-26 19:02:07 +0800 CST

时间戳类型是否有（隐藏的）毫秒？

  • 772

该updated列创建为

| updated | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

一个简单的SELECT给

+---------------------+
| updated             |
+---------------------+
| 2021-12-25 00:15:47 |
+---------------------+

不幸的是，updated没有创建为timestamp(3).

有什么方法可以从中提取更多的准确性updated？（MySQL 是否存储比秒更高的精度，即使它只显示秒数？）

mysql-5.7 timestamp

2 个回答

  1. 有没有办法从更新中提取更多的准确性？

    不可以。无法选择表格中不存在的数据，并且无法根据表格中存在的数据进行计算。

    MySQL 是否存储比秒更高的精度，即使它只显示秒？

    不，它完全存储了您要求的内容。它最多可以存储微秒 - 但您没有要求这种准确性。

    嗯，这个问题真的是似曾相识！至少在 dbfiddle.uk 和 dbfiddle.com 上，MySQL 的实现似乎存在一个错误......（quelle suprise - MySQL 中的一个错误......）。– Vérace - 立即接种疫苗

    你打电话NOW()- 你得到没有毫秒的日期时间。调用NOW(3)orNOW(6)并获得更准确的值。

    https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=ccaf9b2c25e372c7bf0ca82c66ded787

    好吧，来自 MySQL 阵营的好消息 - 你根本不需要担心一秒的小数值 - 5.7 不支持它们 - Vérace - 现在接种疫苗

    永远不要再咨询那个说这种废话的 MySQL 阵营的人。

    • 4
  2. Best Answer

    <TL;DR>

    先回答问题：

    • 有没有办法从更新中提取更多的准确性？（MySQL 是否存储比秒更高的精度，即使它只显示秒数？）

    我的研究强烈表明，如果没有指定精度，则无法恢复比最初存储在字段中更多的数据（这是TIMESTAMP(0)（）的默认值）（请参阅下面的讨论）。p

    可以肯定地说，这是因为如果查看此处各种类型的存储要求，您会看到：

    Data Type   Storage requirement
Timestamp   4 bytes + fractional seconds storage

    在此之下，您将看到：

    在此处输入图像描述

    因此，我从中得到的是，如果您选择不以TIMESTAMP高于 0 的精度专门存储 s，那么您将失去所有更高的精度，因为没有为该额外精度保留空间。

    一个完全确定的答案将需要一个十六进制编辑器和所有家庭探索.ibd文件的乐趣和/或通过 MySQL 的源代码拖网，这两者都比我的工资等级高一点！我认为您可以认为您的额外精度已经消失，或者更确切地说，它从一开始就不存在！

    </TL;DR>

    在评论中犯了一些错误（自删除以来），我决定实际测试（正如他在他自己的回答下的评论中所建议的那样）我的断言（mea culpa, mea maxima culpa）。

    我很高兴地报告我对舍入问题是正确的 - 如果插入一个精度为 6 秒的时间，则该值将被舍入而不被截断。

    下面的所有代码（除非另有说明）都可以在此处的小提琴中找到。

    MySQLTIMESTAMP和时区！

    对于 MySQL，TIMESTAMP 存储为自 UTC 以来的秒数 - 从这里开始：

    时间戳。范围是 '1970-01-01 00:00:01.000000' UTC 到 '2038-01-19 03:14:07.999999' UTC。TIMESTAMP 值存储为自纪元以来的秒数（'1970-01-01 00:00:00' UTC）。

    顺便说一句，这里TIMESTAMP还有另一个小提琴，它显示了 MySQL和之间的区别DATETIME——后一种数据类型没有时区。

    我强烈敦促您（或任何开发人员）在可能的情况下始终使用 UTC 来存储时间 - 它更简单、更清洁，并且服务器会为您处理夏令时 (DST)。

    此外，如果您必须使用本地时区，请不要使用偏移量（小提琴中的 +07:00），因为这样您会丢失任何 DST 信息 - 我这样做的原因是因为 db<>fiddle 的 MySQL 实现不支持时区（请参见此处），但它确实支持偏移量。

    MySQL 安装过程创建时区表，但不加载它们。

    更多关于 MySQL的信息TIMESTAMP：

    无论如何，让我感到震惊的第一件事是TIMESTAMP没有精确度的事实TIMESTAMP(0)，但这是在手册中：

    可以给出 0 到 6 范围内的可选 fsp 值来指定小数秒精度。值 0 表示没有小数部分。如果省略，则默认精度为 0。

    从这里开始：

    （这与标准 SQL 默认值 6 不同，以便与以前的 MySQL 版本兼容。）

    很公平，但他们至少[could | should]有一个服务器 SQL 模式设置来覆盖它，并将其更改为 SQL 标准行为——他们以前为一堆其他 MySQL 主义做过这个！

    另一个棘手的问题（但再次记录在案）是ROUND当您尝试将较高精度的分数压缩到较低精度的字段时会发生 ing ：

    将带有小数秒部分的 TIME、DATE 或 TIMESTAMP 值插入到相同类型但小数位数较少的列中会导致舍入。

    MySQL的行为：

    因此，现在，我们将检查实际行为如下：

    CREATE TABLE test 
(
  s TIMESTAMP,
  t TIMESTAMP(0) NULL DEFAULT NULL,
  u TIMESTAMP(1) NULL DEFAULT NULL,
  v TIMESTAMP(2) NULL DEFAULT NULL,
  w TIMESTAMP(3) NULL DEFAULT NULL,
  x TIMESTAMP(4) NULL DEFAULT NULL,
  y TIMESTAMP(5) NULL DEFAULT NULL,
  z TIMESTAMP(6) NULL DEFAULT NULL
);

    然后，填充它：

    INSERT INTO test VALUES -- insert the same value 8 times into the different fields!
(
  '2021-12-27 10:30:15.567899',
  '2021-12-27 10:30:15.567899', '2021-12-27 10:30:15.567899', 
  '2021-12-27 10:30:15.567899', '2021-12-27 10:30:15.567899',
  '2021-12-27 10:30:15.567899', '2021-12-27 10:30:15.567899',
  '2021-12-27 10:30:15.567899'
);

    然后我们检查我们的数据：

    SELECT 's' AS dt, s  AS ts FROM test  UNION
SELECT 't',       t        FROM test  UNION
SELECT 'u',       u        FROM test  UNION
SELECT 'v',       v        FROM test  UNION
SELECT 'w',       w        FROM test  UNION
SELECT 'x',       x        FROM test  UNION
SELECT 'y',       y        FROM test  UNION
SELECT 'z',       z        FROM test;

    结果：

    dt  ts
s   2021-12-27 10:30:16.000000
t   2021-12-27 10:30:16.000000
u   2021-12-27 10:30:15.600000
v   2021-12-27 10:30:15.570000
w   2021-12-27 10:30:15.568000
x   2021-12-27 10:30:15.567900
y   2021-12-27 10:30:15.567900
z   2021-12-27 10:30:15.567899

    因此，我们可以看到（如记录的那样）TIMESTAMP和TIMESTAMP(0)（无论如何都是相同的）都从15.567899to向上16舍入，并且根据指定的精度，这种舍入继续向下行 - 根据手册！

    那么，如何补救呢？

    有两种可能性-第一种是您正在从某个地方进行旧的 TIMESTAMP(6) 测量，并且您需要截断（而不是舍入它们-参见主要小提琴），和/或可能，您可能想要用作TIMESTAMP(3)您的字段类型和NOW(3)您的输入（不同的小提琴）。

    • TIMESTAMP(6) 测量：

    这些将需要截断TIMESTAMP(6)字段中的值。事实证明，这比看起来要棘手 - 请参阅各个阶段的小提琴，但我能想到的最好的方法是：

    TIMESTAMP(SUBSTRING(DATE_FORMAT(ts, '%Y-%m-%d %H:%i:%s.%f'), 1, 23))

    这可能看起来有点像绕房子转，但是 MySQLDATE_FORMAT函数具有微秒粒度，但（奇怪的是）不是毫秒！欢迎任何关于更简单、更优雅的解决方案的建议！

    然后可以将该值插入 ap = 3 TIMESTAMP（参见fiddle）。

    • NOW() 函数：

    （手册）说：

    如果给出 fsp 参数以指定从 0 到 6 的小数秒精度，则返回值包括那么多数字的小数秒部分。

    所以，快乐的日子。并检查（小提琴）：

    CREATE TABLE now_test 
(
  s TIMESTAMP,
  t TIMESTAMP(0) NULL DEFAULT NULL,
  u TIMESTAMP(1) NULL DEFAULT NULL,
  v TIMESTAMP(2) NULL DEFAULT NULL,
  w TIMESTAMP(3) NULL DEFAULT NULL,
  x TIMESTAMP(4) NULL DEFAULT NULL,
  y TIMESTAMP(5) NULL DEFAULT NULL,
  z TIMESTAMP(6) NULL DEFAULT NULL
);

    插入八个NOW(p)值：

    INSERT INTO now_test VALUES
(NOW(), NOW(0), NOW(1), NOW(2), NOW(3), NOW(4), NOW(5), NOW(6));

    接着：

    SELECT 'NOW()' , 's' AS dt, s  AS ts FROM now_test  UNION
SELECT 'NOW(0)', 't',       t        FROM now_test  UNION
SELECT 'NOW(1)', 'u',       u        FROM now_test  UNION
SELECT 'NOW(2)', 'v',       v        FROM now_test  UNION
SELECT 'NOW(3)', 'w',       w        FROM now_test  UNION
SELECT 'NOW(4)', 'x',       x        FROM now_test  UNION
SELECT 'NOW(5)', 'y',       y        FROM now_test  UNION
SELECT 'NOW(6)', 'z',       z        FROM now_test;

    结果：

    NOW()   dt  ts
NOW()   s   2021-12-27 19:28:38.000000
NOW(0)  t   2021-12-27 19:28:38.000000
NOW(1)  u   2021-12-27 19:28:38.600000
NOW(2)  v   2021-12-27 19:28:38.620000
NOW(3)  w   2021-12-27 19:28:38.628000
NOW(4)  x   2021-12-27 19:28:38.628700
NOW(5)  y   2021-12-27 19:28:38.628760
NOW(6)  z   2021-12-27 19:28:38.628767

    我们可以从上面的示例中看到，如果发生舍入，则w字段 ( NOW(3)) 将被舍入到，19:28:38.629因为以下7. 为了使这一点变得明显，可能需要运行几次小提琴！

    磁盘大小：

    我试图找到一个查询来获取磁盘上 MySQL 字段的各个大小，但 MySQL 显然没有公开这些信息——至少不是通过它的 SQL 接口。PostgreSQL 具有该pg_column_size()功能，并且还有pageinspectcontrib 扩展，供那些喜欢使用 hex dumps 和bit-twiddling 的人使用。

    除了获得十六进制编辑器和/或深入研究 MySQL 的源代码之外，我能想到的最好的方法是这样的（见小提琴底部）：

    SELECT
  table_name  AS "The table",
  data_length AS "Data length"
FROM
  information_schema.tables
WHERE
    TABLE_NAME = 'test'
ORDER BY
  1 + 2
DESC;

    结果：

    The table   Data length
     test         16384

    因此，8 条记录需要 16kB。快速搜索显示（手册）：

    每个表空间由数据库页组成。MySQL 实例中的每个表空间都具有相同的页面大小。默认情况下，所有表空间的页大小为 16KB；

    在同一页面上，提到了innodb_page_size：

    SHOW VARIABLES LIKE 'innodb_page_size';

    结果：

    Variable_name   Value
innodb_page_size    16384

    所以，Data Length...上面的所有查询都告诉我们，我们已经为一个小表使用了 1 页磁盘空间——这不足为奇，也没有太多帮助。请参阅我在<TL;DR>介绍中关于存储要求的讨论，以了解为什么（很可能）您的额外精度位已经进入了天空中的那个大桶...... de profundis ......。

    • 4

相关问题