type_timestamp.result

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

  `t1` timestamp NOT NULL default '2003-01-01 00:00:00' on update CURRENT_TIMESTAMP,
  `t2` datetime default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show columns from t1;
Field	Type	Null	Key	Default	Extra
t1	timestamp	YES		2003-01-01 00:00:00	
t2	datetime	YES		NULL	
drop table t1;
create table t1 (t1 timestamp default now() on update now(), t2 datetime);
SET TIMESTAMP=1000000006;
insert into t1 values ();
select * from t1;
t1	t2
2001-09-09 04:46:46	NULL
SET TIMESTAMP=1000000007;
update t1 set t2=now();
SET TIMESTAMP=1000000007;
insert into t1 (t1) values (default);
select * from t1;
t1	t2
2001-09-09 04:46:47	2001-09-09 04:46:47
2001-09-09 04:46:47	NULL
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t1` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `t2` datetime default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show columns from t1;
Field	Type	Null	Key	Default	Extra
t1	timestamp	YES		CURRENT_TIMESTAMP	
t2	datetime	YES		NULL	
drop table t1;
create table t1 (t1 timestamp, t2 datetime, t3 timestamp);
SET TIMESTAMP=1000000007;
insert into t1 values ();
select * from t1;
t1	t2	t3
2001-09-09 04:46:47	NULL	0000-00-00 00:00:00
SET TIMESTAMP=1000000008;
update t1 set t2=now();
SET TIMESTAMP=1000000008;
insert into t1 (t1,t3) values (default, default);
select * from t1;
t1	t2	t3
2001-09-09 04:46:48	2001-09-09 04:46:48	0000-00-00 00:00:00
2001-09-09 04:46:48	NULL	0000-00-00 00:00:00
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t1` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `t2` datetime default NULL,
  `t3` timestamp NOT NULL default '0000-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show columns from t1;
Field	Type	Null	Key	Default	Extra
t1	timestamp	YES		CURRENT_TIMESTAMP	
t2	datetime	YES		NULL	
t3	timestamp	YES		0000-00-00 00:00:00	
drop table t1;
create table t1 (t1 timestamp default current_timestamp on update current_timestamp, t2 datetime);
SET TIMESTAMP=1000000009;
insert into t1 values ();
select * from t1;
t1	t2
2001-09-09 04:46:49	NULL
SET TIMESTAMP=1000000010;
update t1 set t2=now();
SET TIMESTAMP=1000000011;
insert into t1 (t1) values (default);
select * from t1;
t1	t2
2001-09-09 04:46:50	2001-09-09 04:46:50
2001-09-09 04:46:51	NULL
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t1` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `t2` datetime default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show columns from t1;
Field	Type	Null	Key	Default	Extra
t1	timestamp	YES		CURRENT_TIMESTAMP	
t2	datetime	YES		NULL	
delete from t1;
insert into t1 values ('2004-04-01 00:00:00', '2004-04-01 00:00:00');
SET TIMESTAMP=1000000012;
update t1 set t1= '2004-04-02 00:00:00';
select * from t1;
t1	t2
2004-04-02 00:00:00	2004-04-01 00:00:00
update t1 as ta, t1 as tb set tb.t1= '2004-04-03 00:00:00';
select * from t1;
t1	t2
2004-04-03 00:00:00	2004-04-01 00:00:00
drop table t1;
create table t1 (pk int primary key, t1 timestamp default current_timestamp on update current_timestamp, bulk int);
insert into t1 values (1, '2004-04-01 00:00:00', 10);
SET TIMESTAMP=1000000013;
replace into t1 set pk = 1, bulk= 20;
select * from t1;
pk	t1	bulk
1	2001-09-09 04:46:53	20
drop table t1;
create table t1 (pk int primary key, t1 timestamp default '2003-01-01 00:00:00' on update current_timestamp, bulk int);
insert into t1 values (1, '2004-04-01 00:00:00', 10);
SET TIMESTAMP=1000000014;
replace into t1 set pk = 1, bulk= 20;
select * from t1;
pk	t1	bulk
1	2003-01-01 00:00:00	20
drop table t1;
create table t1 (pk int primary key, t1 timestamp default current_timestamp, bulk int);
insert into t1 values (1, '2004-04-01 00:00:00', 10);
SET TIMESTAMP=1000000015;
replace into t1 set pk = 1, bulk= 20;
select * from t1;
pk	t1	bulk
1	2001-09-09 04:46:55	20
drop table t1;
create table t1 (t1 timestamp default current_timestamp on update current_timestamp);
insert into t1 values ('2004-04-01 00:00:00');
SET TIMESTAMP=1000000016;
alter table t1 add i int default 10;
select * from t1;
t1	i
2004-04-01 00:00:00	10
drop table t1;
create table t1 (a timestamp null, b timestamp null);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` timestamp NULL default NULL,
  `b` timestamp NULL default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (NULL, NULL);
SET TIMESTAMP=1000000017;
insert into t1 values ();
select * from t1;
a	b
NULL	NULL
NULL	NULL
drop table t1;
create table t1 (a timestamp null default current_timestamp on update current_timestamp, b timestamp null);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `b` timestamp NULL default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (NULL, NULL);
SET TIMESTAMP=1000000018;
insert into t1 values ();
select * from t1;
a	b
NULL	NULL
2001-09-09 04:46:58	NULL
drop table t1;
create table t1 (a timestamp null default null, b timestamp null default '2003-01-01 00:00:00');
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` timestamp NULL default NULL,
  `b` timestamp NULL default '2003-01-01 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (NULL, NULL);
insert into t1 values (DEFAULT, DEFAULT);
select * from t1;
a	b
NULL	NULL
NULL	2003-01-01 00:00:00
drop table t1;
create table t1 (a bigint, b bigint);
insert into t1 values (NULL, NULL), (20030101000000, 20030102000000);
set timestamp=1000000019;
alter table t1 modify a timestamp, modify b timestamp;
select * from t1;
a	b
2001-09-09 04:46:59	2001-09-09 04:46:59
2003-01-01 00:00:00	2003-01-02 00:00:00
drop table t1;
create table t1 (a char(2), t timestamp);
insert into t1 values ('a', '2004-01-01 00:00:00'), ('a', '2004-01-01 01:00:00'),
('b', '2004-02-01 00:00:00');
select max(t) from t1 group by a;
max(t)
2004-01-01 01:00:00
2004-02-01 00:00:00
drop table t1;
set sql_mode='maxdb';
create table t1 (a timestamp, b timestamp(19));
show create table t1;
Table	Create Table
t1	CREATE TABLE "t1" (
  "a" datetime default NULL,
  "b" datetime default NULL
)
set sql_mode='';
drop table t1;
create table t1 (a int auto_increment primary key, b int, c timestamp);
insert into t1 (a, b, c) values (1, 0, '2001-01-01 01:01:01'),
(2, 0, '2002-02-02 02:02:02'), (3, 0, '2003-03-03 03:03:03');
select * from t1;
a	b	c
1	0	2001-01-01 01:01:01
2	0	2002-02-02 02:02:02
3	0	2003-03-03 03:03:03
update t1 set b = 2, c = c where a = 2;
select * from t1;
a	b	c
1	0	2001-01-01 01:01:01
2	2	2002-02-02 02:02:02
3	0	2003-03-03 03:03:03
insert into t1 (a) values (4);
select * from t1;
a	b	c
1	0	2001-01-01 01:01:01
2	2	2002-02-02 02:02:02
3	0	2003-03-03 03:03:03
4	NULL	2001-09-09 04:46:59
update t1 set c = '2004-04-04 04:04:04' where a = 4;
select * from t1;
a	b	c
1	0	2001-01-01 01:01:01
2	2	2002-02-02 02:02:02
3	0	2003-03-03 03:03:03
4	NULL	2004-04-04 04:04:04
insert into t1 (a) values (3), (5) on duplicate key update b = 3, c = c;
select * from t1;
a	b	c
1	0	2001-01-01 01:01:01
2	2	2002-02-02 02:02:02
3	3	2003-03-03 03:03:03
4	NULL	2004-04-04 04:04:04
5	NULL	2001-09-09 04:46:59
insert into t1 (a, c) values (4, '2004-04-04 00:00:00'),
(6, '2006-06-06 06:06:06') on duplicate key update b = 4;
select * from t1;
a	b	c
1	0	2001-01-01 01:01:01
2	2	2002-02-02 02:02:02
3	3	2003-03-03 03:03:03
4	4	2001-09-09 04:46:59
5	NULL	2001-09-09 04:46:59
6	NULL	2006-06-06 06:06:06
drop table t1;