主页 / user-8094926

chocalaca's questions

Martin Hope
chocalaca
Asked: 2025-04-20 02:03:31 +0800 CST
  • 8

当用户的电子邮件确认后，mysql 时间戳值会更新，并且其值不应等于“1970-01-01 00:00:01”（默认值）。我正在尝试创建一个 mysql 约束来强制执行此操作。因此，如果用户有以下内容：
confirmed=1
email_confirmed_time='1970-01-01 00:00:01'
...则违反了该约束。但如果 confirmed=0且时间戳相同，则表示该消息被接受。

下面的表格命令有什么问题？如果我插入以下内容，它会告诉我CHK_emailConfirmedTime_when_confirmed存在违规：

insert into user (username, `password`, first_name, last_name, `role`, `group`, logged_in, confirmed, approved, email_confirmed_time) values 
    ("0", "0", "joe", "dill", "{admin, user, guest}", "{create, read, update, delete}", 0, 0, 0, DEFAULT)
;

CREATE TABLE IF NOT EXISTS user (
    `id` SERIAL PRIMARY KEY,
    `username` varchar(75) NOT NULL,
    `password` varchar(100) NOT NULL,
    `first_name` varchar(50) NOT NULL,
    `last_name` varchar(50) NOT NULL,
    `role` varchar(25) NOT NULL DEFAULT "{guest}",
    `group` varchar(50) NOT NULL DEFAULT "{read}",
    `logged_in` int NOT NULL DEFAULT 0,
    `confirmed` INTEGER NOT NULL DEFAULT 0,
    `approved` INTEGER NOT NULL DEFAULT 0,
    `email_confirmed_time` TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:01',
    CONSTRAINT CHK_confirmed CHECK (`confirmed`=0 OR `confirmed`=1),
    CONSTRAINT CHK_approved CHECK (`approved`=0 OR `approved`=1),
    CONSTRAINT CHK_emailConfirmedTime_when_notConfirmed CHECK (`confirmed`=0 AND `email_confirmed_time`=TIMESTAMP('1970-01-01 00:00:01')),
    CONSTRAINT CHK_emailConfirmedTime_when_confirmed CHECK (`confirmed`=1 AND `email_confirmed_time`>TIMESTAMP('1970-01-01 00:00:01'))
);
mysql