MySQL load data

MySQL load data 예제

---------- aaa.txt -------------

100004,3611509,91643,"홍길동"

1000123,3,0,"이순신"

100022,996705,26469,"장보고"

100059,28456594,736957,"김유신"

100103,0,1,"최영"

100176,30570326,503211,"이성계"

100202,53,3,"유재석"

1002239,1,0,"박명수"

1002275,6,0,"하하"

100257,4031,117,"광희"

--------------------------------

CREATE TABLE `bbb` (

  `col1` int(11) DEFAULT NULL,

  `col2` bigint(20) DEFAULT NULL,

  `col3` int(11) DEFAULT NULL,

  `col4` varchar(20) DEFAULT NULL,

  `col5` datetime DEFAULT NULL

) ;

load data local infile 'aaa.txt' 

into TABLE bbb

FIELDS TERMINATED BY ',' 

ENCLOSED BY '"' 

LINES TERMINATED BY '\n' 

(col1, col2, col3, col4, @col5) set col5=date_format(now(),'%Y%m%d%H%i%s');

MariaDB [MP_POC]> select * from bbb;

+---------+----------+--------+-----------+---------------------+

| col1    | col2     | col3   | col4      | col5                |

+---------+----------+--------+-----------+---------------------+

|  100004 |  3611509 |  91643 | 홍길동    | 2016-10-27 11:10:02 |

| 1000123 |        3 |      0 | 이순신    | 2016-10-27 11:10:02 |

|  100022 |   996705 |  26469 | 장보고    | 2016-10-27 11:10:02 |

|  100059 | 28456594 | 736957 | 김유신    | 2016-10-27 11:10:02 |

|  100103 |        0 |      1 | 최영      | 2016-10-27 11:10:02 |

|  100176 | 30570326 | 503211 | 이성계    | 2016-10-27 11:10:02 |

|  100202 |       53 |      3 | 유재석    | 2016-10-27 11:10:02 |

| 1002239 |        1 |      0 | 박명수    | 2016-10-27 11:10:02 |

| 1002275 |        6 |      0 | 하하      | 2016-10-27 11:10:02 |

|  100257 |     4031 |    117 | 광희      | 2016-10-27 11:10:02 |

+---------+----------+--------+-----------+---------------------+

10 rows in set (0.00 sec)

insert into bbb values (1,2,3,"aaa",date_format('2016-01_01','%y%m%d'));

MariaDB [MP_POC]> select * from bbb;

+---------+----------+--------+-----------+---------------------+

| col1    | col2     | col3   | col4      | col5                |

+---------+----------+--------+-----------+---------------------+

|  100004 |  3611509 |  91643 | 홍길동    | 2016-10-27 11:10:02 |

| 1000123 |        3 |      0 | 이순신    | 2016-10-27 11:10:02 |

|  100022 |   996705 |  26469 | 장보고    | 2016-10-27 11:10:02 |

|  100059 | 28456594 | 736957 | 김유신    | 2016-10-27 11:10:02 |

|  100103 |        0 |      1 | 최영      | 2016-10-27 11:10:02 |

|  100176 | 30570326 | 503211 | 이성계    | 2016-10-27 11:10:02 |

|  100202 |       53 |      3 | 유재석    | 2016-10-27 11:10:02 |

| 1002239 |        1 |      0 | 박명수    | 2016-10-27 11:10:02 |

| 1002275 |        6 |      0 | 하하      | 2016-10-27 11:10:02 |

|  100257 |     4031 |    117 | 광희      | 2016-10-27 11:10:02 |

|       1 |        2 |      3 | aaa       | 2016-01-01 00:00:00 |

+---------+----------+--------+-----------+---------------------+

11 rows in set (0.00 sec)