MySQL 学习笔记

MySQL是一个开源的关系型数据库,非常流行。

如无特殊说明,本专题文章的 SQL 均在 5.6 版本中验证。

👉文章列表
MySQL 学习笔记 ⏬ 创建和删除数据库 切换和查看数据库 创建和删除表 在表中增加、删除、修改列 添加和删除索引 增删查改 创建和删除主键 大小写和反引号 字符串类型值的大小写 SQL注释 CHAR类型 VARCHAR类型 整型数字 时间戳 不要使用utf8 唯一索引与NULL 唯一索引的单列长度限制 NULL的判等 DDL、DML、DCL的含义 查看表的状态 show table status InnoDB存储引擎的限制 if和case的使用 自定义函数 InnoDB 索引 事务 事务隔离级别的概念、查看和修改 读已提交(read committed)隔离级别下的锁 可重复读(repeatable read)隔离级别下的锁 修改自增主键id的类型 使用 load data 快速导入数据 使用 select into outfile 导出数据

MySQL : datetime 类型


目录:


简介

datetime 支持的范围是 1000-01-01 00:00:00.0000009999-12-31 23:59:59.999999

datetime(n) 表示秒后面的6位微妙,保留/展示最高n位。

千万不要使用 timestamp 类型,因为它支持的时间范围是 1970-01-01 00:00:01.0000002038-01-19 03:14:07.999999。范围太小了。

datetime 的取值可以是 0000-00-00 00:00:00,这个值不在其支持范围内,可以理解为和字符串类型字段的空字符串类似,代表一个空时间,但又与 null 不同。

使用 Java JDBC 获取时,默认会抛出异常。解决办法是,在 JDBC url 加上zeroDateTimeBehavior=convertToNull,遇到全0时间,转换为 Java 的 null。JDBC url 示例:

jdbc:mysql://127.0.0.1:3306/blog_db?zeroDateTimeBehavior=convertToNull

示例1: 数据插入

建表:

DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (
    id bigint unsigned auto_increment,
    created_at datetime(3) NOT NULL COMMENT '创建时间', 
    primary key(id)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;

插入数据:

mysql> insert into test_table (created_at) values('2020-05-02 20:57:44');
mysql> insert into test_table (created_at) values('2020-05-02 20:57:44.12356');
mysql> insert into test_table (created_at) values('2020-05-02 20:57:44.12');

查询数据:

mysql> select * from test_table;
+----+-------------------------+
| id | created_at              |
+----+-------------------------+
|  1 | 2020-05-02 20:57:44.000 |
|  2 | 2020-05-02 20:57:44.124 |
|  3 | 2020-05-02 20:57:44.120 |
+----+-------------------------+

示例2: 默认为当前时间

DEFAULT CURRENT_TIMESTAMP 可以让字段默认为当前时间。

建表:

DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (
    id bigint unsigned auto_increment,
    created_at_0 datetime NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
    created_at_1 datetime(1) NOT NULL DEFAULT CURRENT_TIMESTAMP(1) COMMENT '创建时间', 
    created_at_4 datetime(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4) COMMENT '创建时间', 
    primary key(id)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;

插入数据:

mysql> insert into test_table (id) values(1);

查询:

mysql> select * from test_table;
+----+---------------------+-----------------------+--------------------------+
| id | created_at_0        | created_at_1          | created_at_4             |
+----+---------------------+-----------------------+--------------------------+
|  1 | 2020-05-02 13:05:26 | 2020-05-02 13:05:26.4 | 2020-05-02 13:05:26.4305 |
+----+---------------------+-----------------------+--------------------------+

示例3: 数据记录变化时时间自动更新

字段加上 ON UPDATE CURRENT_TIMESTAMP 属性即可。

建表:

DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (
    id bigint unsigned auto_increment,
    created_at datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间', 
    updated_at datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '修改时间',  -- 数据记录变更时,时间会自动变化
    primary key(id)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;

插入数据:

mysql> insert into test_table (id) values(1);

查询:

mysql> select * from test_table;
+----+-------------------------+-------------------------+
| id | created_at              | updated_at              |
+----+-------------------------+-------------------------+
|  1 | 2020-05-02 13:49:57.362 | 2020-05-02 13:49:57.362 |
+----+-------------------------+-------------------------+

注意,必须是数据发生变化,时间才会自动发生变化。

update test_table set id=1 where id=1; 不会导致数据变化,所以时间不会变化。

update test_table set id=2 where id=1; 会导致数据发生变化,所以时间会变化。

示例4: 时区(todo)

建表:

DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (
    id bigint unsigned auto_increment,
    created_at datetime(3) NOT NULL COMMENT '创建时间', 
    primary key(id)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;

插入数据:

mysql> insert into test_table (created_at) values('2020-05-02 20:57:44');

查询时区和数据:

mysql> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| SYSTEM      |
+-------------+

mysql> select * from test_table;
+----+---------------------+
| id | created_at          |
+----+---------------------+
| 1  | 2020-05-02 20:57:44 |
+----+---------------------+

示例5: 关于 0000-00-00 00:00:00

建表:

DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (
    id bigint unsigned auto_increment,
    created_at datetime COMMENT '创建时间', 
    primary key(id)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;

插入数据:

insert into test_table (id, created_at) values(1, '2020-05-02 20:57:44');
insert into test_table (id, created_at) values(2, '0000-00-00 00:00:00');
insert into test_table (id, created_at) values(3, null);

查询:

mysql> select * from test_table;
+----+---------------------+
| id | created_at          |
+----+---------------------+
|  1 | 2020-05-02 20:57:44 |
|  2 | 0000-00-00 00:00:00 |
|  3 | NULL                |
+----+---------------------+

mysql> select * from test_table where created_at is null;
+----+------------+
| id | created_at |
+----+------------+
|  3 | NULL       |
+----+------------+

mysql> select * from test_table where created_at = '0000-00-00 00:00:00';
+----+---------------------+
| id | created_at          |
+----+---------------------+
|  2 | 0000-00-00 00:00:00 |
+----+---------------------+

示例6: 判等与比较大小

建表:

DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (
    id bigint unsigned auto_increment,
    created_at datetime(3) COMMENT '创建时间', 
    primary key(id)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;

插入数据:

insert into test_table (id, created_at) values(1, '2020-05-02 20:57:44');
insert into test_table (id, created_at) values(2, '2020-05-02 12:57:44');
insert into test_table (id, created_at) values(3, '0000-00-00 00:00:00');
insert into test_table (id, created_at) values(4, null);

查询所有:

mysql> select * from test_table;
+----+-------------------------+
| id | created_at              |
+----+-------------------------+
|  1 | 2020-05-02 20:57:44.000 |
|  2 | 2020-05-02 12:57:44.000 |
|  3 | 0000-00-00 00:00:00.000 |
|  4 | NULL                    |
+----+-------------------------+

判等查询:

mysql> select * from test_table where created_at is null;
+----+------------+
| id | created_at |
+----+------------+
|  4 | NULL       |
+----+------------+

mysql> select * from test_table where created_at = '0000-00-00 00:00:00';
+----+-------------------------+
| id | created_at              |
+----+-------------------------+
|  3 | 0000-00-00 00:00:00.000 |
+----+-------------------------+

mysql> select * from test_table where created_at = '2020-05-02 20:57:44';
+----+-------------------------+
| id | created_at              |
+----+-------------------------+
|  1 | 2020-05-02 20:57:44.000 |
+----+-------------------------+

比较查询:

mysql> select * from test_table where created_at > '0000-00-00 00:00:00';
+----+-------------------------+
| id | created_at              |
+----+-------------------------+
|  1 | 2020-05-02 20:57:44.000 |
|  2 | 2020-05-02 12:57:44.000 |
+----+-------------------------+

mysql> select * from test_table where created_at >= '2020-05-02 12:57:43';
+----+-------------------------+
| id | created_at              |
+----+-------------------------+
|  1 | 2020-05-02 20:57:44.000 |
|  2 | 2020-05-02 12:57:44.000 |
+----+-------------------------+

mysql> select * from test_table where created_at >= '2020-05-02 13:57:43';
+----+-------------------------+
| id | created_at              |
+----+-------------------------+
|  1 | 2020-05-02 20:57:44.000 |
+----+-------------------------+

mysql> select * from test_table where created_at < '2020-05-02 13:57:43';
+----+-------------------------+
| id | created_at              |
+----+-------------------------+
|  2 | 2020-05-02 12:57:44.000 |
|  3 | 0000-00-00 00:00:00.000 |
+----+-------------------------+

( 本文完 )

文章目录