I was investigating the CSV storage engine for mysql, having never used it. I found out, much to my relief, that it’s a relatively new thing — went standard with 5.1, but certainly not in the kits of luddites like me who have to stay four or five versions back because we can’t afford downtime caused by unexpected glitches with new releases.
The CSV engine is as I expected: It allows MySQL to directly access CSV files. There are probably boocoo performance issues and I don’t know how easy it is to later convert a table with its engine declared as CSV to something more standard such as MyISAM.
I think the big plus of the CSV engine is that you can quickly and easily leverage your existing CSV files from excel, as long as you are not looking for excellence in relational, normalized database design. Having been out in the trenches, I would guesstimate that 98 percent of MySQL users would not know normalized form if it bit them in the butt and they still manage to get work done. The CSV engine would probably give you a speed advantage for quick and dirty projects but I don’t think it is a great idea for a long standing, constantly updated database.
This article even gives you precise instructions how to give MySQL direct access to an existing csv file without going through a lengthy import process.
In a nutshell, you issue a matching CREATE statement from inside MySQL, then you go to the filesystem item that mysql created for the data component and mv your existing file over the location and name of the empty csv that MySQL created to house the data.
This is out of band management: i.e. going beneath the management tools in the mysql clients. They don’t say what happens if you goof up and forget or mispell a field, and that is the caveat to any out of band mucking about outside a management system.
But still, it might be a useful idea to file away if you have a big CSV on the same or very fast access filesystem and waiting for an import would delay something critical.
I have found SQLYog to be the best of all MySQL clients. Unfortunately it doesn’t have a native Mac version but it runs perfectly in Wine.