After completing development of one of my apps, I had to load one of my MySQL tables with data. After putting the data in CSV format, I wanted to upload it to my MySQL table. I searched the web and found the syntax to use (I use MySQL workbench and from there I went into the database and created and executed a new query). However, it took awhile for me to figure out how to make the file name work.
Here’s the syntax I used in MySQL Workbench for the query to execute (replace the text in colors with your data):
load data local infile ‘C://yourdirectory/yourdirectory2/filename.csv‘ into table your_table_name fields terminated by ‘,’
enclosed by ‘”‘
lines terminated by ‘\n’
(field1, field2, field3, field4, field5)
(for some reason the characters don’t copy correctly from this post, so you can copy similar syntax from here)
I had some bad data in my DB where fields had a space or some other value I could not see, so I needed to set the field to null (my program looks for null). To do this, I changed the value of the field, then clicked “Apply” and on the SQL preview screen, I changed the value to NULL (ie city = NULL where…).
My latest tool is the MySQL Workbench – a free tool that allows you to manage your MySQL databases. Previously I was using PHPMyAdmin, but MySQL Workbench seems to run a lot faster. I’m using it for managing both my local databases, as well as my remote databases.
When I first downloaded the app, I was double clicking on the MySQL application which opens a shell and the closes it right away. I didn’t see the MySQL Workbench application which was in the same zip folder – this is the one to launch the app.
I’ve found that PHPMyAdmin was initially easier to understand because all of the links are on the screen, however that meant more screens. In MySQL workbench, you can get to the same screens from the main menu and right clicking on the table (or the submenus of the table in the menu).
If I need to manually add rows to a table:
PHPMyAdmin – Click on Insert for the table and fields for up to two rows appear, if I want to add more, I click the go button, the entries save, new page loads
MySQL Workbench – right click on the table, select “Edit Table Data”, skip to the last row and enter your data, then click enter and the next row appears – no pages reloads
PHPMyAdmin – go to table, next screen click browse, find your entry, click icon to edit, change field in page that loads, then save (and another page reload)
MySQL Workbench – right click on the table, select “Edit Table Data”, find your record and double click your data field, change field value, then click the Apply button when you’re done.
Those are just a couple of examples. I think that a lot of the reason for speed differences is that MySQL Workbench is a standalone app, while PHPMyAdmin uses a browser and doesn’t incorporate as much Java for asynchronous changes (therefore the page reloads). The other downer is that if you don’t have PHP installed on your PC, you need to do so t
PHPMyAdmin is also a great tool, but for now I’m going to try switching to MySQL Workbench.