• 技术文章 >数据库 >mysql教程

    mysql日期用什么类型

    青灯夜游青灯夜游2022-06-15 16:49:08原创86

    有5种类型：1、YEAR，用于表示年，格式为“YYYY”；2、TIME，用于只需要时间信息的值，格式为“HH:MM:SS”；3、DATE，用于仅需要日期值时，格式为“YYYY-MM-DD”；4、DATETIME，用于需要同时包含日期和时间信息的值，格式“YYYY-MM-DD HH:MM:SS”；5、TIMESTAMP，与DATETIME类似，用于需要同时包含日期和时间信息的值。

    本教程操作环境：windows7系统、mysql8版本、Dell G3电脑。

    MySQL 中有多个表示日期的数据类型：YEAR、TIME、DATE、DTAETIME、TIMESTAMP。

    类型名称 日期格式 日期范围 存储需求
    YEAR YYYY 1901 ~ 2155 1 个字节
    TIME HH:MM:SS -838:59:59 ~ 838:59:59 3 个字节
    DATE YYYY-MM-DD 1000-01-01 ~ 9999-12-3 3 个字节
    DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 8 个字节
    TIMESTAMP YYYY-MM-DD HH:MM:SS 1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC 4 个字节

    YEAR 类型

    YEAR 类型是一个单字节类型，用于表示年，在存储时只需要 1 个字节。可以使用各种格式指定 YEAR，如下所示：

    提示：两位整数范围与两位字符串范围稍有不同。例如，插入 3000 年，读者可能会使用数字格式的 0 表示 YEAR，实际上，插入数据库的值为 0000，而不是所希望的 3000。只有使用字符串格式的 '0' 或 '00'，才可以被正确解释为 3000，非法 YEAR值将被转换为 0000。

    TIME 类型

    TIME 类型用于只需要时间信息的值，在存储时需要 3 个字节。格式为 HH:MM:SS。HH 表示小时，MM 表示分钟，SS 表示秒。

    TIME 类型的取值范围为 -838：59：59～838：59：59，小时部分如此大的原因是 TIME 类型不仅可以用于表示一天的时间（必须小于 24 小时），还可能是某个事件过去的时间或两个事件之间的时间间隔（可大于 24 小时，或者甚至为负）。

    可以使用各种格式指定 TIME 值，如下所示。

    提示：为 TIME 列分配简写值时应注意：如果没有冒号，MySQL 解释值时，假定最右边的两位表示秒。（MySQL 解释 TIME 值为过去的时间而不是当前的时间）。例如，读者可能认为 '1112' 和 1112 表示 11：12：00（即 11 点过 12 分钟），但MySQL 将它们解释为 00：11：12（即 11 分 12 秒）。同样 '12' 和 12 被解释为00：00：12。相反，TIME 值中如果使用冒号则肯定被看作当天的时间，也就是说，'11：12' 表示 11：12：00，而不是 00：11：12。

    示例

    让我们看一下在TIME表中使用数据类型的示例。

    首先，创建一个新表命名为tests 一个由四列：id，name，start_at，和end_at。start_at和end_at列的数据类型是TIME。

    CREATE TABLE tests (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    start_at TIME,
    end_at TIME
);

    其次，在tests表中插入一行。

    INSERT INTO tests(name,start_at,end_at)
VALUES('Test 1', '08:00:00','10:00:00');

    第三，从tests表中查询数据。

    SELECT 
    name, start_at, end_at
FROM
    tests;

    1.png

    DATE 类型

    DATE 类型用于仅需要日期值时，没有时间部分，在存储时需要 3 个字节。日期格式为 'YYYY-MM-DD'，其中 YYYY 表示年，MM 表示月，DD 表示日。

    在给 DATE 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATE 的日期格式即可。如下所示：

    示例

    我们来看看下面的例子。

    首先，创建一个新表名为people，其中出生日期列为DATE数据类型。

    CREATE TABLE people (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_date DATE NOT NULL
);

    接下来，在people表中插入一行。

    INSERT INTO people(first_name,last_name,birth_date)
VALUES('John','Doe','1990-09-01');

    然后，查询数据来自people表。

    SELECT 
    first_name, 
    last_name, 
    birth_date
FROM
    people;

    2.png

    之后，使用两位数年份格式将数据插入people表中。

    INSERT INTO people(first_name,last_name,birth_date)
VALUES('Jack','Daniel','01-09-01'),
      ('Lily','Bush','80-09-01');

    在第一行中，我们使用01（范围00-69）作为年份，因此MySQL将其转换为2001年。在第二行中，我们使用80（范围70-99）作为年份，MySQL将其转换为1980年。

    最后，我们可以从people表中查询数据，以检查数据是否已根据转换规则进行转换。

    SELECT 
    first_name, 
    last_name, 
    birth_date
FROM
    people;

    3.png

    DATETIME 类型

    DATETIME 类型用于需要同时包含日期和时间信息的值，在存储时需要 8 个字节。日期格式为 'YYYY-MM-DD HH:MM:SS'，其中 YYYY 表示年，MM 表示月，DD 表示日，HH 表示小时，MM 表示分钟，SS 表示秒。

    在给 DATETIME 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATETIME 的日期格式即可，如下所示。

    DATETIME值可以包括具有YYYY-MM-DD HH:MM:SS[.fraction]例如的格式的尾随小数秒到微秒 2015-12-20 10:01:00.999999。包含小数秒精度时，DATETIME值需要更多存储空间，如下表所示：

    分数秒精度存储 (字节)
    00
    1, 21
    3, 42
    5, 63

    例如，2015-12-20 10:01:00.999999 需要8个字节，5个字节用于2015-12-20 10:01:00 ，3个字节用于.999999 而2015-12-20 10:01:00.9 只需要6个字节，1个字节用于小数秒精度。

    TIMESTAMP 类型

    TIMESTAMP 的显示格式与 DATETIME 相同，显示宽度固定在 19 个字符，日期格式为 YYYY-MM-DD HH：MM：SS，在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，为 '1970-01-01 00：00：01'UTC～'2038-01-19 03：14：07'UTC。在插入数据时，要保证在合法的取值范围内。

    提示：协调世界时（英：Coordinated Universal Time，法：Temps Universel Coordonné）又称为世界统一时间、世界标准时间、国际协调时间。英文（CUT）和法文（TUC）的缩写不同，作为妥协，简称 UTC。

    TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外，还有一个最大的区别是：

    提示：如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值，结果值的时间部分被设置为 '00：00：00'，因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值，结果值的时间部分被删除，因此DATE 值未包含时间信息。

    MySQL TIMESTAMP以UTC值存储。但是，MySQL将DATETIME值存储为没有时区的值。我们来看下面的例子。

    首先，将当前连接的时区设置为+00:00。

    SET time_zone = '+00:00';

    接下来，创建一个表命名timestamp_n_datetime是由两列组成：ts与dt用TIMESTAMP和DATETIME使用下面的语句类型。

    CREATE TABLE timestamp_n_datetime (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ts TIMESTAMP,
    dt DATETIME
);

    然后，将当前日期和时间插入 timestamp_n_datetime 表的两个ts和每dt列 ，

    INSERT INTO timestamp_n_datetime(ts,dt)
VALUES(NOW(),NOW());

    之后，从timestamp_n_datetime表中查询数据。

    SELECT 
    ts, 
    dt
FROM
    timestamp_n_datetime;

    4.png

    DATETIME和TIMESTAMP列中的值都相同。

    最后，将连接的时区设置为+03:00并再次从timestamp_n_datetime表中查询数据。

    SET time_zone = '+03:00';
 
SELECT 
    ts, 
    dt
FROM
    timestamp_n_datetime;

    5.png

    如您所见，TIMESTAMP列中的值不同。这是因为当我们更改时区时，TIMESTAMP列以UTC格式存储日期和时间值，根据新时区调整列的值。

    这意味着如果您使用TIMESTAMP数据存储日期和时间值，则在将数据库移动到位于不同时区的服务器时应认真考虑。

    【相关推荐：mysql视频教程】

    以上就是mysql日期用什么类型的详细内容，更多请关注php中文网其它相关文章！

    声明：本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn核实处理。
    专题推荐：mysql
    上一篇：mysql可以创建联合索引吗 下一篇：mysql int是什么
    php培训_php实战培训【立即报名】-php中文网第20期

    相关文章推荐

    • 【活动】充值PHP中文网VIP即送云服务器• mysql怎么取消所有权限• mysql怎么查询两个字段的值• mysql怎么将时分秒转换成秒数• mysql怎么将秒数转换成时分秒• linux怎么查询mysql端口
    1/1

    PHP中文网

    首页 首页课程 课程文章文章问答 问答博客博客词典 词典手册 手册资源 资源搜索 搜索APP下载 APP下载