我在一个SQLite数据库中创建了这个表:
CREATE TABLE [tickets] (
[id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[coupon_id] INTEGER NULL,
[size] FLOAT NULL,
[phone] VARCHAR(10) NULL,
[date] DATE DEFAULT CURRENT_DATE NULL,
[time] TIME DEFAULT CURRENT_TIME NULL,
[product] TEXT NULL
);
现在我把这个表转换成mysql
CREATE TABLE tickets (
id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
coupon_id INTEGER NULL,
size FLOAT NULL,
phone VARCHAR(10) NULL,
date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL,
time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL,
product TEXT NULL
);
INSERT INTO "tickets" VALUES(429,9,18.16,'949-893-5032','2010-11-30','17:46:39','Kids’ Kups Berry Interesting™');
INSERT INTO "tickets" VALUES(430,9,12.04,'847-188-1359','2010-11-25','10:54:00','Raspberry Collider™');
INSERT INTO "tickets" VALUES(431,9,14.1,'204-682-5560','2010-12-08','15:34:07','Celestial Cherry High™');
当我插入这些值时,我得到了一个错误ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.
我能够在SQLite中插入所有这些值,但我不能将所有这些值插入MySQL。帮我个忙吗?
您可能想转换
date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL,
time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL,
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL,
和连接来自SQLite的值,或者改变它们的类型
date date DEFAULT NULL,
time time DEFAULT NULL,
据我所知,MySQL不允许这样做。如果要在第二个字段中插入当前时间,请考虑在插入时使用触发器更新记录。
DELIMITER $$
CREATE TRIGGER curtime BEFORE INSERT ON tickets FOR EACH ROW
BEGIN
SET NEW.time = CURRENT_TIMESTAMP();
END;
$$
DELIMITER ;