posted by の部分だけ抜き出して別のカラムに登録

gistfile1.sql

# ------------------- #
# 空のカラムをつくる
# ------------------- #
ALTER TABLE
  `wp_posts`
ADD
  `magazine-k-author` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;
# ------------------- #
# 置換する
# （SUBSTRING_INDEX は MySQL 独自の関数）
# ------------------- #
UPDATE
  `wp_posts`
SET
  `magazine-k-authorbefore`= SUBSTRING_INDEX( `post_content`, 'posted by ', 1 ),
  `magazine-k-author`= SUBSTRING_INDEX( `post_content`, 'posted by ', -1 ),
  `magazine-k-author`= SUBSTRING_INDEX( `magazine-k-author`, '\n', 1 ),
  `magazine-k-author`= SUBSTRING_INDEX( `magazine-k-author`, '</p>', 1 )
WHERE
  `post_content` LIKE '%posted by%\n%' AND `post_type` = 'post';
# ------------------- #
# CSV で保存
# ------------------- # 
SELECT `magazine-k-author` FROM `wp_posts`
  INTO OUTFILE '/tmp/users.csv'
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"';