For a migration process I wanted to build in extra validation in some destructive MySQL queries, to eliminate the risk that data might be lost. Of course all data is back-upped, but I rather be safe then sorry.
This is fairly straight forward, create a stored procedure and perform an ALTER statement whenever the previously defined criteria have been met. But I wanted to define a single procedure rather then create one for every table I had to drop columns from. And since you can't simply use variables for column/table names, you have to improvise a little. I came up with the following:
-- Defining the "drop empty column" SP DELIMITER @@ DROP PROCEDURE IFEXISTS drop_empty_column @@ CREATE PROCEDURE drop_empty_column( IN itable VARCHAR(50), IN icolumn VARCHAR(50), OUT succeeded INT ) BEGIN
SET @amount = -1; SET @itable = itable; SET @icolumn = icolumn;
-- Build the query, with dynamic table and column. Store the result in @amount SELECT CONCAT('SELECT COUNT(', @icolumn ,') INTO @amount FROM ', @itable ,' WHERE(', @icolumn ,' IS NOT NULL OR ', @icolumn ,' != "");')INTO @testSql; PREPARE testSqlStmt FROM @testSql;
-- Execute EXECUTE testSqlStmt;
-- Check if we have 0 rows, else we still have data and we can't drop. IF @amount = 0 THEN
-- Build the query SELECT CONCAT('ALTER TABLE ', @itable ,' DROP COLUMN ', @icolumn ,';')INTO @alterSql;
-- Execute PREPARE alterSqlStmt FROM @alterSql; EXECUTE alterSqlStmt;
-- Update the status SELECT1INTO succeeded; ELSE SELECT0INTO succeeded; END IF;
END@@ DELIMITER ;
-- Conditionally drop the column "deprecated_column" from table "some_table" CALL drop_empty_column('some_table', 'deprecated_column', @succeeded);
-- Show the status SELECT @succeeded;
-- Cleanup DROP PROCEDURE IFEXISTS drop_empty_column;
I'm fairly positive that this could be done in a much better way then that I'm doing here, but this works too. Simply repeat the CALL drop_empty_column(..) for every table/column combination you want to DROP and update the definition of an "empty column" to what you want. Currently it drops the column only if the values contain nothing other then NULL or "" (empty string) values.
Optimizing MySQL always has been a hot topic for many developers and DBA's. I always took a lot for granted and never really bothered with optimizing MySQL other then trying to make the best query's I could. Over the years I've learned a lot more about MySQL and so my journey went into trying to optimize my MySQL server(s). After a lot of trial and error on development machines, it's time for the real deal The first thing to do is use your favorite search engine and learn the basics, you'll mostly find these phrases: 'good queries', key_buffer_size (or key_buffer), Key_read_requests and Key_reads and memory.
I'm going to talk mostly about MySQL 5 and MySQL 5.1 database tuning. The basics apply to all version but in 5 and 5.1 there are additional parameters not available in previous versions. I'm asuming you are familair with terms as: web servers, indexes, MyISAM, InnoDB, tables, databases, Linux file system (most I talk about applies to windows builds also)