SQL query to automate switching WordPress from a development to a live server

One of the more tedious things about WordPress is the way that it hardcodes complete fully qualified urls into the database, which makes quickly switching from a development server to a live server an irritation. It only takes a few SQL queries to sort it out, but I’m forever forgetting then exactly. The SQL below lets you define a few constants that then get feed into the UPDATE scripts. I usually develop initially on a local web server with a .dev TLD, so with this little script I just have to set 3 SQL variables, run it and job done ready to export the database to the live server. Which is nice.

START TRANSACTION;

# CONFIGURE THESE VARIABLES FIRST
SET @DOMAIN_NAME = "the-domain-in-question";
SET @DEV_SERVER_SUFFIX = ".dev";
SET @LIVE_SERVER_SUFFIX = ".com";

# CONSTANTS (IF YOUR DEV SERVER ISN'T  MAPPED BY SUFFIX, CONFIGURE AS APPROPRIATE)
SET @WEB_SERVER_SUFFIX = "http://www.";
SET @DEV_SERVER = CONCAT ( @DOMAIN_NAME, @DEV_SERVER_SUFFIX );
SET @LIVE_SERVER = CONCAT ( @DOMAIN_NAME, @LIVE_SERVER_SUFFIX );
SET @WEB_DEV_SERVER = CONCAT ( @WEB_SERVER_SUFFIX, @DOMAIN_NAME, @DEV_SERVER_SUFFIX );
SET @WEB_LIVE_SERVER = CONCAT ( @WEB_SERVER_SUFFIX, @DOMAIN_NAME, @LIVE_SERVER_SUFFIX );

# UPDATE THE SERVER NAME
UPDATE `wp_options` SET `option_value` = @WEB_LIVE_SERVER WHERE `option_value` = @WEB_DEV_SERVER;

#UPDATE THE GUID urls
UPDATE wp_posts SET guid = REPLACE(guid, @WEB_DEV_SERVER, @WEB_LIVE_SERVER);

#UPDATE and urls referenced in the post content
UPDATE wp_posts SET post_content = REPLACE(post_content, @DEV_SERVER, @LIVE_SERVER)
WHERE post_content LIKE (
CONCAT( "%",@DEV_SERVER, "%")
);

COMMIT;