我要建一个表,假如有这样两个字段: `time` varchar(25) NOT NULL default '', ’testtime‘ bigint(19) NOT NULL, time字段格式为2008-09-16 00:00:28 现在我想让testtime字段根据 time字段的值自动生成另外一个值 20080916000028,实际上就是把时间格式转化为数字表示。 每次我只插入time字段的值,testtime的值让他自动生成 这个创建表的语句该怎么写?
调试欢乐多
`time` varchar(25),
`testtime` bigint(19)
);DELIMITER |CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
SET NEW.testtime = CAST(DATE_FORMAT(CAST(new.time AS datetime),'%Y%m%d%H%i%S') AS bigint);
END;
|DELIMITER ;
UPDATE TT SET testtime = CAST(DATE_FORMAT(CAST(new.time AS datetime),'%Y%m%d%H%i%S') AS bigint)
2、用触发器:
DELIMITER $$DROP TRIGGER /*!50032 IF EXISTS */ `test`.`rr`$$CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `rr` BEFORE INSERT ON `tt`
FOR EACH ROW BEGIN
SET NEW.testtime = CAST(DATE_FORMAT(CAST(new.time AS datetime),'%Y%m%d%H%i%S') AS bigint);
END;
$$DELIMITER ;
语句我没测试直接写的。不是把时间转换为bigint,是把 DATE_FORMAT 返回的字符串转为 bigint贴一下你的错误信息。
FOR EACH ROW BEGIN
SET NEW.testtime = 0+DATE_FORMAT(CAST(new.time AS datetime),'%Y%m%d%H%i%S');
END;
|DELIMITER ;换成这种方式让MySQL隐式自动转换
FOR EACH ROW BEGIN
SET NEW.testtime = cast(DATE_FORMAT(CAST(' 2009-01-15 11:02:54' AS datetime),'%Y%m%d%H%i%S') as UNSIGNED );
END;
|DELIMITER ;如果不放心把事情交给MySQL,则用这个强制转换一下.
-> FOR EACH ROW BEGIN
-> SET NEW.testtime = CAST(DATE_FORMAT(CAST(new.time AS datetime),'%Y%
m%d%H%i%S') AS bigint);
-> END;
-> |
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'bigin
t);
END' at line 3
mysql> DELIMITER
ERROR:
DELIMITER must be followed by a 'delimiter' character or string
我换了个转换方式就可以了 SET NEW.testtime = REPLACE(REPLACE(REPLACE(new.time,'-',''),':','')
,' ',''); 直接把字符串扔给testtime了 让他自己去转换 貌似上面的 也可以这样做
是的,MySQL会自己进行转换。只不过有时候另人不放心。自动档和手动档的心情 :-)