1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
CREATE TABLE t1 (
`i1` INT(10) UNSIGNED NOT NULL,
`d1` TIMESTAMP NULL DEFAULT NULL
) ENGINE=innodb;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i1` int(10) unsigned NOT NULL,
`d1` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
INSERT INTO t1 (i1) VALUES (1), (2), (3), (4), (5);
select * from t1;
i1 d1
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL
set sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_DATE';
ALTER TABLE t1 CHANGE `d1` `d1` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
drop table t1;
CREATE TABLE t1 (
`i1` INT(10) UNSIGNED NOT NULL,
`d1` TIMESTAMP NULL DEFAULT NULL
) ENGINE=innodb;
INSERT INTO t1 (i1) VALUES (1), (2), (3), (4), (5);
ALTER TABLE t1 CHANGE `d1` `d1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE t1 ADD COLUMN d2 TIMESTAMP DEFAULT '2017-05-08 16:23:45',
LOCK=NONE;
ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1, LOCK=NONE;
ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED
ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1, ALGORITHM=INPLACE;
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1;
SELECT d1-d3, d2 FROM t1;
d1-d3 d2
0 2017-05-08 16:23:45
0 2017-05-08 16:23:45
0 2017-05-08 16:23:45
0 2017-05-08 16:23:45
0 2017-05-08 16:23:45
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i1` int(10) unsigned NOT NULL,
`d1` timestamp NOT NULL DEFAULT current_timestamp(),
`d2` timestamp NOT NULL DEFAULT '2017-05-08 16:23:45',
`d3` timestamp NOT NULL DEFAULT `d1`
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t1;
|
