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
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
|
drop table if exists t1, t2;
create table t1 (a date, b date, c date not null, d date);
load data infile '../std_data_ln/loaddata1.dat' into table t1 fields terminated by ',';
Warnings:
Warning 1265 Data truncated for column 'a' at row 1
Warning 1265 Data truncated for column 'c' at row 1
Warning 1265 Data truncated for column 'd' at row 1
Warning 1265 Data truncated for column 'a' at row 2
Warning 1265 Data truncated for column 'b' at row 2
Warning 1265 Data truncated for column 'd' at row 2
load data infile '../std_data_ln/loaddata1.dat' into table t1 fields terminated by ',' IGNORE 2 LINES;
SELECT * from t1;
a b c d
0000-00-00 NULL 0000-00-00 0000-00-00
0000-00-00 0000-00-00 0000-00-00 0000-00-00
2003-03-03 2003-03-03 2003-03-03 NULL
2003-03-03 2003-03-03 2003-03-03 NULL
truncate table t1;
load data infile '../std_data_ln/loaddata1.dat' into table t1 fields terminated by ',' LINES STARTING BY ',' (b,c,d);
Warnings:
Warning 1265 Data truncated for column 'c' at row 1
Warning 1265 Data truncated for column 'd' at row 1
Warning 1265 Data truncated for column 'b' at row 2
Warning 1265 Data truncated for column 'd' at row 2
SELECT * from t1;
a b c d
NULL NULL 0000-00-00 0000-00-00
NULL 0000-00-00 0000-00-00 0000-00-00
NULL 2003-03-03 2003-03-03 NULL
drop table t1;
create table t1 (a text, b text);
load data infile '../std_data_ln/loaddata2.dat' into table t1 fields terminated by ',' enclosed by '''';
Warnings:
Warning 1261 Row 3 doesn't contain data for all columns
select concat('|',a,'|'), concat('|',b,'|') from t1;
concat('|',a,'|') concat('|',b,'|')
|Field A| |Field B|
|Field 1| |Field 2'
Field 3,'Field 4|
|Field 5' ,'Field 6| NULL
|Field 6| | 'Field 7'|
drop table t1;
create table t1 (a int, b char(10));
load data infile '../std_data_ln/loaddata3.dat' into table t1 fields terminated by '' enclosed by '' ignore 1 lines;
Warnings:
Warning 1366 Incorrect integer value: 'error ' for column 'a' at row 3
Warning 1262 Row 3 was truncated; it contained more data than there were input columns
Warning 1366 Incorrect integer value: 'wrong end ' for column 'a' at row 5
Warning 1262 Row 5 was truncated; it contained more data than there were input columns
select * from t1;
a b
1 row 1
2 row 2
0 1234567890
3 row 3
0 1234567890
truncate table t1;
load data infile '../std_data_ln/loaddata4.dat' into table t1 fields terminated by '' enclosed by '' lines terminated by '' ignore 1 lines;
Warnings:
Warning 1366 Incorrect integer value: '
' for column 'a' at row 4
Warning 1261 Row 4 doesn't contain data for all columns
select * from t1;
a b
1 row 1
2 row 2
3 row 3
0
drop table t1;
SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
create table t1(id integer not null auto_increment primary key);
insert into t1 values(0);
select * from t1;
id
0
select * from t1;
id
0
SET @@SQL_MODE=@OLD_SQL_MODE;
drop table t1;
create table t1 (a varchar(20), b varchar(20));
load data infile '../std_data_ln/loaddata_dq.dat' into table t1 fields terminated by ',' enclosed by '"' escaped by '"' (a,b);
select * from t1;
a b
field1 field2
a"b cd"ef
a"b c"d"e
drop table t1;
CREATE TABLE t1 (
id INT AUTO_INCREMENT PRIMARY KEY,
c1 VARCHAR(255)
);
CREATE TABLE t2 (
id INT,
c2 VARCHAR(255)
);
INSERT INTO t1 (c1) VALUES
('r'), ('rr'), ('rrr'), ('rrrr'),
('.r'), ('.rr'), ('.rrr'), ('.rrrr'),
('r.'), ('rr.'), ('rrr.'), ('rrrr.'),
('.r.'), ('.rr.'), ('.rrr.'), ('.rrrr.');
SELECT * FROM t1;
id c1
1 r
2 rr
3 rrr
4 rrrr
5 .r
6 .rr
7 .rrr
8 .rrrr
9 r.
10 rr.
11 rrr.
12 rrrr.
13 .r.
14 .rr.
15 .rrr.
16 .rrrr.
SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1' FIELDS ENCLOSED BY 'r' FROM t1;
r1r rrrr
r2r rrrrrr
r3r rrrrrrrr
r4r rrrrrrrrrr
r5r r.rrr
r6r r.rrrrr
r7r r.rrrrrrr
r8r r.rrrrrrrrr
r9r rrr.r
r10r rrrrr.r
r11r rrrrrrr.r
r12r rrrrrrrrr.r
r13r r.rr.r
r14r r.rrrr.r
r15r r.rrrrrr.r
r16r r.rrrrrrrr.r
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1' INTO TABLE t2 FIELDS ENCLOSED BY 'r';
SELECT t1.id, c1, c2 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE c1 != c2;
id c1 c2
SELECT t1.id, c1, c2 FROM t1 RIGHT JOIN t2 ON t1.id=t2.id WHERE c1 != c2;
id c1 c2
DROP TABLE t1,t2;
create table t1 (a int default 100, b int, c varchar(60));
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (a, @b) set b=@b+10, c=concat("b=",@b);
select * from t1;
a b c
NULL 20 b=10
NULL 25 b=15
truncate table t1;
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (a, @b) set c= if(a is null,"oops",a);
select * from t1;
a b c
NULL NULL oops
NULL NULL oops
truncate table t1;
set @c:=123;
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (@a, b) set c= if(@a is null,@c,b);
select * from t1;
a b c
100 10 123
100 15 123
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (@a, @b);
select * from t1;
a b c
100 10 123
100 15 123
100 NULL NULL
100 NULL NULL
select @a, @b;
@a @b
NULL 15
truncate table t1;
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 set c=b;
Warnings:
Warning 1261 Row 1 doesn't contain data for all columns
Warning 1261 Row 2 doesn't contain data for all columns
select * from t1;
a b c
NULL 10 10
NULL 15 15
truncate table t1;
load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, b) set c="Wow";
select * from t1;
a b c
1 2 Wow
3 4 Wow
5 6 Wow
truncate table t1;
load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, b) set c=concat(a,"+",b,"+",@c,"+",b,"+",if(c is null,"NIL",c));
select * from t1;
a b c
1 2 1+2+123+2+NIL
3 4 3+4+123+4+NIL
5 6 5+6+123+6+NIL
load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, @b);
ERROR HY000: Can't load value from file with fixed size rows to variable
create table t2 (num int primary key, str varchar(10));
insert into t2 values (10,'Ten'), (15,'Fifteen');
truncate table t1;
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (@dummy,@n) set a= @n, c= (select str from t2 where num=@n);
select * from t1;
a b c
10 NULL Ten
15 NULL Fifteen
show variables like "secure_file_pri%";
Variable_name Value
secure_file_priv MYSQLTEST_VARDIR/
select @@secure_file_priv;
@@secure_file_priv
MYSQLTEST_VARDIR/
set @@secure_file_priv= 0;
ERROR HY000: Variable 'secure_file_priv' is a read only variable
truncate table t1;
load data infile 'MYSQL_TEST_DIR/t/loaddata.test' into table t1;
ERROR HY000: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
select * from t1;
a b c
select load_file("MYSQL_TEST_DIR/t/loaddata.test");
load_file("MYSQL_TEST_DIR/t/loaddata.test")
NULL
drop table t1, t2;
create table t1(f1 int);
insert into t1 values(1),(null);
create table t2(f2 int auto_increment primary key);
select * from t2;
f2
1
2
SET @@SQL_MODE=@OLD_SQL_MODE;
drop table t1,t2;
create table t1(f1 int, f2 timestamp not null default current_timestamp);
create table t2(f1 int);
insert into t2 values(1),(2);
Warnings:
Warning 1261 Row 1 doesn't contain data for all columns
Warning 1261 Row 2 doesn't contain data for all columns
select f1 from t1 where f2 <> '0000-00-00 00:00:00' order by f1;
f1
1
2
delete from t1;
Warnings:
Warning 1261 Row 1 doesn't contain data for all columns
Warning 1261 Row 2 doesn't contain data for all columns
select f1 from t1 where f2 <> '0000-00-00 00:00:00' order by f1;
f1
1
2
drop table t1,t2;
CREATE TABLE t1 (c1 INT, c2 TIMESTAMP, c3 REAL, c4 DOUBLE);
INSERT INTO t1 (c1, c2, c3, c4) VALUES (10, '1970-02-01 01:02:03', 1.1E-100, 1.1E+100);
SELECT * FROM t1;
c1 c2 c3 c4
10 1970-02-01 01:02:03 1.1e-100 1.1e+100
SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1' FIELDS ENCLOSED BY '-' FROM t1;
-10- -1970\-02\-01 01:02:03- -1.1e\-100- -1.1e+100-
EOF
TRUNCATE t1;
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1' INTO TABLE t1 FIELDS ENCLOSED BY '-';
SELECT * FROM t1;
c1 c2 c3 c4
10 1970-02-01 01:02:03 1.1e-100 1.1e+100
DROP TABLE t1;
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (1);
SET NAMES latin1;
SET character_set_filesystem=filename;
select @@character_set_filesystem;
@@character_set_filesystem
filename
SELECT * INTO OUTFILE 't-1' FROM t1;
DELETE FROM t1;
LOAD DATA INFILE 't-1' INTO TABLE t1;
SELECT * FROM t1;
a
1
DELETE FROM t1;
SET character_set_filesystem=latin1;
select @@character_set_filesystem;
@@character_set_filesystem
latin1
LOAD DATA INFILE 't@002d1' INTO TABLE t1;
SELECT * FROM t1;
a
1
DROP TABLE t1;
SET character_set_filesystem=default;
select @@character_set_filesystem;
@@character_set_filesystem
binary
|
