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
|
drop table if exists t1, t2;
create table t1 (a date, b date, c date not null, d date);
load data infile 'DRIZZLETEST_VARDIR/std_data_ln/loaddata1.dat' into table t1 fields terminated by ',';
load data infile 'DRIZZLETEST_VARDIR/std_data_ln/loaddata1.dat' into table t1 fields terminated by ',' IGNORE 2 LINES;
SELECT * from t1;
a b c d
2003-02-03 2003-02-03 2003-02-03 NULL
2003-03-03 2003-03-03 2003-03-03 NULL
truncate table t1;
load data infile 'DRIZZLETEST_VARDIR/std_data_ln/loaddata1.dat' into table t1 fields terminated by ',' LINES STARTING BY ',' (b,c,d);
SELECT * from t1;
a b c d
NULL 2003-02-03 2003-02-03 NULL
NULL 2003-03-03 2003-03-03 NULL
drop table t1;
create table t1 (a text, b text);
load data infile 'DRIZZLETEST_VARDIR/std_data_ln/loaddata2.dat' into table t1 fields terminated by ',' enclosed by '''';
select concat('|',a,'|'), concat('|',b,'|') from t1;
concat('|',a,'|') concat('|',b,'|')
|Field A| |Field B|
|Field 1| |Field 2'
'Field 5' ,'Field 6|
|Field 6| | 'Field 7'|
drop table t1;
create table t1 (a int, b char(10));
load data infile 'DRIZZLETEST_VARDIR/std_data_ln/loaddata3.dat' into table t1 fields terminated by '' enclosed by '' ignore 1 lines;
select * from t1;
a b
1 row 1
2 row 2
3 row 3
truncate table t1;
select * from t1;
a b
drop table t1;
create table t1 (a varchar(20), b varchar(20));
load data infile 'DRIZZLETEST_VARDIR/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 'DRIZZLETEST_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 'DRIZZLETEST_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 'DRIZZLETEST_VARDIR/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 'DRIZZLETEST_VARDIR/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 'DRIZZLETEST_VARDIR/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 'DRIZZLETEST_VARDIR/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 'DRIZZLETEST_VARDIR/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 'DRIZZLETEST_VARDIR/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 'DRIZZLETEST_VARDIR/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 'DRIZZLETEST_VARDIR/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 Ten
show variables like "secure_file_pri%";
Variable_name Value
secure_file_priv DRIZZLETEST_VARDIR
select @@secure_file_priv;
@@secure_file_priv
DRIZZLETEST_VARDIR
set @@secure_file_priv= 0;
ERROR HY000: Variable 'secure_file_priv' is a read only variable
truncate table t1;
load data infile 'DRIZZLE_TEST_DIR/t/loaddata.test' into table t1;
ERROR HY000: The Drizzle server is running with the --secure-file-priv option so it cannot execute this statement
select * from t1;
a b c
select load_file("DRIZZLE_TEST_DIR/t/loaddata.test");
ERROR HY000: The Drizzle server is running with the --secure-file-priv option so it cannot execute this statement
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);
ERROR 01000: Row 1 doesn't contain data for all columns
select f1 from t1 where f2 IS NOT NULL order by f1;
f1
delete from t1;
ERROR 01000: Row 1 doesn't contain data for all columns
select f1 from t1 where f2 IS NOT NULL order by f1;
f1
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.1e100
SELECT * INTO OUTFILE 'DRIZZLETEST_VARDIR/tmp/t1' FIELDS ENCLOSED BY '-' FROM t1;
EOF
TRUNCATE t1;
LOAD DATA INFILE 'DRIZZLETEST_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.1e100
DROP TABLE t1;
|