Ever have a several gig MySQL dump and only need to extract a table or two out of it? Here’s something you can run from the command line that will split all of the tables into individual files. You’ll need to know if your dump file has the CREATE TABLE format included or is just the data. If it’s just the data then you can change where it says CREATE TABLE to LOCK TABLES below.

One thing to note. If awk ends up giving you an error such as “Program Limit Exceeded”, then you can use gawk instead and it should work without issues.

[sourcecode language="bash"]
cat dumpfile.sql | awk ‘BEGIN {
output = "comments"; } $data ~ /^CREATE TABLE/ {
close(output);
output = substr($3,2,length($3)-2); }
{ print $data >> output }’
[/sourcecode]

Recently was asked a question on how to delete records from multiple tables with a single MySQL query. I’ll give an example below with one caveat. If your tables are INNODB and you have foreign keys then this query will fail. For INNODB you should just do a single delete and rely on the ON DELETE capabilities to handle any other deletes.

Anyways, so on to the query. Say you have 3 tables (table1, table2, and table3). Each of them have an entry for a variable that we’ll call $id and you want to delete from all 3 tables at once. Here you go:

[bash]
delete t1, t2, t3
from table1 as t1
left join table2 as t2 on t2.id = t1.id
left join table3 as t3 on t3.id = t1.id
where t1.id = ‘$id’
[/bash]

This error or the equally frustrating ‘ Incorrect integer value: ‘ error seems to bite quite a few people in the butt that are running MySQL 5. If you’re getting one of these errors then MySQL is running in strict mode and you are trying to run a query that isn’t inserting a proper value into one of your fields. For example, you have an int field and the value you are inserting into that field is blank.

The proper thing to do would be to go through all of your queries and make sure you are always inserting the proper values, ie. some type of integer for an int field.

There isn’t always time for the proper way however. If you need a quick fix you can run the following MySQL command from your favorite MySQL command prompt, the shell, phpmyadmin, etc.

[bash]
SET GLOBAL SQL_MODE=”
[/bash]