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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
|
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(c1 DATE NOT NULL, c2 DATE NULL, c3 DATE NULL, PRIMARY KEY(c1), UNIQUE(c2));
SET TIMESTAMP=1171346973;
INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),ADDTIME(NOW(),'4 04:01:01'),NOW());
Warnings:
Note 1265 Data truncated for column 'c1' at row 1
Note 1265 Data truncated for column 'c2' at row 1
Note 1265 Data truncated for column 'c3' at row 1
INSERT INTO t1 (c1,c2,c3) VALUES(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'3 03:01:01'),NOW());
Warnings:
Note 1265 Data truncated for column 'c1' at row 1
Note 1265 Data truncated for column 'c2' at row 1
Note 1265 Data truncated for column 'c3' at row 1
INSERT INTO t1 (c1,c2,c3) VALUES(ADDTIME(NOW(),'2 02:01:01'),ADDTIME(NOW(),'2 02:01:01'),NOW());
Warnings:
Note 1265 Data truncated for column 'c1' at row 1
Note 1265 Data truncated for column 'c2' at row 1
Note 1265 Data truncated for column 'c3' at row 1
INSERT INTO t1 (c1,c2,c3) VALUES(ADDTIME(NOW(),'3 03:01:01'),ADDTIME(NOW(),'1 01:01:01'),NOW());
Warnings:
Note 1265 Data truncated for column 'c1' at row 1
Note 1265 Data truncated for column 'c2' at row 1
Note 1265 Data truncated for column 'c3' at row 1
INSERT INTO t1 (c1,c2,c3) VALUES(ADDTIME(NOW(),'4 04:01:01'),NOW(),NOW());
Warnings:
Note 1265 Data truncated for column 'c1' at row 1
Note 1265 Data truncated for column 'c2' at row 1
Note 1265 Data truncated for column 'c3' at row 1
INSERT INTO t1 (c1,c2,c3) VALUES(ADDTIME(NOW(),'2 02:01:01'),ADDTIME(NOW(),'2 02:01:01'),NOW()) ON DUPLICATE KEY UPDATE c3=ADDTIME(NOW(),'6 06:01:01');
Warnings:
Note 1265 Data truncated for column 'c1' at row 1
Note 1265 Data truncated for column 'c2' at row 1
Note 1265 Data truncated for column 'c3' at row 1
Note 1265 Data truncated for column 'c3' at row 1
INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),NOW(),NOW()) ON DUPLICATE KEY UPDATE c3=ADDTIME(NOW(),'6 06:01:01');
Warnings:
Note 1265 Data truncated for column 'c1' at row 1
Note 1265 Data truncated for column 'c2' at row 1
Note 1265 Data truncated for column 'c3' at row 1
Note 1265 Data truncated for column 'c3' at row 1
SELECT * FROM t1;
c1 c2 c3
2007-02-13 2007-02-17 2007-02-19
2007-02-14 2007-02-16 2007-02-13
2007-02-15 2007-02-15 2007-02-19
2007-02-16 2007-02-14 2007-02-13
2007-02-17 2007-02-13 2007-02-13
DROP TABLE t1;
CREATE TABLE t1(c1 YEAR NOT NULL, c2 YEAR NULL, c3 YEAR NULL, PRIMARY KEY(c1), UNIQUE(c2));
INSERT INTO t1 (c1,c2,c3) VALUES(2000,2004,2000);
INSERT INTO t1 (c1,c2,c3) VALUES(2001,2003,2000);
INSERT INTO t1 (c1,c2,c3) VALUES(2002,2002,2000);
INSERT INTO t1 (c1,c2,c3) VALUES(2003,2001,2000);
INSERT INTO t1 (c1,c2,c3) VALUES(2004,2000,2000);
INSERT INTO t1 (c1,c2,c3) VALUES(2002,2002,2000) ON DUPLICATE KEY UPDATE c3=2011;
INSERT INTO t1 (c1,c2,c3) VALUES(2000,2000,2000) ON DUPLICATE KEY UPDATE c3=2011;
SELECT * FROM t1;
c1 c2 c3
2000 2004 2011
2001 2003 2000
2002 2002 2011
2003 2001 2000
2004 2000 2000
DROP TABLE t1;
CREATE TABLE t1(c1 TIME NOT NULL, c2 TIME NULL, c3 TIME NULL, PRIMARY KEY(c1), UNIQUE(c2));
SET TIMESTAMP=1171346973;
INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),ADDTIME(NOW(),'4 04:01:01'),NOW());
INSERT INTO t1 (c1,c2,c3) VALUES(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'3 03:01:01'),NOW());
INSERT INTO t1 (c1,c2,c3) VALUES(ADDTIME(NOW(),'2 02:01:01'),ADDTIME(NOW(),'2 02:01:01'),NOW());
INSERT INTO t1 (c1,c2,c3) VALUES(ADDTIME(NOW(),'3 03:01:01'),ADDTIME(NOW(),'1 01:01:01'),NOW());
INSERT INTO t1 (c1,c2,c3) VALUES(ADDTIME(NOW(),'4 04:01:01'),NOW(),NOW());
INSERT INTO t1 (c1,c2,c3) VALUES(ADDTIME(NOW(),'2 02:01:01'),ADDTIME(NOW(),'2 02:01:01'),NOW()) ON DUPLICATE KEY UPDATE c3=ADDTIME(NOW(),'6 06:01:01');
INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),NOW(),NOW()) ON DUPLICATE KEY UPDATE c3=ADDTIME(NOW(),'6 06:01:01');
SELECT * FROM t1;
c1 c2 c3
09:09:33 13:10:34 15:10:34
10:10:34 12:10:34 09:09:33
11:10:34 11:10:34 15:10:34
12:10:34 10:10:34 09:09:33
13:10:34 09:09:33 09:09:33
DROP TABLE t1;
CREATE TABLE t1(c1 YEAR NOT NULL, c2 YEAR NULL, c3 YEAR NULL, PRIMARY KEY(c1), UNIQUE(c2));
INSERT INTO t1 (c1,c2,c3) VALUES(2000,2004,2000);
INSERT INTO t1 (c1,c2,c3) VALUES(2001,2003,2000);
INSERT INTO t1 (c1,c2,c3) VALUES(2002,2002,2000);
INSERT INTO t1 (c1,c2,c3) VALUES(2003,2001,2000);
INSERT INTO t1 (c1,c2,c3) VALUES(2004,2000,2000);
INSERT INTO t1 (c1,c2,c3) VALUES(2002,2002,2000) ON DUPLICATE KEY UPDATE c3=2011;
INSERT INTO t1 (c1,c2,c3) VALUES(2000,2000,2000) ON DUPLICATE KEY UPDATE c3=2011;
SELECT * FROM t1;
c1 c2 c3
2000 2004 2011
2001 2003 2000
2002 2002 2011
2003 2001 2000
2004 2000 2000
DROP TABLE t1;
CREATE TABLE t1(c1 DATETIME NOT NULL, c2 DATETIME NULL, c3 DATETIME NULL, PRIMARY KEY(c1), UNIQUE(c2));
SET TIMESTAMP=1171346973;
INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),ADDTIME(NOW(),'4 04:01:01'),NOW());
INSERT INTO t1 (c1,c2,c3) VALUES(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'3 03:01:01'),NOW());
INSERT INTO t1 (c1,c2,c3) VALUES(ADDTIME(NOW(),'2 02:01:01'),ADDTIME(NOW(),'2 02:01:01'),NOW());
INSERT INTO t1 (c1,c2,c3) VALUES(ADDTIME(NOW(),'3 03:01:01'),ADDTIME(NOW(),'1 01:01:01'),NOW());
INSERT INTO t1 (c1,c2,c3) VALUES(ADDTIME(NOW(),'4 04:01:01'),NOW(),NOW());
INSERT INTO t1 (c1,c2,c3) VALUES(ADDTIME(NOW(),'2 02:01:01'),ADDTIME(NOW(),'2 02:01:01'),NOW()) ON DUPLICATE KEY UPDATE c3=ADDTIME(NOW(),'6 06:01:01');
INSERT INTO t1 (c1,c2,c3) VALUES(NOW(),NOW(),NOW()) ON DUPLICATE KEY UPDATE c3=ADDTIME(NOW(),'6 06:01:01');
SELECT * FROM t1;
c1 c2 c3
2007-02-13 09:09:33 2007-02-17 13:10:34 2007-02-19 15:10:34
2007-02-14 10:10:34 2007-02-16 12:10:34 2007-02-13 09:09:33
2007-02-15 11:10:34 2007-02-15 11:10:34 2007-02-19 15:10:34
2007-02-16 12:10:34 2007-02-14 10:10:34 2007-02-13 09:09:33
2007-02-17 13:10:34 2007-02-13 09:09:33 2007-02-13 09:09:33
DROP TABLE t1;
CREATE TABLE t1(c1 YEAR NOT NULL, c2 YEAR NULL, c3 YEAR NULL, PRIMARY KEY(c1), UNIQUE(c2));
INSERT INTO t1 (c1,c2,c3) VALUES(2000,2004,2000);
INSERT INTO t1 (c1,c2,c3) VALUES(2001,2003,2000);
INSERT INTO t1 (c1,c2,c3) VALUES(2002,2002,2000);
INSERT INTO t1 (c1,c2,c3) VALUES(2003,2001,2000);
INSERT INTO t1 (c1,c2,c3) VALUES(2004,2000,2000);
INSERT INTO t1 (c1,c2,c3) VALUES(2002,2002,2000) ON DUPLICATE KEY UPDATE c3=2011;
INSERT INTO t1 (c1,c2,c3) VALUES(2000,2000,2000) ON DUPLICATE KEY UPDATE c3=2011;
SELECT * FROM t1;
c1 c2 c3
2000 2004 2011
2001 2003 2000
2002 2002 2011
2003 2001 2000
2004 2000 2000
DROP TABLE t1;
|