|MySQL Data Load 2|
Review: MySQL database second data import and load tests
In the first review of MySQL database loading and then testing of its MySQL Migration toolkit, I found MySQL to be fairly versatile in loading in the numerically oriented Baseball database. But now the Wordweb database poses a more sophisticated challenge with CLOB fields having embedded punctuation such as comma and single quote - ' and double quote - ". These embedded characters become a problem when a CLOB field is followed by another field because then for example, a comma embedded in the CLOB field can be misinterpreted as the field delimiter. But if you use either single quote or double quote as the enclosed by delimiter - goes awry for about 200-300 of the rows which contain those characters embedded as part of the CLOB field.
Fortunately MySQL's LOAD DATA INFILE command is more than equal to the task providing for just about any character as the FIELDS ENCLOSED BY '`' (a backquote character is being used here) allows. This means then that the onus is to get a dump of the datafile with fields enclosed by backquote or tilde. Or one has to replace the current delimiter, say single quote, with a backquote or tilde using an adept editor like Notepad++. Fortunately, we did not have to do this because all the the Wordweb tables had their CLOBS as the last field and LOAD DATA INFILE was smart enough to pickup all the data to the end of line even when there were embedded commas. The following query shows that:
However, it is welcome to know that the LOAD DATA INFILE command has available not just the FIELD ENCLOSED BY clause but also a FIELD ESCAPED BY clause as well. So for example, I used backslash as an escape character for commas when creating a copy of an Access NUTRITION database . The result was that the LOAD DATA INFILE command was able to read the NUTRITION database with no problems. Since the MYIMPORT command line tool uses LOAD DATA INFILE, I assume it is equally adept at reading in data.
In general I found like DB2, MySQL's data import commands are very solid. But I wondered how good third party tools like SQLyog which are very impressive elsewhere for MySQL admin and querying would do. Well I should have guessed that third party toolmakers would take advantage of the strong MySQL data loaders:
So MySQL gets top marks along with DB2 for ease of data loading and importing. MySQL's loader was one of the fastest I observed - admittedly in no tests were the number of records in a table to be loaded more than a half million rows. In contrast, the MySQL Migration utility was a bit disappointing because it hiccupped on some datatypes and simply appears not yet ready to pickup constraints, triggers, keys, and stored procedures from other databases in a clean and thorough import process. So give good marks to MySQL but also look to improvement in the Migration Toolkit.
Top of Page Home Tutorials Developers Weblog Graphics Reviews and Tips