How to drop multiple tables in mysql at once

There are a couple of different ways to remove multiple tables from MySQL at one time.  Almost all of the ways of dropping many mysql tables at once are dangerous and that's probably why the MySQL developers have overlooked this "feature."  You run the risk of dropping tables outside of the ones you meant to drop.

But if you're dead set on this, here's a stored procedure for dropping multiple MySQL tables at one time that does the job quick and easy.

First you have to add the procedure to the schema.  You can technically add this to any schema that you have access to.  The way I use it is from a "global" schema that needs to make changes to other schemas that all have similar names.  The MySQL user calling the procedure needs to be able to perform the DROP as well as READ the information schema tables.  Don't mess with your permissions unless you have to however.

-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE PROCEDURE `drop_tables`( db varchar( 100 ), pattern varchar(100))
BEGIN
  SELECT @str_sql:=concat('drop table ', group_concat( concat(db, '.', table_name)) )
 FROM information_schema.tables
 WHERE table_schema = db AND table_name LIKE pattern;

 PREPARE stmt FROM @str_sql;
 EXECUTE stmt;
END

Once the procedure is set up, to test it out, you can use the test_tables.sql file which adds a bunch of tables to your schema.

Calling the procedure can be done with:

call drop_tables( 'test', 'jjj%' );

So in this case, I'm dropping all the tables that start with "jjj" in the "test" schema.  The "pattern" parameter can be any valid MySQL "LIKE" parameter.

In a MySQL client, it looks like this:

mysql> call drop_tables( 'test', 'jjj%' );
+------------------------------------------------------------------------------+
| @str_sql:=concat('drop table ', group_concat( concat(db, '.', table_name)) ) |
+------------------------------------------------------------------------------+
| drop table test.jjj_test1,test.jjj_test2,test.jjj_test3,test.jjj_test4       |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)


Query OK, 0 rows affected (0.21 sec)


Now, obviously, if you put in a pattern that returns no resulting table names, then you'll get an error.  But fixing that error is left to the reader as an exercise ( do feel free to contribute that back to me!)

Any questions or comments?  Do you have a better way to do this?

Explode a string with no delimiter in PHP

I needed a way to split a string by the character and put the results in an array.  Sounds like the perfect job for the explode function right?  Well, according to the manual:

If delimiter is an empty string (""), explode() will return FALSE


That's a bummer.  


UPDATE: I also tried str_split('whatever') but that has some problems with foreign strings.


But we can do something like this:


$str = "Let's split this string!";
// get the string length so we aren't paying for this in the loop
$strLen = strlen( $str );
      
// basically an "explode" on the string without needing delimiters
for ( $i = 0; $i < $strLen; $i++ )
{
    $arr[] = $str{$i};
}


print_r( $arr );

Enjoy!  Let me know what other ways you can come up with to do something similar.

SQLSTATE[HY000]: General error: could not call class constructor

SQLSTATE[HY000]: General error: could not call class constructor

This rather vague error message has been popping up in some of my unit tests lately.  It's a PHP error in PDO querying MySQL that happens when you're using the setFetchMode method of the PDOStatement.  If you use the fetchMode of "FETCH_CLASS" you can get this error in one of three ways:

• The class you specified has not been included/required so when PDO gets results, it cannot create an object of the class you specified since it doesn't have that class definition.
• Properties on the class you specified have been marked as protected or private and there aren't any setters for them so PDO can't set the properties on the new object.
• You used the optional third argument "ctorargs" to give constructor arguments on your new object however your arguments are incorrect and don't match those on the class.

Hope this helps other poor souls out there trying to figure this error out.

"show tables not like" in mysql

I was looking for the tables in my database not starting with "p_".  MySQL doesn't let you execute this command:

show tables not like "p_%";

Which is odd considering it will allow "like" here:

show tables like "p_%";

Instead, you have to use the information schema like this:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'mydb' AND table_name NOT LIKE 'p_%';