V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
coderstory
V2EX  ›  MySQL

关于 MYSQL8 时间类型字段的一些疑问

  •  
  •   coderstory ·
    coderstory · 2022-09-20 10:54:41 +08:00 · 2070 次点击
    这是一个创建于 807 天前的主题,其中的信息可能已经有所发展或是发生改变。

    数据库使用 mysql 8.0.16

    管理工具使用 Navicat


    创建表 SQL

    CREATE TABLE
    IF
            NOT EXISTS `Table2` (
                    `id` BIGINT NOT NULL AUTO_INCREMENT,
                    `Attribute1` datetime NOT NULL DEFAULT ( CURRENT_TIMESTAMP ),
                    `Attribute2` date NOT NULL DEFAULT ( CURRENT_DATE ),
                    `Attribute3` datetime NULL,
                    `Attribute4` date NULL,
                    `created_time` datetime NULL,
                    `updated_time` datetime NULL,
                    `created_by` BIGINT NULL,
                    `updated_by` BIGINT NULL,
            PRIMARY KEY ( Id ));
    
    

    Attribute1 是 datetime 类型 默认值使用了函数 CURRENT_TIMESTAMP

    Attribute2 是 date 类型 使用默认值函数 CURRENT_DATE ,都是必填

    查询表结构

    SHOW FULL COLUMNS FROM `table2`
    

    可以看到 Attribute1 的默认值变成了 now() Attribute2 的默认值变成了 curdate()

    我很疑惑 为什么 默认值 换了函数?

    新建索引

    ALTER TABLE`table2` 
    ADD INDEX `111`(`Attribute1`, `Attribute2`);
    
    > 1067 - Invalid default value for 'Attribute1'
    > 时间: 0.002s
    
    

    在这 2 个字段上加索引会报错 错误的默认值

    修改字段类型 并加索引

    ALTER TABLE `rcpr_rd1_aa1_b`.`table2` 
    MODIFY COLUMN `Attribute1` datetime NOT NULL DEFAULT (CURRENT_TIMESTAMP) AFTER `id`,
    MODIFY COLUMN `Attribute2` date NOT NULL DEFAULT (CURRENT_DATE) AFTER `Attribute1`,
    ADD INDEX `121`(`Attribute1`, `Attribute2`);
    

    此时 我直接修改 这 2 个字段的类型 并直接加上 索引

    挺神奇的,居然没报错,索引也加上去了。

    疑问:

    mysql 的那两个默认值为什么会变

    为什么加索引提示错误默认值

    修改字段语句和加索引语句放一起为什么又没报错

    第 1 条附言  ·  2022-09-20 13:23:47 +08:00
    贴的 SQL 有点问题 ,补充下

    lower_case_table_names 值为 1

    所有的 sql 都是操作 table2


    ```sql

    ALTER TABLE `table2`
    ADD INDEX `111`(`Attribute1`, `Attribute2`)
    > 1067 - Invalid default value for 'Attribute1'
    > 时间: 0.002s

    ALTER TABLE `table2`
    MODIFY COLUMN `Attribute1` datetime NOT NULL DEFAULT (CURRENT_TIMESTAMP) AFTER `id`,
    MODIFY COLUMN `Attribute2` date NOT NULL DEFAULT (CURRENT_DATE) AFTER `Attribute1`,
    ADD INDEX `123`(`Attribute1`, `Attribute2`)
    > OK
    > 时间: 0.012s

    ```
    10 条回复    2022-09-21 08:56:42 +08:00
    asmile1993
        1
    asmile1993  
       2022-09-20 11:42:37 +08:00
    Q1: mysql 的那两个默认值为什么会变
    CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW().
    CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE().

    Q2: 为什么加索引提示错误默认值
    执行成功了,你是不是分别有 Table2 和 table2 这两张表?看看自己的 lower_case_table_names 参数是否为 0
    leegradyllljjjj
        2
    leegradyllljjjj  
       2022-09-20 11:55:27 +08:00
    1. 那不是默认值,那是默认的取值函数;
    2. show variables where Variable_name in ('lower_case_table_names'); 看看是否区分表名大小写;
    3. 同 2
    顺便吐槽一下:有些高版本的 mysql 驱动是真的坑人啊
    coderstory
        3
    coderstory  
    OP
       2022-09-20 13:20:03 +08:00
    @asmile1993 代码贴的有问题 都是 table2


    ```
    ALTER TABLE `table2`
    ADD INDEX `111`(`Attribute1`, `Attribute2`);


    ALTER TABLE `table2`
    MODIFY COLUMN `Attribute1` datetime NOT NULL DEFAULT (CURRENT_TIMESTAMP) AFTER `id`,
    MODIFY COLUMN `Attribute2` date NOT NULL DEFAULT (CURRENT_DATE) AFTER `Attribute1`,
    ADD INDEX `12111`(`Attribute1`, `Attribute2`);
    ```

    第二个没报错
    coderstory
        4
    coderstory  
    OP
       2022-09-20 13:21:39 +08:00
    @leegradyllljjjj

    忽略大小写的

    lower_case_table_names 是 1

    贴的 sql 有点问题 实际都是 table2
    asmile1993
        5
    asmile1993  
       2022-09-20 14:03:06 +08:00
    @coderstory 我的版本是 8.0.26 ,加索引操作没问题
    nothingistrue
        6
    nothingistrue  
       2022-09-20 14:30:08 +08:00
    根源:SQL 本身就是弱类型语言,MYSQL 又是特别“聪明”的数据库,所以会比 HTML 、JavaScript 这些语言更变态的自动纠错。

    对于你的问题,CREATE TABLE 、ALTER TABLE 、ADD INDEX 这些,只是一次性的动态执行语句,它们不是数据库物理模型的最终的定义语句。故,类型不一样,同样的逻辑有些能执行有些不能执行,这都是正常情况。

    最后提一点,对于日期时间、或者时间戳类型,建议不要在数据库上设置默认值,坑太多。NOT NULL 但是没有默认值,这是允许的。
    coderstory
        7
    coderstory  
    OP
       2022-09-20 15:06:49 +08:00
    @nothingistrue
    @asmile1993 刚本地装了 8.0.30 发现也没问题
    coderstory
        8
    coderstory  
    OP
       2022-09-20 15:09:32 +08:00
    @nothingistrue 做的是低代码平台 这些表都是用户设计的 表操作的逻辑也是动态生成的。
    最终执行的 SQL 很难插入相关字段的默认值 TAT
    9y7cz863P00C7Lie
        9
    9y7cz863P00C7Lie  
       2022-09-20 16:12:11 +08:00
    8.0.25 测试加索引操作没问题,估计是中间修复了某些 bug 吧
    julyclyde
        10
    julyclyde  
       2022-09-21 08:56:42 +08:00
    @nothingistrue not null 但没有默认值
    那如果没有写值,读出来是啥内容呢?
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   5758 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 929ms · UTC 03:24 · PVG 11:24 · LAX 19:24 · JFK 22:24
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.