github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/load_data/import_data.result (about) 1 drop table if exists t1; 2 create table t1( 3 col1 tinyint, 4 col2 smallint, 5 col3 int, 6 col4 bigint, 7 col5 tinyint unsigned, 8 col6 smallint unsigned, 9 col7 int unsigned, 10 col8 bigint unsigned 11 ); 12 import data infile '$resources/load_data/integer_numbers_1.csv' into table t1; 13 select * from t1; 14 col1 col2 col3 col4 col5 col6 col7 col8 15 127 32767 2147483647 9223372036854775807 255 65535 4294967295 18446744073709551615 16 -128 -32768 -2147483648 -9223372036854775808 0 0 0 0 17 0 0 0 0 0 0 0 0 18 null null null null null null null null 19 1 null null 0 0 0 null null 20 null null null null null null null null 21 127 32767 2147483647 9223372036854775807 255 65535 4294967295 18446744073709551615 22 select * from t1 into outfile '$resources/into_outfile_2/outfile_integer_numbers_1.csv'; 23 col1 col2 col3 col4 col5 col6 col7 col8 24 delete from t1; 25 import data infile '$resources/into_outfile_2/outfile_integer_numbers_1.csv' into table t1 ignore 1 lines; 26 select * from t1; 27 col1 col2 col3 col4 col5 col6 col7 col8 28 127 32767 2147483647 9223372036854775807 255 65535 4294967295 18446744073709551615 29 -128 -32768 -2147483648 -9223372036854775808 0 0 0 0 30 0 0 0 0 0 0 0 0 31 null null null null null null null null 32 1 null null 0 0 0 null null 33 null null null null null null null null 34 127 32767 2147483647 9223372036854775807 255 65535 4294967295 18446744073709551615 35 delete from t1; 36 import data infile '$resources/load_data/integer_numbers_2.csv' into table t1 fields terminated by'*'; 37 select * from t1; 38 col1 col2 col3 col4 col5 col6 col7 col8 39 127 32767 2147483647 9223372036854775807 255 65535 4294967295 18446744073709551615 40 -128 -32768 -2147483648 -9223372036854775808 0 0 0 0 41 0 0 0 0 0 0 0 0 42 null null null null null null null null 43 1 null null 0 0 0 null null 44 null null null null null null null null 45 127 32767 2147483647 9223372036854775807 255 65535 4294967295 18446744073709551615 46 delete from t1; 47 drop table t1; 48 drop table if exists t2; 49 create table t2( 50 col1 char(225), 51 col2 varchar(225), 52 col3 text, 53 col4 varchar(225) 54 ); 55 import data infile '$resources/load_data/char_varchar_1.csv' into table t2; 56 select * from t2; 57 col1 col2 col3 col4 58 a b c d 59 a b c d 60 'a' 'b' 'c' 'd' 61 'a' 'b' 'c' 'd' 62 aa,aa bb,bb cc,cc dd,dd 63 aa, bb, cc, dd, 64 aa,,,aa bb,,,bb cc,,,cc dd,,,dd 65 aa',',,aa bb',',,bb cc',',,cc dd',',,dd 66 aa"aa bb"bb cc"cc dd"dd 67 aa"aa bb"bb cc"cc dd"dd 68 aa"aa bb"bb cc"cc dd"dd 69 aa""aa bb""bb cc""cc dd""dd 70 aa""aa bb""bb cc""cc dd""dd 71 aa",aa bb",bb cc",cc dd",dd 72 aa"",aa bb"",bb cc"",cc dd"",dd 73 74 75 null null null null 76 77 " " " " 78 "" "" "" "" 79 select * from t2 into outfile '$resources/into_outfile_2/outfile_char_varchar_1.csv'; 80 col1 col2 col3 col4 81 delete from t2; 82 83 import data infile '$resources/into_outfile_2/outfile_char_varchar_1.csv' into table t2 ignore 1 lines; 84 85 select * from t2; 86 col1 col2 col3 col4 87 a b c d 88 a b c d 89 'a' 'b' 'c' 'd' 90 'a' 'b' 'c' 'd' 91 aa,aa bb,bb cc,cc dd,dd 92 aa, bb, cc, dd, 93 aa,,,aa bb,,,bb cc,,,cc dd,,,dd 94 aa',',,aa bb',',,bb cc',',,cc dd',',,dd 95 aa"aa bb"bb cc"cc dd"dd 96 aa"aa bb"bb cc"cc dd"dd 97 aa"aa bb"bb cc"cc dd"dd 98 aa""aa bb""bb cc""cc dd""dd 99 aa""aa bb""bb cc""cc dd""dd 100 aa",aa bb",bb cc",cc dd",dd 101 aa"",aa bb"",bb cc"",cc dd"",dd 102 103 104 null null null null 105 106 " " " " 107 "" "" "" "" 108 delete from t2; 109 110 import data infile '$resources/load_data/char_varchar_2.csv' into table t2; 111 112 select * from t2; 113 col1 col2 col3 col4 114 aa""aa bb""bb cc""cc dd""dd 115 aa"aa bb"bb cc"cc dd"dd 116 aa"""aa bb"""bb cc"""cc dd"""dd 117 aa""aa bb""bb cc""cc dd""dd 118 aa""""aa bb""""bb cc""""cc dd""""dd 119 aa"""aa bb""bb cc""cc dd""dd 120 delete from t2; 121 122 import data infile '$resources/load_data/char_varchar_3.csv' into table t2; 123 select * from t2; 124 col1 col2 col3 col4 125 aa",aa bb",bb cc",cc dd",dd 126 aa" aa" bb" bb" 127 aa"",aa bb"",bb cc"",cc dd"",dd 128 aa"" aa" bb"",bb cc"",cc 129 delete from t2; 130 import data infile '$resources/load_data/char_varchar_4.csv' into table t2 fields terminated by'|'; 131 select * from t2; 132 col1 col2 col3 col4 133 a b c d 134 a b c d 135 'a' 'b' 'c' 'd' 136 'a' 'b' 'c' 'd' 137 aa|aa bb|bb cc|cc dd|dd 138 aa| bb| cc| dd| 139 aa|||aa bb|||bb cc|||cc dd|||dd 140 aa'|'||aa bb'|'||bb cc'|'||cc dd'|'||dd 141 aa"aa bb"bb cc"cc dd"dd 142 aa"aa bb"bb cc"cc dd"dd 143 aa"aa bb"bb cc"cc dd"dd 144 aa""aa bb""bb cc""cc dd""dd 145 aa""aa bb""bb cc""cc dd""dd 146 aa"|aa bb"|bb cc"|cc dd"|dd 147 aa""|aa bb""|bb cc""|cc dd""|dd 148 149 150 151 " " " " 152 "" "" "" "" 153 delete from t2; 154 import data infile '$resources/load_data/char_varchar_5.csv' into table t2 fields terminated by'?'; 155 select * from t2; 156 col1 col2 col3 col4 157 a b c d 158 a b c d 159 'a' 'b' 'c' 'd' 160 'a' 'b' 'c' 'd' 161 aa?aa bb?bb cc?cc dd?dd 162 aa? bb? cc? dd? 163 aa???aa bb???bb cc???cc dd???dd 164 aa'?'??aa bb'?'??bb cc'?'??cc dd'?'??dd 165 aa"aa bb"bb cc"cc dd"dd 166 aa"aa bb"bb cc"cc dd"dd 167 aa"aa bb"bb cc"cc dd"dd 168 aa""aa bb""bb cc""cc dd""dd 169 aa""aa bb""bb cc""cc dd""dd 170 aa"?aa bb"?bb cc"?cc dd"?dd 171 aa""?aa bb""?bb cc""?cc dd""?dd 172 173 174 175 " " " " 176 "" "" "" "" 177 delete from t2; 178 drop table t2; 179 drop table if exists t3; 180 create table t3( 181 col1 float, 182 col2 double, 183 col3 decimal(5,2), 184 col4 decimal(20,5) 185 ); 186 insert into t3 values (1.3,1.3,1.3,1.3); 187 select * from t3; 188 col1 col2 col3 col4 189 1.3 1.3 1.30 1.30000 190 import data infile '$resources/load_data/float_1.csv' into table t3; 191 192 select * from t3; 193 col1 col2 col3 col4 194 1.3 1.3 1.30 1.30000 195 1.3 1.3 1.30 1.30000 196 delete from t3; 197 import data infile '$resources/load_data/float_2.csv' into table t3; 198 select * from t3; 199 col1 col2 col3 col4 200 1.0 1.0 1.00 1.00000 201 null null null null 202 1.23456 1.23456 1.23 1.23456 203 1.2345679 1.23456789 1.23 1.23457 204 1.0123457 1.0123456789 1.01 1.01235 205 1.0E-10 1.0E-10 0.00 0.00000 206 select * from t3 into outfile '$resources/into_outfile_2/outfile_float_2.csv'; 207 col1 col2 col3 col4 208 delete from t3; 209 import data infile '$resources/into_outfile_2/outfile_float_2.csv' into table t3 ignore 1 lines; 210 select * from t3; 211 col1 col2 col3 col4 212 1.0 1.0 1.00 1.00000 213 null null null null 214 1.23456 1.23456 1.23 1.23456 215 1.2345679 1.23456789 1.23 1.23457 216 1.0123457 1.0123456789 1.01 1.01235 217 1.0E-10 1.0E-10 0.00 0.00000 218 delete from t3; 219 import data infile '$resources/load_data/float_3.csv' into table t3; 220 internal error: the input value '1000.001' is invalid Decimal64 type for column 2 221 222 drop table t3; 223 drop table if exists t4; 224 create table t4( 225 col1 date, 226 col2 datetime, 227 col3 timestamp, 228 col4 bool 229 ); 230 import data infile '$resources/load_data/time_date_1.csv' into table t4; 231 232 select * from t4; 233 col1 col2 col3 col4 234 1000-01-01 0001-01-01 00:00:00 1970-01-01 00:00:01 false 235 9999-12-31 9999-12-31 00:00:00 2038-01-19 00:00:00 true 236 delete from t4; 237 import data infile '$resources/load_data/time_date_2.csv' into table t4; 238 select * from t4; 239 col1 col2 col3 col4 240 1000-01-01 0001-01-01 00:00:00 null false 241 1000-01-01 0001-01-01 00:00:00 null false 242 1000-01-01 0001-01-01 00:00:00 null false 243 1000-01-01 0001-01-01 00:00:00 null false 244 9999-12-31 9999-12-31 00:00:00 null true 245 9999-12-31 9999-12-31 00:00:00 null true 246 9999-12-31 9999-12-31 23:59:59 null true 247 null null null true 248 null null null true 249 null null null false 250 null null null true 251 null null null null 252 null null null null 253 select * from t4 into outfile '$resources/into_outfile_2/outfile_time_date_2.csv'; 254 col1 col2 col3 col4 255 delete from t4; 256 import data infile '$resources/into_outfile_2/outfile_time_date_2.csv' into table t4 ignore 1 lines; 257 select * from t4; 258 col1 col2 col3 col4 259 1000-01-01 0001-01-01 00:00:00 null false 260 1000-01-01 0001-01-01 00:00:00 null false 261 1000-01-01 0001-01-01 00:00:00 null false 262 1000-01-01 0001-01-01 00:00:00 null false 263 9999-12-31 9999-12-31 00:00:00 null true 264 9999-12-31 9999-12-31 00:00:00 null true 265 9999-12-31 9999-12-31 23:59:59 null true 266 null null null true 267 null null null true 268 null null null false 269 null null null true 270 null null null null 271 null null null null 272 delete from t4; 273 import data infile '$resources/load_data/time_date_3.csv' into table t4; 274 275 delete from t4; 276 import data infile '$resources/load_data/time_date_4.csv' into table t4 fields terminated by';'; 277 select * from t4; 278 col1 col2 col3 col4 279 1000-01-01 0001-01-01 00:00:00 null false 280 1000-01-01 0001-01-01 00:00:00 null false 281 9999-12-31 9999-12-31 00:00:00 null true 282 9999-12-31 9999-12-31 00:00:00 null true 283 null null null true 284 null null null true 285 null null null false 286 null null null true 287 null null null null 288 null null null null 289 delete from t4; 290 import data infile '$resources/load_data/time_date_5.csv' into table t4; 291 Data truncation: data truncated: data type DATETIME, value '9999-12-31 23:59:59.999999' for column 'col2' at row '1' 292 293 select * from t4; 294 col1 col2 col3 col4 295 delete from t4; 296 drop table t4; 297 drop table if exists t5; 298 create table t5( 299 col1 int auto_increment primary key, 300 col2 int, 301 col3 int 302 ); 303 insert into t5 values (1,1,1); 304 import data infile '$resources/load_data/auto_increment_1.csv' into table t5; 305 Duplicate entry '1' for key 'col1' 306 select * from t5; 307 col1 col2 col3 308 1 1 1 309 drop table t5; 310 drop table if exists t6; 311 create table t6( 312 col1 int auto_increment primary key, 313 col2 int, 314 col3 int 315 ); 316 import data infile '$resources/load_data/auto_increment_2.csv' into table t6; 317 Duplicate entry '4' for key 'col1' 318 select * from t6; 319 col1 col2 col3 320 drop table t6;