Creates a column full of 301 redirects from published Wordpress post based on permalink.

wp2redirect.sql

--
-- Wordpress perma to 301 redirect 
--
-- This script will output a column called rewrite_rule with a set of 301 redirect rules for an .htaccess file.
-- 

SET @table_prefix = "wp_";
SET @url_base_target = "http://www.soemthing.new/blog";

SET @generateSql = CONCAT("
  SELECT
    wpp.guid,
    wpp.post_date,
    CONCAT(wpo_su.option_value, 
      REPLACE(
        REPLACE(
          REPLACE(
            REPLACE(wpo.option_value, 
              '%year%', DATE_FORMAT(wpp.post_date, '%Y')), 
              '%monthnum%', DATE_FORMAT(wpp.post_date, '%m')), 
              '%day%', DATE_FORMAT(wpp.post_date, '%d')), 
              '%postname%', wpp.post_name)
    ) AS permalink,
    CONCAT('", @url_base_target, "', 
      REPLACE(
        REPLACE(
          REPLACE(
            REPLACE(wpo.option_value, 
              '%year%', DATE_FORMAT(wpp.post_date, '%Y')), 
              '%monthnum%', DATE_FORMAT(wpp.post_date, '%m')), 
              '%day%', DATE_FORMAT(wpp.post_date, '%d')), 
              '%postname%', wpp.post_name)
    ) AS target,
    CONCAT(
      'RewriteCond %{HTTP_HOST} ^',
      REPLACE(
        REPLACE(
          REPLACE(wpo_su.option_value, 
            'https://', ''), 
            'http://', ''),
            '.', '\.'),
      '$ [NC]\n',
      'RewriteCond %{QUERY_STRING}  ^$\n',
      'RewriteRule ^',
      REPLACE(
        REPLACE(
          REPLACE(
            REPLACE(wpo.option_value, 
              '%year%', DATE_FORMAT(wpp.post_date, '%Y')), 
              '%monthnum%', DATE_FORMAT(wpp.post_date, '%m')), 
              '%day%', DATE_FORMAT(wpp.post_date, '%d')), 
              '%postname%', wpp.post_name),
      '$ ', 
      '", @url_base_target, "',
      REPLACE(
        REPLACE(
          REPLACE(
            REPLACE(wpo.option_value, 
              '%year%', DATE_FORMAT(wpp.post_date, '%Y')), 
              '%monthnum%', DATE_FORMAT(wpp.post_date, '%m')), 
              '%day%', DATE_FORMAT(wpp.post_date, '%d')), 
              '%postname%', wpp.post_name),
      '? [R=301,NE,NC,L]\n'
    ) AS rewrite_rule
  FROM ", @table_prefix, "_posts wpp
    INNER JOIN ", @table_prefix, "_options wpo
      ON wpo.option_name = 'permalink_structure'
    INNER JOIN ", @table_prefix, "_options wpo_su
      ON wpo_su.option_name = 'siteurl'
  WHERE wpp.post_type = 'post' AND wpp.post_status = 'publish'
  ORDER BY wpp.post_date DESC" 
);

PREPARE outputStatement FROM @generateSql;

EXECUTE outputStatement;

DEALLOCATE PREPARE outputStatement;

An example of the output in the rewrite_rule column:

RewriteCond %{HTTP_HOST} ^blog.olddomain.example$ [NC]
RewriteCond %{QUERY_STRING}  ^$
RewriteRule ^/2013/10/22/justins-contextual-google-glass-development/$ http://www.mynewblog.example/blog/2013/10/22/justins-contextual-google-glass-development/? [R=301,NE,NC,L]