mysql: Create external table using CSV engine and file


sqlexpertz

One thing that the CSV engine allows for is the instantaneous data transfer from flat files to information accessible via SQL in MySQL. For example, imagine having the need to load a file containing 5 million records into a MySQL table that uses the MyISAM storage engine:

mysql> desc client_detail; +-----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------+------+-----+---------+-------+ | client_transaction_id | decimal(22,0) | YES | | NULL | | | transaction_timestamp | datetime | YES | | NULL | | | transaction_comment | varchar(30) | YES | | NULL | | +-----------------------+---------------+------+-----+---------+-------+ 3 rows in set (0.02 sec) mysql> load data infile '/usr/local/mysql519/data/gim/flatdata.dat' -> into table client_detail -> fields terminated by ','; Query OK, 5023575 rows affected, 0 warnings (27.38 sec) Records: 5023575 Deleted: 0 Skipped: 0 Warnings:…

While MyISAM accepts the data pretty quickly, you can make all the data in the…

View original post 234 more words