Case: I was building a CLI script to aggregate data from one MySql database and write results to another MySql database using 2 open db-connections. This way the script’s execution time was in the some-minutes-range… not good! I tried a more effective attempt: I let the CLI script output all necessary statements to drop and create all needed tables and fill them with insert-statements. I called it like this from the command line and diverted output to a file:

$ php aggregatData.php > all_sql_i_need.sql

I could then import the whole thing in one go into my target database like this:

$ mysql the_dbname -h localhost -uMarco -pPass < all_sql_i_need.sql

Insight: The execution-time was way better than my first attempt and took only seconds. I could also have combined the two lines in a shell-script.

The problem with the generated SQL statements was, that the data also contained relations between primary and foreign keys of records and that these IDs were generated at import-time by auto_increment fields. I solved this issue by using MySql variables which can be set and use d again like this:

INSERT INTO gruppen (name) VALUES ('Gruppe 1');
SET @temp_gruppe_id = LAST_INSERT_ID();
INSERT INTO gruppe_eigenschaften (id_gruppe, name, wert) VALUES
     (@temp_gruppe_id, 'email1', 'email1@emailxxx.com');

The variable @temp_gruppe_id is set and can be used in the subsequent SQL statements. It also worked in MySql4.

Here is an example of SQL statements illustrating this mechanism in the above mentioned script. You can cut-and-paste it into your PhpMyAdmin.

Case: I had many .sql-files from a backup containing one table-dump each which should be loaded into another MySql-DB.

Simply using phpMyAdmin would not have worked with regard to the size of the tables. So I was thinking about a way to do it on the commandline under Linux. A foreach-solution would have caused me to enter the MySql password for each file… which was what I wanted to avoid since the DB contained 70 tables.

Solution: I concatenated all .sql files into one and imported it into MySql in one go. Supposed your present working directory is the directory containing all .sql-files do the following:

cat *.sql >> ../complete_dump.sql;
cd ..;
mysql my_database -h mysql_host -u myuser -p < complete_dump.sql;
password: ****

And your’re done!

I am currently migrating MySQL databases. One DB uses InnoDB tables with many foreign-key constraints. In this case you can not just dump your CREATE-TABLEs and INSERTs into an empty database. This would lead to many relation violations and incomplete imports, since records are not imported in ‘the right order’ by the dump.

To prevent errors you can temporarily switch off this feature. Like this:

SET FOREIGN_KEY_CHECKS = 0;

<do your import without errors>

SET FOREIGN_KEY_CHECKS = 1;

A nice shortcut I came across to update content in a mySQL-table without writing a script to do it - which was the first thing that came to my mind…

Case: I had many HTML-snippets in a CMS table called ‘freepages’ containing hard coded domain-names in URLs to images etc. Now guess what happened as we switched the overall site domain from ‘www.mydomain.de’ to ‘info.mydomain.de’? Much of the content seemed to have gone.

This snippet was the elegant way of solving this directly in mySQL without headaches:

UPDATE freepages SET body=REPLACE(body, 'www.mydomain.de', 'info.mydomain.de');

Sweet!

I have been struggeling many times to do initial filling of databases for projects - especially if the data is a deliverable of a customer and the database structure is already fixed. So I built a VBA macro that generates SQL statements for creation of tables and data inserts from a given XLS-file. The idea is to build a ‘template’ of tables (=worksheets with fieldnames in the column headings) which have to be filled with data by the customer. As soon as this file is returned you execute the macro and voilà you have generated SQL statements which you can easily be feed into phpMyAdmin.

You can understand it as follows:

• The XLS-file represents one database with structural information and content.
• Worksheets represent the tables (their name being the tablename). This results in a CREATE TABLE statement.
• Headings (the first line, their name being the fieldname) of each worksheet for as many consecutive cells as you need represent the fields of that table. This results in the fields created in the CREATE TABLE statements.
• Lines from line 2 on downwards until the first empty line is met will be converted to INSERT statements, INSERTS INTO the fields of each cell’s column.

Puzzled? Check out the examples, they should be self explanatory:

VBA macro source

Excel file containing example ‘tables’/worksheets to be exported to SQL and the macro

Resulting SQL saved to C:\exported.sql

The VBA snippet has to be added as a ‘module’ in the Excel VBA editor in the XLS-file holding your structure and data and can be saved with it as a macro. Triggering the macro will then export your SQL statements. You can see this from the example XLS-file above.